Today, we are going to be going over a very useful tool called Sequelize. It is an Object Relational Mapper (ORM), which allows us to interact with relational databases using Node.js.

Sequelize supports many different databases, but in this blog, we will be using MySQL. We will go over how to set up a local MySQL instance and a Node.js server using Sequelize to communicate with it.

Along the way, we’ll learn basic Sequelize functionality, such as the command line tool and migrations, to understand how to work with the database. The code and applications used here are all done on Mac.

Installing Docker

Before getting started, we will need to install Docker, a platform that allows us to containerize applications and their dependencies. We will be using this in order to install and run a MySQL database.

We will also be using Docker Compose, which should be installed along with the main Docker application. To check the version that was installed, run the following command.

docker-compose version

Docker Compose is a tool for defining and configuring the applications that are running through a YAML file.

Setting Up a Local MySQL Instance

Go ahead and create a new file, docker-compose.yml, in the folder you will be running the project in. Populate that file with the following code.

version: '3.8'

services:
  db:
    image: mysql:8.0
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: the_root_password
    ports:
      - "3306:3306"

This is a very simple Docker Compose file. Basically, we are using the 3.8 file format to create a MySQL service with a specified password to connect.

Now, open up the terminal, navigate to the folder with the newly created file, and run the following command.

docker-compose up -d

It should download what it needs and start the container in docker like so:

Now that the MySQL server is running, let’s try to connect to it. Go here, and download the MySQL workbench. We will use this application to connect to the database. Once you have the workbench installed, open it, set up a new connection, and enter the following.

The password will be “the_root_password” that we set up in the Docker Compose file. Go ahead and connect to the database. It will look something like this:

In the query editor, run this command:

show databases

It will show the default databases that were created.

We will now use Sequelize to create the databases and tables and seed them with some data.

Installing Sequelize

First, install Node.js from here. This will install the NPM that we will use to install Sequelize.

After that, go to the folder where you have the project, and run the following command.

npm install -g sequelize-cli

This command will install the Sequelize command line tools we will be using.

Using Sequelize

After those tools are installed successfully, go ahead and run this command:

sequelize init

It will generate some initial files in your folder and should look something like this:

Find the config.json file, and set the development password property to “the_root_password,” which was what we defined in the docker-compose.yml file.

Now, we will create the database by running:

sequelize db:create

It will create a new database called “database_development,” which was defined in the config file. Before advancing, go ahead and use the MySQL workbench tool to check that the database was created.

Now, let’s create a simple table. We will create a student model like the following.

class Student {
string firstName;
string lastName;
int age;
}

Then, type the following command.

sequelize model:generate --name Student --attributes firstName:string,lastName:string,age:integer

Sequelize will now have generated a model and a migration. The file defines two methods, up and down. The up method will manipulate the database; the down method will roll back the changes if needed.

Once we have these files created, we need to run the migrate command to actually update the database. Run the following.

sequelize db:migrate

Now, we can go to the workbench to check that the table has been created.

Notice that there is also a table called SequelizeMeta. This table is auto-generated by Sequelize. It allows us to keep track of the migrations that have run on the current database.

Now that we have the table created, let’s seed the database with some initial data. Run the following command to create the seed file.

sequelize seed:generate --name students_sample

It should have created a file in the seeders folder. We will now add some sample data in the up method. Replace the code in the newly generated seed file with the following.

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.bulkInsert(
      "Students",
      [
        {
          firstName: "Bruce",
          lastName: "Wayne",
          age: 23,
          createdAt: new Date(),
          updatedAt: new Date(),
        },
        {
          firstName: "Tim",
          lastName: "Drake",
          age: 19,
          createdAt: new Date(),
          updatedAt: new Date(),
        },
      ],
      {}
    );
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.bulkDelete("Students", null, {});
  },
};

In the up method, we are adding two rows of data in the students table. We set all the required data for the class along with the createdAt and updatedAt timestamps, which are columns that are automatically created when we run the migration to create the table.

In the down method, all that’s happening is we delete all the data in the table.

Now run this command:

sequelize db:seed:all

It will populate the students table with two new rows. To see the newly created rows, head over to the MySQL workbench and run the following query.

select * from Students

If we want to undo all the data that we inserted, we can run:

sequelize db:seed:undo

Now, all the inserted data is gone.

The same thing can be done with the students table that we created. Run the following command to undo that migration.

sequelize db:migrate:undo

Conclusion

Migrations allow us to change the structure of the database over time if requirements change. We can create new tables, new columns, remove tables, and much more. Also, if we ever need to roll back the changes for whatever reason, it is quite simple to do.

In addition, Sequelize allows us to define much more complex relationships, such as one-to-many or many-to-many, and to create complex queries that can retrieve data. As you can see, Sequelize is a very useful ORM tool that allows us to manipulate databases in many ways!

I encourage you to try Sequelize today, and if you do, let me know what you think in the comments below. Be sure to head over to the Keyhole Dev Blog for more great content.