How to connect a nodejs application to PostgreSQL

A nodejs application on its own doesn't serve any purpose. The application needs to fetch data from an API backend or the application needs to connect to a DB to carry out the business logic.

In our previous post we saw how can bring up a postgres DB using docker.

In this article, we will see how a nodejs app can be connected to postgresql database and get the data from the DB. We will be using sequilize to connect to the database. Sequelize is an ORM (Object Relational Mapping) library to connect to DB and execute the SQL statements through javascript methods.

Sequelize is a promise-based Node.js ORM tool for Postgres, MySQL, MariaDB, SQLite, Microsoft SQL Server, Oracle Database, Amazon Redshift and Snowflake’s Data Cloud. It features solid transaction support, relations, eager and lazy loading, read replication and more.

Let us start that by adding sequelize module to our app. We will install both postgres and sequelize to our app using npm.

    npm install --save sequelize
    npm install --save pg pg-hstore # Postgres

Connecting to a database

To connect to our database, we will need to create a Sequelize instance. This can be done by passing the connection parameters to the constructor or a single connection URI.

NOTE: Remember that, we will use the config module to configure the username, password and host for the postgres database.

Step 1: Create a Models directory

We will use Models directory for all our DB related methods. Starting from connection to defining schemas, we will use this models directory.

Start by creating an index.js in the Models directory.

//importing modules
const { Sequelize } = require("sequelize");
const config = require("config");

//Database connection with dialect of postgres specifying the database we are using
//port for my database is 5433
//database name is discover
const sequelize = new Sequelize(config.dbConfig.dbName, config.dbConfig.dbUser,config.dbConfig.dbPassword, {
    dialect: 'postgres',
    port: config.dbConfig.port,
    host: config.dbConfig.host,
}
);

//checking if connection is done
sequelize
  .authenticate()
  .then(() => {
    console.log(`Database connected to discover`);
  })
  .catch((err) => {
    console.log(err);
  });

const db = {};
db.Sequelize = Sequelize;
db.sequelize = sequelize;


//exporting the module
module.exports = db;