Basic usage

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'])

This will save the passed database credentials and provide all further methods. Furthermore you can specify a non-default host/port:

var sequelize = new Sequelize('database', 'username', 'password', {
  host: "my.server.tld",
  port: 12345
})

If you just don't have a password:

var sequelize = new Sequelize('database', 'username')
// or
var sequelize = new Sequelize('database', 'username', null)

You can also use a connection string: js var sequelize = new Sequelize('mysql://user:pass@example.com:9821/dbname', { // Look to the next section for possible options })

Options

Besides the host and the port, Sequelize comes with a whole bunch of options. Here they are:

var sequelize = new Sequelize('database', 'username', 'password', {
  // custom host; default: localhost
  host: 'my.server.tld',

  // custom port; default: 3306
  port: 12345,

  // custom protocol
  // - default: 'tcp'
  // - added in: v1.5.0
  // - postgres only, useful for heroku
  protocol: null,

  // disable logging; default: console.log
  logging: false,

  // max concurrent database requests; default: 50
  maxConcurrentQueries: 100,

  // the sql dialect of the database
  // - default is 'mysql'
  // - currently supported: 'mysql', 'sqlite', 'postgres'
  dialect: 'mysql',

  // the storage engine for sqlite
  // - default ':memory:'
  storage: 'path/to/database.sqlite',

  // disable inserting undefined values as NULL
  // - default: false
  omitNull: true,

  // Specify options, which are used when sequelize.define is called.
  // The following example:
  //   define: {timestamps: false}
  // is basically the same as:
  //   sequelize.define(name, attributes, { timestamps: false })
  // so defining the timestamps for each model will be not necessary
  // Below you can see the possible keys for settings. All of them are explained on this page
  define: {
    underscored: false
    freezeTableName: false,
    syncOnAssociation: true,
    charset: 'utf8',
    collate: 'utf8_general_ci',
    classMethods: {method1: function() {}},
    instanceMethods: {method2: function() {}},
    timestamps: true
  },

  // similiar for sync: you can define this to always force sync for models
  sync: { force: true }

  // sync after each association (see below). If set to false, you need to sync manually after setting all associations. Default: true
  syncOnAssociation: true

  // use pooling in order to reduce db connection overload and to increase speed
  // currently only for mysql and postgresql (since v1.5.0)
  pool: { maxConnections: 5, maxIdleTime: 30}
})

Hint: You can also define a custom function for the logging part. Just pass a function. The first parameter will be the string that is logged.

Read replication

Sequelize supports read replication, i.e. having multiple servers that you can connect to when you want to do a SELECT query. When you do read replication, you specify one of more servers to act as read replicas, and one server to act as the write master, which handles all writes and updates and propagates them to the replicas (note that the actual replication process is not handled by Sequelize, but should be set up in MySql).

var sequelize = new Sequelize('database', null, null, {
  dialect: 'mysql',
  port: 3306
  replication: {
    read: [
      { host: '8.8.8.8', username: 'anotherusernamethanroot', password: 'lolcats!' },
      { host: 'localhost', username: 'root', password: null }
    ],
    write: { host: 'localhost', username: 'root', password: null }
  },
  pool: { // If you want to override the options used for the read pool you can do so here
    maxConnections: 20,
    maxIdleTime: 30000
  },
})

If you have any general settings that apply to all replicas you do not need to provide them for each instance. In the code above, database name and port is propagated to all replicas. The same will happen for user and password, if you leave them out for any of the replicas. Each replica has the following options: host, port, username, password, database

Sequelize uses a pool to manage connections to your replicas. The default options are:

{
  maxConnections: 10,
  minConnections: 0,
  maxIdleTime:    1000
}

If you want to modify these, you can pass pool as an options when instantiating Sequelize, as shown above.

Note: Read replication only works for MySQL at the moment!

Dialects

With the release of Sequelize v1.6.0, the library got independent from specific dialects. That mean, that you'll have to add the respective dialect library yourself. Another option is the use of the sequelize packages that ship the dialect libraries as well.

MySQL

In order to get Sequelize working nicely together with MySQL, you'll need to install mysql@~2.0.0-alpha7. Once that's done you can use it like this:

var sequelize = new Sequelize('database', 'username', 'password', {
  // mysql is the default dialect, but you know...
  // for demo purporses we are defining it nevertheless :)
  // so: we want mysql!
  dialect: 'mysql'
})

Also possible is the use of sequelize-mysql. Just install it via npm install sequelize-mysql and use it like this:

var Sequelize = require('sequelize-mysql').sequelize
var mysql     = require('sequelize-mysql').mysql

var sequelize = new Sequelize('database', 'username', 'password', {
  // mysql is the default dialect, but you know...
  // for demo purporses we are defining it nevertheless :)
  // so: we want mysql!
  dialect: 'mysql'
})

SQLite

For SQLite compatibility you'll need sqlite3@~2.1.5. Configure Sequelize like this:

var sequelize = new Sequelize('database', 'username', 'password', {
  // sqlite! now!
  dialect: 'sqlite',

  // the storage engine for sqlite
  // - default ':memory:'
  storage: 'path/to/database.sqlite'
})

If you want to use the bundled version, run npm install sequelize-sqlite and use the following code:

var Sequelize = require('sequelize-sqlite').sequelize
var sqlite    = require('sequelize-sqlite').sqlite

 var sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'sqlite',
  storage: 'path/to/database.sqlite'
})

