Getting Started with Sequelize for Node.js, Express.js and MySQL Applications

Before we start…

Let’s take a moment to review the tools we’re going to be using:

NodeJS: We’re going to use this to run JavaScript code on the server. I’ve decided to use the latest version of Node, v12.16.3 at the time of writing.

Express: As per their website, Express is a “Fast, unopinionated, minimalist web framework for Node.js”.

NPM: for the package management (both server, frontend, and development packages). It’ll be easier to maintain one package management system, than using NPM and Bower together.

MySQL: This is a powerful open-source database that we’re going to use to store our Todos.

Sequelize: In addition, we’re going to use Sequelize, which is a database ORM that will interface with the Mysql database for us.

Postman: A Chrome app that we’ll use to practically test our API.

Let’s start!

Assuming you’ve already installed Node.js, create a directory to hold your application and make that your working directory.

Let’s npm init before installing our packages!

$ npm init

Express Setup:

Install Express and a few of its dependencies

$ npm install -save express body-parser morganapp.js.

Let’s install nodemon so we can restart our app every time when we update it

npm install nodemon

Then, open up your package.json file and create a command to run the server. That command will be created under the scripts section. Edit your package.jsonin the scripts section as follows:

......
"scripts": {
    "start": "nodemon ./index.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
.....

Sequelize Setup

For this part, we are going to require a working MySQL installation.

Next, we are going to require Sequelize. This is an ORM that will interface with the MYSQL database for us.

We are going to be making use of the Sequelize CLI package to bootstrap the project for us. It will also help us generate database migrations.

Let’s begin by installing Sequelize CLI package.

$ npm install -g sequelize-cli

Install sequelize

At this point, we are going to need to install the actual Sequelize package, alongside its dependencies.

Sequelize is an easy-to-use multi SQL dialect ORM for Node.js. We gonna use MySQL as our database. So let install Sequelize ORM and mysql2 dialect.

$ sequelize init // final, Initializes project with sequelize cil

If you inspect your directory right now, you will realize that the above command just created the directories and generated the boilerplate code. Your directory structure should now look like this.

sequelizeSetup
── index.js
├── package.json
├── config
│   └── config.json
├── migrations
├── models
│   └── index.js
└── seeders

Let’s consider, for example, the sequelizeSetup/models/index.js file that was autogenerated.

In this file, we are requiring the modules we’re going to be using. Then, we’re reading the configuration specific to our current Node environment. If we don’t have a Node environment defined, we’re defaulting to development. Then, we are establishing a connection with our database, after which we read our models folder.

Database Configurations

Now config.json file which is located at config\config.json and update your database details.

Creating the first Model (and Migration)

Once we have properly configured CLI config file we are ready to create our first migration. It’s as simple as executing a simple command.

We will use model:generate command. This command requires two options:

• name: the name of the model;
• attributes: the list of model attributes.

Let’s create a model named User.

npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string,email:string

This will generate a user.js file in the sequelizeSetup/models folder as well as a <date>-create-company.js migration file in the sequelizeSetup/migrations folder. <date> will be the date the model was generated.

The generated user.js model code is:

and user migration code is:

When we run these migrations, the up function will be executed. It will take care of creating the table and it's associated columns for us. If, for whatever reason, we needed to rollback (undo) the migration, the down function would be executed and it would undo whatever the up function did, thus returning the our database to the same state it was in before we performed the migration.

These migrations are a representation of how we want our models to look like in the database. Sequelize automatically generates the id, createdAt and updatedAt fields for you. In addition to that, any time a model is saved, the updatedAt field is automatically updated to reflect the new update time.

With the models and migrations in place, we’re now ready to persist the models to the database by running the migrations. To do this, we run the following command:

$ sequelize db:migrate

This will discover the migrations in our migrations folder and execute them. If you try running the same command again, it would not execute any migrations since it’s clever enough to know that all of the current migrations have been executed.

Creating Controllers and Routing

Creating User Controller

Create a user.js file inside sequelizeSetup/controllers/. Inside this file.userController will be responsible for creating, listing, updating and deleting company.

Let create a user. sequelizeSetup/controllers/user.js

The above code snippet creates a new user and if successful, it returns it. If it encounters an error, it returns that error instead.

This create function is designed to be a route handler for whichever Express route we'll choose to attach it to. The req parameter is the incoming request from the client. The res parameter is the response we're preparing to eventually send back to the client in response to their request.

Next, we create an index.js file inside sequelizeSetup/controllers, where we're going to be exporting our controllers from. I find this helpful since it helps me consolidate my imports (require statements) from once central place.

server/controllers/index.js

const user = require('./user');module.exports = {
       user
   }

Let’s consume the API calls with Express

Next, we need to add an API route that maps to this functionality. Create a routesfolder inside the sequelizeSetup folder. Inside the new routes folder, create an index.jsfile. We are going to place all our routes in this index.js file. However, in a real-world application, you might want to split up your routes and place them in different folders.

Inside sequelizeSetup/routes/index.js add the following code:

This will add two new routes, a welcome route at /api and a route to create user at /api/user.

If we post some data to /api/user, we are telling our application to run the userController.create function, which will take the request object, extract the posted data and create a user. In this case, we say that the userController.create function is the POST route handler for the /api/user endpoint.

Let’s create our server

Create a file in the root folder and call it index.js.

const express = require('express');
const logger = require('morgan');
const bodyParser = require('body-parser');
// This will be our application entry. We'll setup our server here.
const http = require('http');
// Set up the express app
const app = express();
// Log requests to the console.
app.use(logger('dev'));
// Parse incoming requests data (https://github.com/expressjs/body-parser)
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
// Setup a default catch-all route that sends back a welcome message in JSON format."
var models = require('./models');
const port = parseInt(process.env.PORT, 10) || 3000;
app.set('port', port);
console.log("server started on ",port)
models.sequelize.sync().then(function () {
app.listen(port);
});
require('./routes')(app);
app.get('*', (req, res) => res.status(200).send({
message: 'Welcome to the beginning of nothingness.',
}));
module.exports = app;

We are in the last step.

now run the application by below command.
$ npm start

our application server started on port no 3000.

TESTING WITH POSTMAN

Next, we open up Postman and issue a POST request to create a new user as in the image below.

Create User: POST http://localhost:3000/api/user

CONCLUSION

We just successfully setup sequelize in our application.
If you came thus far congrats, ✌.
Surely, you’ve got questions or issues as you went through this tutorial, kindly drop your comments and you’d be responded to ASAP.
Once again thanks for reading, Clap, and share!👌

GitHub: Checkout the complete code here