Morioh
Login
Louis Jones

Louis Jones

4 years ago

Learn Sequelize ORM in Node.js with MySQL From Scratch

Getting Started

Sequelize is a promise-based ORM for Node.js v4 and up. It supports the dialects PostgreSQL, MySQL, SQLite, and MSSQL plus some cool features like transactions and eager-loading.

Q: Why use Sequelize instead of standard SQL Queries?

A: Basically because it supports more than one database system and it provides you an OOP Database API with all queries in form of methods and function so you don’t have to write or care about queries plus its advanced Association system for putting relations between Models.

I assume you have a basic Node.js project for working with Sequelize.

Now let’s start by installing Sequelize on our project.

npm install sequelize --save

Sequelize Also requires your database system driver (depending on what dialect you choose) on our case we will be working with MySQL.

npm install mysql2 --save

This will install MySQL adapter which allows Sequelize to connect and to the MySQL Deamon server.

We also, need to install Sequelize-CLI for managing our project and keeping track of migrations.

npm install sequelize-cli -g

We save the CLI globally because you will be using on more than one project and you need to access directly through the command line.

Connecting to Database

First, we need to open connection steam between Sequelize and the running MySQL Deamon.

Create a database folder under src/database and add a connection.js file under it.

/* connection/js */

const Sequelize = require("sequelize");

const sequelize = new Sequelize("socialnetwork", "root", "", {
  host: "127.0.0.1",
  dialect: "mysql",
  operatorsAliases: false
});

module.exports = sequelize;
global.sequelize = sequelize;

Change the config depending on your database server settings.

we export the connection instance as well as we export it globally which means we can access the Sequelize instance from any module on our environment without import (Global variables aren’t good but in our case, it is the best scenario).

Now under the app.js (or main entry point module), you can require the connection module to connect to the database and expose the connection instance globally.

require("./src/database/connection");

Models & Schemas

Models are the building blocks of your tables each model represents a database table where you define a model by putting the schema and giving it a name.
The Schema is a set of attributes (table columns) and associations (table relations).

So let’s say we are going to create a social media application something like twitter clone, ao first we start by creating the database, this is better done manually from the CLI of any other MySQL client.
mysql$ CREATE DATABASE socialnetwork
The above command will create a database for us.

For a basic Social network, we would have a tweet and a user (tweet’s author), the user can tweet one or more tweets but a tweet belongs to a single user.

Let’s create the tweet model.

const Sequelize = require("sequelize");

module.exports = sequelize.define("Tweet", {
  id: {
    type: Sequelize.INTEGER(11),
    allowNull: false,
    autoIncrement: true,
    primaryKey: true
  },
  content: Sequelize.STRING(300)
});

The Sequelize define method registers a new model using the provided name and schema.
Attributes can have the type directly, all are defined in the main namespace of Sequelize (STRING, DATE…).
Or you can define attributes using options with type and allowNull, unique.

for type sizes, you simply use the type as a function and pass the length (number) as an argument.

the id attributes are the primary key that uniquely identifies each tweet.

We export the defined model so later we will be able to use it for inserting, fetching, updating or deleting model’s data.

We also need to define and register the user model the same way we did the tweet but with different attributes.

const Sequelize = require("sequelize");

module.exports = sequelize.define("User", {
  id: {
    type: Sequelize.INTEGER(11),
    allowNull: false,
    autoIncrement: true,
    primaryKey: true
  },
  username: {
    type: Sequelize.STRING(35),
    allowNull: false,
    unique: true
  },
  passwd: {
    type: Sequelize.STRING(20),
    allowNull: false
  }
});

The username has to be unique so we make it unique (only one persistence can be saved with a certain username string), we set unique to true.

You can create default attributes on Sequelize which gets added to any model (like the id).

Sequelize Migrations

Sequelize other than the ORM comes with a Database Builder which allow you to create and manage the structure of your database tables over time without manually interacting with the CLI or typing silly commands.

All Migrations features are manipulated through Sequelize-CLI that we have installed earlier.

First, for migrations to work we need to initialize our project with the CLI and add some database configuration.

sequelize init

This, in fact, it is used to initialize a whole Sequelize project to work properly with models, migrations, and seeds and since we already created the connection stream and add some models we will remove the models folder.

This what will be created under the root of our project.

  • config: database connection configuration for (development, testing, and production). So make sure to put your settings in the development object.
  • migrations: All migrations will be stored in here with their timestamp of creation.
  • seeders: A seed is a data that gets added to the database by default.
  • models: All models should go here where there is also a connection stream to Sequelize (in our case we will delete this).

Make sure to delete the newly added models folder by the CLI.

Let’s add our tweets and users tables migrations, we will use the CLI to generate a new migration which will be placed by default under the migrations/ folder with some pre-defined code.

sequelize migration:generate --name create_tweets_table

This will generate a new migration named (create_tweets_table) and place it under migrations/ for the naming convention of migrations you always want it to be like a verb or a sentence tells what the migration does exactly.

Open the tweets migration file (Sequelize will add a timestamp represents when the migration has been generated to the filename), and add your column types which to be created on up method and dropTable command to drop and remove the table from the database if down is executed.

