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

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

Install Dependencies: Install Express and pg (PostgreSQL client for Node.js) by running:
```
bash
npm install express pg
```

Step 2: Writing Your Express Application

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;

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`,
});

Set Up Express JSON Middleware: To handle JSON payload in requests, use express.json().

javascript
app.use(express.json());

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");
  }
});

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");
  }
});

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

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.
Start Your Application: Run your application using Node.js.
```
bash
node app.js
```
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"}'