SequelizeA multi-dialect Object-Relational-Mapper for Node.JS

To get the ball rollin' you first have to create an instance of Sequelize. Use it the following way:

var sequelize = new Sequelize('database', 'username'[, 'password'])

Sequelize currently supports the following datatypes:

Sequelize.STRING  ===> VARCHAR(255)
Sequelize.TEXT    ===> TEXT
Sequelize.INTEGER ===> INTEGER
Sequelize.DATE    ===> DATETIME
Sequelize.BOOLEAN ===> TINYINT(1)
Sequelize.FLOAT   ===> FLOAT

In v1.3.0 model validations have been added. They allow you to specify format/content/inheritance validations for each attribute of the model. You can perform the validation by calling the validate() method on an instance before saving.

var ValidateMe = sequelize.define('Foo', {
  foo: {
    type: Sequelize.STRING,
    validate: {
      is: ["[a-z]",'i'],        // will only allow letters
      not: ["[a-z]",'i'],       // will not allow letters
      isEmail: true,            // checks for email format (foo@bar.com)
      isUrl: true,              // checks for url format (http://foo.com)
      isIP: true,               // checks for IPv4 format (129.89.23.1)
      isAlpha: true,            // will only allow letters
      isAlphanumeric: true      // will only allow alphanumeric characters, so "_abc" will fail
      isNumeric: true           // will only allow numbers
      isInt: true,              // checks for valid integers
      isFloat: true,            // checks for valid floating point numbers
      isDecimal: true,          // checks for any numbers
      isLowercase: true,        // checks for lowercase
      isUppercase: true,        // checks for uppercase
      notNull: true,            // won't allow null
      isNull: true,             // only allows null
      notEmpty: true,           // don't allow empty strings
      equals: 'specific value', // only allow a specific value
      contains: 'foo',          // force specific substrings
      notIn: 'foo',             // force specific substrings
      isIn: "foo",              // force specific substrings
      notContains: 'bar',       // don't allow specific substrings
      len: [2,10],              // only allow values with length between 2 and 10
      isUUID: 4,                // only allow uuids
      isDate: true,             // only allow date strings
      isAfter: "2011-11-05",    // only allow date strings after a specific date
      isBefore: "2011-11-05",   // only allow date strings before a specific date
      max: 23,                  // only allow values <= 23
      min: 23,                  // only allow values >= 23
      isArray: true,            // only allow arrays
      isCreditCard: true,       // check for valid credit card numbers
 
      // custom validations are also possible:
      isEven: function(value) {
        if(parseInt(value) % 2 != 0)
          throw new Error('Only even values are allowed!')
      }
    }
  }
})

You can also take influence of the way, Sequelize handles your column names:

var Bar = sequelize.define('Bar', { /* bla */ }, {
  // don't add the timestamp attributes (updatedAt, createdAt)
  timestamps: false,
  // don't delete database entries but set the newly added attribute deletedAt
  // to the current date (when deletion was done). paranoid will only work if
  // timestamps are not disabled
  paranoid: true,
  // don't use camelcase for automatically added attributes but underscore style
  // so updatedAt will be updated_at
  underscored: true,
  // disable the modification of tablenames; By default, sequelize will automatically
  // transform all passed model names (first parameter of define) into plural.
  // if you don't want that, set the following
  freezeTableName: true
})

You can also store your model definitions in a single file using the import-method. The returned object is exactly the same as defined in the imported file's function.

// in your server file - e.g. app.js
var Project = sequelize.import(__dirname + "/path/to/models/project")
 
// /path/to/models/project.js
// as you might notice, the DataTypes are the very same as explained above
module.exports = function(sequelize, DataTypes) {
  return sequelize.define("Project", {
    name: DataTypes.STRING,
    description: DataTypes.TEXT
  })
}

Synchronization with database

When starting a new project you won't have a database structure and using Sequelize you won't need to. Just specify your model structures and let the library do the rest. Currently supported is the creation and deletion of tables:

// create my nice tables:
Project.sync() // will emit success or failure event
Task.sync() // will emit success or failure event
 
// force the creation!
Project.sync({force: true}) // this will drop the table first and re-create it afterwards
 
// drop the tables:
Project.drop() // will emit success or failure event
Task.drop() // will emit success or failure event
 
