Models

Definition

To define mappings between a model and a table, use thedefinemethod. Sequelize will then automatically add the attributescreatedAtandupdatedAtto it. So you will be able to know when the database entry went into the db and when it was updated the last time. If you do not want timestamps on your models, only want some timestamps, or you are working with an existing database where the columns are named something else, jump straight on to configuration to see how to do that.

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 on each column:

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 allowNull to false will add NOT NULL to the column, which means an error will be
 // thrown from the DB when the query is executed if the column is null. If you want to check that a value
 // is not null before querying the DB, look at the validations section below.
 title: { type: Sequelize.STRING, allowNull: false},

 // Creating two objects with the same value will throw an error. The unique property can be either a
 // boolean, or a string. If you provide the same string for multiple columns, they will form a
 // composite unique key.
 someUnique: {type: Sequelize.STRING, unique: true},
 uniqueOne: { type: Sequelize.STRING,  unique: 'compositeIndex'},
 uniqueTwo: { type: Sequelize.INTEGER, unique: 'compositeIndex'}

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

 // Comments can be specified for each field for MySQL and PG
 hasComment: { type: Sequelize.INTEGER, comment: "I'm a comment!" },

 // You can specify a custom field name via the "field" attribute:
 fieldWithUnderscores: { type: Sequelize.STRING, field: "field_with_underscores" }
})

The comment option can also be used on a table, see model configuration

Data types

Sequelize currently supports the following datatypes:

Sequelize.STRING                      // VARCHAR(255)
Sequelize.STRING(1234)                // VARCHAR(1234)
Sequelize.STRING.BINARY               // VARCHAR BINARY
Sequelize.TEXT                        // TEXT
 
Sequelize.INTEGER                     // INTEGER
Sequelize.BIGINT                      // BIGINT
Sequelize.BIGINT(11)                  // BIGINT(11)
Sequelize.FLOAT                       // FLOAT
Sequelize.FLOAT(11)                   // FLOAT(11)
Sequelize.FLOAT(11, 12)               // FLOAT(11,12)
 
Sequelize.DECIMAL                     // DECIMAL
Sequelize.DECIMAL(10, 2)              // DECIMAL(10,2)
 
Sequelize.DATE                        // DATETIME for mysql / sqlite, TIMESTAMP WITH TIME ZONE for postgres
Sequelize.BOOLEAN                     // TINYINT(1)
 
Sequelize.ENUM('value 1', 'value 2')  // An ENUM with allowed values 'value 1' and 'value 2'
Sequelize.ARRAY(Sequelize.TEXT)       // Defines an array. PostgreSQL only.
 
Sequelize.BLOB                        // BLOB (bytea for PostgreSQL)
Sequelize.BLOB('tiny')                // TINYBLOB (bytea for PostgreSQL. Other options are medium and long)
Sequelize.UUID                        // UUID datatype for PostgreSQL and SQLite, CHAR(36) BINARY for MySQL (use defaultValue: Sequelize.UUIDV1 or Sequelize.UUIDV4 to make sequelize generate the ids automatically)

The BLOB data type allows you to insert data both as strings and as buffers. When you do a find or findAll on a model which has a BLOB column, that data will always be returned as a buffer.

If you are working with the PostgreSQL TIMESTAMP WITHOUT TIME ZONE and you need to parse it to a different timezone, please use the pg library's own parser:

require('pg').types.setTypeParser(1114, function(stringValue) {
  return new Date(stringValue + "+0000"); // e.g., UTC offset. Use any offset that you would like.
});

In addition to the type mentioned above, integer, bigint and float also support unsigned and zerofill properties, which can be combined in any order:

Sequelize.INTEGER.UNSIGNED              // INTEGER UNSIGNED
Sequelize.INTEGER(11).UNSIGNED          // INTEGER(11) UNSIGNED
Sequelize.INTEGER(11).ZEROFILL          // INTEGER(11) ZEROFILL
Sequelize.INTEGER(11).ZEROFILL.UNSIGNED // INTEGER(11) UNSIGNED ZEROFILL
Sequelize.INTEGER(11).UNSIGNED.ZEROFILL // INTEGER(11) UNSIGNED ZEROFILL

The examples above only show integer, but the same can be done with bigint and float

