Basic usage

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

  1. const sequelize = new Sequelize('database', 'username', 'password', {
  2.   dialect: 'mysql'
  3. });

This will save the passed database credentials and provide all further methods.

Furthermore you can specify a non-default host/port:

  1. const sequelize = new Sequelize('database', 'username', 'password', {
  2.   dialect: 'mysql',
  3.   host: "my.server.tld",
  4.   port: 9821,
  5. })

If you just don't have a password:

  1. const sequelize = new Sequelize({
  2.   database: 'db_name',
  3.   username: 'username',
  4.   password: null,
  5.   dialect: 'mysql'
  6. });

You can also use a connection string:

  1. const sequelize = new Sequelize('mysql://user:pass@example.com:9821/db_name', {
  2.   // Look to the next section for possible options
  3. })

Options

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

  1. const sequelize = new Sequelize('database', 'username', 'password', {
  2.   // the sql dialect of the database
  3.   // currently supported: 'mysql', 'sqlite', 'postgres', 'mssql'
  4.   dialect: 'mysql',
  6.   // custom host; default: localhost
  7.   host: 'my.server.tld',
  9.   // custom port; default: dialect default
  10.   port: 12345,
  12.   // custom protocol; default: 'tcp'
  13.   // postgres only, useful for Heroku
  14.   protocol: null,
  16.   // disable logging; default: console.log
  17.   logging: false,
  19.   // you can also pass any dialect options to the underlying dialect library
  20.   // - default is empty
  21.   // - currently supported: 'mysql', 'postgres', 'mssql'
  22.   dialectOptions: {
  23.     socketPath: '/Applications/MAMP/tmp/mysql/mysql.sock',
  24.     supportBigNumbers: true,
  25.     bigNumberStrings: true
  26.   },
  28.   // the storage engine for sqlite
  29.   // - default ':memory:'
  30.   storage: 'path/to/database.sqlite',
  32.   // disable inserting undefined values as NULL
  33.   // - default: false
  34.   omitNull: true,
  36.   // a flag for using a native library or not.
  37.   // in the case of 'pg' -- set this to true will allow SSL support
  38.   // - default: false
  39.   native: true,
  41.   // Specify options, which are used when sequelize.define is called.
  42.   // The following example:
  43.   //   define: { timestamps: false }
  44.   // is basically the same as:
  45.   //   sequelize.define(name, attributes, { timestamps: false })
  46.   // so defining the timestamps for each model will be not necessary
  47.   define: {
  48.     underscored: false
  49.     freezeTableName: false,
  50.     charset: 'utf8',
  51.     dialectOptions: {
  52.       collate: 'utf8_general_ci'
  53.     },
  54.     timestamps: true
  55.   },
  57.   // similar for sync: you can define this to always force sync for models
  58.   sync: { force: true },
  60.   // pool configuration used to pool database connections
  61.   pool: {
  62.     max: 5,
  63.     idle: 30000,
  64.     acquire: 60000,
  65.   },
  67.   // isolation level of each transaction
  68.   // defaults to dialect default
  69.   isolationLevel: Transaction.ISOLATION_LEVELS.REPEATABLE_READ
  70. })

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 or 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 by database backend).

  1. const sequelize = new Sequelize('database', null, null, {
  2.   dialect: 'mysql',
  3.   port: 3306
  4.   replication: {
  5.     read: [
  6.       { host: '8.8.8.8', username: 'read-username', password: 'some-password' },
  7.       { host: '9.9.9.9', username: 'another-username', password: null }
  8.     ],
  9.     write: { host: '1.1.1.1', username: 'write-username', password: 'any-password' }
  10.   },
  11.   pool: { // If you want to override the options used for the read/write pool you can do so here
  12.     max: 20,
  13.     idle: 30000
  14.   },
  15. })

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. Internally Sequelize will maintain two pools created using pool configuration.

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

Each write or useMaster: true query will use write pool. For SELECT read pool will be used. Read replica are switched using a basic round robin scheduling.

Dialects

With the release of Sequelize 1.6.0, the library got independent from specific dialects. This means, that you'll have to add the respective connector library to your project yourself.

MySQL