// event handling:
Project.[sync|drop]().success(function() {
  // ok ... everything is nice!
}).error(function(error) {
  // oooh, did you entered wrong database credentials?
})

Because synchronizing and dropping all of your tables might be a lot of lines to write, you can also let Sequelize do the work for you:

// create all tables... now!
sequelize.sync() // will emit success or failure
 
// force it!
sequelize.sync({force: true}) // emit ... nomnomnom
 
// want to drop 'em all?
sequelize.drop() // I guess you've got it (emit)
 
// emit handling:
sequelize.[sync|drop]().success(function() {
  // woot woot
}).error(function(error) {
  // whooops
})

Expansion of models

Sequelize allows you to pass custom methods to a model and it's instances. Just do the following:

var Foo = sequelize.define('Foo', { /* attributes */}, {
  classMethods: {
    method1: function(){ return 'smth' }
  },
  instanceMethods: {
    method2: function() { return 'foo' }
  }
})
 
// ==>
 
Foo.method1()
Foo.build().method2()

Finders

Finder methods are designed to get data from the database. The returned data isn't just a plain object, but instances of one of the defined classes. Check the next major chapter about instances for further information. But as those things are instances, you can e.g. use the just describe expanded instance methods. So, here is what you can do:

// search for known ids
Project.find(123).success(function(project) {
  // project will be an instance of Project and stores the content of the table entry
  // with id 123. if such an entry is not defined you will get null
})
 
// search for attributes
Project.find({ where: {title: 'aProject'} }).success(function(project) {
  // project will be the first entry of the Projects table with the title 'aProject' || null
})
 
// since v1.3.0: only select some attributes and rename one
Project.find({
  where: {title: 'aProject'},
  attributes: ['id', ['name', 'title']]
}).success(function(project) {
  // project will be the first entry of the Projects table with the title 'aProject' || null
  // project.title will contain the name of the project
})
 
// find multiple entries
Project.findAll().success(function(projects) {
  // projects will be an array of all Project instances
})
 
// also possible:
Project.all().success(function(projects) {
  // projects will be an array of all Project instances
})
 
// search for specific attributes - hash usage
Project.findAll({where: {name: 'A Project'}}).success(function(projects) {
  // projects will be an array of Project instances with the specified name
})
 
// search with string replacements
Project.findAll({where: ["id > ?", 25]}).success(function(projects) {
  // projects will be an array of Projects having a greater id than 25
})
 
// search within a specific range
Project.findAll({where: { id: [1,2,3] }}).success(function(projects) {
  // projects will be an array of Projects having the id 1, 2 or 3
  // this is actually doing an IN query
})
 
// or
Project.findAll({where: "name = 'A Project'"}).success(function(projects) {
  // the difference between this and the usage of hashes (objects) is, that string usage
  // is not sql injection safe. so make sure you know what you are doing!
})

Of course you can pass a some options to the finder methods, to get more relevant data:

// define the order of the queried data
Project.findAll({order: 'title DESC'})
 
// limit the results of the query
Project.findAll({limit: 10})
 
// step over some elements
// this only works with a specified limit
Project.findAll({offset: 10, limit: 2})

There is also a method for counting database objects:

Project.count().success(function(c) {
  console.log("There are " + c + " projects!")
})
 
Project.count({where: ["id > ?", 25]}).success(function(c) {
  console.log("There are " + c + " projects with an id greater than 25.")
})

And here is a method for getting the min and the max value of an attribute:

/*
  Let's assume 3 person objects with an attribute age.
  The first one is 10 years old,
  the second one is 5 years old,
  the third one is 40 years old.
*/
Project.min('age').success(function(min) {
  // this will return 5
})
Project.max('age').success(function(max) {
  // this will return 40
})

Building an instance

In order to create instances of defined classes just do as follows. You might recognize the syntax if you coded Ruby in the past. Using the build-method will return an unsaved object, which you explicitly have to save.

var project = Project.build({
  title: 'my awesome project',
  description: 'woot woot. this will make me a rich man'
})
 
var task = Task.build({
  title: 'specify the project idea',
  description: 'bla',
  deadline: new Date()
})

Built instances will automatically get default values when they were defined:

