Using Sequelize in a Node.js Application
Sequelize is an ORM -- an Object Relational Mapping library. Object-relational mapping (ORM) is a technique that helps programmers work with data by implementing language-level models and functions that represent the underlying database tables and data. The benefit of an ORM is that it abstracts SQL query dialect into application language, in this case JavaScript.
It also converts the response of any query into application level data types such as arrays or custom objects. Sequelize calls these custom objects "models." Adding an ORM like Sequelize, we are adding the data model layer of the application.
Data model mapping
A database table is a collection of objects with specified data attributes. Viewed as a table, there is a row for each specific object and columns for attributes an object can have. Consider a ficticious "posts" table that contains a collection of resources with the attributes of title, content, and created_at. Here is an example query in the psql interface for all posts in the table.
postgres=# select * from posts;
id | title | content | publishDate
---+---------------------+---------------------------------+------------------------
1 | My First Post | Hello World! | 2022-04-03 12:00:00-06
2 | How to JavaScript | Objects and functions. | 2022-04-04 12:00:00-06
3 | SOLID Breakfast | The principles of SOLID design. | 2022-04-05 12:00:00-06
(3 rows)
With an ORM, this structure is made accessible in the programming language, such as JavaScript's class objects.
We can use sequelize to generate both the database tables and JavaScript classes.
1. Generate the model
Use the model:generate command to create a posts migration and a Post JavaScript class.
$ npx sequelize model:generate --name Post --attributes title:string,content:text,publishDate:date
This creates two files
- models/post.js
- db/migrations/[timestamp]-create-post.js
2. Run the migration
Migration files are used to keep track of changes made to a database. They track creating tables, adding and removing columns to existing tables, and other operations. Migration files record how to transition the database to a new state, and how to rollback the changes to get back to the older state. Database migration files are like a version control system for the application database. By keeping a sequential changeset to database schema, the migration workflow provides easy ability to keep different environments' databases in sync.
Looking at the migration file generated by model:generate, we can see that Sequelize added two attributes to the ones we declared -- createdAt and updatedAt. This is convention, and the values will be set and kept up to date by the Sequelize engine.
Run Sequelize's db:migrate command to create the posts table in the database.
$ npx sequelize db:migrate
This command executes the following steps:
- Ensures a table called SequelizeMeta is in database. This table is used to record which migrations have run on the database.
- Runs all migration files which haven't run yet. This is possible by checking SequelizeMeta table.
In this case, the create-post migration file will be executed against the database resulting in a new posts table being created.
3. Seed the database
To "seed" the database is to programmatically insert values into the database -- no user interaction required. This is useful for transferring a known data set into the database or populating tables with dummy data for development.
1. Track development seeds within database.
Unlike migrations, database seeding events are not stored anywhere by default. This means every time the db:seed:all command is run, the database will be re-seeded with previously run seeds. To change from the default behavior, add the configuration "seederStorage": "sequelize" to the development object of config/sequelize.js.
This will save to the database which seeds have been run, allowing for use of the developer friendly db:seed:all.
module.exports = {
"development": {
...,
"seederStorage": "sequelize"
},
...
}
2. Generate a new seed file
Use the seed:generate command provided by sequelize to generate a seed file for the Post model.
$ npx sequelize seed:generate --name first-posts
This command creates a file, db/seeds/[timestamp]-first-posts.js. Like a migration, the seed file implements an up/down interface. The up command specifies what actions should be performed to seed the database. The down function should specify how to undo the actions.
3. Define a few post objects in an array
const posts = [{
title: 'Hello World',
content: 'This is the first post!',
publishDate: new Date('2022-01-01'),
createdAt: new Date(),
updatedAt: new Date()
}, {
title: 'Lorem Ipsum',
content: `
Lorem Ipsum is simply dummy text of the printing and typesetting industry.
Lorem Ipsum has been the industry's standard dummy text ever since the 1500s,
when an unknown printer took a galley of type and scrambled it to make a type specimen book.
Contrary to popular belief, Lorem Ipsum is not simply random text.
It has roots in a piece of Latin literature from 45 BC.
`,
publishDate: new Date('2022-01-02'),
createdAt: new Date(),
updatedAt: new Date()
}];
4. Define the up and down methods
In the up method, use the provided queryInterface class to bulk insert the posts into the database.
async up (queryInterface, Sequelize) {
await queryInterface.bulkInsert('Posts', posts, {});
},
In the down method, perform the reverse action of the up method by deleting the posts. To use Sequelize's Op (short for "operation") library, import it at the top of the file.
const { Op } = require("sequelize");
const posts = [...];
module.exports = {
async up (queryInterface, Sequelize) { ... },
async down (queryInterface, Sequelize) {
await queryInterface.bulkDelete('Posts', {
title: {
[Op.in]: posts.map((post) => post.title)
}
}, {});
}
}
The bulkDelete query generates the following SQL: DELETE FROM "Posts" WHERE posts.title IN ["Hello World", "Lorem Ipsum"];.
5. Seed the database
$ npx sequelize-cli db:seed:all
This command executes the JavaScript code written in the seed file. In our case, it inserts the post data into the database.
4. Display database records
The purpose of a database is to keep data organized. The purpose of keeping the data around is for human end-users to view and manipulate the information. To get the data viewable by the user, we will provide a webpage that lists the data. When a user requests the webpage, we will query the database for the records, and supply the records as template variables.
1. Create the route
Create a route /posts in index.js. Place the route near the other routes definitions, yet above the app.listen invocation.
app.get('/posts', (request, response) => {
response.render('posts', {
posts: []
});
});
2. Create the view template
Create a new file, app/views/posts.liquid with the following code:
{% layout 'layouts/default-html.liquid' %}
{% block content %}
<div>
<h1>Posts</h1>
{% for post in posts %}
<h2>{{ post['title']}}</h2>
<p><i>{{ post['publishDate'] }}</i></p>
<p>{{ post['content'] }}</p>
{% else %}
<p><i>There are no posts to display.</i></p>
{% endfor %}
</div>
{% endblock %}
Navigate to localhost:3000/posts. You should see a page that says "There are no posts to display." This is because the posts template variable is hard-coded to be an empty array.
3. Query the database for posts
Sequelize provides JavaScript classes as abstraction over the SQL query language. The Post class found in models/post.js is such a class. We will import the class into index.js and use the .findAll() method to populate the posts template variable.
const { Post } = require('./models');
app.get('/posts', async function(request, response) {
response.render('posts', {
posts: await Post.findAll()
});
});
Notice the addtion of the keyword async before the function defintion. With the addition of the asynchronous method Post.findAll(), we must also label the route handling function as async.
Refreshing the /posts web page now shows the two posts seeded in the database.
4. Link to the page
Show off the database! Add a navigation link to the views/index.liquid. Add a link to Posts on the homepage.
<li><a href="/posts">Posts</a></li>
Resources
Sequelize model basics: https://sequelize.org/docs/v6/core-concepts/model-basics
Sequelize Seeds: https://sequelize.org/docs/v6/other-topics/migrations
Sequelize Query Interface: https://sequelize.org/docs/v6/other-topics/query-interface/
Sequelize QueryInterface API : https://sequelize.org/api/v6/class/src/dialects/abstract/query-interface.js