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:
-
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
-
Install Sequelize and PostgreSQL Driver
Install Sequelize along with the PostgreSQL driver
pg
:1
npm install pg sequelize
-
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>';
-
Import Sequelize
Import the necessary objects from Sequelize:
1
const { Sequelize, Model, DataTypes } = require('sequelize');
-
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.
-
Define a Model
Suppose you have a table named
cats
with columnsname
andage
. 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’sModel
class.init()
sets up the model with column definitions and configuration.- We use
DataTypes.STRING
andDataTypes.INTEGER
to define column types. timestamps: false
disables automatic timestamp fields (createdAt
andupdatedAt
).
Querying Data
Sequelize provides a range of methods to query your database. Here’s how to retrieve data using Sequelize:
-
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. -
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);
-
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);
-
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:
|
|
Updating Data
To update existing records, use the update()
method:
|
|
If you want to update all rows (be careful with this operation), omit the where
clause:
|
|
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:
- Sequelize Documentation
- PostgreSQL Documentation
- Node.js Documentation
By leveraging Sequelize, you can streamline your database interactions and focus more on building robust applications. Happy coding!