Can You Communicate With Databases Without Learning SQL | Part #1 | Sequelize

Sequelize is a promise-based Node.js ORM.

For a better understanding of Sequelize in action, we’re going to use the concept of an online shopping system. For simplicity, we will just be playing with the following 2 entities, i) Customer, ii) Order. Where each customer can place one or more orders and each order belongs to a single customer.

1) Installation

You can use npm to install the sequelize package:

npm install --save sequelize

You'll also have to manually install one of the following drivers for your database of choice:

$ npm install --save pg pg-hstore #Postgres 
$ npm install --save mysql2 
$ npm install --save mariadb 
$ npm install --save sqlite3 
$ npm install --save tedious #Microsoft SQL Server

All the examples present in this article will be based on the MySQL driver, i.e., mysql2. Also, the database is running on my local machine.

2) Establish Database Connection

Place the following code in your ./util/database.js

const Sequelize = require("sequelize");

const sequelize = new Sequelize("your_db_name", "your_username", "your_password", {
  dialect: "mysql",
  host: "localhost", //Optional 
});

module.exports = sequelize;

Notice that it’s optional to use the host parameter when you’re using localhost to run your database, as the default value for this parameter is already set to localhost. However, you can always overwrite this value.

3) Define Models

A model is an abstraction that represents a table in your database. In Sequelize, it is a class that extends the Model class.

Define the Customer model by placing the following code in your ./models/customer.js

const Sequelize = require("sequelize");
const sequelize = require("../util/database");

const Customer = sequelize.define("customer", {
  id: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    allowNull: false,
    primaryKey: true,
  },
  name: {
    type: Sequelize.STRING,
    allowNull: false,
  },
  email: {
    type: Sequelize.STRING,
    allowNull: false,
  },
});

module.exports = Customer;

Each customer has an id, a name, and an email. This represents the 3 fields/columns of the Customer table.

Define the Order model by placing the following code in your ./models/order.js

const Sequelize = require("sequelize");

const sequelize = require("../util/database");

const Order = sequelize.define("order", {
  id: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    allowNull: false,
    primaryKey: true,
  },
  total: {
    type: Sequelize.INTEGER,
    allowNull: false,
  },
});

module.exports = Order;

Each order will have an id and a total purchase price. This represents the 2 fields/columns of the Order table.

4) Create Actual DB Tables from Sequelize Models

The following code will go in the ./index.js file.

const sequelize = require("./util/database");

const Customer = require("./models/customer");
const Order = require("./models/order");

sequelize
  .sync()
  // .sync({force: true})
  .catch((err) => {
    console.log(err);
  });

We use the .sync() method on the sequelize object in order to map our sequelize models to actual tables in our database. Executing the above-mentioned code will have the following effect.

Executing (default): CREATE TABLE IF NOT EXISTS `customers` (`id` INTEGER NOT NULL auto_increment , `name` VARCHAR(255) NOT NULL, `email` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `customers`

Executing (default): CREATE TABLE IF NOT EXISTS `orders` (`id` INTEGER NOT NULL auto_increment , `total` INTEGER NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `customerId` INTEGER, PRIMARY KEY (`id`), FOREIGN KEY (`customerId`) REFERENCES `customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `orders`

Now, as you must’ve noticed, the .sync() method has another variation, which is, passing an object to this method with the force field set to true.

With .sync({force: true}) Sequelize adds “DROP TABLE” queries additionally, therefore, each time this method is called, the table is dropped if it already exists. Executing the .sync({force: true}) will have the following effect.

Executing (default): DROP TABLE IF EXISTS `orders`;
Executing (default): DROP TABLE IF EXISTS `customers`;

Executing (default): DROP TABLE IF EXISTS `customers`;
Executing (default): CREATE TABLE IF NOT EXISTS `customers` (`id` INTEGER NOT NULL auto_increment , `name` VARCHAR(255) NOT NULL, `email` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `customers`

Executing (default): DROP TABLE IF EXISTS `orders`;
Executing (default): CREATE TABLE IF NOT EXISTS `orders` (`id` INTEGER NOT NULL auto_increment , `total` INTEGER NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `customerId` INTEGER, PRIMARY KEY (`id`), FOREIGN KEY (`customerId`) REFERENCES `customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `orders`

In conclusion, use the force option to sync between model schema and database only when you don’t need to maintain data in the table.

If you are starting a project from scratch, and your database does not exist yet, Sequelize can be used from the beginning in order to automate the creation of every table in your database. Also, if you want to use Sequelize to connect to a database that is already filled with tables and data, that works as well! Sequelize has got you covered in both cases.

5) Define Associations

So, the Customer model is associated with the Order model by a hasMany relation. Now, since we’ve informed Sequelize of this association it will take care of creating the customerId foreign key(linked to the primary key, id of the Customer table) in the Order table for you.

const sequelize = require("./util/database");

const Customer = require("./models/customer");
const Order = require("./models/order");

Customer.hasMany(Order);

sequelize
  .sync()
  .catch((err) => {
    console.log(err);
  });

Click here for more info on associations.

6) Writing Queries

In the following code snippet, we perform the following actions:

i) Insert a new customer

ii) Insert a new order for this customer using the customerId foreign key.

iii) Select all the orders for the current customer.

const sequelize = require("./util/database");

const Customer = require("./models/customer");
const Order = require("./models/order");

Customer.hasMany(Order);

let customerId = null;
sequelize
  .sync()
  .then((result) => {
    return Customer.create({name: "Chandler Bing", email: "cb@gmail.com"})
    console.log(result);
  })
  .then(customer => {
    customerId = customer.id;
    console.log("First Customer Created: ",customer);
    return customer.createOrder({total: 45});
  })
  .then(order => {
    console.log("Order is : ",order);
    return Order.findAll({ where: customerId});
  })
  .then(orders => {
    console.log("All the Orders are : ",orders);
  })
  .catch((err) => {
    console.log(err);
  });

So, that’s it about this useful Node.js ORM. I hope the information provided in this article provides value to you and helps you simplify and optimize your database manipulations.

Additional Resources: