Basic implementation of a Node.JS ORM (Sequelize)

ABOUT

This document reads as an introductory beginner tutorial surrounding the topics of ORM (Object Relational Models) and how they can be implemented to simplify database querying.

We will be using Javascript as the programming language, and Sequelize as the ORM.

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. Using sequelize allows database querying by using javascript instead of SQL queries and only requires minor configuration to change depending on the vendor.

The database / table creation section assumes beginner knowledge of SQL.

The general concepts from this tutorial still apply to other languages and ORMs.

BENEFITS

• ORM knows how to write vendor-specific SQL
• SQL syntax changes slightly depending on the vendor and may change over time
• Use javascript instead SQL
• User friendly — great documentation
• Can add restrictions via the table model through the ORM

REFERENCES

1. Sequelize documentation
2. PostgreSQL documentation
3. Github link with code

INITIALIZATION

The following set of instructions will go over a basic configuration of a database and set up of Sequelize.

• Create database called “orm_test” (for consistency, the code image could go here if you want)
• Create table “users” as well as attributes “first_name” and “last_name”

PostgreSQL ‘user’ table creation

• Insert dummy data into the “users” table

PostgreSQL ‘user’ table inserts

• Below is the resulting “users” table

PostgreSQL users table

• Install “sequelize” and “pg” (node-postgres) packages.

npm install pg sequelize — save

• Require “sequelize”

const Sequelize = require(‘sequelize’);

• Initialize connection between postgreSQL and sequelize.

Note:

Timestamps are set to false to further simplify our example.

If timestamps are set to true would require us to create a “createdAt” and “updatedAt” field in the database.

initialize connection between PostgreSQL and Sequelize

• Initialize User model.
• This will allow us to manipulate the database using objects instead of SQL queries

Sequelize User model

QUERYING

This section will demonstrate a select few different types of queries that can be performed simply using the ORM instead of more complex SQL queries.

SELECT → FINDALL

The below example demonstrates a simple select query and how it can be created using Sequelize.

Sequelize User find all statement

Results from the findAll query

INSERT → CREATE

The below example demonstrates a simple insert query and how it can be created using sequelize.

Sequelize User create statement

Results from the create query

MASS CREATE/INSERT

The below example demonstrates a simple bulk insert query and how it can be created using Sequelize.

Sequelize User bulk create statement

Results from the bulk create query

DELETE → DESTROY

The below example demonstrates a simple insert query and how it can be created using Sequelize.

Sequelize User delete statement

Results from the destroy query

UPDATE → UPDATE

The below example demonstrates a simple update query and how it can be created using sequelize.

Sequelize User update statement

Results from the update query

UPDATING OBJECT MODEL WITH CONSTRAINTS

The initial object model was simple and did not include any constraints. However, in the example below, we have updated the model to now include the additional allowNull: false section. This new piece of code ensures that all records in the user table must have a first_name field.

Note: the PostgreSQL schema does not have any constraints, however the ORM does.

Let’s see what happens when we try to add a record without a first_name field.

Sequelize User model V2 (first_name constraint added)

Sequelize User insert statement (includes .catch and removal of first_name field)

As shown below, even though the database user table schema does not have a constraint on the first_name field, the ORM’s constraint for first_name prevents the record from being added.

Results from the create query

SUMMARY

In this tutorial, we have performed simple database queries using the ORM, Sequelize.

Again, the benefit of using Sequelize is that it allows us to use javascript instead of SQL for database querying, which simplifies our code and makes it more readable and with a few minor changes to the code (initialization portion — connection between Node.js and the database), the ORM created SQL can be changed depending on the vendor.

I hope you are able to leverage the information taken from this tutorial and apply it to more complex problems.

Here is a StackOverflow post on why you should use an ORM.