Executing SQL Query in PostgreSQL Database in Node.js Express.js Project

In this article, we will learn how to execute SQL queries in a PostgreSQL database in a Node.js Express.js project. We will be using the ‘pg’ npm package to interact with the PostgreSQL database.

Setting up the Project

First, make sure you have Node.js and npm installed. Then, create a new directory for your project and run the following command in your terminal to initialize a new Node.js project:

      
        $ mkdir postgresql-node-express
        $ cd postgresql-node-express
        $ npm init -y
      
    

Next, install the required npm packages by running the following command:

      
        $ npm install express pg
      
    

Connecting to the PostgreSQL Database

Create a new file called ‘db.js’ and add the following code to connect to the PostgreSQL database:

      
        const { Pool } = require('pg');
        const pool = new Pool({
          user: 'your_username',
          host: 'localhost',
          database: 'your_database',
          password: 'your_password',
          port: 5432,
        });

        module.exports = {
          query: (text, params) => pool.query(text, params),
        };
      
    

Executing SQL Queries

In your Express.js route handlers, you can use the ‘query’ method exported from ‘db.js’ to execute SQL queries. Here’s an example of executing a simple SELECT query:

      
        const db = require('./db');

        app.get('/users', async (req, res) => {
          try {
            const { rows } = await db.query('SELECT * FROM users');
            res.json(rows);
          } catch (err) {
            console.error(err);
            res.status(500).send('Internal Server Error');
          }
        });
      
    

Conclusion

In this article, we learned how to execute SQL queries in a PostgreSQL database in a Node.js Express.js project. The ‘pg’ npm package provides a simple and efficient way to interact with PostgreSQL databases in a Node.js application. We hope this article helps you in your future projects!