Relational databases rely on SQL, or Structured Query Language, as the standard way to control and interact with their stored data. In the Node.js ecosystem, Sequelize is a prominent Object-Relational Mapping (ORM) library that bridges the gap between Node.js apps and SQL databases, enabling you to interact with your data using familiar JavaScript idioms.

This article will guide you through using Sequelize to connect to databases, run queries, and handle results within your Node.js projects. We'll cover the basics of model definition, relationships, and CRUD (Create, Read, Update, Delete) operations, giving you the skills to confidently interact with SQL databases from your Node.js applications.

Ready? Let's dive in!

A Quick Primer on Databases

Databases serve as organized repositories of electronically stored data within computer systems. When choosing a general-purpose database, you'll usually pick between a relational database (like SQL) or a non-relational one (such as MongoDB).

You can think of relational databases as highly structured spreadsheets: data is neatly arranged in tables, rows, and columns, with clear connections between different pieces of information. Non-relational databases offer more flexibility without these rigid structures.

This article will zero in on relational databases and SQL, the language used to communicate with and retrieve data from them. While SQL has many "dialects" (variations), we'll concentrate on PostgreSQL, a widely-used open source option. Don't worry, though: the concepts we'll cover apply across all SQL dialects supported by Sequelize.

Prerequisites

Before proceeding with this tutorial, ensure that you have a recent version of Node.js and PostgreSQL installed on your computer. If you don't have PostgreSQL installed, I'll show you how to set it up with Docker in an upcoming section.

Setting up the Demo Project

In this tutorial, we'll explore how to interact with a PostgreSQL database by building a Node.js application that manages blog posts. We'll cover creating, updating, and deleting posts — the essential CRUD operations.

To get you started quickly, I've prepared the starter code in the application repository. This allows us to focus on the core database integration rather than set up the entire application from scratch.

Begin by cloning the repository to your local machine:

git clone https://github.com/damilolaolatunji/node-blogging-app

Next, navigate to the project directory and install the necessary dependencies. We'll be using Fastify as our web framework.

cd node-blogging-app
npm install

With the dependencies installed, rename the .env.sample file to .env and launch the development server:

mv .env.sample .env
npm run dev

You should now be able to access the application at http://localhost:5000.

At this point, the application only renders basic templates without any real functionality. If you open the controllers/post.controllers.js file, you'll see that most of the route handlers simply return a "Not Implemented" message.

// controllers/post.controllers.js
async function createPost(req, reply) {
  return reply.code(501).send("Not Implemented");
}
 
async function renderPost(req, reply) {
  return reply.code(501).send("Not Implemented");
}
 
async function renderPostForm(req, reply) {
  return reply.render("post-form");
}
 
async function getPosts(req, reply) {
  return reply.render("home");
}
 
async function updatePost(req, reply) {
  return reply.code(501).send("Not Implemented");
}
 
async function deletePost(req, reply) {
  return reply.code(501).send("Not Implemented");
}
 
export {
  createPost,
  getPosts,
  renderPostForm,
  renderPost,
  updatePost,
  deletePost,
};

Our primary objective in this tutorial is to connect this application to a PostgreSQL database. Once connected, we'll implement the necessary logic to perform CRUD operations on blog posts, bringing the application to life.

Setting up PostgreSQL Locally

Before you can establish a connection to PostgreSQL from your Node.js app, you need a running PostgreSQL instance on your machine.

If you don't have PostgreSQL installed, the simplest approach to get up and running quickly is to utilize Docker and the official PostgreSQL image.

We'll opt for the lightweight alpine variant which you can download by running the command below in a new terminal instance:

docker pull postgres:alpine

Once downloaded, launch a container from the image with:

docker run \
  --rm \
  --name node-blog-db \
  --env POSTGRES_PASSWORD=admin \
  --env POSTGRES_DB=node-blog \
  --volume node-pg-data:/var/lib/postgresql/data \
  --publish 5432:5432 \
  postgres:alpine

The container is named node-blog-db and the --rm flag ensures that it is automatically deleted when stopped. However, the --volume flag ensures that the database data is preserved even when the container is stopped or deleted.

The container also needs two environment variables:

• POSTGRES_PASSWORD: This is mandatory and sets the password for the default postgres user.
• POSTGRES_DB: This lets you choose the name of the database that will be created when the container is initialized.

