Part 2 - Adding a PostgreSQL Database to the RESTful API

Welcome back to our continuing saga of building a modern RESTful API. Last time, we set the stage with Node.js and Express, creating a marketplace of data that handled vendor information with a simple in-memory array. But let’s face it: an array is like a cardboard box—it’s great for storing things temporarily, but not exactly robust or scalable. Today, we’re upgrading to a proper database: PostgreSQL, the reliable workhorse of relational databases.

So grab your favorite beverage and get ready, because we’re about to breathe new life into our API, making it persistent, powerful, and production-ready.

Setting the Scene: Why PostgreSQL?

PostgreSQL, affectionately known as Postgres, is one of the most versatile and stable relational databases out there. It’s open-source, battle-tested, and perfect for handling structured data with relationships—like vendors and products in our marketplace. Think of it as upgrading from that cardboard box to a secure, organized warehouse.

Step 1: Setting Up PostgreSQL

Before we can connect Postgres to our Express app, we need to make sure it’s installed and running on our system. Here’s a quick rundown:

1. Install PostgreSQL: If you haven’t already, head to PostgreSQL’s website and download the latest version. Follow the installation instructions for your operating system.

Create a Database: Open the Postgres command line (psql) and create a new database:

CREATE DATABASE marketplace;

Now we have a database called marketplace ready to store our vendors and products.

Start the PostgreSQL Server: Once installed, make sure your Postgres server is running. You can usually start it with a command like:

sudo service postgresql start

Step 2: Setting Up Your Project for Postgres

Back in your project directory, we need to add some packages to help us communicate with Postgres:

npm install pg

The pg package is a PostgreSQL client for Node.js, and it’s our ticket to interacting with the database from within our API.

1400+ Free HTML Templates
325+ Free News Articles
62+ Free AI Prompts
292+ Free Code Libraries
51+ Free Code Snippets & Boilerplates for Node, Nuxt, Vue, and more!
25+ Free Open Source Icon Libraries

Step 3: Connecting to the Database

In app.js, let’s set up a connection to Postgres. We’ll use environment variables to keep our credentials secure, so create a .env file in your project root:

DB_HOST=localhost
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=marketplace
DB_PORT=5432

Then, update your app.js to include the connection setup:

require('dotenv').config(); // Load environment variables
const express = require('express');
const { Pool } = require('pg');
const app = express();
const port = 3000;

// Set up a connection pool to the database
const pool = new Pool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    port: process.env.DB_PORT
});

app.use(express.json());

// Test the connection
pool.query('SELECT NOW()', (err, res) => {
    if (err) {
        console.error('Error connecting to the database', err);
    } else {
        console.log('Connected to the database at', res.rows[0].now);
    }
});

app.get('/', (req, res) => {
    res.send('Welcome to our RESTful API, now powered by PostgreSQL!');
});

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

With this setup, our Express app connects to the Postgres database, and we’ve confirmed the connection by querying the current time. If everything’s working, you should see a friendly timestamp in your terminal.

Step 4: Creating Tables for Vendors and Products

Now it’s time to structure our data. Vendors and products have a relationship: each product belongs to a vendor. We’ll define this relationship with two tables: vendors and products.

Open psql and run the following SQL commands to create these tables:

-- Create the vendors table
CREATE TABLE vendors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    specialty VARCHAR(255)
);

-- Create the products table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    vendor_id INTEGER REFERENCES vendors(id),
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

Here’s what we’ve done:

• Vendors Table: Holds information about each vendor, with id as the primary key.
• Products Table: Stores product details, with a vendor_id column that references the vendors table, establishing a relationship between vendors and their products.

Step 5: Updating Your API to Use Postgres

Now that our tables are ready, let’s update our CRUD operations to interact with the database instead of the in-memory array.

Creating a Vendor

app.post('/vendors', async (req, res) => {
    try {
        const { name, specialty } = req.body;
        const result = await pool.query(
            'INSERT INTO vendors (name, specialty) VALUES ($1, $2) RETURNING *',
            [name, specialty]
        );
        res.status(201).json(result.rows[0]);
    } catch (error) {
        console.error('Error creating vendor', error);
        res.status(500).send('Internal Server Error');
    }
});

Notice how we use pool.query() to execute an SQL query. The $1 and $2 are placeholders for our name and specialty values, protecting us from SQL injection attacks.

Reading All Vendors

app.get('/vendors', async (req, res) => {
    try {
        const result = await pool.query('SELECT * FROM vendors');
        res.json(result.rows);
    } catch (error) {
        console.error('Error fetching vendors', error);
        res.status(500).send('Internal Server Error');
    }
});

Creating a Product

app.post('/products', async (req, res) => {
    try {
        const { vendor_id, name, price } = req.body;
        const result = await pool.query(
            'INSERT INTO products (vendor_id, name, price) VALUES ($1, $2, $3) RETURNING *',
            [vendor_id, name, price]
        );
        res.status(201).json(result.rows[0]);
    } catch (error) {
        console.error('Error creating product', error);
        res.status(500).send('Internal Server Error');
    }
});

And there you have it: a fully functional API that now uses PostgreSQL to store and retrieve data. No more data disappearing when the server restarts!

The Benefits of Adding PostgreSQL

1. Persistent Data: Your vendor and product information is now safe and sound in a proper database, even if the server crashes.
2. Data Integrity: Postgres enforces data types and relationships, making your data more reliable and structured.
3. Scalability: As your API grows, PostgreSQL can handle the increased load with ease, thanks to its powerful indexing and querying capabilities.

Conclusion: From Temporary to Timeless

By adding a PostgreSQL database to our Express API, we’ve transformed a simple, ephemeral data store into a robust, persistent system. Our API is now ready to handle real-world data with all the reliability of a well-oiled machine.

Stay tuned for more in this series as we continue to refine and expand our API. Because in the world of web development, there’s always another level to explore—and today, we’ve leveled up in a big way.

For more tips on web development, check out DailySandbox and sign up for our free newsletter to stay ahead of the curve!