How to create a model with Sequelize for Nodejs

What is Sequelize

Sequelize is a SQL ORM for Nodejs. ORM means Object-Relational Mapping. This type of interface offers you to take some distance from the database administration hassle. It will create the tables, the indexes and auto generate and execute the queries directly for you. You don’t have to execute those very painful and repetitive tasks.

Sequelize is an ORM that is developed for database using SQL as a query language. MySQL for instance or Postgresql.

Installation

Go to the directory of your nodejs project :

$ cd my_project_forlder

Then use npm to install sequelize ad all it’s dependencies.
We will use the --saveoption to write in our package.json file that the project needs this module.
It will be more easier when you will deploy it, or update your packages.

$ npm install sequelize —-save

Configuration

Prerequesite having a running SQL database. For demonstration purpose we will use a MySQL one.

Create a new file in your project folder named sequelize.js with the following content :

// Let's require our module
var Sequelize=require('sequelize');
// Let's create a new sequelize instance
// And connect tou our database
var sequelize = new Sequelize('database_name', 'username', 'password', {
  host: 'XXX.us-west-2.rds.amazonaws.com',
  dialect: 'mysql',

  pool: {
    max: 5,
    min: 0,
    idle: 10000
  },
});

That’s it ! You have now a brand new connection to your server.
By default sequelize will use a pool connection. We give it in the last lines some additional options to configure the pooling system. Basically a pool of connection is a cache of database connections that can be used again and again in case of multiple queries to perform.

Creation of the model

You can see the model as an abstract representation of data that will be stored in your system. By definig a model you will warn Sequelize that you want to be able to insert, select, delete or update rows of data that will be stored and retrieved following the instruction you specified when you define your model.

Do not be affraid by this abstraction step. I have experienced with many of my students that being able to fully understand what is a model and what it brings to developer can be really hard. Just keep in mind that you are just defining the structure of a traditional database table with indexes, constraints and types.

// Creation of the model "client"
var client = sequelize.define('client', {
  // Here are the columns of the table
  family_name: {type: Sequelize.STRING},
  surname: {type: Sequelize.STRING},
  title: {type: Sequelize.STRING},
  email: {type: Sequelize.STRING}
});

In this code we have created a model client, (that will be stored in the table client) with the columns family_name, surname …

Synchronization with the database

Creating a model is cool, but now you have to synchronize it with your physic database. If you forget this step you will get errors because the table will not be created and it will be impossible to insert or select data for a non existing table.

client.sync().then(function () {
  // Table created
  return client.create({
    family_name: 'Jean',
    surname: 'Dupont',
    title : "Mr",
    email : "jean.dupont@gmail.com"
  });
});

This code will create the tables and the column defined before. Bonus : Sequelize will add automatically an id , createdAtand updatedAt column !

After the creatin is done it will create a client with the information provided in the create method of our model object (client).

Enjoy and use without moderation but becareful ! Never use (unless you know what you are doing) the method sync with the option force:true. It will drop the table if one exist with the same name and create a new one ! It can be a real disaster in a production environment !