// first define the model
var Task = sequelize.define('Project', {
  title: Sequelize.STRING,
  rating: { type: Sequelize.STRING, defaultValue: 3 }
})
 
// now instantiate an object
var task = Task.build({title: 'very important task'})
 
task.title // ==> 'very important task'
task.rating // ==> 3

To get it stored in the database, use the save-method and catch the events … if needed:

project.save().success(function() {
  // my nice callback stuff
})
 
task.save().error(function(error) {
  // mhhh, wth!
})
 
// you can also build, save and access the object with chaining:
Task
  .build({ title: 'foo', description: 'bar', deadline: new Date() })
  .save()
  .success(function(anotherTask) {
    // you can now access the currently saved task with the variable anotherTask... nice!
  })

Creating an instance

Besides constructing objects, that needs an explicit save call to get stored in the database, there is also the possibility to do all those steps with one single command. It's called create.

Task.create({ title: 'foo', description: 'bar', deadline: new Date() }).success(function(task) {
  // you can now access the newly created task via the variable task
})

Updating / Saving an instance

Now lets change some values and save changes to the database... There are two ways to do that:

// way 1
task.title = 'a very different title now'
task.save().success(function() {})
 
// way 2
task.updateAttributes({
  title: 'a very different title now'
}).success(function() {})

Configuration

In order to define the encoding of a table you can do this:

// charset only
var Person = sequelize.define('Person', { /* attributes */ }, {
  charset: 'latin1'
})
 
// charset + collate; this is still experimental
var Person = sequelize.define('Person', { /* attributes */ }, {
  charset: 'utf8',
  collate: 'utf8_general_ci'
})
 
// or of course global
var sequelize = new Sequelize(db, user, pw, {
  define: { charset: 'latin1' }
})

You can also change the database engine, e.g. to MyISAM. InnoDB is default since v1.2.1.

// charset only
var Person = sequelize.define('Person', { /* attributes */ }, {
  engine: 'MYISAM'
})
 
// or of course global
var sequelize = new Sequelize(db, user, pw, {
  define: { engine: 'MYISAM' }
})

One-To-One associations

One-To-One associations are connecting one source with exactly one target. In order to define a proper database schema, Sequelize utilizes the methods belongsTo and hasOne. You can use them as follows:

var User = sequelize.define('User', {/* ... */})
var Project = sequelize.define('Project', {/* ... */})
 
// One-way associations
Project.hasOne(User)
 
/*
  In this example hasOne will add an attribute ProjectId to the User model!
  Furthermore, Project.prototype will gain the methods getUser and setUser according
  to the first parameter passed to define. If you have underscore style
  enabled, the added attribute will be project_id instead of ProjectId.
 
  You can also define the foreign key, e.g. if you already have an existing
  database and want to work on it:
*/
 
Project.hasOne(User, { foreignKey: 'initiator_id' })
 
/*
  Because Sequelize will use the model's name (first parameter of define) for
  the accessor methods, it is also possible to pass a special option to hasOne:
*/
 
Project.hasOne(User, { as: 'Initiator' })
// Now you will get Project#getInitiator and Project#setInitiator
 
// Or let's define some self references
var Person = sequelize.define('Person', { /* ... */})
 
Person.hasOne(Person, {as: 'Father'})
// this will add the attribute FatherId to Person
 
// also possible:
Person.hasOne(Person, {as: 'Father', foreignKey: 'DadId'})
// this will add the attribute DadId to Person
 
// In both cases you will be able to do:
Person#setFather
Person#getFather
 
// Since v1.3.0 you can also chain associations:
Project
  .hasOne(User)
  .hasOne(Deadline)
  .hasOne(Attachment)

To get the association working the other way around (so from User to Project), it's necessary to do this:

var User = sequelize.define('User', {/* ... */})
var Project = sequelize.define('Project', {/* ... */})
 
// One-way back associations
Project.belongsTo(User)
 
/*
  In this example belongsTo will add an attribute UserId to the Project model!
  That's the only difference to hasMany. Self references are working the very same way!
*/

One-To-Many associations

One-To-Many associations are connecting one source with multiple targets. The targets however are again connected to exactly one specific source.

var User = sequelize.define('User', {/* ... */})
var Project = sequelize.define('Project', {/* ... */})
 