Usage in object notation:

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

Getters & setters

It is possible to define 'object-property' getters and setter functions on your models, these can be used both for 'protecting' properties that map to database fields and for defining 'pseudo' properties.

Getters and Setters can be defined in 2 ways (you can mix and match these 2 approaches!):

  • as part of a single property definition
  • as part of a model options

N.B.:If a getter or setter is defined in both places then the function found in the relevant property definition will always take precedence.

Defining as part of a property

var Foo = sequelize.define('Foo', {
  name: { Sequelize.STRING },
  title: {
    type     : Sequelize.STRING,
    allowNull: false,
    get      : function()  {
       /*
         do your magic here and return something!
         'this' allows you to access attributes of the model.
 
        example: this.getDataValue('name') works
      */
    },
    set      : function(v) { /* do your magic with the input here! */ }
  }
});

Defining as part of the model options

Below is an example of defining the getters and setters in the model options, notice thetitle_sluggetter, it shows how you can definepseudoproperties on your models! (theslugify()function was taken from the Underscore.String module, it is slightly modified here so that the example remains self-contained), note that thethis.titlereference in thetitle_sluggetter function will trigger a call to thetitlegetter function. if you do not want that then use thegetDataValue()method (see below).

var defaultToWhiteSpace = function(characters) {
    if (characters == null)
      return '\\s';
    else if (characters.source)
      return characters.source;
    else
      return ;
  };
 
var slugify = function(str) {
  var from  = "ąàáäâãåæćęèéëêìíïîłńòóöôõøśùúüûñçżź",
      to    = "aaaaaaaaceeeeeiiiilnoooooosuuuunczz",
      regex = new RegExp('[' + from.replace(/([.*+?^=!:${}()|[\]\/\\])/g, '\\$1') + ']', 'g');
 
  if (str == null) return '';
 
  str = String(str).toLowerCase().replace(regex, function(c) {
    return to.charAt(from.indexOf(c)) || '-';
  });
 
  return str.replace(/[^\w\s-]/g, '').replace(/([A-Z])/g, '-$1').replace(/[-_\s]+/g, '-').toLowerCase();
}
 
var Foo = sequelize.define('Foo', {
  title: {
    type     : Sequelize.STRING,
    allowNull: false,
  }
}, {
 
  getterMethods   : {
    title       : function()  { /* do your magic here and return something! */ },
    title_slug  : function()  { return slugify(this.title); }
  },
 
  setterMethods   : {
    title       : function(v) { /* do your magic with the input here! */ },
  }
});

Helper functions for use inside getter/setter definitions

  • retrieving an underlying property value? always usethis.getDataValue(), e.g.:
/* a getter for 'title' property */
function() {
    return this.getDataValue('title');
}
  • setting an underlying property value? always usethis.setDataValue(), e.g.:
/* a setter for 'title' property */
function(title) {
    return this.setDataValue('title', title.toString().toLowerCase());
}

N.B.: It is important to stick to using thesetDataValue()andgetDataValue()functions (as opposed to accessing the underlying "data values" property directly) - doing so protects your custom getters and setters from changes in the underlying model implementations (i.e. how and where data values are stored in your model instances)

Setter methods and Object Initialization

!!!TODO: write about how setters affect object initialization (both creating new objects with Model.build and retrieving existing objects from storage) !!!!!

Validations

Inv1.3.0model 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 thevalidate()method on an instance before saving. The validations are implemented by validator, and we are currently using v. 3.2.x.

Note: Inv1.7.0validations will now be called when executing thebuild()orcreate()functions.

