Introduction

Sequelize has been one of the leading ORMs for Node.js. Having objects to match your database schema with a variety of utilities out of the box, means you’re sprinting in development. There is also a Sequelize CLI tool to help with creating migrations, seeds, & more. ORMs work great for scalability & organization. We enjoy keeping projects consistent to eliminate confusion for developers when switching code bases.
The following tutorial is how to integrate the Sequelize ORM into your Node + Express project.
Stack:

Directory Structure

We’ll be continuing with using a simple Web Application with NodeJS & Express. The directory structure is as follows.
tutorialProject/
├── app.js
├── bin
├── node_modules
├── package.json
├── package-lock.json
├── public
├── routes
└── views

Initializing Sequelize

Step 1) Install Sequelize

Note: We’ll be using MariaDB for this example so make sure to install the mysql2 package. Check out our Docker + MariaDB tutorial on how we quickly launch development databases.
npm install --save sequelize sequelize-cli mysql2

./node_modules/.bin/sequelize

Sequelize CLI [Node: 9.11.2, CLI: 5.4.0, ORM: 4.41.2]

Step 2) Init Sequelize

./node_modules/.bin/sequelize init

Sequelize CLI [Node: 9.11.2, CLI: 5.4.0, ORM: 4.41.2]

Created "config/config.json"
Successfully created models folder at "tutorialProject/models".
Successfully created migrations folder at "tutorialProject/migrations".
Successfully created seeders folder at "tutorialProject/seeders".
What’s happening here?
As we can see, the Sequelize CLI tool initiated our project by creating ‘config’, ‘models’, ‘migrations’ & ‘seeders’ directories. We personally like to move these into a separate ‘database’ folder, however, we’ll skip this for simplicities’ sake.

Step 3) The Sequelize Init Script

Sequelize init creates an index.js in the ‘models’ folder. This script initializes any models that are created. We’ve broken down the script with comments to explain what exactly is happening here.
Note: We’ll go into more detail on the models & associations in a later tutorial post.
'use strict';

// NodeJS file system manipulation package.
const fs = require('fs');
// NodeJS path package.
const path = require('path');
// Sequelize package.
const Sequelize = require('sequelize');
// File currently being processed.
const basename = path.basename(__filename);
// Our environment, default to 'development'.
const env = process.env.NODE_ENV || 'development';
// Our config.json file created earlier.
const config = require(__dirname + '/../config/config.json')[env];
// Let's reference our models in this object.
const db = {};

/** 
 * Initialize Sequelize with our environment configuration. 
 * Sequelize checks if the 'use_env_variable' is in the config.
 * This allows the user to specifiy a connection string using an
 * environment variable.
 * Otherwise, use the default Sequelize initialization.
 */
let sequelize;
if (config.use_env_variable) {
  sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
  sequelize = new Sequelize(config.database, config.username, config.password, config);
}
/**  
 * 1) Read in each file from the models directory.
 * 2) Filter out the current script, & any none .js files.
 * 3) Initialize each model & store them into our db object.
 */
fs
  .readdirSync(__dirname)
  .filter(file => {
    return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js');
  })
  .forEach(file => {
    const model = sequelize['import'](path.join(__dirname, file));
    db[model.name] = model;
  });

/** 
 * 1) Iterate each model initialized before.
 * 2) Run any associations that need to be configured.
 */
Object.keys(db).forEach(modelName => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

// Export our configured sequelize module.
db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

Connecting To The Database

Step 1) Configure Sequelize

In the previous step, Sequelize created a ‘config’ folder which contains a config.json file with sections for development, testing, & production. Sequelize uses this config.json file by default.
Go ahead & configure the database credentials in the development section. Sequelize uses this by default when no environment is specified.
"development": {
    "username": "notroot",
    "password": notroot,
    "database": "db",
    "host": "127.0.0.1",
    "dialect": "mysql"
 }

Step 2) Testing The Connection

Since we don’t have any migrations yet, a quick test we like to run is with the ‘db:migrate” command. Note that this will create the SequelizeMeta table, this keeps tracks of our migrations.
./node_modules/.bin/sequelize db:migrate

Sequelize CLI [Node: 9.11.2, CLI: 5.4.0, ORM: 4.41.2]