// OK. Now things get more complicated (not really visible to the user :)).
// First let's define a hasMany association
Project.hasMany(User, {as: 'Workers'})
 
/*
  This will add the attribute ProjectId or project_id to User.
  Instances of Project will get the accessors getWorkers and setWorkers.
 
  We could just leave it the way it is and let it be a one-way association.
  But we want more! Let's define the other way around:
*/

Many-To-Many associations

Many-To-Many associations are connecting sources with multiple targets. Furthermore the targets can also have connections to multiple sources.

// again the Project association to User
Project.hasMany(User, { as: 'Workers' })
 
// now comes the association between User and Project
User.hasMany(Project)
 
/*
  This will remove the attribute ProjectId (or project_id) from User and create
  a new model called ProjectsUsers with the equivalent foreign keys ProjectId
  (or project_id) and UserId (or user_id). If the attributes are camelcase or
  not depends on the Model it represents.
 
  Now you can use Project#getWorkers, Project#setWorkers, User#getTasks and
  User#setTasks.
*/
 
// Of course you can also define self references with hasMany:
 
Person.hasMany(Person, { as: 'Children' })
// This will create the table ChildrenPersons which stores the ids of the objects.

Project.hasMany(Task)
Task.hasMany(Project)
 
Project.create()...
Task.create()...
Task.create()...
 
// save them... and then:
project.setTasks([task1, task2]).success(function() {
  // saved!
})
 
// ok now they are save... how do I get them later on?
project.getTasks().success(function(associatedTasks) {
  // associatedTasks is an array of tasks
})

// remove the association with task1
project.setTasks([task2]).success(function(associatedTasks) {
  // you will get task2 only
})
 
// remove 'em all
projects.setTasks([]).success(function(associatedTasks) {
  // you will get an empty array
})
 
// or remove 'em more directly
projects.removeTask(task1).success(function() {
  // it's gone
})
 
// and add 'em again
projects.addTask(task1).success(function() {
  // it's back again
})

// project is associated with task1 and task2
task2.setProject(null).success(function() {
  // and it's gone
})

Task.hasOne(User, {as: "Author"})
Task#setAuthor(anAuthor)

The sequelize binary

In order to run migrations, sequelize comes with a handy binary file which can setup your project and run migrations. The following snippet shows the possible things:

sequelize -h
sequelize --help
--> prints the help
 
sequelize -V
sequelize --version
--> prints the version
 
sequelize -i
sequelize --init
--> creates a migration folder
--> creates a config folder
--> saves a config.json inside the config folder
 
sequelize -i -f
sequelize --init --force
--> forced creation of migration and config folder
--> existing data will be deleted first
 
sequelize -m
sequelize --migrate
--> needs a valid config.json
--> runs pending migrations
--> saves successfully executed migrations inside the database
 
sequelize -m -u
sequelize --migrate --undo
--> needs a valid config.json
--> reverts the last successfull migration
--> when there were multiple executed migrations, all of them are reverted

Migration Skeleton

The following skeleton shows a typical migration file. All migrations are expected to be located in a folder called "migrations" at the very top of the project.

module.exports = {
  up: function(migration, DataTypes) {
    // logic for transforming into the new state
  },
  down: function(migration, DataTypes) {
    // logic for reverting the changes
  }
}

The passed migration object can be used to modify the database. The DataTypes object stores the available data types such as STRING or INTEGER. The available methods of the migration object are the following.

Migration functions

Using the migration object describe before, you will have access to most of already introduced functions. Furthermore there are some other methods, which are designed to actually change the database schema.

createTable(tableName, attributes, options)

This method allows creation of new tables. It is allowed to pass simple or complex attribute definitions. You can define the encoding of the table and the table's engine via options.

migration.createTable(
  'nameOfTheNewTable',
  {
    attr1: DataTypes.STRING,
    attr2: DataTypes.INTEGER,
    attr3: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
      allowNull: false
    }
  },
  {
    engine: 'MYISAM', // default: 'InnoDB'
    charset: 'latin1' // default: null
  }
)

dropTable(tableName)

This method allows deletion of an existing table.

migration.dropTable('nameOfTheExistingTable')

dropAllTables()

This method allows deletion of all existing tables in the database.

migration.dropAllTables()

