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:
bashmkdir express-postgres-app cd express-postgres-app npm init -y
-
Install Dependencies: Install Express and
pg
(PostgreSQL client for Node.js) by running:bashnpm 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.javascriptconst 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.javascriptconst 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()
.javascriptapp.use(express.json());
-
Create an API to Fetch Data: Define a GET endpoint to fetch all customers from your database.
javascriptapp.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.
javascriptapp.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.
javascriptapp.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 likedotenv
. -
Start Your Application: Run your application using Node.js.
bashnode app.js
-
Testing Your APIs: You can test your APIs using tools like Postman or
curl
commands. For example, to fetch customer data:bashcurl http://localhost:3000/api/data
To add a new client:
bashcurl -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"}'