Featured image of post How to connect PostgreSQL with Node.js and Sequelize

How to connect PostgreSQL with Node.js and Sequelize

Learn how to connect your PostgreSQL database to a Node.js application using Sequelize. A step-by-step guide for beginners.

When it comes to working with databases, developers often face the choice between using raw database queries or leveraging libraries that abstract away some of the complexity. Sequelize is one such library—a popular Node.js ORM (Object-Relational Mapper) that works with PostgreSQL, MySQL, and other relational databases. In this tutorial, we will dive into how to use Sequelize on Node.js to connect with a PostgreSQL database, covering installation, configuration, model creation, and performing CRUD operations.

Install and Configure Sequelize

Sequelize simplifies database interactions by providing an abstraction layer over SQL queries. It utilizes the pg library under the hood to connect to PostgreSQL. To get started with Sequelize, follow these steps:

  1. Initialize Your Project

    If you’re starting with a new project, initialize a Node.js project to create a package.json file:

    1
    
    npm init -y
    
  2. Install Sequelize and PostgreSQL Driver

    Install Sequelize along with the PostgreSQL driver pg:

    1
    
    npm install pg sequelize
    
  3. Set Up Database Configuration

    In your Node.js application, you first need to define your PostgreSQL database access variables:

    1
    2
    3
    4
    5
    
    const user = '<postgres_user>';
    const host = 'localhost';
    const database = '<postgres_db_name>';
    const password = '<postgres_password>';
    const port = '<postgres_port>';
    
  4. Import Sequelize

    Import the necessary objects from Sequelize:

    1
    
    const { Sequelize, Model, DataTypes } = require('sequelize');
    
  5. Initialize Sequelize

    Create a new Sequelize instance with your database configuration:

    1
    2
    3
    4
    5
    6
    
    const sequelize = new Sequelize(database, user, password, {
      host,
      port,
      dialect: 'postgres',
      logging: false
    });
    

    Here, we specify dialect: 'postgres' to indicate we are using PostgreSQL. We also disable SQL query logging to keep the console output clean, though you might enable it during debugging.

Creating a Sequelize Model

Models in Sequelize represent tables in your database. Each model defines the structure of the corresponding table and provides methods for interacting with it.

  1. Define a Model

    Suppose you have a table named cats with columns name and age. You can define a Sequelize model for this table as follows:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    
    class Cat extends Model {}
    
    Cat.init({
      name: {
        type: DataTypes.STRING,
        allowNull: false
      },
      age: {
        type: DataTypes.INTEGER,
        allowNull: false
      }
    }, {
      sequelize,
      modelName: 'cat',
      timestamps: false
    });
    

    In this example:

    • Cat extends Sequelize’s Model class.
    • init() sets up the model with column definitions and configuration.
    • We use DataTypes.STRING and DataTypes.INTEGER to define column types.
    • timestamps: false disables automatic timestamp fields (createdAt and updatedAt).

Querying Data

Sequelize provides a range of methods to query your database. Here’s how to retrieve data using Sequelize:

  1. Retrieve All Records

    To fetch all records from the cats table:

    1
    2
    
    const results = await Cat.findAll();
    console.log(results);
    

    findAll() returns a promise that resolves to an array of instances representing the rows in the table.

  2. Limit the Columns Retrieved

    If you only need certain columns, use the attributes option:

    1
    2
    3
    4
    
    const results = await Cat.findAll({
      attributes: ['age']
    });
    console.log(results);
    
  3. Add a WHERE Clause

    To filter results, use the where option. For example, to find all cats aged 8:

    1
    2
    3
    4
    5
    6
    
    const results = await Cat.findAll({
      where: {
        age: 8
      }
    });
    console.log(results);
    

    To find all cats aged 5 or older:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    
    const { Op } = require('sequelize');
    
    const results = await Cat.findAll({
      where: {
        age: {
          [Op.gte]: 5
        }
      }
    });
    console.log(results);
    
  4. Apply Sorting and Limiting

    To sort results and limit the number of rows returned:

    1
    2
    3
    4
    5
    6
    7
    
    const results = await Cat.findAll({
      limit: 10,
      order: [
        ['name', 'DESC']
      ]
    });
    console.log(results);
    

Inserting Data

To insert a new record into the database, use the create() method:

1
2
3
4
5
const name = 'Garfield';
const age = 8;

const result = await Cat.create({ name, age });
console.log('New Cat:', result);

Updating Data

To update existing records, use the update() method:

1
2
3
4
5
6
7
8
await Cat.update(
  { age: 9 },
  {
    where: {
      name: 'Garfield'
    }
  }
);

If you want to update all rows (be careful with this operation), omit the where clause:

1
2
3
await Cat.update(
  { age: 10 }
);

Conclusion

In this tutorial, we explored how to use Sequelize to interact with a PostgreSQL database. We started by installing and configuring Sequelize, then created models to represent database tables.

We learned how to query, insert, and update data using Sequelize’s methods. This powerful ORM simplifies working with relational databases by providing a higher-level abstraction over SQL queries.

For further exploration of Sequelize and its features, consider the following resources:

By leveraging Sequelize, you can streamline your database interactions and focus more on building robust applications. Happy coding!