Users & Posts REST API – TypeScript, Express, PostgreSQL

This project contains a TypeScript and Express REST API that manages users and posts in PostgreSQL.

Overview

• TypeScript Express app with Zod v4 validation
• PostgreSQL 18 persistence via Drizzle ORM
• Native PostgreSQL uuidv7() primary keys
• JWT authentication for protected write routes
• User CRUD endpoints:
  • POST /users
  • GET /users
  • GET /users/:id
  • PATCH /users/:id
  • DELETE /users/:id
• Auth endpoint:
  • POST /auth/login
• Post CRUD endpoints:
  • POST /posts
  • PUT /posts/:id
  • GET /posts
  • GET /posts/:id
  • DELETE /posts/:id
• Jest test suite backed by an in-memory PostgreSQL-compatible database

Data Model

• users stores the account record, password hash, and timestamps
• posts stores post records and references users.id
• user_favorite_foods stores one favorite food per row, unique per user, with stable ordering
• user_hobbies stores one hobby per row, unique per user, with stable ordering and frequency per week

Prerequisites

• Node.js: 24.14.0
• npm: 11.9.0
• Docker, if you want the local PostgreSQL helper container
• PostgreSQL 18, if you want to use an external database instead of Docker

Quick Start

1. Install dependencies

npm install

2. Create the application env file

cp .env.example .env

Supported variables:

• PORT: HTTP port, defaults to 3111
• DATABASE_URL: PostgreSQL connection string
• DATABASE_SSL: Set to true for TLS-enabled environments such as Neon
• DATABASE_SSL_REJECT_UNAUTHORIZED: Keep true unless you intentionally want relaxed certificate validation
• JWT_SECRET: JWT signing secret, minimum 32 characters
• ALLOWED_ORIGINS: A single origin or a comma-separated list of origins

3. Start local PostgreSQL 18

cp docker/postgres.env.example docker/postgres.env
npm run postgres:start

The helper starts PostgreSQL 18 on port 5432 by default.

4. Initialize the database schema

npm run db:migrate

This applies the generated Drizzle SQL migrations, including the PostgreSQL 18 uuidv7() defaults and indexes.

5. Start the API

npm start

Swagger UI is available at http://localhost:<PORT>/api-docs. The generated OpenAPI JSON is available at http://localhost:<PORT>/openapi.json.

Scripts

Script	Description
npm start	Starts the server in development mode with nodemon.
npm run build	Compiles TypeScript and writes a generated OpenAPI spec to dist/openapi.json.
npm run openapi:generate	Writes a generated OpenAPI spec to openapi.json.
npm run start:prod	Starts the compiled server from dist/index.js.
npm run postgres:start	Starts the local PostgreSQL Docker container.
npm run db:generate	Generates a new Drizzle SQL migration from the schema.
npm run db:migrate	Applies generated Drizzle migrations to the database.
npm run db:reset-local	Drops and recreates the configured local development database.
npm test	Runs the Jest test suite.
npm run type:check	Runs TypeScript type checking.
npm run format:check	Checks code formatting with Prettier.
npm run format:fix	Fixes code formatting with Prettier.

Project Structure

src/
    index.ts          # Process entrypoint, connects to PostgreSQL and starts HTTP server
    app.ts            # Express app setup, middleware, routes
    env.ts            # Environment variable loading and validation
    openapi/          # Generated OpenAPI document and export script

    auth/             # JWT and password helpers
    constants-types/  # Shared constants, messages, and types
    controllers/      # Route handlers
    errors/           # Typed API errors
    middleware/       # Auth, validation, and error handling middleware
    routes/           # Express routers

    db/
        client.ts     # Shared database connection, connection checks, and migration helpers
        init.ts       # Migration entrypoint used by `npm run db:migrate`
        reset-local.ts # Local-only admin script that drops and recreates the configured database
        schema.ts     # Drizzle table definitions
        test-utils.ts # Test-only helpers for clearing the in-memory database between Jest cases
        models/       # API-facing data types
        queries/      # Drizzle select/insert/update/delete operations
        utils/        # UUID validation helper

