How to Perform CRUD Operations on MySQL With Sequelize

Sequelize is a Node.js Object-Relational Mapper (ORM) that features SOLID transaction support, relations, eager/lazy loading and read replication, among other things.

In this article, you’ll learn how to:

• Connect to a MySQL database using Sequelize.
• Create and synchronise a model.
• Perform CRUD operations on a table.

Getting started

In order to work with a MySQL database, sequelize requires an additional component called a database driver, which is a program that implements a protocol for database connection. In the case of MySQL this driver is called mysql2.

$ npm install --save sequelize mysql2

Connecting to the database

Creating a database handler

The creation of a database handler for MySQL is done by instantiating the Sequelize class exported by the sequelize module.

const { Sequelize } = require('sequelize');

const db = new Sequelize(name, user, password, {
  host,
  port,
  dialect,
  logging
});

Where:

• name is the name of the database.
• user is the name of the database user.
• password is the password to connect to the database.
• host is the IP address of the machine the database is running on (e.g. 127.0.0.1).
• port is the port the database is listening to; the default port for MySQL is 3306.
• dialect is the type of database used; in this case mysql.
• logging is a boolean to activate/deactivate the database operation logs in the console.

For example:

const db = new Sequelize('auth', 'admin', 'admin', {
  host: '127.0.0.1',
  port: 3306,
  dialect: 'mysql',
  logging: true
});

Performing a connection attempt

Once instantiated, we can perform a connection attempt using the asynchronous authenticate() method, that will throw an error in case of connection failure.

await db.authenticate();

Creating a model

What is a model?

A model is an abstraction that represents a table in the database. It tells several things about the entity it represents, such as its name, the name of its columns, their data types and constraints, and so on.

Creating a model

To create a model, we can use the define() method of the database handler we previously created (i.e. db) that takes as arguments:

• A string of character representing the model name.
• An object representing its columns, where each key corresponds to the name of the column, and its properties to its type and constraints.

db.define('table', {
  column: {
    property: value
  }
});

The data-type of a column is defined using the type property whose value must be one of the types exported by the sequelize module. For example, Sequelize.STRING for VARCHAR(255) or Sequelize.INTEGER for INTEGER, etc.

The constraints of a column are defined using properties such as:

• primaryKey to define it as the primary key of the table.
• autoIncrement to increment its value every time a new record is inserted.
• allowNull to allow for a null value.
• unique to guarantee its uniqueness.

Note that the model name should always be defined in the singular form of the word (e.g. user) since Sequelize will automatically create the respective database table in the plural form (e.g. users).

Example

For example, the following User model:

const User = db.define('user', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    allowNull: false,
    autoIncrement: true
  },
  email: {
    type: Sequelize.STRING,
    allowNull: false,
    unique: true
  },
  hash: {
    type: Sequelize.STRING,
    allowNull: false
  }
});

Represents the mapping of the following SQL users table:

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| email | varchar(255) | NO   | UNI | NULL    |                |
| hash  | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

Synchronizing a model

In order for a table to be physically created in the database, we need to synchronize the model with the database instance using its asynchronous sync() method, which can be used in three different ways:

• Calling sync() without arguments will create the table only if it doesn’t already exist.
• Calling sync({ force: true }) will first drop (i.e. delete) the table if it already exists and re-create it.
• Calling sync({ alter: true }) will check the current state of the table (its columns, their data types, etc.) and perform the necessary changes to ensure it matches the current model (i.e. update).

For example:

await User.sync({ alter: true });

Note that this operation is only required when you need to create or update of database table, and should be used with extreme caution when in production.

Performing CRUD operations

Create

To create a record into a table, we can use the asynchronous create() method, which takes as argument an object containing the values of the record, and returns that same record in the case of a successful write operation, or throws an error otherwise.

await User.create({
  email: 'username@domain.ext',
  hash: 'isdf92mXOsdfm23odsU'
});

Which is the equivalent of the following SQL query:

INSERT INTO users VALUES('username@domain.ext', 'isdf92mXOsdfm23odsU');

Read

To read a record from a table, we can use the asynchronous findOne() method, which returns the first entry it finds or a null value otherwise.

It takes an optional query object that allows us to specify which columns we want to fetch using the attributes clause and the criteria of our search using the where clause.

await User.findOne({
  attributes: ['email'],
  where: {
    id: 1
  }
});

Which is the equivalent of the following SQL query:

SELECT email FROM users WHERE id = 1;

Update

To update a record in a table, we have to:

• Fetch the desired record using the findOne() method.
• Update the field(s) of the object returned by the previous method.
• Call the asynchronous save() method.

const user = await User.findOne({
  where: {
    id: 1
  }
});

user.email = 'newusername@domain.ext';
await user.save();

Which is the equivalent of the following SQL query:

UPDATE users SET email = 'newusername@domain.ext' WHERE id = 1;

Delete

To delete a record from a table, we can use the asynchronous destroy() method, which takes as argument an object that allows us to specify which record we want to delete using the where clause.

await User.destroy({
  where: {
    id: 1
  }
});

Which is the equivalent of the following SQL query:

DELETE FROM users WHERE id = 1;

Closing the database connection

In order to prevent a memory leak — which is memory that is no longer needed by a program but not released — we can use the asynchronous close() method of the database handler to close the database connection.

await db.close();