Loaded configuration file "config/config.js".
Using environment "development".
Executed (default): SELECT 1+1 AS result
Executed (default): SHOW TABLES;
Executed (default): DESCRIBE `SequelizeMeta`;
Executed (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;
Executed (default): SHOW INDEX FROM `SequelizeMeta`
Executed (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;
No migrations were executed, database schema was already up to date.

If your connection did not succeed, we should receive the following error message. In this case, the error message signifies that the connection was refused. There could be many other reasons why the connection didn’t succeed, such as having the wrong port or credentials.
ERROR: connect ECONNREFUSED 127.0.0.1:3306

Integrate With Express

Step 1) Modify Express /bin/www Script.

In our Express launch script, let’s get Sequelize to initialize!
The only thing needed to be done here is to make sure we import our Sequelize initialization script & run the synchronization process.
#!/usr/bin/env node

/**
 * Module dependencies.
 */
const app = require('../app');
const debug = require('debug')('express-sequelize');
const http = require('http');
// Import initialization script here.
const models = require('../models/sequelize.js');

/**
 * Get port from environment and store in Express.
 */
const port = normalizePort(process.env.PORT || '3000');
app.set('port', port);

/**
 * Create HTTP server.
 */
const server = http.createServer(app);

// Add sync call here, then initialize Express Server after.
models.sequelize.sync().then(function () {
  /**
   * Listen on provided port, on all network interfaces.
   */
  server.listen(port, function () {
    debug('Express server listening on port ' + server.address().port);
  });
  server.on('error', error => {
    if (error.syscall !== 'listen') {
      throw error;
    }

    const bind = typeof port === 'string' ? 'Pipe ' + port : 'Port ' + port;

    // handle specific listen errors with friendly messages
    switch (error.code) {
      case 'EACCES':
        console.error(bind + ' requires elevated privileges');
        process.exit(1);
        break;
      case 'EADDRINUSE':
        console.error(bind + ' is already in use');
        process.exit(1);
        break;
      default:
        throw error;
    }
  });
  server.on('listening', () => {
    const addr = server.address();
    const bind =
      typeof addr === 'string' ? 'pipe ' + addr : 'port ' + addr.port;
    debug('Listening on ' + bind);
  });
});

/**
 * Normalize a port into a number, string, or false.
 */
function normalizePort(val) {
  var port = parseInt(val, 10);

  if (isNaN(port)) {
    // named pipe
    return val;
  }

  if (port >= 0) {
    // port number
    return port;
  }

  return false;
}

Our First Model

Step 1) Creating The Model.

With our Sequelize CLI tools handy, let’s go ahead & use it to generate our first model.

./node_modules/.bin/sequelize model:generate --name User --attributes email:string,username:string,password:string

Sequelize CLI [Node: 9.11.2, CLI: 5.4.0, ORM: 4.41.2]

New model was created at /tutorialProject/models/user.js .
New migration was created at /tutorialProject/migrations/20181203173724-User.js .

As you can see, Sequelize created 2 separate files. Our user.js model script & a migrations script to create it in our database. We won’t go into too many details here, as we’ll be covering this more in a later tutorial post.
Our User model was created with email, username, & password string attributes.

Step 2) Migrate!

./node_modules/.bin/sequelize db:migrate

Sequelize CLI [Node: 9.11.2, CLI: 5.4.0, ORM: 4.41.2]

Loaded configuration file "config/config.json".
Using environment "development".
sequelize deprecated String based operators are now deprecated. Please use Symbol based operators for better security, read more at http://docs.sequelizejs.com/manual/tutorial/querying.html#operators node_modules/sequelize/lib/sequelize.js:242:13
== 20181203173724-create-user: migrating =======
== 20181203173724-create-user: migrated (0.177s)
As we can see, Sequelize successfully migrated our User.

Step 3) Testing 1…2

Let’s make sure Sequelize created the Users table in the database. You’ll notice that it also populated a row in the SequelizeMeta table. This lets Sequelize CLI know which migrations were executed already.
MariaDB [db]> SHOW TABLES;
+---------------+
| Tables_in_db  |
+---------------+
| SequelizeMeta |
| Users         |
+---------------+
2 rows in set (0.000 sec)

MariaDB [db]> SELECT * FROM SequelizeMeta;
+-------------------------------+
| name |
+-------------------------------+
| 20181203173724-create-user.js |
+-------------------------------+
1 row in set (0.001 sec)

MariaDB [db]> SELECT * FROM Users;
Empty set (0.001 sec)

🎉 Congratulations! 🎉

You have successfully implemented Sequelize into your NodeJS + Express project! Next step is how to use Sequelize to generate complex models & how we can use them in our Node scripts.

 


Have any questions, concerns, found a bug or mistake? Leave us a comment below.
Need a Web Application or Website developed? Get in touch with us!

 

About the Author Sean Proulx

Fullstack Software geek with a passion for music and art.

Leave a Reply