Bootcamp
Search…
4.1: ORM, Sequelize

Introduction

Lets do some wishful thinking...
  • What if we could write a program that writes these SQL queries for us?
  • What if we did not need to remember this crazy error prone SQL syntax?
  • What if we could call data their actual names, i.e; Users, Sightings, rather than the generic rows?

Introducing Sequelize (our ORM of choice)

​ORM stands for "object-relational mapping", where database tables (also known as "relations") are mapped to objects or classes, such that SQL relations and their relevant associations can be manipulated directly from application code by generating templatized SQL query code. Sequelize is the most popular ORM for Node.js. We will use Sequelize during Coding Bootcamp for our web applications to replace raw SQL.

ORMs Replace Simple SQL Queries

You may have noticed that SQL queries for each set of CRUD routes follow a similar pattern. Most CRUD queries follow this same pattern, and can be relatively easily substituted with ORM syntax by templatizing the standard SQL query syntax.
// Create Query
INSERT INTO <TABLENAME> (<COLUMN_NAMES>) VALUES (<VALUES>);
// Retrieve Query
SELECT * FROM <TABLE_NAME> WHERE id=<ID>;
ORM is the answer to the question: "What if we could write a program that writes these SQL queries for us?" When we finish implementing Sequelize ORM in our Express app, it will form the Model part of our Model-View-Controller architecture.

SQL and Sequelize CRUD Side by Side

Imagine a database setup with the following ERD:

SELECT with SQL vs. Sequelize

SQL
Sequelize
// Find all
SELECT * FROM "categories";
​
// Find all with specific columns
SELECT id, name FROM "categories";
​
// Find all with where clause
SELECT * FROM "categories" WHERE createdAt = '2021-20-06'
​
// Find one
SELECT * FROM "categories" WHERE id = 5 LIMIT 1;
// Find all
Category.findAll();
​
// Find all with specific columns
Category.findAll({
attributes: ["id", "name"],
});
​
// Find all with where clause
Category.findAll({
where: {
createdAt: "2021-20-06",
},
});
​
// Find one
Category.findOne({
where: {
id: 5,
},
});
​
// Find by PrimaryKey(id)
Category.findByPk(5);

INSERT and UPDATE with SQL vs.Sequelize

SQL
Sequelize
// Create
INSERT INTO "categories" (id, name, createdAt, updatedAt)
VALUES (1, 'music', NOW(), NOW());
​
// UPDATE
UPDATE "categories"
SET name = 'films'
WHERE id = 1;
// Create
Category.create({
id: 1,
name: "music",
createdAt: Date.now(),
updatedAt: Date.now(),
});
​
// Update
Category.update({ name: "films", updatedAt: Date.now() }, { where: { id: 1 } });
​
// Create and update instance, no need for specifying where clause in update
// The created instance acts as reference to the entry in the db
const newCategory = Category.create({
id: 2,
name: "cooking",
createdAt: Date.now(),
updatedAt: Date.now(),
});
​
newCategory.update({ name: "gardening ", updatedAt: Date.now() });

DELETE with SQL vs. Sequelize

SQL
Sequelize
// Delete
DELETE FROM "categories" WHERE id = 1;
// Delete
Category.destroy({
where: {
id: 1,
},
});
​
// Delete instance
const persistenceCategory = Category.findByPk(1);
​
persistenceCategory.destroy();

​

Copy link
On this page
Introduction
Introducing Sequelize (our ORM of choice)
ORMs Replace Simple SQL Queries
SQL and Sequelize CRUD Side by Side