PostgreSQL

The library for PostgreSQL is pg@~0.10.2. You'll just need to define the dialect:

var sequelize = new Sequelize('database', 'username', 'password', {
  // gimme postgres, please!
  dialect: 'postgres'
})

In order to use the bundled version, use this npm install sequelize-postgres and require the lib this way:

var Sequelize = require('sequelize-postgres').sequelize
var postgres  = require('sequelize-postgres').postgres

 var sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'postgres'
})

Executing raw SQL queries

As there are often use cases in which it is just easier to execute raw / already prepared SQL queries, you can utilize the function sequelize.query.

Here is how it works:

sequelize.query("SELECT * FROM myTable").success(function(myTableRows) {
  console.log(myTableRows)
})

One note: If the attribute names of the table contain dots, the resulting objects will get a nesting:

sequelize.query('select 1 as `foo.bar.baz`').success(function(rows) {
  console.log(JSON.stringify(rows))

  /*
    [{
      "foo": {
        "bar": {
          "baz": 1
        }
      }
    }]
  */
})

Definition

To define mappings between a model and a table, use the define method. Sequelize will then automatically add the attributes createdAt and updatedAt to it. So you will be able to know when the database entry went into the db and when it was updated the last time.

var Project = sequelize.define('Project', {
  title: Sequelize.STRING,
  description: Sequelize.TEXT
})

var Task = sequelize.define('Task', {
  title: Sequelize.STRING,
  description: Sequelize.TEXT,
  deadline: Sequelize.DATE
})

You can also set some options:

var Foo = sequelize.define('Foo', {
  // instantiating will automatically set the flag to true if not set
  flag: { type: Sequelize.BOOLEAN, allowNull: false, defaultValue: true},

  // default values for dates => current time
  myDate: { type: Sequelize.DATE, defaultValue: Sequelize.NOW },

  // setting no title will throw an error when trying to save
  title: { type: Sequelize.STRING, allowNull: false},

  // Creating two objects with the same value will throw an error. Currently composite unique
  // keys can only be created 'addIndex' from the migration-section below

  someUnique: {type: Sequelize.STRING, unique: true},
  // Go on reading for further information about primary keys

  identifier: { type: Sequelize.STRING, primaryKey: true},
  // autoIncrement can be used to create auto_incrementing integer columns

  incrementMe: { type: Sequelize.INTEGER, autoIncrement: true }
})

Data types

Sequelize currently supports the following datatypes:

Sequelize.STRING  // VARCHAR(255)
Sequelize.TEXT    // TEXT
Sequelize.INTEGER // INTEGER
Sequelize.BIGINT  // BIGINT
Sequelize.DATE    // DATETIME
Sequelize.BOOLEAN // TINYINT(1)
Sequelize.FLOAT   // FLOAT

Sequelize.ENUM('value 1', 'value 2') // An ENUM with allowed values 'value 1' and 'value 2'
Sequelize.DECIMAL(10, 2)             // DECIMAL(10,2)
Sequelize.ARRAY(Sequelize.TEXT)      // Defines an array. PostgreSQL only.

Usage in object notation:

// for enums:
sequelize.define('model', {
  states: {
    type:   Sequelize.ENUM,
    values: ['active', 'pending', 'deleted']
  }
})

Validations

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. The validations are implemented by node-validator, and we are currently using v. 1.1.1.

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 (129.89.23.1) or IPv6 format
      isIPv4: true,             // checks for IPv4 (129.89.23.1)
      isIPv6: true,             // checks for IPv6 format
      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', 'bar']],  // check the value is not one of these
      isIn: [['foo', 'bar']],   // check the value is one of these
      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!')
        // we also are in the model's context here, so this.otherField
        // would get the value of otherField if it existed
        }
      }
    }
  }
})

Note that where multiple arguments need to be passed to the built-in validation functions, the arguments to be passed must be in an array. But if a single array argument is to be passed, for instance an array of acceptable strings for isIn, this will be interpreted as multiple string arguments instead of one array argument. To work around this pass a single-length array of arguments, such as [['one', two']] as shown above.

To use a custom error message instead of that provided by node-validator, use an object instead of the plain value or array of arguments, for example a validator which needs no argument can be given a custom message with

isInt: {
  msg: "Must be an integer number of pennies"
}

or if arguments need to also be passed add an args property:

isIn: {
  args: [['en', 'zh']],
  msg: "Must be English or Chinese"
}

When using custom validator functions the error message will be whatever message the thrown Error object holds.

See the node-validator project for more details on the built in validation methods.

Hint: You can also define a custom function for the logging part. Just pass a function. The first parameter will be the string that is logged.

Validators and allowNull

Since v1.7.0 if a particular field of model is set to allow null (with allowNull: true) and that value has been set to null, its validators do not run. This means you can, for instance, have a string field which validates its length to be at least 5 characters, but which also allows null.

Model validations

Since v1.7.0, validations can also be defined to check the model after the field-specific validators. Using this you could, for example, ensure either neither of latitude and longitude are set or both, and fail if one but not the other is set.

Model validator methods are called with the model object's context and are deemed to fail if they throw an error, otherwise pass. This is just the same as with custom field-specific validators.

Any error messages collected are put in the validation result object alongside the field validation errors, with keys named after the failed validation method's key in the validate option object. Even though there can only be one error message for each model validation method at any one time, it is presented as a single string error in an array, to maximize consistency with the field errors. (Note that the structure of validate()'s output is scheduled to change in v2.0 to avoid this awkward situation. In the mean time, an error is issued if a field exists with the same name as a custom model validation.)

An example:

var Pub = Sequelize.define('Pub', {
  name: { type: Sequelize.STRING },
  address: { type: Sequelize.STRING },
  latitude: {
    type: Sequelize.INTEGER,
    allowNull: true,
    defaultValue: null,
    validate: { min: -90, max: 90 }
  },
  longitude: {
    type: Sequelize.INTEGER,
    allowNull: true,
    defaultValue: null,
    validate: { min: -180, max: 180 }
  },
}, {
  validate: {
    bothCoordsOrNone: function() {
      if ((this.latitude === null) === (this.longitude === null)) {
        throw new Error('Require either both latitude and longitude or neither')
      }
    }
  }
})

In this simple case an object fails validation if latitude or longitude is given, but not both. If we try to build one with an out-of-range latitude and no longitude, raging_bullock_arms.validate() might return

{
  'latitude': ['Invalid number: latitude'],
  'bothCoordsOrNone': ['Require either both latitude and longitude or neither']
}

Configuration

You can also influence 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,

  // define the table's name
  tableName: 'my_very_custom_table_name'
})

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