Finally, it also makes port 5432 in the container accessible on your host machine at the same port number.

If successful, you should see output indicating the service is running on port 5432.

. . .
2024-09-11 11:42:37.380 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-09-11 11:42:37.404 UTC [1] LOG:  database system is ready to accept connections

With your PostgreSQL database instance up and running, you're ready to establish a connection from your Node.js application.

Connecting to PostgreSQL from Node.js

Node.js offers a rich ecosystem of libraries for seamless database interactions. In our project, we'll leverage the widely-used pg PostgreSQL client in conjunction with the powerful Sequelize ORM.

This combination allows us to establish a connection to our PostgreSQL database and execute operations using intuitive JavaScript methods, abstracting away the complexities of raw SQL queries.

While we're focusing on PostgreSQL in this tutorial, note that Sequelize supports other popular SQL databases, including MySQL, SQLite, and others, making it a valuable tool for a wide range of projects.

Let's begin by installing Sequelize and the necessary PostgreSQL driver packages:

npm install sequelize pg pg-hstore

Once installed, create a new db folder in your project root and a sequelize.js file within it. Populate the file with the following code:

mkdir db
touch db/sequelize.js

// db/sequelize.js
import { Sequelize } from "sequelize";
import env from "../config/env.js";
import logger from "../config/logger.js";
 
const { user, password, db, db_dev, host } = env.postgres;
 
const database = env.node_env === "development" ? db_dev : db;
 
const sequelize = new Sequelize(database, user, password, {
  host,
  dialect: "postgres",
  logging: (msg) => logger.debug(msg),
});
 
export default sequelize;

This file sets up a connection to your PostgreSQL database using the Sequelize ORM. It provides Sequelize with the necessary information to interact with your database, such as credentials and configuration options.

It also configures Sequelize to log database-related messages using the configured Pino logger at the debug level for easier debugging of SQL queries.

Before proceeding, ensure that the POSTGRES_DB and POSTGRES_PASSWORD values in your .env file are consistent with the values you provided when launching the PostgreSQL container in the previous section.

Next up, let's test the database connection by updating the server.js file as follows:

// server.js
import app from './app.js';
import env from './config/env.js';
import logger from './config/logger.js';
import sequelize from './db/sequelize.js';
 
. . .
 
try {
  await sequelize.authenticate();
 
  logger.info('Connected to the database');
 
  . . .
} catch (err) {
  . . .
}

This code attempts to establish a connection to the database using the Sequelize instance we set up in the db/sequelize.js file. If the connection is successful, a log message is printed indicating a successful connection.

When you save the file now, you will see the following logs in the Node.js server console:

[14:41:45.832] DEBUG (2682586): Executing (default): SELECT 1+1 AS result
    host: "ubuntu"
[14:41:45.832] INFO (2682586): Connected to the database
    host: "ubuntu"

The authenticate() method executes a simple query (SELECT 1+1 AS result) against the database to verify that the connection is valid and operational. If the query succeeds, it confirms that Sequelize can communicate with the database, and the "Connected to the database" message is logged.

Now that we have a successful database connection, let's move on to creating the Post model using Sequelize.

Creating the Database Model for Posts

In Sequelize, a model is a JavaScript class representing a database table. It defines the table's structure (its columns or attributes) and any validation rules, relationships with other models, and methods for interacting with the data.

In this section, you'll create a Post model representing a posts table with columns for id, title, and content.

Begin by creating a db/models folder and a post.model.js file within it:

mkdir db/models
touch db/models/post.model.js

Open the file in your editor and populate it with the following content:

// db/models/post.model.js
import { DataTypes } from "sequelize";
import sequelize from "../sequelize.js";
 
const Post = sequelize.define(
  "Post",
  {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true,
    },
    title: {
      type: DataTypes.STRING(128),
      allowNull: false,
      validate: {
        notEmpty: true,
      },
    },
    content: {
      type: DataTypes.TEXT,
      allowNull: false,
      validate: {
        notEmpty: true,
      },
    },
  },
  {
    tableName: "posts",
  }
);
 
export default Post;

The above code defines a Post model which represents how you'll interact with a posts table in your database. Each post will have an id, a title (up to 128 characters), and some content. Both the title and content are required fields and cannot be empty.

The id field is designated as the primary key and the database automatically assigns increasing unique values to this column for each new record according to the autoIncrement: true property.

