Sequelize is a promise-based ORM for Node.

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
const Sequelize = require('sequelize')
const sequelize = new Sequeslize('database', 'username', 'password')

const User = sequelize.define('user', {
  username: Sequelize.STRING,
  birthday: Sequelize.DATE,
})

sequelize.sync().then(() => {
  User.create({
    username: 'Jane',
    birthday: new Date(1980, 5, 2),
  })
}).then(jane => {
  console.log('create successfully')
})

Start

Installation

1
2
3
4
5
6
yarn add sequelize
# add one of the following:
yarn add pg pg-hstore
yarn add mysql2
yarn add sqlite3
yarn add tedious // MSSQL

Setup a connection

Sequelize will setup a connection pool on initialization so you should ideally only ever create one instance per database if you’re connecting to the DB from a single process. If you’re connecting to the DB from multiple processes, you’ll have to create one instance per process, but each instance should have a maximum connection pool size of “max connection pool size divided by number of instances”. So, if you wanted a max connection pool size of 90 and you have 3 worker process, each process’s instance should have a max connection pool size of 30.

1
2
3
4
5
6
7
8
9
10
11
12
13
const Conn = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql'|'sqlite'|'postgres'|'mssql',
  pool: {
    max: 5,
    min: 0,
    idle: 10000,
  },
  storage: 'path/to/database.sqlite',
})

// or you can simply use a connection uri
const Conn = new Sequelize('postgres://user:[email protected]:5432/dbname')

Test the connection

You can use the .authenticate() function like this to test the connection.

1
2
3
4
5
Conn.authenticate().then(() => {
  console.log('Connection has been established successfully')
}).catch(e => {
  console.error('Unable to connect to the database: ', e)
})

Model

Models are defined with Conn.define('name', {attributes}, {options})

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
const User = Conn.define('user', {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  }
})

// force: true will drop the table if it already exists
User.sync({ force: true }).then(() => {
  // Table created
  return User.create({
    firstName: 'John',
    lastName: 'Hank',
  })
})

Query

1
2
3
User.findAll().then(users => {
  console.log(users)
})

Application wide model options

The Sequelize constructor takes a define option which will be used as the default options for all defined models.

1
2
3
4
5
6
7
8
9
10
const Conn = new Sequelize('connectionUri', {
  define: {
    timestamps: false, // true by default
  }
})

const User = Conn.define('user', {}) // timestamps is false by default
const Post = Conn.define('post', {}, {
  timestamps: true,
})

Promises

Sequelize uses promises to control async control-flow.

Basically, a promise represents a value which will be present at some point.

1
2
user = await User.fineOne()
console.log(user)

Model Definition

To define mapping 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.

1
2
3
4
5
6
7
8
9
10
const Project = Conn.define('project', {
  title: Sequelize.STRING,
  description: Sequelize.TEXT,
})

const Task = Conn.define('tastk', {
  title: Sequelize.STRING,
  description: Sequelize.TEXT,
  deadline: Sequelize.DATE,
})

You can also set some options on each column:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
const Foo = Conn.define('foo', {
  // instantiating will automatically set the flat to true if not set
  flag: {
    type: Sequelize.BOOLEAN,
    allowNull: false,
    defaultValue: true,
  },
  myDate: {
    type: Sequelize.DATE,
    defaultValue: Sequelize.NOW,
  },
  title: {
    type: Sequelize.STRING,
    allowNull: false,
  },
  uniqueOne: {
    type: Sequelize.STRING,
    unique: 'compositeIndex',
  },
  uniqueTwo: {
    type: Sequelize.INTEGER,
    unique: 'compositeIndex',
  },
  foo: {
    type: Sequelize.STRING,
    validate: {
      isEmail: true,
    }
  }
})

Configuration

You can also influence the way Sequelize hadnles your column names:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
const Bar = Conn.define('bar', {
  /* ... */
}, {
  // 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 data(when deletion was done)
  paranoid: true,
  // don't use camelcase for automatically added attributes but underscore style so updatedAt will be udpated_at
  underscore: true,
  // disable the modification of table names; by default, sequelize will automatically transform all passes 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',
  // Enable optimistic locking. When enabled, sequelize will add a version count attribute to the model and throw an OptimisticLockingError error when stale instances are saved.
  version: true,
})

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.

1
2
3
4
5
6
7
8
9
10
// in your server file
const Project = Conn.import(__dirname, "/path/to/models/project")

// The model definition file
module.exports = (Conn, DataType) => {
  return Conn.define('project', {
    name: DataTypes.STRING,
    description: DataTypes.TEXT,
  })
}

The import method can also accept a callback as an argument