var Person = sequelize.define('Person', { /* attributes */ }, {
  engine: 'MYISAM'
})

// or globally
var sequelize = new Sequelize(db, user, pw, {
  define: { engine: 'MYISAM' }
})

Import

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. Since v1.5.0 of Sequelize the import is cached, so you won't run into troubles when calling the import of a file twice or more often.

// in your server file - e.g. app.js
var Project = sequelize.import(__dirname + "/path/to/models/project")

// The model definition is done in /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
  })
}

Database synchronization

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 the 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' }
  }
})

// Example:
Foo.method1()
Foo.build().method2()

Of course you can also access the instance's data and generate virtual getters:

var User = sequelize.define('User', { firstname: Sequelize.STRING, lastname: Sequelize.STRING }, {
  instanceMethods: {
    getFullname: function() {
      return [this.firstname, this.lastname].join(' ')
    }
  }
})

// Example:
User.build({ firstname: 'foo', lastname: 'bar' }).getFullname() // 'foo bar'

You can also set custom methods to all of your models during the instantiation:

var sequelize = new Sequelize('database', 'username', 'password', {
  // Other options during the initialization could be here
  define: {
    classMethods: {
      method1: function() {},
      method2: function() {}
    },
    instanceMethods: {
      method3: function() {}
    }
  }
})

// Example:
var Foo = sequelize.define('Foo', { /* attributes */});
Foo.method1()
Foo.method2()
Foo.build().method3()

Data retrieval a.k.a. 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:

find - Search for one specific element in the database

// 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
})

findOrCreate - Search for a specific element or create it if not available

The method findOrCreate can be used to check if a certain element is already existing in the database. If that is the case the method will result in a respective instance. If the element does not yet exist, it will be created.

Let's assume we have an empty database with a User model which has a username and a job.

User.findOrCreate({ username: 'sdepold' }, { job: 'Technical Lead JavaScript' }).success(function(user) {
  console.log(user.values)

  /*
    {
      username: 'sdepold',
      job: 'Technical Lead JavaScript',
      id: 1,
      createdAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET),
      updatedAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET)
    }
  */
})

The code created a just created instance.

So when we already have an instance ...

User.create({ username: 'fnord', job: 'omnomnom' }).success(function() {
  User.findOrCreate({ username: 'fnord' }, { job: 'something else' }).success(function(user) {
    console.log(user.values)

    /*
      {
        username: 'fnord',
        job: 'omnomnom',
        id: 2,
        createdAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET),
        updatedAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET)
      }
    */
  })
})

... the existing entry will not be changed. See the job of the second user.

findAll - Search for multiple elements in the database

// 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})

Sometimes you might be expecting a massive dataset that you just want to display, without manipulation. For each row you select, Sequelize creates a DAO, with functions for update, delete, get associations etc. If you have thousands of rows, this might take some time. If you only need the raw data and don't want to update anything, you can do like this to get the raw data.

// Are you expecting a masssive dataset from the DB, and don't want to spend the time building DAOs for each entry?
// You can pass an extra query option to get the raw data instead:
Project.findAll({ where: ... }, { raw: true })

count - Count the occurences of elements in the database

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.")
})

max - Get the greatest value of a specific attribute within a specific table

And here is a method for getting 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.max('age').success(function(max) {
  // this will return 40
})

Eager loading

When you are retrieving data from the database there is a fair chance that you also want to get their associations. This is possible since v1.6.0 and is called eager loading. The basic idea behind that, is the use of the attribute include when you are calling find or findAll. Lets assume the following setup:

var User = sequelize.define('User', { name: Sequelize.STRING })
  , Task = sequelize.define('Task', { name: Sequelize.STRING })
  , Tool = sequelize.define('Tool', { name: Sequelize.STRING })

Task.belongsTo(User)
User.hasMany(Task)
User.hasMany(Tool, { as: 'Instruments' })

sequelize.sync().done(function() {
  // this is where we continue ...
})

OK. So, first of all, let's load all tasks with their associated user.

Task.findAll({ include: [ User ] }).success(function(tasks) {
  console.log(JSON.stringify(tasks))

  /*
    [{
      "name": "A Task",
      "id": 1,
      "createdAt": "2013-03-20T20:31:40.000Z",
      "updatedAt": "2013-03-20T20:31:40.000Z",
      "UserId": 1,
      "user": {
        "name": "John Doe",
        "id": 1,
        "createdAt": "2013-03-20T20:31:45.000Z",
        "updatedAt": "2013-03-20T20:31:45.000Z"
      }
    }]
  */
})

Notice that the accessor of the associated data is the name of the model in camelcase with lowercased first character. Also the accessor is singular as the association is one-to-something.

Next thing: Loading of data with many-to-something associations!

User.findAll({ include: [ Task ] }).success(function(users) {
  console.log(JSON.stringify(users))

  /*
    [{
      "name": "John Doe",
      "id": 1,
      "createdAt": "2013-03-20T20:31:45.000Z",
      "updatedAt": "2013-03-20T20:31:45.000Z",
      "tasks": [{
        "name": "A Task",
        "id": 1,
        "createdAt": "2013-03-20T20:31:40.000Z",
        "updatedAt": "2013-03-20T20:31:40.000Z",
        "UserId": 1
      }]
    }]
  */
})

Notice that the accessor is plural. This is because the association is many-to-something.

One last thing is the use of aliased associations. In order to get that right you have to specify the model you want to load, as well as the alias:

User.findAll({ include: [{ model: Tool, as: 'Instruments' }] }).success(function(users) {
  console.log(JSON.stringify(users))

  /*
    [{
      "name": "John Doe",
      "id": 1,
      "createdAt": "2013-03-20T20:31:45.000Z",
      "updatedAt": "2013-03-20T20:31:45.000Z",
      "instruments": [{
        "name": "Toothpick",
        "id": 1,
        "createdAt": null,
        "updatedAt": null,
        "UserId": 1
      }]
    }]
  */
})

Final note: If you include an object which is not associated, Sequelize will throw an error.

Tool.findAll({ include: [ User ] }).success(function(tools) {
  console.log(JSON.stringify(tools))
})

// Error: User is not associated to Tool!

Building a not-persistant 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!
  }).error(function(error) {
    // Ooops, do some error-handling
  })

Creating persistant instances

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
})

Sequelize v1.5.0 introduced the possibility to define attributes which can be set via the create method. This can be especially very handy if you create database entries based on a form which can be filled by a user. Using that would for example allow you to restrict the User model to set only a username and an address but not an admin flag:

User.create({ username: 'barfooz', isAdmin: true }, [ 'username' ]).success(function(user) {
  // let's assume the default of isAdmin is false:
  console.log(user.values) // => { username: 'barfooz', isAdmin: false }
})

Updating / Saving / Persisting 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() {})

Since v1.4.1 it's also possible to define which attributes should be saved when calling save. This is useful when you set attributes based on a previously defined object. E.g. if you get the values of an object via a form of a web app. Furthermore this is used internally for updateAttributes. This is how it looks like:

task.title = 'foooo'
task.description = 'baaaaaar'
task.save(['title']).success(function() {
 // title will now be 'foooo' but description is the very same as before
})

Destroying / Deleting persistant instances

