Sequelize.js: How to connect to PostgreSQL database

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.