How to Use PostgreSQL with Node.js and Express.js

In this tutorial, we will learn how to use PostgreSQL with Node.js and Express.js to build a web application. PostgreSQL is a powerful, open-source object-relational database system, while Node.js is a JavaScript runtime environment that allows us to run JavaScript on the server-side. Express.js is a fast and minimalist web application framework that runs on top of Node.js.

By the end of this tutorial, we will have a basic understanding of how to connect to a PostgreSQL database, perform CRUD (Create, Read, Update, Delete) operations, and handle errors in a Node.js and Express.js application.

Prerequisites

Before we begin, make sure you have the following installed:

Node.js (version 10 or above)
PostgreSQL (version 9.6 or above)

Setting up the Project

Let’s start by setting up a new Node.js and Express.js project.

Create a new directory for your project and navigate into it using your terminal.

mkdir node-postgresql
cd node-postgresql

Initialize a new Node.js project using the npm init command.

npm init -y

This will generate a package.json file in your project directory.

Install the required dependencies for our project, which include express, pg-promise, and dotenv.

npm install express pg-promise dotenv

Now that we have set up our project, let’s move on to connecting it to a PostgreSQL database.

Connecting to the PostgreSQL Database

To connect to a PostgreSQL database from our Node.js and Express.js application, we will use the pg-promise library. pg-promise is a PostgreSQL driver for Node.js that provides a higher-level API for interacting with the database.

Create a new file called db.js in the root of your project.

touch db.js

Open the db.js file and import the required dependencies.

// db.js

const pgp = require('pg-promise')();
require('dotenv').config();

// Database configuration
const config = {
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD
};

// Create a new database connection
const db = pgp(config);

module.exports = db;

In the above code, we import the pg-promise and dotenv packages. pg-promise is initialized, and a new database connection is created using the configuration details provided in the .env file (which we will create shortly).

Create a new file called .env in the root of your project.

touch .env

Open the .env file and add the following environment variables.

DB_HOST=localhost
DB_PORT=5432
DB_NAME=mydatabase
DB_USER=postgres
DB_PASSWORD=mypassword

Replace the placeholder values with your PostgreSQL database configuration details.

Now, we can import the database connection in our main app.js file.

// app.js

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

const app = express();

// Express.js app configuration

// ...

// Start the server
app.listen(3000, () => {
  console.log('Server started on port 3000');
});

In the above code, we import the db connection object from the db.js file and start the Express.js server.

With the connection set up, we can now move on to performing CRUD operations on our PostgreSQL database.

Performing CRUD Operations

In this section, we will create a simple RESTful API using Express.js to perform CRUD operations on our PostgreSQL database.

Create a new file called routes.js in the root of your project.

touch routes.js

Open the routes.js file and add the following code.

// routes.js

const express = require('express');
const router = express.Router();
const db = require('./db');

// Create a new record
router.post('/records', async (req, res, next) => {
  try {
    const { name, age } = req.body;
    const record = await db.none('INSERT INTO records(name, age) VALUES($1, $2)', [name, age]);
    res.json({
      message: 'Record created successfully'
    });
  } catch (error) {
    next(error);
  }
});

// Get all records
router.get('/records', async (req, res, next) => {
  try {
    const records = await db.any('SELECT * FROM records');
    res.json(records);
  } catch (error) {
    next(error);
  }
});

// Get a single record by ID
router.get('/records/:id', async (req, res, next) => {
  try {
    const { id } = req.params;
    const record = await db.one('SELECT * FROM records WHERE id = $1', id);
    res.json(record);
  } catch (error) {
    next(error);
  }
});

// Update a record by ID
router.put('/records/:id', async (req, res, next) => {
  try {
    const { id } = req.params;
    const { name, age } = req.body;
    const record = await db.none('UPDATE records SET name = $1, age = $2 WHERE id = $3', [name, age, id]);
    res.json({
      message: 'Record updated successfully'
    });
  } catch (error) {
    next(error);
  }
});

// Delete a record by ID
router.delete('/records/:id', async (req, res, next) => {
  try {
    const { id } = req.params;
    const record = await db.none('DELETE FROM records WHERE id = $1', id);
    res.json({
      message: 'Record deleted successfully'
    });
  } catch (error) {
    next(error);
  }
});

module.exports = router;

In the above code, we define various routes using the Express.js Router middleware. Each route corresponds to a specific CRUD operation on our PostgreSQL database.

Open the app.js file and import the routes.js file.

// app.js

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

const app = express();

// Express.js app configuration

// ...

// Routes
app.use('/', routes);

// Start the server
app.listen(3000, () => {
  console.log('Server started on port 3000');
});

By adding app.use('/', routes);, we are telling Express.js to use the routes defined in the routes.js file for all paths starting with /.

Start the application.

node app.js

Now that our application is running, we can test our endpoints using a tool like curl or an API testing platform like Postman.

To create a new record, send a POST request to `http://localhost:3000/records` with the following JSON payload:

curl -X POST -H "Content-Type: application/json" -d '{
  "name": "John Smith",
  "age": 30
}' http://localhost:3000/records

To fetch all records, send a GET request to `http://localhost:3000/records`:

curl http://localhost:3000/records

To fetch a single record by ID, send a GET request to `http://localhost:3000/records/{id}`:

curl http://localhost:3000/records/1

To update a record by ID, send a PUT request to `http://localhost:3000/records/{id}` with the updated JSON payload:

curl -X PUT -H "Content-Type: application/json" -d '{
  "name": "John Doe",
  "age": 35
}' http://localhost:3000/records/1

To delete a record by ID, send a DELETE request to `http://localhost:3000/records/{id}`:

curl -X DELETE http://localhost:3000/records/1

Congratulations! You have successfully connected to a PostgreSQL database, performed CRUD operations, and handled errors in a Node.js and Express.js application.

Conclusion

In this tutorial, we learned how to use PostgreSQL with Node.js and Express.js to build a web application. We started by setting up a new Node.js and Express.js project, and we connected it to a PostgreSQL database using the pg-promise library.

We then created a simple RESTful API to perform CRUD operations on our PostgreSQL database, and we tested the endpoints using curl or Postman.

Feel free to explore more features of PostgreSQL, Node.js, and Express.js to enhance your web application.

Prerequisites

Setting up the Project

Connecting to the PostgreSQL Database

Performing CRUD Operations

Conclusion

Related Post