| 2024-03-22

Express App with PostgreSQL Integration

    Create a simple Node.js application using Express and integrate it with a PostgreSQL database using the pg library. Our application will provide APIs to fetch and add customer data in a PostgreSQL database.


    • Node.js installed on your machine.
    • PostgreSQL server accessible either locally or remotely.
    • Basic understanding of JavaScript and Node.js.

    Step 1: Setting Up Your Project

    1. Initialize a New Node.js Project: Create a new directory for your project and initialize a new Node.js project by running:

      mkdir express-postgres-app
      cd express-postgres-app
      npm init -y
    2. Install Dependencies: Install Express and pg (PostgreSQL client for Node.js) by running:

      npm install express pg

    Step 2: Writing Your Express Application

    1. Create an app.js File: This file will contain our application code. Start by requiring necessary modules and setting up the Express app.

      const express = require("express");
      const { Pool } = require("pg");
      const app = express();
      const PORT = process.env.PORT || 3000;
    2. Configure PostgreSQL Connection: Use the pg library to connect to your PostgreSQL database. Ensure you use environment variables for sensitive data like passwords.

      const password = process.env.PG_PASSWORD;
      const pool = new Pool({
        connectionString: `postgres://default:${password}@YOUR_DATABASE_HOST:5432/YOUR_DATABASE_NAME?sslmode=require`,
    3. Set Up Express JSON Middleware: To handle JSON payload in requests, use express.json().

    4. Create an API to Fetch Data: Define a GET endpoint to fetch all customers from your database.

      app.get("/api/data", async (req, res) => {
        try {
          const { rows } = await pool.query("SELECT * FROM customers");
        } catch (error) {
          console.error("Error fetching data from the database:", error);
          res.status(500).send("Server error");
    5. Create an API to Add a Client: Define a POST endpoint to add new customer data to your database.

      app.post("/api/clients", async (req, res) => {
        const { id, name, email, image_url } = req.body;
        try {
          const query = `
            INSERT INTO customers (id, name, email, image_url) VALUES ($1, $2, $3, $4)
            RETURNING *;`;
          const { rows } = await pool.query(query, [id, name, email, image_url]);
        } catch (error) {
          console.error("Error adding client to the database:", error);
          res.status(500).send("Server error");
    6. Start Your Express Server: Finally, listen on the specified port.

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

    Step 3: Running Your Application

    1. Set Your Database Password: Before running your application, make sure to set the PG_PASSWORD environment variable to your PostgreSQL user's password. This can be done in your terminal or within a .env file if you're using a package like dotenv.

    2. Start Your Application: Run your application using Node.js.

      node app.js
    3. Testing Your APIs: You can test your APIs using tools like Postman or curl commands. For example, to fetch customer data:

      curl http://localhost:3000/api/data

    To add a new client:

    curl -X POST http://localhost:3000/api/clients \
         -H "Content-Type: application/json" \
         -d '{"id":"YOUR_UUID", "name":"John Doe", "email":"john@example.com", "image_url":"http://example.com/image.jpg"}'

    Thanks for reading. If you enjoyed this post, I invite you to explore more of my site. I write about web development, programming, and other fun stuff.