How to Use Sequelize to Interact with PostgreSQL: A Comprehensive Guide

When it comes to working with databases, you have the option to directly use the primitives provided by the database or leverage a library that abstracts away the intricate details for you. Sequelize is a popular Node.js library that serves as a wrapper for PostgreSQL, MySQL, and various other databases. In this blog post, we will explore how to use Sequelize specifically with a PostgreSQL database.

Install and Configure Sequelize

Under the hood, Sequelize utilizes the pg library to establish a connection with PostgreSQL. Therefore, when installing the sequelize npm package, make sure to also install pg:

npm install pg sequelize

Pro tip: If you’re starting a new project and don’t have a package.json file yet, run npm init -y before installing the packages.

Within your Node.js file, begin by defining the variables necessary for database access:

const user = '<postgres user>';
const host = 'localhost';
const database = '<postgres db name>';
const password = '<postgres password>';
const port = '<postgres port>';

Next, import three objects from the sequelize package:

import { Sequelize, Model, DataTypes } from 'sequelize';

Finally, create a new instance of the Sequelize object, passing in the required parameters:

const sequelize = new Sequelize(database, user, password, {
  host,
  port,
  dialect: 'postgres',
  logging: false,
});

Od note, by setting the dialect property, you inform Sequelize about the type of database you are using. It’s worth mentioning that Sequelize is not limited to PostgreSQL and can handle other databases as well. Additionally, we disable logging to avoid unnecessary verbosity unless we are specifically debugging an issue.

How to Create a Sequelize Model

For each table you wish to interact with using Sequelize, you must create a corresponding model. Let’s assume we have a dogs table with two columns: name and age. We’ll start by creating a Dog class that extends the Model base class:

import { Sequelize, Model, DataTypes } from 'sequelize';

class Dog extends Model {}

Then, invoke the init() static method on the class, specifying the table’s structure and any rules you wish to apply. In this example, we disable null values for both columns:

Dog.init({
  name: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  age: {
    type: DataTypes.INTEGER,
    allowNull: false,
  },
}, {
  sequelize,
  modelName: 'dog',
  timestamps: false,
});

In the example above, we utilized DataTypes.STRING and DataTypes.INTEGER to define the column types. The DataTypes object encompasses references to all the types you can utilize, with each type mapping to a specific database type. Refer to the official documentation for more available types.

How to Retrieve Data from the Database

Now, let’s explore how we can retrieve data from a table using Sequelize. The findAll() method is used to accomplish this:

const results = await Dog.findAll();

Assign the returned list of rows to a variable using await since findAll() returns a promise. To limit the columns retrieved, pass an object containing an attributes array:

Dog.findAll({
  attributes: ['age'],
});

To add a WHERE clause to the query, use the where property. For instance, we can retrieve all dogs with an age of 8:

Dog.findAll({
  where: {
    age: 8,
  },
});

Alternatively, to retrieve all dogs with an age greater than 5:

Dog.findAll({
  where: {
    age: {
      [Op.gte]: 5,
    },
  },
});

Other properties, such as limit and order, enable various operations:

Dog.findAll({
  limit: 10,
  order: [
    ['name', 'DESC'],
  ],
});

How to Insert Data into the Database

To insert a new row in the database using Sequelize, employ the Dog.create() method and pass in an object containing the necessary data:

const name = 'Roger';
const age = 8;
const result = await Dog.create({ name, age });

How to Update Data

To update values within a table, use the update() method. In the following example, we are updating the age of the dog named ‘Roger’ to 9:

Dog.update({
  age: 9,
}, {
  where: {
    name: 'Roger',
  },
});

If you omit the where property, all rows within the table will be updated:

Dog.update({
  age: 10,
});

And there you have it! You now have a comprehensive understanding of how to use Sequelize to interact with a PostgreSQL database. Happy coding!

Tags: Sequelize, PostgreSQL, Node.js, Database Interactions, ORM