Tutorial: Using PostgreSQL with Node.js in Docker

Introduction

In this tutorial, we will build a simple Node.js application that interacts with a PostgreSQL database. We will use Docker and Docker Compose to manage our containers efficiently. By the end of this tutorial, you will have a functional Node.js app that can perform CRUD operations using PostgreSQL.

Prerequisites

Basic knowledge of Node.js and PostgreSQL.
Docker and Docker Compose installed on your machine.

Setting Up the Project Structure

Create a new directory for your project:

mkdir node-postgres-app
cd node-postgres-app

Create the Following File Structure:

node-postgres-app/
│
├── src/
│   ├── index.js
│   ├── db.js
│   └── .env
├── docker-compose.yml
└── nginx/
    └── default.conf

Creating a PostgreSQL Docker Service

Update `docker-compose.yml`

Here is the updated docker-compose.yml file that includes the PostgreSQL service:

# ========================== #
# === docker-compose.yml === #
# ========================== #

version: "3.8"

services:
  node-app:
    container_name: node-app-dev
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "4002:4002"
    env_file:
      - ./.env
    depends_on:
      - postgres
      - redis

  postgres:
    image: postgres:latest
    container_name: postgres-dev
    restart: always
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: example
      POSTGRES_DB: mydatabase
    volumes:
      - postgres-db:/var/lib/postgresql/data

  redis:
    image: redis:alpine
    restart: always
    ports:
      - "6379:6379"
    command: redis-server --requirepass example
    volumes:
      - redis-db:/data

  nginx:
    image: nginx:stable-alpine
    restart: always
    ports:
      - "8080:80"
    volumes:
      - ./nginx/default.conf:/etc/nginx/conf.d/default.conf
    depends_on:
      - node-app

volumes:
  postgres-db:
  redis-db:

Explanation:

The postgres service uses the official PostgreSQL Docker image and creates a database with specified credentials.
We expose port 5432 to access the PostgreSQL database from the host machine.

Configuring Node.js to Use PostgreSQL

Create `db.js` to Handle Database Connection

In src/db.js, we will set up the PostgreSQL client:

// src/db.js
const { Pool } = require("pg");
require("dotenv").config();

const pool = new Pool({
  user: process.env.DB_USER,
  host: "postgres", // Service name in docker-compose
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: 5432,
});

module.exports = pool;

Create Environment Variables

In src/.env, specify your environment variables:

DB_USER=root
DB_PASSWORD=example
DB_NAME=mydatabase

Create `index.js` to Set Up the Server

In src/index.js, set up a simple Express server and define routes for CRUD operations:

// src/index.js
const express = require("express");
const pool = require("./db");

const app = express();
app.use(express.json()); // for parsing application/json

// Create a new item
app.post("/items", async (req, res) => {
  const { name } = req.body;
  try {
    const newItem = await pool.query(
      "INSERT INTO items (name) VALUES ($1) RETURNING *",
      [name]
    );
    res.json(newItem.rows[0]);
  } catch (err) {
    console.error(err.message);
    res.status(500).send("Server Error");
  }
});

// Get all items
app.get("/items", async (req, res) => {
  try {
    const allItems = await pool.query("SELECT * FROM items");
    res.json(allItems.rows);
  } catch (err) {
    console.error(err.message);
    res.status(500).send("Server Error");
  }
});

// Start the server
const PORT = process.env.PORT || 4002;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);
});

Create the `items` Table

To create a simple table for storing items, we can use the following SQL command. We can run this SQL in a Postgres interactive terminal or set it up to run on container startup:

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

You can run this SQL command by accessing the Postgres container:

docker-compose exec postgres-dev psql -U root -d mydatabase

Once inside the PostgreSQL shell, run the above SQL command.

Running the Application

Build and Run Docker Containers

To start your application, navigate to your project directory and run:

docker-compose up --build

Testing the CRUD API

You can use Postman or any other API testing tool to test the endpoints.

Create a New Item:
Method: POST
URL: http://localhost:4002/items
Body:
```
{
  "name": "Sample Item"
}
```
Get All Items:
Method: GET
URL: http://localhost:4002/items

Conclusion

In this tutorial, we set up a Node.js application that interacts with a PostgreSQL database using Docker. We created a simple CRUD API to demonstrate how to insert and retrieve data from the PostgreSQL database. This setup can be extended to include more features as needed.