In order to get Sequelize working nicely together with MySQL, you'll need to installmysql2@^1.0.0-rc.10or higher. Once that's done you can use it like this:

  1. const sequelize = new Sequelize('database', 'username', 'password', {
  2.   dialect: 'mysql'
  3. })

Note: You can pass options directly to dialect library by setting thedialectOptions parameter. See Optionsfor examples (currently only mysql is supported).

SQLite

For SQLite compatibility you'll needsqlite3@~3.0.0. Configure Sequelize like this:

  1. const sequelize = new Sequelize('database', 'username', 'password', {
  2.   // sqlite! now!
  3.   dialect: 'sqlite',
  5.   // the storage engine for sqlite
  6.   // - default ':memory:'
  7.   storage: 'path/to/database.sqlite'
  8. })

Or you can use a connection string as well with a path:

  1. const sequelize = new Sequelize('sqlite:/home/abs/path/dbname.db')
  2. const sequelize = new Sequelize('sqlite:relativePath/dbname.db')

PostgreSQL

The library for PostgreSQL ispg@^5.0.0 || ^6.0.0 You'll just need to define the dialect:

  1. const sequelize = new Sequelize('database', 'username', 'password', {
  2.   // gimme postgres, please!
  3.   dialect: 'postgres'
  4. })

MSSQL

The library for MSSQL istedious@^1.7.0 You'll just need to define the dialect:

  1. const sequelize = new Sequelize('database', 'username', 'password', {
  2.   dialect: 'mssql'
  3. })

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:

  1. // Arguments for raw queries
  2. sequelize.query('your query', [, options])
  4. // Quick example
  5. sequelize.query("SELECT * FROM myTable").then(myTableRows => {
  6.   console.log(myTableRows)
  7. })
  9. // If you want to return sequelize instances use the model options.
  10. // This allows you to easily map a query to a predefined model for sequelize e.g:
  11. sequelize
  12.   .query('SELECT * FROM projects', { model: Projects })
  13.   .then(projects => {
  14.     // Each record will now be mapped to the project's model.
  15.     console.log(projects)
  16.   })
  19. // Options is an object with the following keys:
  20. sequelize
  21.   .query('SELECT 1', {
  22.     // A function (or false) for logging your queries
  23.     // Will get called for every SQL query that gets send
  24.     // to the server.
  25.     logging: console.log,
  27.     // If plain is true, then sequelize will only return the first
  28.     // record of the result set. In case of false it will all records.
  29.     plain: false,
  31.     // Set this to true if you don't have a model definition for your query.
  32.     raw: false,
  34.     // The type of query you are executing. The query type affects how results are formatted before they are passed back.
  35.     type: Sequelize.QueryTypes.SELECT
  36.   })
  38. // Note the second argument being null!
  39. // Even if we declared a callee here, the raw: true would
  40. // supersede and return a raw object.
  41. sequelize
  42.   .query('SELECT * FROM projects', { raw: true })
  43.   .then(projects => {
  44.     console.log(projects)
  45.   })

Replacements in a query can be done in two different ways, either usingnamed parameters (starting with :), or unnamed, represented by a ?

The syntax used depends on the replacements option passed to the function:

  • If an array is passed, ? will be replaced in the order that they appear in the array
  • If an object is passed, :key will be replaced with the keys from that object.If the object contains keys not found in the query or vice versa, an exceptionwill be thrown.
  1. sequelize
  2.   .query(
  3.     'SELECT * FROM projects WHERE status = ?',
  4.     { raw: true, replacements: ['active']
  5.   )
  6.   .then(projects => {
  7.     console.log(projects)
  8.   })
  10. sequelize
  11.   .query(
  12.     'SELECT * FROM projects WHERE status = :status ',
  13.     { raw: true, replacements: { status: 'active' } }
  14.   )
  15.   .then(projects => {
  16.     console.log(projects)
  17.   })

One note: If the attribute names of the table contain dots, the resulting objects will be nested:

  1. sequelize.query('select 1 as `foo.bar.baz`').then(rows => {
  2.   console.log(JSON.stringify(rows))
  4.   /*
  5.     [{
  6.       "foo": {
  7.         "bar": {
  8.           "baz": 1
  9.         }
  10.       }
  11.     }]
  12.   */
  13. })