Getting Started with Node, Express and Mysql Using Sequelize

10 min readJun 19, 2018

Required Tool

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, v6.3.0 at the time of writing, so that we’ll have access to most of the new features introduced in ES6.

Express : As per their website, Express is a “Fast, unopinionated, minimalist web framework for Node.js”, that we’re going to be building our Todo list application on.

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.

Project Setup

Let’s begin by setting up our workspace.

You all are familiar with NPM. Before setup the project, open the terminal and check node and npm version. If version is displaying its means node and npm installed. If not then you must have to install the node and npm.

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

$ mkdir myapp$ cd myapp

Use the npm init command to create a package.json file for your application. For more information on how package.json works, see Specifics of npm’s package.json handling.

$ npm init

This command prompts you for a number of things, such as the name and version of your application. For now, you can simply hit RETURN to accept the defaults for most of them, with the following exception:

entry point: (index.js)

Enter app.js, or whatever you want the name of the main file to be. If you want it to be index.js, hit RETURN to accept the suggested default file name.

Express Setup

Install Express and a few of it’s dependencies.

$ npm install --save express body-parser morgan

The --save flag will save these packages to the dependencies section of your package.json file.

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

In this file, let’s create our Express application.

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 appconst 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.app.get('*', (req, res) => res.status(200).send({message: 'Welcome to the beginning of nothingness.',}));const port = parseInt(process.env.PORT, 10) || 8000;app.set('port', port);const server = http.createServer(app);server.listen(port);module.exports = app;now run the application by below command.$ npm start

The application will be run scuccessfully with port 8000

With that in place, we’ll need a way to restart the server every time we change something in our code. For that, we’ll use the excellent nodemon npm package.

$ npm i -D 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:dev": "nodemon ./bin/www","test": "echo \"Error: no test specified\" && exit 1"},....

Now try running the application by executing

$ npm start

and visiting http://localhost:8000. You should see {“message”:”Welcome to the beginning of nothingness.”}

At this point in time, your project structure should look like:

myApp├── app.js├── package.json└── node_modules

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

You can install the sequelize-cli package in your project locally by using -D(equivalent to using --save-dev) instead of the -g (--global) flag.

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.

$ npm install --save sequelize$ npm install --save mysql2

Initializes sequelize

After installation, let use the CLI to generate migrations, seeders, config and models directories and config file

$ 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.

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

Let’s consider, for example, the server/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, discovering and importing any and all the models in it, adding them to the db object and applying relationships between the models, if such relationships exist.

If you are windows user correct the config file path in models/index.js by changing
var config = require(__dirname + '/..\config\config.json')[env]; to var config = require(__dirname + '/../config/config.json')[env];

Database Configurations

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

Generating Model & Migrations

Now again we gonna use sequelize cli command to generate model and migrations files.

We are going to have two models, Company and Employee.

The relationship between a Company it's Employee is going to be one-to-many, such that a Company can have many Employees while a Employee can only belong to one Company .

Run the following command.

$ sequelize model:create --name Company --attributes name:string

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

The generated company model code is:

Here is the generated model code, you can add or remove columns to it, make sure to update migration file as for your changes on this model.

Project_Root/migrations/<date>-create-company.js

Here is the generated migration code, you can add or remove columns to it, make sure to update model file as for your changes on this migration file.

Now lets generate Employee Model.

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

We are also going to be defining the relationships between our models(Company and Employee). One Company can hired many employees.(one to many relationship).

we arrive at: server/models/company.js

Notice that we edited the name field and added a not-null constraint. This means that the database will not allow us to write to it if we don't provide a value for the name field. We also defined the relationship between a Companyand it's Employee in the Company.associate class method. The as: 'employees' means that every time we query for a company and include it's employee details, they'll be included under the key employees instead of Employees (Sequelize defaults to using the pluralized model name). We're going to see how to make that inclusion a little later. Personally, I think it looks better this way.

server/models/employee.js

Notice that we’ve edited both the name and designamtion fields. We've added a not-null constraint in the name field and a default value for the designation field. In general, having a default value means that if we don't provide a value for that field when creating it, the database is going to use the provided default value for that field. In addition to that, we've also defined the relationship between the Employees and the Company objects. The onDelete: CASCADE tells Postgres that if we delete a Company, it's associated Employees Details should be deleted as well (cascade the delete action).

Now, We have to update migration file as for your changes on this model.

server/migrations/<date>-create-company.js

server/migrations/<date>-create-employee.js

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. Notice we define the relationship between our models in the create-employee.js migration file as well. The companyId field was not automatically generated and we've had to manually define it. 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 Company Controller

Create a company.js file inside server/controllers/. Inside this file.

companyController will be responsible for creating, listing, updating and deleting company.

Let create company.

server/controllers/company.js

The above code snippet creates a new companu 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 :). All Express route handlers follow this method signature. We can have a third parameter, conventionally named next, which is a function that passes the request on to the next route handler (meaning that a route can be handled by multiple route handlers, in which case it's piped or passed along all of those route handlers). We are, however, not going to see a use case for that in this application :(.

Next, we create an index.js file inside server/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

Implementing the API calls with Express

Next, we need to add an API route that maps to this functionality. Create a routesfolder inside the server 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 then in different folders.

Inside server/routes/index.js, add the following code:

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

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

Next, we need to make the application aware that we just added the routes. Open up your app.js. We're going to be adding a require statement right before the route we'd earlier created, such that our app.js file now looks like:

app.js