Once you created an object and got a reference to it, you can delete it from the database. The relevant method is destroy:

Task.create({ title: 'a task' }).success(function(task) {
  // now you see me...

  task.destroy().success(function() {
    // now i'm gone :)
  })
})

Values of an instance

If you log an instance you will notice, that there is a lot of additional stuff. In order to hide such stuff and reduce it to the very interesting information, you can use the values-attribute. Calling it will return only the values of an instance.

Person.create({
  name: 'Rambow',
  firstname: 'John'
}).success(function(john) {
  console.log(john.values)
})

// result:

// { name: 'Rambow',
//   firstname: 'John',
//   id: 1,
//   createdAt: Tue, 01 May 2012 19:12:16 GMT,
//   updatedAt: Tue, 01 May 2012 19:12:16 GMT
// }

Hint: You can also transform an instance into JSON by using JSON.stringify(instance). This will basically return the very same as values.

Reloading instances

If you need to get your instance in sync, you can use the method reload. It will fetch the current data from the database and overwrite the attributes of the model on which the method has been called on.

Person.find({ where: { name: 'john' } }).success(function(person) {
  person.name = 'jane'
  console.log(person.name) // 'jane'

  person.reload().success(function() {
    console.log(person.name) // 'john'
  })
})

Incrementing certain values of an instance

In order to increment values of an instance without running into concurrency issues, you may use increment.

First of all you can define a field and the value you want to add to it.

User.find(1).success(function(user) {
  user.increment('my-integer-field', 2).success(/* ... */)
})

Second, you can define multiple fields and the value you want to add to them.

User.find(1).success(function(user) {
  user.increment([ 'my-integer-field', 'my-very-other-field' ], 2).success(/* ... */)
})

Third, you can define an object containing fields and its increment values.

User.find(1).success(function(user) {
  user.increment({
    'my-integer-field':    2,
    'my-very-other-field': 3
  }).success(/* ... */)
})

Decrementing certain values of an instance

In order to decrement values of an instance without running into concurrency issues, you may use decrement.

First of all you can define a field and the value you want to add to it.

User.find(1).success(function(user) {
  user.decrement('my-integer-field', 2).success(/* ... */)
})

Second, you can define multiple fields and the value you want to add to them.

User.find(1).success(function(user) {
  user.decrement([ 'my-integer-field', 'my-very-other-field' ], 2).success(/* ... */)
})

Third, you can define an object containing fields and its decrement values.

User.find(1).success(function(user) {
  user.decrement({
    'my-integer-field':    2,
    'my-very-other-field': 3
  }).success(/* ... */)
})

With Sequelize you can also specify associations between multiple classes. Doing so will help you to easily access and set those associated objects. The library therefore provides for each defined class different methods, which are explained in the following chapters.

Note: Associations with models that use custom primaryKeys (so not the field 'id') are currently unsupported.

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

// If you need to join a table twice you can double join the same table
Team
  .hasOne(Game, {foreignKey : 'homeTeamId'});
  .hasOne(Game, {foreignKey , 'awayTeamId'});
Game
  .belongsTo(Team);


// 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.

// Since v1.5.0 you can also reference the same Model without creating a junction
// table (but only if each object will have just one 'parent'). If you need that,
// use the option foreignKey and useJunctionTable:
Person.hasMany(Person, { as: 'Children', foreignKey: 'ParentId', useJunctionTable: false })

// You can also use a predefined junction table using the option joinTableName:
Project.hasMany(User, {joinTableName: 'project_has_users'})
User.hasMany(Project, {joinTableName: 'project_has_users'})

// If you need your association table to have additional attributes, an alternative
// way to do this would be to define the table and then use two hasMany relationship.

UserProject = sequelize.define('user_projects',{
   count : Sequelize.INTEGER
})

Project.hasMany(UserProjects);
User.hasMany(UserProjects);
UserProject.belongsTo(User);
UserProject.belongsTo(Project);

