How to connect PostgreSQL with Node.js and Sequelize

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:

   npm init -y
   

2. Install Sequelize and PostgreSQL Driver

   Install Sequelize along with the PostgreSQL driver pg:

   npm install pg sequelize
   

3. Set Up Database Configuration

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

   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:

   const { Sequelize, Model, DataTypes } = require('sequelize');
   

5. Initialize Sequelize

   Create a new Sequelize instance with your database configuration:

   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:

   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:

   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:

   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:

   const results = await Cat.findAll({ where: { age: 8 }
   });
   console.log(results);
   

   To find all cats aged 5 or older:

   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:

   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:

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:

await Cat.update( { age: 9 }, { where: { name: 'Garfield' } }
);

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

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!