1
2
3
4
5
6
Conn.import('project', (Conn, DataTypes) => {
  return Conn.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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// Create the Tables
Project.sync()
Task.sync()

// Force the Creation
Project.sync({ force: true }) // this will drop the table first and re-create it afterwards

// Drop the Tables
Project.drop()
Task.drop()

// Event Handling
Project.[sync|drop]().then(() => {
  // ok ... everything is nice
}).catch(err => {
  // ooh, did you enter 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// sync all models that aren't already in the database
Conn.sync()

// Force sync all models
Conn.sync({ force: true })

// Drop all Tables
Conn.drop()

// Emit Handling
Conn.[sync|drop]().then(() => {
  // ...
}).catch(err => {
  // ...
})

Expansion of models

Sequelize Models are ES6 classes, you can very easily add custom instance or class level methods.

1
2
3
4
5
6
7
8
9
10
11
12
13
const User = Conn.define('user', {
  firstName: Sequelize.STRING,
})

// add a class level method
User.classLevelMethod = function () {
  retunr 'foo'
}

// add an instance level method
User.prototype.instanceLevelMethod = function () {
  retunr 'bar'
}

Indexes

Sequelize supports adding indexes to the model definition which will be created during Model.sync() or Conn.sync()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Conn.define('user', {}, {
  indexes: [
    // Create a unique index on poem
    {
      unique: true,
      fields: ['poen']
    },
    // create a gin index on data with the jsonb_path_ops operator
    {
      fields: ['data'],
      using: 'gin',
      operator: 'jsonb_path_ops',
    },
    // by default index name will be [table]_[fields]
    // create a multi column partial index
    {
      name: 'public_by_author',
      fields: ['author', 'status'],
      where: {
        status: 'public'
      }
    }
  ]
})

Model Usage

Data Retrieval / Finders

Finder methods are intended to query data from the database. They do not return plain objects but instead return model instances. Because finder methods return model instances you can call any model instance member.

find - search for one specific element in the database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// search for known id
Project.findById(123).then(project => {
  // project will be an instance of Project and store the content of the table entry with id 123. If such an entry is not defined you will get null
})

// search for attributes
Project.findOne({
  where: {
    title: 'aProject'
  }
}).then(project => {
  // project will be the first entry of the Projects table with the title 'aProject' || null
})

Project.findONe({
  where: {
    title: 'aProject',
  },
  attributes: ['id', ['name', 'title']]
}).then(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 already exists in the database. If that is the case the method will result in a respective instance, if the element does not yet exists, it will be created.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
User.findOrCreate({
  where: {
    username: 'sdepold',
  },
  defaults: {
    job: "Technical Lead JS",
  }
}).spread((user, created) => {
  console.log(user.get({
    plain: true,
  }))
  /**
   * findOrCreate returns an array containing the object that was found or created and a boolean will be true if a new object was created, and false if not
    [ {
      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)
    },
    true ]
   */
})

The code created a new instance.

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

findAll - Search for multiple elements in the database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
// find multiple entries
Project.findAll().then(projects => {
  // projects will be an array of all Project instances
})

// also possible
Project.all().then(projects => {
  // projects will be an array of all Project instances
})
// search for specific attribute - hash usage
Project.findAll({
  where: {
    name: 'A Project',
  }
}).then(projects => {
  // ...
})
// search within a specific range
Project.findAll({
  where: {
    id: [1, 2, 3],
  }
}).then(projects => {
  // return instances with id 1, 2, 3
})

Complex filtering /OR/NOT queries

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Project.findAll({
  where: {
    name: 'a project',
    $or: [
      { id: [1, 2, 3] },
      { id: { $gt: 10 }},
    ]
  }
})

Project.findAll({
  where: {
    name: 'a project',
    id: {
      $or: [
        [ 1, 2, 3],
        { $gt: 10 },
      ]
    }
  }
})

Both pieces of code will generate the following:

1
2
3
4
SELECT * FROM `Projects` WHERE (
  `Projects`.`name`=`a project`
  AND (`Projects`.`id` IN (1,2,3) OR `Projects`.`id`>10)
  )

Manipulating the database with limit, offset, order and group

1
2
3
4
5
6
7
8
// 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 element 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.

1
2
3
4
5
// yields ORDER BY title DESC
Project.findAll({ order: 'title DESC' })

// yields GROUP BY name
Project.findAll({ group: 'name' })

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 an instance with functions for update, delete, get association 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.

1
2
3
4
5
6
7
// Are you expecting a massive 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 insetead:
Project.findAll({
  where: {
    // ...
  },
  raw: true,
})

Queries

Attributes

To select only some attributes, you can use the attributes option. Most often, you pass an array:

1
2
3
4
5
Model.findAll({
  attributes: ['foo', 'bar']
})
// Equal to
SELECT foo, bar ...

Where

Whether you are querying with findAll/find or doing bulk updates/destroy you can pass a where object to filte the query.

where generally takes an object from attribute:value pairs, where value can be primitive for equality matches or keyed objects for other operators.

It’s also possible to generate complex AND/OR conditions by nesting of $or and $and

Basics