NodeJS

How to use Sequelize with Node and Express

NodeJS with Sequelize

If you are willing to use MySQL with your Node App, this guide will show you how to use Sequelize with Node and Express.

In my opinion, Sequilize ORM is the best way to use MySQL with Node. Sequelize offers features like Migrations, Seeders and Relationship management. These features are handy when you are in need of a quick working project structure.

1- Creating project structure using express-generator

Create a directory which will contain the application code. mkdir express-app && cd express-app

Inside the application directory, now run npx express-generator

The application directory will now look like this. This is a well known project structure for any express framework based web application.

While still in the root of project directory, run npm install to install all the packages listed in package.json file.

├── app.js
├── bin
├── node_modules
├── package.json
├── package-lock.json
├── public
├── routes
└── views

2- Installing Sequelize ORM

In order to communicate with MySQL database, The Sequelize ORM depends on mysql2 which is a mysql client for NodeJS.

Let’s first install mysql2 by running npm install --save mysql2. Once the mysql client is installed, We can install Sequelize with npm install --save sequelize

3- Setting Up Sequelize-CLI

As the last step, we will be setting up the Sequelize-CLI package. Sequelize command line interface makes it very easy to create and modify migrations, seeders and models with just few simple commands.

Run npm install --save-dev sequelize-cli to install CLI package. Note that we are saving it as a dev dependency because we will not need it when the application is in production.

We will now use sequelize-cli to convert our project structure to use Sequelize features. npx sequelize-cli init command will automatically create some folders.

The project directory should now look as below:

├── app.js
├── bin
├── config
├── migrations
├── models
├── node_modules
├── package.json
├── package-lock.json
├── public
├── routes
├── seeders
└── views

Note that the npx sequelize-cli init command has added config, migrations, models and seeders directories.

4- Connecting to MySQL database using Sequelize

As we have already generated the project structure with Express, Sequelize and Sequelize-CLI. Let’s open config.json file from config directory.

As you can see that config.json file is very well structured, Just change the parameters in config.json file according to your DB credentials.

It is not necessary that the database should be created before adding it into the config details. However, we should always have a MySQL user with all the privileges. In most cases, you have the database and it’s user available when you are going to deploy your app.

If you are working on local environment and have no issues in creating a user and assign all the privileges (including db creation privilege), You can use following commands while in MySQL terminal as root user:

Once you add all the details in config.json file, following sequelize-cli command can be used to create a DB in MySQL server. Skip this step if you have created the database already.

npx sequelize-cli db:create

You should now be able to connect with MySQL DB using Sequelize ORM.

5- Creating first model and migration in Sequelize

A model is a representation of a DB table in the form of a JS object. In Sequelize ORM, we will use these objects (one for each table) to communicate with the table and performing CRUD operations.

Migrations are like version controlling of your DB, it keeps track of all the changes being made. We will create a migration whenever a table creation or alteration takes place.

Whenever we create a model using Sequelize-cli, the corresponding migration file is also created. As we have connected MySQL with node and express.js, Let’s create our first migration file.

npx sequelize-cli model:generate --name User --attributes first_name:string,last_name:string

By running above command, we should now have a model and it’s migration file created in /models and /migrations directories respectively.

/models/user.js

/migrations/******-create-user.js

Just run npx sequelize-cli db:migrate and you should have a new table created named Users.

Conclusion

In order to work with Sequelize features, We can now use Sequelize-CLI commands in our project. I will share more about migrations, relations and seeders in my upcoming posts.

If you have liked this post, Kindly consider sharing it and also subscribing to this blog.

Tagged , , ,

About Zohaib Shah

Passionate software engineer with expertise in Django, Laravel and NodeJS. Working with different SaaS based products and API connected apps. Software Architect of UK's leading online travel agency. Get in touch
View all posts by Zohaib Shah →

Leave a Reply

Your email address will not be published.