/* 20190214224339-create_tweets_table.js */
"use strict";

module.exports = {
  up: (queryInterface, Sequelize) => {
    //You must return a promise 
    return queryInterface.createTable("tweets", {
      id: {
        type: Sequelize.INTEGER(11),
        allowNull: false,
        autoIncrement: true,
        primaryKey: true
      },
      content: Sequelize.STRING(300),
      userId: Sequelize.INTEGER(11),
      //Those are added by default on insertion (make sure to create the their columns)
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE
    });
  },

  down: (queryInterface, Sequelize) => {
    //Return a promise that drops a table in case of (migration:undo)
    return queryInterface.dropTable("tweets");
  }
};

If we run migrate command the up method will be called and the table will be created, but Sequelize is smart enough to only run the up command once (it keeps track of migrated tables).

the userId attribute we will use it later to associate a user with one or more tweets.

And in case you run migration:undo or migration:undo:all it will rollback either the last submitted migration or simply or migrations in this case whatever you put inside the down method will be executed.

Add the user’s migration too.

sequelize migration:generate --name create_users_table

And add the schema of the table on up and drop table on down.

/* 20190214225010-create_users_table.js */

"use strict";

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable("users", {
      id: {
        type: Sequelize.INTEGER(11),
        allowNull: false,
        autoIncrement: true,
        primaryKey: true
      },
      username: {
        type: Sequelize.STRING(35),
        allowNull: false,
        unique: true
      },
      passwd: {
        type: Sequelize.STRING(20),
        allowNull: false
      },
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE
    });
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable("users");
  }
};

Now our migrations are created now we need to migrate them to the database.

sequelize db:migrate

It will output the successfully migrated migration and errors in case of any.

You can use the CLI to take a look at the users and tweets tables structure using the describe command on your MySQL CLI.

use socialnetwork;
describe tweets;
describe users;

Inserting to Database

Let’s create a bootstrap.js module that will do the fake data insertion on the database for our social network.

/* src/bootstrap.js */
//Export a default function (we use Async/Await)
module.exports = async () => {
  //Require models
  const Tweet = require("./models/Tweet");
  const User = require("./models/User");
  //Create Relations 
  User.hasMany(Tweet, { as: "Tweets", foreignKey: "userId" });
  Tweet.belongsTo(User, { as: "User", foreignKey: "userId" });
};

For our Models Association (Relation) we have two constraints:

  • User: can have one or more Tweets (one-to-many).
  • Tweet: can have one user only as an author (one-to-one)

Each Sequelize model has a built-in methods used to describe associations among other models, the first parameter it takes is the target and an options object as the second parameter.

  • hasMany: for a one-to-many association, will associate User with many tweets with configuration (foreignKey will be added to the tweets table).
  • as option: used to set setters in the User Model so you can call userInstance.setTweets(tweets).
  • belongsTo: for a one-to-one association to associate each tweet with one and only one user.

Check the Association Docs for more detailed info.

Let’s insert some User to the database.

//Generic Error Handler 
const errHandler = err => {
  //Catch and log any error.
  console.error("Error: ", err);
};
//create returns a promise which gets resolved to the user instance 
//We also use await, you can use standard then callback.
const user = await User.create({
  username: "alexdmc",
  passwd: "1234567890"
}).catch(errHandler); ///< Catch any errors that gets thrown
//You must provide the userId to get each tweet linked to a single user.
const tweet = await Tweet.create({
  content: "This is actually the tweet content Tweeted from Iphone",
  userId: user.id
}).catch(errHandler);

A model.create method is used to create a user instance using the provided data and then save it and finally returns a promise holding the new user instance.

We use await which gets resolved if the creation and saving succeed otherwise the catch callback (errHandler) will get called.

To associate a tweet with its author (user) we have to put the userId attribute under the tweet to point to the author’s unique id.

Fetching from Database (Eager Loading)

For fetching specific data related to a specific model we have to use the Model class providing it with a filter (condition) to fetch a specific record(s) from the database.

Let’s try to fetch users data plus all its tweets (using eager-loading).

eager-loading is the process of fetching a models data that is associated with another model in one fetch call (also known as Populating).

//Find All Users with Thier Tweets
const users = await User.findAll({
  where: { username: "alexdmc" },
  include: [{ model: Tweet, as: "Tweets" }] ///< include used to eager-load associated model 
}).catch(errHandler);
//log users & tweets
console.log("AlexDMC Tweets: ", JSON.stringify(users));

We findAll the users with their associated tweets the where attributes take the filter object and include is an array on which you specify the associated model you want to eager-load alongside the current model (User).

the include takes an array of object we need to specify associated model’s model class and the as attribute (which you used to define the association between the two models).

The output should have all the registered users with an array of their tweets.

Read More on Eager-Loading Docs.

What’s Next

This tutorial is just a quick introduction to Sequelize and how to use its main features, if you want to master and work with Sequelize you must read the http://docs.sequelizejs.comDocs for more detailed pieces of information and instructions.

Video

#Nodejs #MySQL #Sequelize #Databases #WebDev

Learn Sequelize ORM in Node.js with MySQL From Scratch
183.20 GEEK