OWolf

2024-09-09 web, development, javascript

Express App with PostgreSQL Integration

By O. Wolfson

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.

Prerequisites

  • 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:

    bash
    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:

    bash
    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.

    javascript
    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.

    javascript
    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().

    javascript
    app.use(express.json());
    
  4. Create an API to Fetch Data: Define a GET endpoint to fetch all customers from your database.

    javascript
    app.get("/api/data", async (req, res) => {
      try {
        const { rows } = await pool.query("SELECT * FROM customers");
        res.json(rows);
      } 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.

    javascript
    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]);
        res.status(201).json(rows[0]);
      } 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.

    javascript
    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.

    bash
    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:

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

To add a new client:

bash
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"}'