var ValidateMe = sequelize.define('Foo', {
  foo: {
    type: Sequelize.STRING,
    validate: {
      is: ["^[a-z]+$",'i'],     // will only allow letters
      is: /^[a-z]+$/i,          // same as the previous example using real RegExp
      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
      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 forisIn, 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 anargsproperty:

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

When using custom validator functions the error message will be whatever message the thrownErrorobject holds.

See the node-validator projectfor 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 andallowNull

Sincev1.7.0if a particular field of a model is set to allow null (withallowNull: true) and that value has been set tonull, 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 allowsnull.

Model validations

Sincev1.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 oflatitudeandlongitudeare 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 thevalidateoption 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 ofvalidate()'s output is scheduled to change inv2.0to 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 enabled
  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'
})

If you want sequelize to handle timestamps, but only want some of them, or want your timestamps to be called something else, you can override each column individually:

var Foo = sequelize.define('Foo',  { /* bla */ }, {
  // don't forget to enable timestamps!
  timestamps: true,
 
  // I don't want createdAt
  createdAt: false,
 
  // I want updatedAt to actually be called updateTimestamp
  updatedAt: 'updateTimestamp'
 
  // And deletedAt to be called destroyTime (remember to enable paranoid for this to work)
  deletedAt: 'destroyTime',
  paranoid: true
})

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

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

Finaly you can specify a comment for the table in MySQL and PG

var Person = sequelize.define('Person', { /* attributes */ }, {
  comment: "I'm a table comment!"
})

Import

You can also store your model definitions in a single file using theimport-method. The returned object is exactly the same as defined in the imported file's function. Sincev1.5.0of 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
  })
}

Sincev1.7.0theimport-method can now accept a callback as an argument.

sequelize.import('Project', 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 / 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 methodfindOrCreatecan 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 aUsermodel which has ausernameand ajob.

User
  .findOrCreate({ username: 'sdepold' }, { job: 'Technical Lead JavaScript' })
  .success(function(user, created) {
    console.log(user.values)
    console.log(created)
   
    /*
      {
        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)
      }
      created: true
    */
  })

The code created a new 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, created) {
        console.log(user.values)
        console.log(created)
     
        /*
          {
            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)
          }
          created: false
        */
      })
  })

... the existing entry will not be changed. See thejobof the second user, and the fact that created was false.

Notice that the success callback has two arguments. When usingpromisesyou should callspread(API ref)instead ofthen, sincethenwill only recieve the first argument (the DAO), whilespreadwill recieve both the DAO, and thecreatedboolean.

findAndCountAll - Search for multiple elements in the database, returns both data and total count

This is a convienience method that combinesfindAll()andcount()(see below), this is useful when dealing with queries related to pagination where you want to retrieve data with alimitandoffsetbut also need to know the total number of records that match the query.

The success handler will always receive an object with two properties:

  • count- an integer, total number records (matching the where clause)
  • rows- an array of objects, the records (matching the where clause) within the limit/offset range
Project
  .findAndCountAll({
     where: ["title LIKE 'foo%'"],
     offset: 10,
     limit: 2
  })
  .success(function(result) {
    console.log(result.count);
    console.log(result.rows);
  });

The options [object] that you pass tofindAndCountAll()is the same as forfindAll()(described below).

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!
})
 
// since v1.7.0 we can now improve our where searches
Project.findAll({
  where: {
    id: {
      gt: 6,              // id > 6
      gte: 6,             // id >= 6
      lt: 10,             // id < 10
      lte: 10,            // id <= 10
      ne: 20,             // id != 20
      between: [6, 10],   // BETWEEN 6 AND 10
      nbetween: [11, 15]  // NOT BETWEEN 11 AND 15
    }
  }
})

Complex filtering / OR queries

Since v1.7.0-rc3, it is possible to do complex where queries with multiple levels of nested AND and OR conditions. In order to do that you can use Sequelize.or and Sequelize.and and pass an arbitrary amount of arguments to it. Every argument will get transformed into a proper SQL condition and gets joined with the either AND or OR.

Project.find({
  where: Sequelize.and(
    { name: 'a project' },
    Sequelize.or(
      { id: [1,2,3] },
      { id: { lt: 10 } }
    )
  )
})

This code will generate the following query:

SELECT *
FROM `Projects`
WHERE (
  `Projects`.`name`='a project'
   AND (`Projects`.`id` IN (1,2,3) OR `Projects`.`id` < 10)
)
LIMIT 1
;

Notice, that instead of Sequelize.and you can also use a plain array which will be treated as Sequelize.and if it contains objects or hashes or other complex data types. Furthermore you can use Sequelize.or as value for the where clause.

Manipulating the dataset with limit, offset, order and group

To get more relevant data, you can use limit, offset, order and grouping:

// limit the results of the query
Project.findAll({ limit: 10 })
 