// NOTE : this does NOT allow you direct access from Project to User. You can access
// UserProject which will give you access to the User, but it is not a direct relationship

Associating objects

Because Sequelize is doing a lot of magic, you have to call Sequelize#sync after setting the associations! Doing so will allow you the following:

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
})

// You can also pass filters to the getter method.
// They are equal to the options you can pass to a usual finder method.
project.getTasks({ where: 'id > 10' }).success(function(tasks) {
  // tasks with an id greater than 10 :)
})

// You can also only retrieve certain fields of a associated object.
// This example will retrieve the attibutes "title" and "id"
project.getTasks({attributes: ['title']}).success(function(tasks) {
  // tasks with an id greater than 10 :)
})

To remove created associations you can just call the set method without a specific id:

// 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
})

You can of course also do it vice versa:

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

For hasOne/belongsTo its basically the same:

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

Check associations

Sequelize v1.5.0 introduced methods which allows you, to check if an object is already associated with another one (N:M only). Here is how you'd do it:

// check if an object is one of associated ones:
Project.create({ /* */ }).success(function(project) {
  User.create({ /* */ }).success(function(user) {
    project.hasUser(user).success(function(result) {
      // result would be false
      project.addUser(user).success(function() {
        project.hasUser(user).success(function(result) {
          // result would be true
        })
      })
    })
  })
})

// check if all associated objects are as expected:
// let's assume we have already a project and two users
project.setUsers([user1, user2]).success(function() {
  project.hasUsers([user1]).success(function(result) {
    // result would be false
    project.hasUsers([user1, user2]).success(function(result) {
      // result would be true
    })
  })
})

Sequelize v1.3.0 introduced migrations. With those mechanism you can transform your existing database into another state and vice versa. Those state transitions are saved in migration files, which describe the way how to get to the new state and how to revert the changes in order to get back to the old state.

The 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

sequelize -c [migration-name]
sequelize --create-migration [migration-name]
--> creates the migrations folder
--> creates a file with current timestamp + migration-name
--> migration-name has the default 'unnamed-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. Sequelize 1.4.1 added the possibility to let the sequelize binary generate a migration skeleton. See the aboves section for more details.

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

  down: function(migration, DataTypes, done) {
    // 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 third parameter is a callback function which needs to be called once everything was executed. The first parameter of the callback function can be used to pass a possible error. In that case, the migration will be marked as failed. Here is some code:

module.exports = {
  up: function(migration, DataTypes, done) {
    migration.dropAllTables().complete(done)

    // equals:
    migration.dropAllTables().complete(function(err) {
      if (err) {
        done(err)
      } else {
        done(null)
      }
    })
  }
}

The available methods of the migration object are the following.

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) {
  /*
    attributes will be something like:

    {
      name: {
        type:         'VARCHAR(255)', // this will be 'CHARACTER VARYING' for pg!
        allowNull:    true,
        defaultValue: null
      },
      isBetaMember: {
        type:         'TINYINT(1)', // this will be 'BOOLEAN' for pg!
        allowNull:    false,
        defaultValue: false
      }
    }
  */
})

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.changeColumn(
  'nameOfAnExistingTable',
  'nameOfAnExistingAttribute',
  DataTypes.STRING
)

// or

