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.

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

   Copy

   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

   Copy

   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

   Copy

   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

   Copy

   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

   Copy

   app.use(express.json());
   

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

   javascript

   Copy

   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

   Copy

   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

   Copy

   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

   Copy

   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

   Copy

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