// step over the first 10 elements
Project.findAll({ offset: 10 })
 
// step over the first 10 elements, and take 2
Project.findAll({ offset: 10, limit: 2 })

The syntax for grouping and ordering are equal, so below it is only explained with a single example for group, and the rest for order. Everything you see below can also be done for group

Project.findAll({order: 'title DESC'})
// yields ORDER BY title DESC
 
Project.findAll({group: 'name'})
// yields GROUP BY name

Notice how in the two examples above, the string provided is inserted verbatim into the query, i.e. column names are not escaped. When you provide a string to order / group, this will always be the case as per v 1.7.0. If you want to escape column names, you should provide an array of arguments, even though you only want to order / group by a single column

something.find({
  order: [
    'name',
    // will return `name`
    'username DESC',
    // will return `username DESC` -- i.e. don't do it!
    ['username', 'DESC'],
    // will return `username` DESC
    sequelize.fn('max', sequelize.col('age')),
    // will return max(`age`)
    [sequelize.fn('max', sequelize.col('age')), 'DESC'],
    // will return max(`age`) DESC
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
    // will return otherfunction(`col1`, 12, 'lalala') DESC
    [sequelize.fn('otherfunction', sequelize.fn('awesomefunction', sequelize.col('col'))), 'DESC']
    // will return otherfunction(awesomefunction(`col`)) DESC, This nesting is potentially infinite!
    [{ raw: 'otherfunction(awesomefunction(`col`))' }, 'DESC']
    // This won't be quoted, but direction will be added
  ]
})

To recap, the elements of the order / group array can be the following:

  • String - will be quoted
  • Array - first element will be qouted, second will be appended verbatim
  • Object -
    • Raw will be added verbatim without quoting
    • Everything else is ignored, and if raw is not set, the query will fail
  • Sequelize.fn and Sequelize.col returns functions and quoted cools

Raw queries

Sometimes you might be expecting a massive dataset that you just want to display, without manipulation. For each row you select, Sequelize creates aDAO, 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
})
 
Project.max('age', { where: { age: { lt: 20 } } }).success(function(max) {
  // wil be 10
})

min - Get the least value of a specific attribute within a specific table

And here is a method for getting the min 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.min('age', { where: { age: { gt: 5 } } }).success(function(min) {
  // wil be 10
})

sum - Sum the value of specific attributes

In order to calculate the sum over a specific column of a table, you can use the `sum` method.

/*
  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.sum('age').success(function(sum) {
  // this will return 55
})
 
Project.sum('age', { where: { age: { gt: 5 } } }).success(function(sum) {
  // wil be 50
})

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 sincev1.6.0and is called eager loading. The basic idea behind that, is the use of the attributeincludewhen you are callingfindorfindAll. 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.

If an association is aliased (using theasoption), youmustspecify this alias when including the model. Notice how the user'sTools are aliased asInstrumentsabove. 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
      }]
    }]
  */
})

Ordering Eager Loaded Associations

In the case of a one-to-many relationship.

Company.findAll({ include: [ Division ], order: [ [ Division, 'name' ] ] });
Company.findAll({ include: [ Division ], order: [ [ Division, 'name', 'DESC' ] ] });
Company.findAll({
  include: [ { model: Division, as: 'Div' } ],
  order: [ [ { model: Division, as: 'Div' }, 'name' ] ]
});
Company.findAll({
  include: [ { model: Division, include: [ Department ] } ],
  order: [ [ Division, Department, 'name' ] ]
});

In the case of many-to-many joins, you are also able to sort by attributes in the through table.

Company.findAll({
  include: [ { model: Division, include: [ Department ] } ],
  order: [ [ Division, DepartmentDivision, 'name' ] ]
});

Nested eager loading
User.findAll({
  include: [
    {model: Tool, as: 'Instruments', include: [
      {model: Teacher, include: [ /* etc */]}
    ]}
  ]
}).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": [{ // 1:M and N:M association
        "name": "Toothpick",
        "id": 1,
        "createdAt": null,
        "updatedAt": null,
        "UserId": 1,
        "Teacher": { // 1:1 association
          "name": "Jimi Hendrix"
        }
      }]
    }]
  */
})

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!
© Sascha Depold, et al. 2006 - 2022