Express PostgreSQL CRUD API Starter

An Express 5 CRUD API starter for Node.js 22 in plain JavaScript that stores one resource, items, in PostgreSQL through node-postgres and a connection pool. It is for developers who want a tested create, read, update and delete service to rename, with input validation, limit and offset paging, JSON errors and a /health check that queries the database.

by Light CloudCRUD APIs

Features

  • GET, POST, PUT and DELETE on /items, with GET /items?limit=20&offset=0 paging oldest first and limit capped at 100
  • The items table is created on start with CREATE TABLE IF NOT EXISTS, so there is no migration step to run
  • Validation without a library: name is trimmed and must be 1 to 200 characters, description a string or absent
  • JSON errors: 400 for bad input or a malformed body, 404 for an unknown item, a non-numeric id or an unknown route
  • GET /health runs SELECT 1 and answers 200, or 503 when the database does not answer
  • A node-postgres pool of up to 5 connections with a 5 second connect timeout; the app exits at start if it cannot connect
  • Graceful shutdown on SIGTERM: in-flight requests finish, then the pool closes
  • 11 node:test tests: unit and HTTP tests run on a fake pool, and a full CRUD test runs when DATABASE_URL is set

Tech stack

What you need

  • A paid plan: deploying creates a PostgreSQL database, which the Free plan does not include
  • To run it locally: Node.js 22 or newer, npm, and Docker for a local PostgreSQL
  • No API keys: it starts with nothing to configure
  • DATABASE_URL: Light Cloud sets it on deploy; for local runs you export it, as the app does not read .env

About

createApp(pool) in src/app.js builds the app without listening, and src/server.js creates the items table before it starts the server, so a broken database connection fails the deploy rather than the first request. Express 5 sends errors thrown in async handlers to the JSON error handler, which is why the routes have no try/catch. The SQL is a handful of parameterised queries in src/routes/items.js, and express and pg are the only runtime dependencies. Renaming items to your own resource starts in src/db.js, src/lib/validation.js and that routes file. On Light Cloud the deploy runs npm start with DATABASE_URL set, and every push to your copy redeploys.

FAQ

How do I deploy an Express API with a PostgreSQL database?

Press Deploy on this template: Light Cloud copies the repo into your GitHub account, creates a PostgreSQL database and sets DATABASE_URL on the app, with no secrets to fill in. Databases need a paid plan.

How do I connect Express to PostgreSQL locally?

Start PostgreSQL in Docker with docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres:16, export DATABASE_URL=postgresql://postgres:postgres@localhost:5432/postgres and run npm run dev. Leave sslmode out of the URL, since the local container has no TLS.

How do I add a column to the items table?

Add it to the CREATE TABLE in src/db.js, to parseItem in src/lib/validation.js and to the queries in src/routes/items.js. CREATE TABLE IF NOT EXISTS does not change a table that already exists, so run an ALTER TABLE once on an existing database.

Does this Express CRUD API use an ORM like Prisma?

No. It uses node-postgres with a few parameterised SQL queries, which keeps the runtime dependencies to express and pg. You can add Prisma, Drizzle or Knex later without changing the shape of the routes.

Can I host this Express API without Light Cloud?

Yes. It has no Light Cloud code: any host with Node.js 22 and a reachable PostgreSQL runs it with npm install --omit=dev and npm start, given DATABASE_URL and PORT.

Price
Free
Framework
Express · JavaScript
Updated