renameTable(before, after)

This method allows renaming of an existing table.

migration.renameTable('Person', 'User')

showAllTables()

This method returns the name of all existing tables in the database.

migration.showAllTables().success(function(tableNames) {})

describeTable(tableName)

This method returns an array of hashes containing information about all attributes in the table.

migration.describeTable('Person').success(function(attributes) {
  /*
    tableNames will be something like:
    [ { Field: 'name',
      Type: 'varchar(255)',
      Null: 'YES',
      Key: '',
      Default: null,
      Extra: '' },
    { Field: 'isBetaMember',
      Type: 'tinyint(1)',
      Null: 'NO',
      Key: '',
      Default: '0',
      Extra: '' } ]
  */
})

addColumn(tableName, attributeName, dataTypeOrOptions)

This method allows adding columns to an existing table. The data type can be simple or complex.

migration.addColumn(
  'nameOfAnExistingTable',
  'nameOfTheNewAttribute',
  DataTypes.STRING
)
 
// or
 
migration.addColumn(
  'nameOfAnExistingTable',
  'nameOfTheNewAttribute',
  {
    type: DataTypes.STRING,
    allowNull: false
  }
)

removeColumn(tableName, attributeName)

This method allows deletion of a specific column of an existing table

migration.removeColumn('Person', 'signature')

changeColumn(tableName, attributeName, dataTypeOrOptions)

This method changes the meta data of an attribute. It is possible to change the default value, allowance of null or the data type. Please make sure, that you are completely describing the new data type. Missing information are expected to be defaults.

migration.addColumn(
  'nameOfAnExistingTable',
  'nameOfAnExistingAttribute',
  DataTypes.STRING
)
 
// or
 
migration.addColumn(
  'nameOfAnExistingTable',
  'nameOfAnExistingAttribute',
  {
    type: DataTypes.FLOAT,
    allowNull: false,
    default: 0.0
  }
)

renameColumn(tableName, attrNameBefore, attrNameAfter)

This methods allows renaming attributes.

migration.renameColumn('Person', 'signature', 'sig')

addIndex(tableName, attributes, options)

This methods creates indexes for specific attributes of a table. The index name will be automatically generate if it is not passed via in the options (indexName).

migration.addIndex('Person', ['firstname', 'lastname'])
// will create the index person_firstname_lastname
 
// or with options
&nbp;
migration.addIndex(
  'Person',
  ['firstname', 'lastname'],
  {
    indexName: 'SuperDuperIndex'
  }
)
// will create the index SuperDuperIndex

showIndex(tableName, options)

This method returns all indexes on the table.

migration.showIndex('Person')

removeIndex(tableName, indexNameOrAttributes)

This method deletes an existing index of a table.

migration.removeIndex('Person', 'SuperDuperIndex')
 
// or
 
migration.removeIndex('Person', ['firstname', 'lastname'])

Compatibility

Sequelize is compatible to the following versions of Node.JS:

• 0.4.12
• 0.5.10
• 0.6.x

Asynchronicity

Since v1.3.0 there are multiple ways of adding listeners to asynchronous requests. First of all, each time you call a finder method or save an object, sequelize triggers asynchronous logic. To react to the success or the failure of the request, you can do the following:

// the old, pre-v1.3.0 way
Model.findAll().on('success', function(models) { /* foo */ })
Model.findAll().on('failure', function(err) { /* bar */ })
 
// the new, >=v1.3.0 way
// each one is valid
Model.findAll().on('success', function(models) { /* foo */ })
Model.findAll().success(function(models) { /* foo */ })
Model.findAll().ok(function(models) { /* foo */ })
 
Model.findAll().on('failure', function(err) { /* bar */ })
Model.findAll().error(function(err) { /* bar */ })
Model.findAll().failure(function(err) { /* bar */ })
Model.findAll().fail(function(err) { /* bar */ })
 
/*
  Please notice:
  I will replace .on('failure') with .on('error') in future releases.
  This will be done, due to being more native in the node environment.
  Doing so will throw an error each time you don't listen for the 'error'-
  event.
*/

Additional links

If you want to keep track about latest development of sequelize or to just discuss things with other sequelize users you might want to take a look at the following resources.

• Twitter
• IRC
• XING