Note that Sequelize automatically includes the createdAt and updatedAt fields (with type: DataTypes.DATE) in every model to keep track of creation and update times. To disable this behavior, you can use:

sequelize.define(
  "Post",
  {
    // ... (attributes)
  },
  {
    timestamps: false,
  }
);

Finally, the tableName: posts option explicitly tells Sequelize that the corresponding table in the database should be named posts. If you omit this, Sequelize will pluralize the model name and use that as the table name (Posts in this case).

The Post model is now defined to represent records in a corresponding posts database table. If this table does not exist in the database or if its structure differs from the model, this leads to errors (when the application tries to access or modify columns that don't exist).

Model synchronization, achieved by calling the asynchronous sync() method, addresses this. It automatically generates and executes an SQL query to align the database table with your model definition so that the model and corresponding table are in sync.

Here's how to use it:

Post.sync(); // creates `posts` table if it does already not exist
Post.sync({ force: true }); // drops `posts` table if it exists, then recreates it
Post.sync({ alter: true }); // If the table exists, perform updates necessary to match the model

Since we know that the database table does not exist yet, we can just call sync() as follows:

import { DataTypes } from 'sequelize';
import sequelize from '../sequelize.js';
 
const Post = sequelize.define(
    . . .
);
 
// Add this line
await Post.sync();
 
export default Post;

Note that the use of force: true or alter: true with model synchronization is only recommended in development environments. In production, it's better to use migrations to apply database updates. We'll cover migrations in a future tutorial.

Creating Posts in the Database

Now that you have the Post database model, let's implement the functionality to create posts within your application.

In controllers/post.controller.js, update the createPost() function as follows:

// controllers/post.controller.js
import Post from '../db/models/post.model.js';
 
async function createPost(req, reply) {
  const { title, content } = req.body;
 
  const post = await Post.create({ title, content });
 
  req.log.debug(`created post with id: ${post.id}`);
 
  reply.redirect(`/post/${post.id}`);
}
 
. . .

This createPost() function utilizes the Post model to create a new record within the database. It extracts the title and content values from the incoming request body and uses them to populate the corresponding fields in the new database record.

Sequelize takes care of generating and executing the necessary SQL query behind the scenes. It then returns an object representing the newly created post, which includes the auto-generated id assigned by the database. This id is subsequently used to redirect the user to the page where they can view the newly created post.

Save the file and navigate to http://localhost:5000/post/new in your browser. Fill out the form and submit it to create a new post.

You should observe a redirection response, indicating that the post creation was successful. However, we haven't rendered the post template yet, so you'll see a "Not Implemented" message:

Additionally, you'll see confirmation messages in your terminal, similar to the following:

[17:20:34.153] DEBUG (2730973): Executing (default): INSERT INTO "posts" ("id","title","content","createdAt","updatedAt") VALUES (DEFAULT,$1,$2,$3,$4) RETURNING "id","title","content","createdAt","updatedAt";
    host: "ubuntu"
[17:20:34.171] DEBUG (2730973): created post with id: 1
    host: "ubuntu"
    reqId: "req-7"

These messages confirm the successful creation of the post.

Now, let's proceed to set up the post rendering so that you can view the newly created post.

Retrieving a Post

The route responsible for rendering a post is registered as /post/:id, and its handler is the renderPost() function. However, before rendering a post, you first need to retrieve it from the database.

Let's implement this retrieval and rendering process:

// controllers/post.controller.js
. . .
async function findPost(req) {
  const { id } = req.params;
 
  const post = await Post.findByPk(id);
 
  req.log.debug(`found post with id: ${post.id}`);
 
  return post;
}
 
async function renderPost(req, reply) {
  const post = await findPost(req);
 
  return reply.render('post', { post });
}
 
. . .

When a user visits a URL like /post/1, the renderPost() function is triggered. It extracts the post ID (1 in this case) from the URL parameters, fetches the corresponding post using the findByPk() method, and then renders the post.pug template, passing the retrieved post data to it.

The findByPk() method efficiently locates a record in the database table based on its primary key. Once the post is found, it's returned to the calling function and the post.pug template is rendered, displaying the post's content to the user.

You can test this by refreshing the http://localhost:5000/post/1 link in your browser. You should now see the post being rendered on the page:

In the next section, we'll explore how to render all posts on the homepage.

Rendering all Posts

In this section, we'll modify the getPosts() function to render all posts on the homepage:

// controllers/post.controllers.js
async function getPosts(req, reply) {
  const posts = await Post.findAll();
 
  req.log.debug(`Found ${posts.length} posts`);
 
  return reply.render("home", { posts });
}

The findAll() method retrieves all records from the posts table in the database. These retrieved posts are stored in the posts array and then passed to the home template for rendering.

Once you reload the homepage, you should now see all the posts on the site. You can create additional posts and refresh the homepage to see them listed.

Important Considerations for Production Environments

In real-world applications with large datasets, it's crucial to limit the number of retrieved posts and implement pagination to optimize performance. You can achieve this using Sequelize's query options:

Project.findAll({ offset: 10, limit: 10 });

The offset option specifies that the first 10 records should be skipped, while limit instructs Sequelize to retrieve only the next 10 records after the offset.

Here's a generic Sequelize query for paginating posts, allowing you to fetch a specific "page" of results:

const page = req.query.page || 1; // Get the page number from the query parameters, default to 1
const limit = 10; // Number of posts per page
 
const offset = (page - 1) * limit;
 
const posts = await Post.findAll({
  offset,
  limit,
});

By default, Sequelize orders records by their primary key (id). If you need a different order, you can use the order option within findAll():

const posts = await Post.findAll({
  order: [["createdAt", "DESC"]],
});

This example orders posts in descending order based on their creation date.

Now that you can render all posts on the homepage, let's move on to updating posts in the posts table.

Editing Posts

When it comes to editing posts, you'll need to do two things:

1. Display the existing post content in a form for the user to modify.
2. Save the edited content to the database.

Step 1: Render the Post for Editing

Update the renderPostForm() function to retrieve the post if the id parameter is present and pass it to the post-form template:

// controllers/post.controllers.js
async function renderPostForm(req, reply) {
  const { id } = req.params;
 
  if (!id) {
    return reply.render("post-form");
  }
 
  const post = await findPost(req);
 
  return reply.render("post-form", { post });
}

With this change, clicking the Edit button on a post will now populate the form with the post's existing content, ready for editing.

Step 2: Update the Database

Let's now set up the updatePost() function to handle saving the edited post to the database:

// controllers/post.controllers.js
async function updatePost(req, reply) {
  const { title, content } = req.body;
 
  const post = await findPost(req);
  post.title = title;
  post.content = content;
 
  await post.save();
 
  req.log.debug(`updated post with id: ${post.id}`);
 
  reply.redirect(`/post/${post.id}`);
}

This function takes the new title and content from the request, finds the corresponding post in the database, updates its title and content fields, and saves the changes to the database. It finally redirects the user to the updated post's page.

You can try it out by editing the content of the post and clicking Save post. You should see the updated post as follows:

Deleting Posts

The final task needed for managing posts is the ability to delete them. This can be achieved by retrieving the post from the database and then calling the delete() method on the retrieved post object.

Go ahead and update the deletePost() function in your controllers/post.controller.js file as follows:

// controllers/post.controllers.js
async function deletePost(req, reply) {
  const post = await findPost(req);
 
  await post.destroy();
 
  req.log.debug(`deleted post with id: ${post.id}`);
 
  reply.redirect("/");
}

With this implementation, when a user clicks the "Delete" button on a post, the deletePost() function will be executed and the post will be removed from the database.

Soft Delete (Optional)

In some scenarios, it might be beneficial to implement "soft delete" behavior. This means that instead of permanently deleting the record from the database, you mark it as deleted or inactive. This allows you to retain the data for future reference or potential recovery if needed.

You can explore the Sequelize documentation on paranoid instances to implement soft delete functionality in your application.

With the delete functionality in place, you have now completed the basic CRUD operations needed for managing posts!

Wrapping Up

In this tutorial, we've journeyed through the essentials of using Sequelize with Node.js to manage your SQL databases. From setting up connections to defining models, establishing relationships, and performing CRUD operations, you now have a solid foundation to build upon.

Remember that this is just the tip of the iceberg. Sequelize offers a wealth of advanced features for transactions, migrations, raw queries, and much more. So don't stop here! Dive deeper into the Sequelize documentation and explore its full potential.

Thanks for reading!