drizzle/             # Generated Drizzle SQL migrations and metadata
drizzle.config.ts    # Drizzle migration configuration

test/
    helpers/          # Shared test helpers
    setup/            # Jest environment bootstrap files

docker/
    postgres.env.example

scripts/
    run-postgres.sh

Notes on Behavior Preservation

• Route shapes, response shapes, validation rules, auth flow, and ownership checks remain the same
• Users still embed favoriteFoods, hobbies, and posts in API responses
• User and post lookups validate UUIDs
• Favorite foods and hobbies are stored through lookup tables with globally unique names while preserving each user's input order
• Duplicate favorite food names are deduplicated by exact value while keeping the first occurrence
• Duplicate hobby names are deduplicated by name while keeping the first occurrence, including that first hobby's frequencyPerWeek value

API Documentation

The OpenAPI document is generated from the route-layer Zod schemas and OpenAPI registry in src/openapi/.

• npm run openapi:generate writes the current spec to openapi.json
• npm run build writes the compiled app and a generated spec to dist/openapi.json
• GET /openapi.json returns the live generated document used by Swagger UI

How the docs update

• src/routes/*.ts contains the Zod request schemas that define validation rules such as required fields, enums, array shapes, and min/max constraints.
• src/openapi/document.ts reuses those schemas and registers the public API contract for each route, including path, method, tags, auth requirements, and documented responses.
• src/openapi/write-openapi.ts serializes the generated OpenAPI document to JSON when you run npm run openapi:generate or npm run build.

What updates automatically

• Changes to reused Zod schemas in the route layer usually flow into the generated OpenAPI schemas automatically.
• Examples include field type changes, new required properties, enum updates, nested object changes, and validation constraints such as .min() or .max().

What still needs manual updates

• New endpoints still need a matching registry.registerPath(...) entry in src/openapi/document.ts.
• Route metadata changes such as path, method, summary, tags, security requirements, response codes, and response body documentation must also be updated in src/openapi/document.ts.
• If an endpoint stops being exposed publicly, remove both the runtime route and its OpenAPI registration.

Recommended workflow after API changes

1. Update the API code and Zod schemas in the route layer.
2. Update src/openapi/document.ts if you changed route behavior, responses, auth, or added a new endpoint.
3. Run npm run type:check.
4. Run npm run openapi:generate if you want a local generated spec artifact.
5. Run npm run build to verify the production build also emits dist/openapi.json.

In short, the current setup keeps request and schema details close to the route validation code, but src/openapi/document.ts remains the place where the public API surface is described.

Testing

The test suite uses Jest with an in-memory PostgreSQL-compatible backend so the repository does not need Docker just to run tests. Test cleanup is separate from local development reset behavior:

• src/db/test-utils.ts truncates tables inside the in-memory test database between test cases.
• src/db/reset-local.ts drops and recreates a real local PostgreSQL database, and refuses to run against non-local hosts.

npm test

Migration Workflow

1. Update the schema in src/db/schema.ts.
2. Generate a new migration with npm run db:generate.
3. Apply migrations locally with npm run db:migrate.
4. Run npm test and npm run type:check.

Never hand-edit files in drizzle/. Treat them as generated artifacts and regenerate them from the schema whenever the model changes.

To recreate your local development database from scratch, run npm run db:reset-local and then npm run db:migrate. The reset command only runs when DATABASE_URL points to a local PostgreSQL host such as localhost or 127.0.0.1.

src/db/client.ts no longer contains data-reset helpers because row-level cleanup is only needed in tests. Its responsibilities are now limited to opening the active database, verifying connectivity, applying migrations, and closing the database cleanly.

Tech Stack

• Runtime: Node.js
• Language: TypeScript
• Framework: Express.js
• Database: PostgreSQL 18
• ORM: Drizzle ORM
• Validation: Zod
• Testing: Jest, Supertest, PGlite
• Tooling: ESLint, Prettier, Nodemon

Deployment Summary

• Local development: PostgreSQL 18 Docker container
• Production database: Neon PostgreSQL 18
• Production compute: Amazon ECS on AWS Fargate
• Container registry: Amazon ECR

See deployment.md for the production deployment workflow.