Build a Node + Express + PostgreSQL API

This guide builds a notes API - create, list and delete - with Express and PostgreSQL, using patterns that hold up in real projects: a connection pool, parameterised queries, input validation and configuration from environment variables. Then it deploys the API with a free hosted database.

1. Get a PostgreSQL database

The quickest route is a hosted database, so you don't install anything locally. Create a free project on Neon (Supabase works too), and copy the connection string it gives you. It looks like:

postgresql://user:[email protected]/neondb?sslmode=require

In the provider's SQL editor, create the table:

CREATE TABLE notes (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  body TEXT NOT NULL CHECK (char_length(body) BETWEEN 1 AND 2000),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

The CHECK constraint means the database itself refuses empty or oversized notes, even if a bug in your code lets one through.

2. Set up the project

mkdir notes-api
cd notes-api
npm init -y
npm install express pg

Open package.json and add "type": "module" so you can use import syntax, plus a start script:

{
  "type": "module",
  "scripts": {
    "dev": "node --watch --env-file=.env server.js",
    "start": "node server.js"
  }
}

(Keep the other fields npm generated; just add these.) Create .env with your connection string, and .gitignore so it never gets committed:

# .env
DATABASE_URL=postgresql://user:[email protected]/neondb?sslmode=require

# .gitignore
node_modules/
.env

3. A shared connection pool

Create db.js:

import pg from "pg";

export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 5,
});

A pool keeps a few connections open and lends them to requests as needed. Creating a new connection for every request is slow and will exhaust the database's connection limit under load.

4. The API

Create server.js:

import express from "express";
import { pool } from "./db.js";

const app = express();
app.use(express.json());

app.get("/api/notes", async (req, res) => {
  const { rows } = await pool.query(
    "SELECT id, body, created_at FROM notes ORDER BY id DESC LIMIT 100",
  );
  res.json(rows);
});

app.post("/api/notes", async (req, res) => {
  const body = typeof req.body?.body === "string" ? req.body.body.trim() : "";
  if (body.length < 1 || body.length > 2000) {
    return res.status(400).json({ error: "body must be 1-2000 characters" });
  }
  const { rows } = await pool.query(
    "INSERT INTO notes (body) VALUES ($1) RETURNING id, body, created_at",
    [body],
  );
  res.status(201).json(rows[0]);
});

app.delete("/api/notes/:id", async (req, res) => {
  const id = Number(req.params.id);
  if (!Number.isInteger(id)) {
    return res.status(400).json({ error: "invalid id" });
  }
  const { rowCount } = await pool.query("DELETE FROM notes WHERE id = $1", [id]);
  res.status(rowCount ? 204 : 404).end();
});

app.use((err, req, res, next) => {
  console.error(err);
  res.status(500).json({ error: "something went wrong" });
});

const port = process.env.PORT || 3000;
app.listen(port, () => console.log(`API listening on port ${port}`));

What makes this code safe

• Parameterised queries. $1 placeholders send values separately from the SQL, so user input can never change the query. Never build SQL with template strings.
• Validation at the edge. The API checks the type and length of body and the format of id before touching the database, and returns 400 with a useful message.
• Express 5 error handling. If a query throws inside an async handler, Express 5 passes the error to the error middleware at the bottom automatically - older Express 4 tutorials wrap everything in try/catch because v4 didn't.
• Accurate status codes. 201 for created, 204 for deleted, 404 when there was nothing to delete.

5. Run and test it

npm run dev

--watch restarts the server when you save a file, and --env-file loads .env - both built into modern Node, no extra packages. In another terminal:

curl -X POST http://localhost:3000/api/notes -H "Content-Type: application/json" -d '{"body": "First note"}'
curl http://localhost:3000/api/notes
curl -i -X DELETE http://localhost:3000/api/notes/1

Try sending an empty body or a non-numeric id, and confirm you get a 400, not a crash.

6. Deploy to Render

1. Commit and push the project to a GitHub repository (your .gitignore keeps .env out).
2. In Render, choose New → Web Service and connect the repository.
3. Set Build command to npm install and Start command to npm start.
4. Under Environment, add DATABASE_URL with the same connection string.
5. Create the service. Render sets PORT for you, which the code already reads.

Your API is now live at a .onrender.com address. On the free tier it sleeps after a period of inactivity, so the first request after a pause is slow.

Next steps

• Calling it from a browser app on another domain? Install the cors package and allow your frontend's exact origin.
• Schema changes belong in migration files (node-pg-migrate, Drizzle or Prisma) rather than ad-hoc SQL.
• Validation scales better with a schema library like Zod once you have more than a couple of fields.
• Tests: Node's built-in node:test runner plus Supertest can exercise every route.
• A frontend: pair it with React - see the React + Node + PostgreSQL pick.