Express Functions


Express and PostgreSQL: Integrating a Relational Database

In the realm of web development, the need to store, manage, and retrieve data efficiently is paramount. This is where databases come into play. Among the various types of databases, relational databases have stood the test of time due to their structured data storage and powerful querying capabilities. In this tutorial, we will delve into the integration of Express.js, a popular Node.js web application framework, with PostgreSQL, a robust open-source relational database management system. By combining the capabilities of these two technologies, you can build dynamic and data-driven applications that scale seamlessly.

Express and PostgreSQL: Integrating a Relational Database

1. Prerequisites

Before we dive into the integration process, let’s ensure you have the necessary tools installed:

  • Node.js: Download and install the latest version of Node.js from the official website.
  • PostgreSQL: Install PostgreSQL on your machine or use a cloud-based service for database hosting.
  • Express.js: Set up a new Express.js application using the Express Generator or create one from scratch.

1.1 Setting Up the Project

Let’s start by creating a new Express.js project and configuring it to work with PostgreSQL.

Step 1: Initialize a New Project

Open your terminal and run the following commands:

# Create a new directory for your project
mkdir express-postgresql-integration
cd express-postgresql-integration

# Initialize a new Node.js project
npm init -y

Step 2: Install Dependencies

We’ll need a few packages to facilitate communication between Express.js and PostgreSQL. Install them using the following command:

npm install express pg

  • Express.js (express): This is the backbone of our web application, helping us create routes, handle requests, and manage middleware.
  • pg: This package provides a PostgreSQL client for Node.js, enabling us to interact with the database.

Step 3: Create Configuration Files

Create a new file named config.js in your project directory to store your database connection configuration:

// config.js
module.exports = {
  database: 'your_database_name',
  user: 'your_database_user',
  password: 'your_database_password',
  host: 'localhost', // Change this if your database is hosted elsewhere
  port: 5432, // Default PostgreSQL port

Replace the placeholders with your actual database credentials.

2. Establishing Database Connection

Now that our project is set up and configured, let’s establish a connection to the PostgreSQL database using the pg package.

Step 4: Create a Database Connection Module

In your project directory, create a new file named db.js to handle the database connection:

// db.js
const { Pool } = require('pg');
const config = require('./config');

const pool = new Pool(config);

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

Here, we’re using the Pool class from the pg package to manage a pool of client connections. This improves performance by reusing connections instead of creating new ones for each request.

3. Creating Routes and Handling Queries

With the database connection established, let’s move on to creating routes in Express.js and handling PostgreSQL queries.

Step 5: Set Up Express Routes

In your project directory, create a new file named app.js or index.js (depending on your preference) and set up basic Express routes:

// app.js (or index.js)
const express = require('express');
const db = require('./db');

const app = express();
const PORT = process.env.PORT || 3000;

app.get('/', async (req, res) => {
  try {
    const data = await db.query('SELECT * FROM your_table');
  } catch (error) {
    console.error('Error fetching data:', error);
    res.status(500).json({ error: 'Internal server error' });

app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);

In this example, we’ve created a simple route that queries the database and returns the results as JSON.

Step 6: Test the Endpoint

Run your Express application using the following command:

node app.js

Visit http://localhost:3000 in your browser or use tools like Postman to test the endpoint. You should see the JSON data retrieved from your PostgreSQL database.

4. Handling Data Insertion

Data retrieval is only one part of the equation. Let’s explore how to handle data insertion into the database.

Step 7: Create an Insert Route

Expand your Express application by adding a route to insert data into the database:

// app.js (or index.js)
// ... (previous code)'/add', async (req, res) => {
  const { name, email } = req.body;
  if (!name || !email) {
    return res.status(400).json({ error: 'Name and email are required' });

  try {
    const query = 'INSERT INTO your_table (name, email) VALUES ($1, $2) RETURNING *';
    const values = [name, email];
    const data = await db.query(query, values);
  } catch (error) {
    console.error('Error inserting data:', error);
    res.status(500).json({ error: 'Internal server error' });

// ... (remaining code)

This route receives POST requests with name and email fields, inserts the data into the database, and returns the inserted record.

Step 8: Test the Insert Endpoint

Use a tool like Postman to send a POST request to http://localhost:3000/add with JSON data containing name and email fields. The response should contain the newly inserted data.


Integrating Express.js with PostgreSQL opens up a world of possibilities for building robust and scalable web applications. By following this guide, you’ve learned how to set up a basic project, establish a connection to the database, create routes, and handle data retrieval and insertion. This is just the beginning; you can further enhance your application by implementing authentication, optimizing queries, and structuring your code for maintainability. With this knowledge, you’re well on your way to creating powerful and data-driven web applications.

Remember, practice makes perfect. Experiment with different scenarios, explore advanced PostgreSQL features, and continue to refine your Express.js skills. Happy coding!

Previously at
Flag Argentina
time icon
Experienced Software Engineer skilled in Express. Delivered impactful solutions for top-tier companies with 17 extensive professional experience.