Sling Academy
Home/Node.js/Sequelize.js: How to connect to PostgreSQL database

Sequelize.js: How to connect to PostgreSQL database

Last updated: December 29, 2023

Overview

Sequelize.js is a promise-based ORM for Node.js, which supports multiple database engines, including PostgreSQL. It offers solid transaction support, relations, eager and lazy loading, read replication, and more. This tutorial aims to guide you through connecting a Node.js application to a PostgreSQL database using Sequelize.

We’ll start by setting up a basic connection, move on to defining models and relationships, and finish with some advanced configurations for production environments.

Getting Started with Sequelize

First, ensure you have Node.js and npm installed. Then, set up a new Node.js project and install Sequelize along with the PostgreSQL driver:

npm init -y
npm install sequelize pg pg-hstore

With the dependencies in place, you can begin coding your connection. Start by requiring Sequelize and setting up a new instance.

const Sequelize = require('sequelize');
 const sequelize = new Sequelize('database', 'username', 'password', {
   host: 'localhost',
   dialect: 'postgres'
 });

Replace the ‘database’, ‘username’, and ‘password’ with your PostgreSQL credentials. The host should be where your database is located; ‘localhost’ is used for a local database.

Testing the Connection

Use the authenticate method to test the connection.

sequelize.authenticate().then(() => {
   console.log('Connection has been established successfully.');
 }).catch(err => {
   console.log('Unable to connect to the database:', err);
 });

If the connection is successful, you should see a console message confirming it. Any errors will also be logged to the console.

Defining Models

Models in Sequelize represent tables in your database. Define a model using the define method:

const User = sequelize.define('user', {
   username: Sequelize.STRING,
   birthday: Sequelize.DATE
 });
 

This will create a user model with ‘username’ and ‘birthday’ columns.

Synchronizing the Model with the Database

To create the table in your database, use the sync method:

User.sync().then(() => {
   // Table created
   return User.create({
     username: 'janedoe',
     birthday: new Date(1980, 6, 20)
   });
 });

Advanced Configurations

For production, you might want to use a configuration file to handle different database settings across environments.

const env = process.env.NODE_ENV || 'development';
 const config = require(__dirname + '/../config/config.json')[env];
 const sequelize = new Sequelize(config.database, config.username, config.password, config);

Here, Sequelize initializes based on a configuration file that contains environment-specific settings.

Pooling

Database pooling can greatly enhance performance. Sequelize makes pooling configuration straightforward:

const sequelize = new Sequelize('database', 'username', 'password', {
   host: 'localhost',
   dialect: 'postgres',
   pool: {
     max: 9,
     min: 0,
     acquire: 30000,
     idle: 10000
   }
 });
 

Conclusion

In this tutorial, we covered the basics of connecting a Node.js application to a PostgreSQL database using Sequelize. Starting from installing the necessary packages, we moved to establishing a connection, defining models, syncing them with the database, and even configuring advanced options for production environments. With the knowledge gained, you can start integrating Sequelize into your Node.js projects for seamless database interactions.

Next Article: Sequelize.js: How to Connect to SQLite Database

Previous Article: Sequelize.js: How to connect to MySQL database

Series: Sequelize.js Tutorials

Node.js

You May Also Like

  • NestJS: How to create cursor-based pagination (2 examples)
  • Cursor-Based Pagination in SequelizeJS: Practical Examples
  • MongooseJS: Cursor-Based Pagination Examples
  • Node.js: How to get location from IP address (3 approaches)
  • SequelizeJS: How to reset auto-increment ID after deleting records
  • SequelizeJS: Grouping Results by Multiple Columns
  • NestJS: Using Faker.js to populate database (for testing)
  • NodeJS: Search and download images by keyword from Unsplash API
  • NestJS: Generate N random users using Faker.js
  • Sequelize Upsert: How to insert or update a record in one query
  • NodeJS: Declaring types when using dotenv with TypeScript
  • Using ExpressJS and Multer with TypeScript
  • NodeJS: Link to static assets (JS, CSS) in Pug templates
  • NodeJS: How to use mixins in Pug templates
  • NodeJS: Displaying images and links in Pug templates
  • ExpressJS + Pug: How to use loops to render array data
  • ExpressJS: Using MORGAN to Log HTTP Requests
  • NodeJS: Using express-fileupload to simply upload files
  • ExpressJS: How to render JSON in Pug templates