migration.changeColumn(
  '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 generated if it is not passed via in the options (see below).

// This example will create the index person_firstname_lastname
migration.addIndex('Person', ['firstname', 'lastname'])

// This example will create a unique index with the name SuperDuperIndex using the optional 'options' field.
// Possible options:
// - indicesType: UNIQUE|FULLTEXT|SPATIAL
// - indexName: The name of the index. Default is <tableName>_<attrName1>_<attrName2>
// - parser: For FULLTEXT columns set your parser
// - indexType: Set a type for the index, e.g. BTREE. See the documentation of the used dialect
migration.addIndex(
  'Person',
  ['firstname', 'lastname'],
  {
    indexName: 'SuperDuperIndex',
    indicesType: 'UNIQUE'
  }
)

removeIndex(tableName, indexNameOrAttributes)

This method deletes an existing index of a table.

migration.removeIndex('Person', 'SuperDuperIndex')

// or

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

Sequelize comes with some handy utils including references to lodash as well as some individual helpers. You can access them via Sequelize.Utils.

Lodash.js

You can access all the methods of lodash like this:

Sequelize.Utils._.each(/* ... */)
Sequelize.Utils._.map(/* ... */)
Sequelize.Utils._...

Also Sequelize ships the Underscore extension underscore.string, which allows nifty string manipulation:

Sequelize.Utils._.camelize('something') // Something

Check out the page of Lodash, Underscore and underscore.string for further information.

QueryChainer

Because you will want to save/create/delete several items at once and just go on after all of them are saved, Sequelize provides the QueryChainer module. It can be used like this:

var chainer = new Sequelize.Utils.QueryChainer
chainer.add(/* Query | EventEmitter */)
chainer.run().success(function(){}).error(function(errors){})

And a real world example:

var chainer = new Sequelize.Utils.QueryChainer
var Task    = sequelize.define('Task', /* ... */)

chainer
  .add(Task.drop())
  .add(Task.sync())

for(var i = 0; i < 20; i++)
  chainer.add(Task.create({}))

chainer
  .run()
  .success(function(){})
  .error(function(errors){})

It is also possible to force a serial run of the query chainer by using the following syntax:

new Sequelize.Utils.QueryChainer()
  .add(Model, 'function', [param1, param2])
  .add(Model, 'function2', [param1, param2])
  .runSerially()
  .success(function() { /* no problems :) */ })
  .error(function(err) { /* hmm not good :> */ })

// and with options:

new Sequelize.Utils.QueryChainer()
  .add(Model, 'function', [param1, param2], {
    // Will be executed before Model#function is called
    before: function(model) {},

    /*
      Will be executed after Model#function was called
      and the function emitted a success or error event.
      If the following success option is passed, the function
      will be executed after the success function.
    */
    after: function(migration) {},

    // Will be executed if Model#function emits a success event.
    success: function(migration, callback) {}
  })
  // skipOnError: don't execute functions once one has emitted an failure event.
  .runSerially({ skipOnError: true })
  .success(function() { /* no problems :) */ })
  .error(function(err) { /* hmm not good :> */ })

If the success callbacks of the added methods are passing values, they can be utilized in the actual success method of the query chainer:

chainer.add(Project.getTasks())
chainer.add(Project.getTeam())
chainer.run().success(function(results){
  var tasks = results[0]
  var team = results[1]
})

Compatibility

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

• 0.8.x
• 0.6.x
• 0.5.10
• 0.4.12

HINT: Compatibility for versions < 0.6 was dropped with Sequelize v1.4.1. It might still work with Node.JS v0.4.x but as tests were moved to BusterJS, total test coverage for < v0.6 was not possible anymore.

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 (or both) 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 */ }) ==> invalid since v1.5.0
Model.findAll().on('error', function(err) { /* bar */ }) //   ==> new since v1.5.0
Model.findAll().error(function(err) { /* bar */ })
Model.findAll().failure(function(err) { /* bar */ })
Model.findAll().fail(function(err) { /* bar */ })

Model.findAll().complete(function(err, result) { /* bar */ })
Model.findAll().done(function(err, result) { /* bar */ })

Please notice: Since v1.5.0 the 'error' event is used to notify about errors. If such events aren't caught however, Node.JS will throw an error. So you would probably like to catch them :D

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: @sdepold
• Twitter: @sequelizejs
• ADN: @sdepold
• IRC: sequelizejs@freenode.net
• XING
• Facebook

Company & Projects

Here is a list of companies and projects that are using Sequelize in real world applications: