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.
1
// Create Query
2
INSERT INTO <TABLENAME> (<COLUMN_NAMES>) VALUES (<VALUES>);
3
// Retrieve Query
4
SELECT * FROM <TABLE_NAME> WHERE id=<ID>;
Copied!
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
1
// Find all
2
SELECT * FROM "categories";
3
​
4
// Find all with specific columns
5
SELECT id, name FROM "categories";
6
​
7
// Find all with where clause
8
SELECT * FROM "categories" WHERE createdAt = '2021-20-06'
9
​
10
// Find one
11
SELECT * FROM "categories" WHERE id = 5 LIMIT 1;
Copied!
1
// Find all
2
Category.findAll();
3
​
4
// Find all with specific columns
5
Category.findAll({
6
attributes: ["id", "name"],
7
});
8
​
9
// Find all with where clause
10
Category.findAll({
11
where: {
12
createdAt: "2021-20-06",
13
},
14
});
15
​
16
// Find one
17
Category.findOne({
18
where: {
19
id: 5,
20
},
21
});
22
​
23
// Find by PrimaryKey(id)
24
Category.findByPk(5);
Copied!

INSERT and UPDATE with SQL vs.Sequelize

SQL
Sequelize
1
// Create
2
INSERT INTO "categories" (id, name, createdAt, updatedAt)
3
VALUES (1, 'music', NOW(), NOW());
4
​
5
// UPDATE
6
UPDATE "categories"
7
SET name = 'films'
8
WHERE id = 1;
Copied!
1
// Create
2
Category.create({
3
id: 1,
4
name: "music",
5
createdAt: Date.now(),
6
updatedAt: Date.now(),
7
});
8
​
9
// Update
10
Category.update({ name: "films", updatedAt: Date.now() }, { where: { id: 1 } });
11
​
12
// Create and update instance, no need for specifying where clause in update
13
// The created instance acts as reference to the entry in the db
14
const newCategory = Category.create({
15
id: 2,
16
name: "cooking",
17
createdAt: Date.now(),
18
updatedAt: Date.now(),
19
});
20
​
21
newCategory.update({ name: "gardening ", updatedAt: Date.now() });
Copied!

DELETE with SQL vs. Sequelize

SQL
Sequelize
1
// Delete
2
DELETE FROM "categories" WHERE id = 1;
Copied!
1
// Delete
2
Category.destroy({
3
where: {
4
id: 1,
5
},
6
});
7
​
8
// Delete instance
9
const persistenceCategory = Category.findByPk(1);
10
​
11
persistenceCategory.destroy();
Copied!

​