Build a REST API with Express and PostgreSQL

Build a Student Management REST API using Express, PostgreSQL, Bun, and TypeScript.

Introduction

30 Second Summary

Every time you check a grade, submit a form, or look up a profile online, a REST API is handling that request behind the scenes. It takes in your request, talks to a database, and sends back a clean response.

In this project, you will build a Student Management API from scratch using Express, PostgreSQL, and Bun with TypeScript. You will create endpoints that store, retrieve, update, and delete student records in a real database.

What You'll Build

You start the server, fire off curl commands from your terminal, and get back JSON responses for every operation. Creating a student, looking them up by ID, updating their email, deleting them entirely.

By the end of this project, you'll have:

  • A running REST API where you POST a new student with their name and email, and get back the created record with a database-generated ID.
  • Full CRUD operations (GET, POST, PUT, DELETE) backed by PostgreSQL, with parameterized queries that prevent SQL injection.
  • A production-ready project structure with separate layers for routes, controllers, services, and middleware that future projects will extend.
  • Secret Mission: Add pagination and search filtering to your GET endpoint so you can browse large datasets efficiently.

Are there any prerequisites?

You should be comfortable with TypeScript basics (types, interfaces, functions) and basic SQL (SELECT, INSERT, UPDATE, DELETE). No prior experience with Express, Bun, or building APIs is needed.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters to you.

Set Up the Project and Database

Every API needs a foundation before you can write a single endpoint. You need a project with its dependencies installed, a database running and ready to accept connections, and a folder structure that keeps your code organized as the project grows.

In this step, you'll scaffold the project with Bun (which gives you TypeScript support out of the box), spin up PostgreSQL in Docker, and create the layered folder structure your API will use throughout this project.

In this step, get ready to:
  • Initialize the Bun project and install dependencies.
  • Set up PostgreSQL in Docker with environment configuration.
  • Create the production folder structure with a config module.
Create the project and install dependencies

Your terminal is where you'll create the project folder, initialize it with Bun, and install the packages your API depends on.

  • Open your terminal by pressing Cmd+Space (macOS) or the Windows key (Windows) and typing Terminal, then pressing Enter.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir student-course-api
cd student-course-api
  • Initialize the Bun project by running:
bun init

Bun prompts you to select a template. Choose Blank and press Enter. You should see Bun create package.json, tsconfig.json, index.ts, and a .gitignore.

  • Install the two production dependencies your API needs by running:
bun add express pg

What are these packages?

  • express is the web framework that handles HTTP requests and routing.
  • pg is the PostgreSQL client library that lets your app talk to the database.
  • Install the TypeScript type definitions for Express as a development dependency by running:
bun add -d @types/express @types/pg

Why type definitions?

Express and pg are written in JavaScript. The @types/express and @types/pg packages provide TypeScript type information so your editor can autocomplete methods and catch type errors.

The -d flag marks them as dev dependencies since they are only needed during development, not at runtime.

Now you need to update package.json so that the project scripts use Bun's built-in watch mode for development.

  • In your terminal, open VS Code in the current folder by running:
code .

code command not found?

If code . does not work, open VS Code manually. Press Cmd+Space (macOS) or the Windows key (Windows), type VS Code, press Enter, then use File > Open Folder and select the student-course-api folder on your Desktop.

help me set up the code command for VS Code in my terminal

  • In the VS Code Explorer sidebar, click on package.json to open it.
  • Find this section near the top of the file:
"module": "index.ts",
"type": "module",
  • Replace that section with the following:
"module": "server.ts",
"type": "module",
"scripts": {
  "start": "bun run server.ts",
  "dev": "bun --watch server.ts"
},

What do these scripts do?

  • start runs the server with Bun. This is what a production environment would use.
  • dev runs the server with Bun's built-in --watch flag, which restarts automatically when you edit files. No extra tool like nodemon needed.
  • Save package.json by pressing Cmd+S (macOS) or Ctrl+S (Windows).

Seeing red squiggly lines in package.json?

Make sure your JSON is valid. Check for missing commas between fields, mismatched curly braces, or trailing commas after the last item in an object.

help me fix a JSON syntax error in my package.json

Set up Docker and environment configuration

Your API needs a PostgreSQL database to store student records. Instead of installing PostgreSQL directly on your machine, you'll run it inside a Docker container. This keeps your setup portable and easy to tear down.

  • In VS Code, create a new file in the project root by pressing Cmd+N (macOS) or Ctrl+N (Windows).
  • Save it immediately as docker-compose.yml in the student-course-api folder.
  • Paste the following configuration into the file:
services:
  db:
    image: postgres:16-alpine
    container_name: student_course_db
    restart: unless-stopped
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: secret123
      POSTGRES_DB: student_course_db
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

What does this configuration do?

  • image: postgres:16-alpine pulls a lightweight PostgreSQL 16 image.
  • container_name gives the container a fixed name so you can reference it easily.
  • ports: "5432:5432" maps the container's database port to your machine so your Bun app can connect.
  • volumes: pgdata persists your database data even if the container restarts.
  • Save docker-compose.yml.
  • Start the database container by running this command in your terminal:
docker compose up -d

You should see Docker pulling the PostgreSQL image and then starting the container. The -d flag runs it in the background so you keep your terminal free.

Seeing a connection error or permission denied?

Make sure Docker Desktop is running. Look for the Docker whale icon in your system tray (Windows) or menu bar (macOS). If Docker is not running, open it and wait for it to fully start before retrying.

help me troubleshoot Docker compose not starting my PostgreSQL container

Now you'll create the environment files that keep your database credentials configurable and out of version control.

  • Create a new file called .env in the project root and paste the following:
PORT=3000
DB_HOST=localhost
DB_PORT=5432
DB_USER=admin
DB_PASSWORD=secret123
DB_NAME=student_course_db

What is a .env file?

A .env file stores configuration values that your application reads at startup. Bun loads .env files automatically, so you don't need any extra package.

By keeping credentials here instead of hardcoding them, you can change database connections without editing source code.

  • Create a file called .env.example in the project root with the following content:
PORT=3000
DB_HOST=localhost
DB_PORT=5432
DB_USER=
DB_PASSWORD=
DB_NAME=
  • Create a file called .gitignore in the project root with the following content:
node_modules/
.env

Why .env.example and .gitignore?

.env.example shows other developers which variables they need without revealing your actual values. .gitignore tells Git to never commit node_modules/ or your .env file. This keeps secrets out of your repository.

  • Save all three files.
  • Confirm your project files exist by running this in the terminal:
ls -a

You should see .env, .env.example, .gitignore, docker-compose.yml, node_modules, package.json, and tsconfig.json listed in the output.

Create the folder structure and config module

Production APIs organize code into layers so that routing, business logic, and database access live in separate files. You'll set up this structure now so every file you create in later steps has a clear home.

  • Create all the source folders at once by running:
mkdir -p src/config src/routes src/controllers src/services src/middleware src/db
  • Verify the folders were created by running:
ls src

You should see six folders listed: config, controllers, db, middleware, routes, and services.

What is each folder for?

  • config/ holds environment and app configuration.
  • routes/ maps URL paths to controller functions.
  • controllers/ handles HTTP request/response logic.
  • services/ contains business logic and database queries.
  • middleware/ holds reusable request processing functions like error handlers.
  • db/ manages database connections and schema files.

Now create the first real module: the config file that reads your environment variables into a structured, typed object.

  • Create a new file at src/config/index.ts and paste the following:
// Bun loads .env automatically — no dotenv import needed

const config = {
  port: Number(process.env.PORT) || 3000,
  db: {
    host: process.env.DB_HOST || 'localhost',
    port: Number(process.env.DB_PORT) || 5432,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
  },
};

export default config;

What does this config do?

Bun automatically loads your .env file and makes its values available on process.env. No extra package needed.

The config object groups related values together. The || fallbacks provide defaults if a variable is missing. Other files in your project will import config from '../config' instead of reading process.env directly, keeping environment access centralized.

  • Save src/config/index.ts.

File not appearing in the src/config folder?

Make sure you created the file inside the src/config/ directory, not in the project root. In VS Code, right-click the config folder in the Explorer sidebar, select New File, and name it index.ts.

help me create files in the right folder in VS Code

Before you run the next command, what do you think you'll see if the database container started correctly?

  • Verify the PostgreSQL container is running by executing:
docker compose ps

You should see student_course_db listed with a status of Up.

  • Confirm Bun can find your project by running:
bun --version

You should see a version number printed to the terminal. This confirms Bun is installed and working inside your project folder.

Container not showing as Up?

Run docker compose logs db to see what went wrong. Common causes: port 5432 is already in use by another process, or Docker Desktop is not running.

help me fix my Docker PostgreSQL container that won't start

✔️ Awesome, I've got everything!

Great. Double check you've saved all your files, then move on to the next step.

ⓧ I'd like to double check the full code

Here are the key files you should have at this point. Scroll up and confirm each one matches.

package.json should contain:

{
  "name": "student-course-api",
  "module": "server.ts",
  "type": "module",
  "scripts": {
    "start": "bun run server.ts",
    "dev": "bun --watch server.ts"
  },
  "private": true,
  "dependencies": {
    "express": "^5.2.1",
    "pg": "^8.22.0"
  },
  "devDependencies": {
    "@types/bun": "latest",
    "@types/express": "^5.0.0",
    "@types/pg": "^8.0.0"
  },
  "peerDependencies": {
    "typescript": "^6"
  }
}

docker-compose.yml should contain:

services:
  db:
    image: postgres:16-alpine
    container_name: student_course_db
    restart: unless-stopped
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: secret123
      POSTGRES_DB: student_course_db
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

src/config/index.ts should contain:

const config = {
  port: Number(process.env.PORT) || 3000,
  db: {
    host: process.env.DB_HOST || 'localhost',
    port: Number(process.env.DB_PORT) || 5432,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
  },
};

export default config;

.env should contain:

PORT=3000
DB_HOST=localhost
DB_PORT=5432
DB_USER=admin
DB_PASSWORD=secret123
DB_NAME=student_course_db

.gitignore should contain:

node_modules/
.env

Your project foundation is solid. You have dependencies installed, a PostgreSQL database running in Docker, and a clean folder structure ready for code. Next up, you'll create the Express server and prove it responds to requests.

Create the Express Server with a Health Check

Your project is set up, dependencies are installed, and PostgreSQL is running in Docker. Now it is time to create the actual server that listens for HTTP requests.

A running server with a visible response proves your foundation works before you add any complexity. The health check endpoint you build here is also a production best practice for container orchestration tools that need to know if your service is alive.

In this step, get ready to:
  • Create the Express application module with a health check endpoint.
  • Create the server entry point that starts listening for requests.
  • Verify your API responds to a real HTTP request with curl.
Create the Express app and its supporting modules

In production backends, the Express app is defined in its own module, separate from the code that starts the server. This separation lets you import the app into test files without binding to a port.

Your src/app.ts will import a router and an error handler from other modules. Those modules will be built out in later steps, but the files need to exist now so the imports do not crash.

  • In VS Code, create a new file called src/routes/index.ts inside your src/routes/ folder.
  • Add this placeholder router that you will expand in Step 4:
import { Router } from 'express';

// Routes will be added in Step 4
const router = Router();

export default router;
  • Create another new file called src/middleware/errorHandler.ts inside your src/middleware/ folder.
  • Add this placeholder error handler that you will improve in Step 5:
import type { Request, Response, NextFunction } from 'express';

// Express identifies error middleware by its four-parameter signature
const errorHandler = (err: Error, req: Request, res: Response, next: NextFunction) => {
  console.error('Error:', err.message);

  res.status(500).json({
    error: 'Internal server error',
  });
};

export default errorHandler;

Why do these placeholders exist?

  • src/routes/index.ts exports an empty router. In Step 4, you will add student CRUD routes to it.
  • src/middleware/errorHandler.ts catches any unhandled error and returns a generic 500 response. In Step 5, you will make it map specific database errors to meaningful HTTP responses.
  • Express identifies error-handling middleware by its four-parameter signature (err, req, res, next). All four parameters must be present even if next is unused.
  • Confirm both files exist by running this in your VS Code terminal:
ls src/routes/index.ts src/middleware/errorHandler.ts

You should see both file paths echoed back with no errors.

  • Now create the main application file src/app.ts in your src/ folder.
  • Add the following code to set up Express with JSON parsing, a health check, route mounting, and error handling:
import express from 'express';
import routes from './routes/index.js';
import errorHandler from './middleware/errorHandler.js';

const app = express();

// Parse incoming JSON request bodies
app.use(express.json());

// Health check endpoint for monitoring
app.get('/health', (req, res) => {
  res.json({ status: 'ok', timestamp: new Date().toISOString() });
});

// Mount all API routes under /api/v1
app.use('/api/v1', routes);

// Centralized error handling (must be registered last)
app.use(errorHandler);

export default app;

What does this code do?

  • express.json() is middleware that automatically parses JSON request bodies into req.body.
  • The GET /health route returns the server status and current time. Monitoring tools ping this to confirm the API is alive.
  • Routes mount under /api/v1. This API versioning prefix lets you evolve the API in the future without breaking existing clients.
  • The error handler is registered last so it catches any error passed via next(err) from route handlers.
  • export default app exports the app so the server entry point can import it without the app binding to a port itself.
  • Save src/app.ts.

Seeing a red underline on the import paths?

  • Confirm src/routes/index.ts and src/middleware/errorHandler.ts both exist at exactly those paths. A missing file causes a "Cannot find module" error at runtime.
  • The import paths use ./routes/index.js and ./middleware/errorHandler.js with .js extensions. Bun resolves these to the .ts files automatically. This is standard ESM behavior for TypeScript projects.

Help me fix a Cannot find module error in my Express app

Create the server entry point and verify with curl

The server file is the entry point that imports your app and tells it to start listening on a port. Keeping this separate from the app module means your app stays importable for testing without port conflicts.

  • Create a new file called server.ts in the root of your student-course-api folder (next to package.json).
  • Add this code to start the server:
import app from './src/app.js';
import config from './src/config/index.js';

// Start listening for HTTP connections
app.listen(config.port, () => {
  console.log(`Server running on http://localhost:${config.port}`);
});

What does this code do?

  • import app from './src/app.js' imports the Express app you just created.
  • import config from './src/config/index.js' imports the config object that reads PORT from your .env file.
  • app.listen(config.port) binds the server to port 3000 and starts accepting HTTP connections.
  • Save server.ts.
  • Delete the default index.ts that bun init created (you won't need it).
  • Start your development server by running this command in the VS Code terminal:
bun run dev

Bun starts in watch mode and monitors your files for changes. You should see output that includes:

Server running on http://localhost:3000

Before you test the health check, what do you think you will get back when you send a GET request to /health?

  • Open a second terminal in VS Code by clicking the + icon in the terminal panel.
  • Test the health check endpoint by running:
curl http://localhost:3000/health

You should see a JSON response like this:

{"status":"ok","timestamp":"2026-08-01T12:34:56.789Z"}

The timestamp reflects your current time. This proves Express is running and responding to real HTTP requests from your database-backed project.

Not seeing a JSON response?

  • Check that bun run dev is still running in the first terminal. If it crashed, look at the error message for a missing module or syntax error.
  • Make sure you are running the curl command in a separate terminal. The first terminal is occupied by Bun's watch process.
  • Confirm your .env file has PORT=3000 set.

Help me debug why my Express server is not responding

Why separate app.ts from server.ts?

If the app and server lived in one file, every test would spin up a real HTTP server on a port. Tests would fight over port numbers and run slowly.

With this separation, a test file can import app from './src/app' and send fake requests to it without ever opening a port. This is a pattern you will use when you add automated testing in a future project.

✔️ Awesome, I've got everything!

Great. Double check you have saved all files before moving on.

ⓧ I'd like to double check the full code

Here is the full src/app.ts:

import express from 'express';
import routes from './routes/index.js';
import errorHandler from './middleware/errorHandler.js';

const app = express();

app.use(express.json());

app.get('/health', (req, res) => {
  res.json({ status: 'ok', timestamp: new Date().toISOString() });
});

app.use('/api/v1', routes);

app.use(errorHandler);

export default app;

Here is the full server.ts:

import app from './src/app.js';
import config from './src/config/index.js';

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

Here is the full src/routes/index.ts:

import { Router } from 'express';

const router = Router();

export default router;

Here is the full src/middleware/errorHandler.ts:

import type { Request, Response, NextFunction } from 'express';

const errorHandler = (err: Error, req: Request, res: Response, next: NextFunction) => {
  console.error('Error:', err.message);

  res.status(500).json({
    error: 'Internal server error',
  });
};

export default errorHandler;

Your Express server is running and responding to real HTTP requests. Next up, you will connect it to PostgreSQL and create the database table that stores student records.

Connect to PostgreSQL and Create the Schema

Your Express server handles requests, but everything lives in memory. To store student records that persist between restarts, you need a database connection.

In this step, you will connect to the PostgreSQL container using a connection pool and create the students table that stores your API data.

In this step, get ready to:
  • Create a connection pool module for database queries.
  • Define the students table schema in SQL.
  • Verify the database connection works from Bun.
Create the database connection pool

A connection pool keeps a set of database connections ready to use. Instead of opening and closing a connection for every single query, your API grabs an idle connection from the pool, runs the query, and returns it.

  • Create a new file called src/db/pool.ts and add the following code:
import pg from 'pg';
import config from '../config/index.js';

const { Pool } = pg;

// Create a pool of reusable database connections
const pool = new Pool({
  host: config.db.host,
  port: config.db.port,
  user: config.db.user,
  password: config.db.password,
  database: config.db.database,
});

export default pool;

What does this code do?

  • import pg from 'pg' imports the pg library, then const { Pool } = pg destructures the Pool class from it.
  • The Pool constructor receives your database credentials from the config module, so connection details live in .env rather than being hardcoded.
  • Exporting the pool means every file in your project can import the same pool instance and run queries through it.
  • Save src/db/pool.ts.

✔️ Awesome, I've got everything!

Great. Double check you have saved the file.

ⓧ I'd like to double check the full code

import pg from 'pg';
import config from '../config/index.js';

const { Pool } = pg;

const pool = new Pool({
  host: config.db.host,
  port: config.db.port,
  user: config.db.user,
  password: config.db.password,
  database: config.db.database,
});

export default pool;
Define and run the students table schema

The students table needs columns for a unique ID, name fields, a unique email address, an optional date of birth, and automatic timestamps that track when each record was created and updated.

  • Create a new file called src/db/init.sql and add the following SQL:
CREATE TABLE IF NOT EXISTS students (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  date_of_birth DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

What does this schema do?

  • id SERIAL PRIMARY KEY auto-generates a unique integer ID for every new row.
  • UNIQUE NOT NULL on email prevents duplicate student emails at the database level.
  • created_at and updated_at are set automatically by PostgreSQL when a row is inserted.
  • IF NOT EXISTS means you can run this file multiple times without errors.
  • Save src/db/init.sql.

✔️ Awesome, I've got everything!

Great. Double check you have saved the file.

ⓧ I'd like to double check the full code

CREATE TABLE IF NOT EXISTS students (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  date_of_birth DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
  • Run the SQL file against your PostgreSQL database by running this command in your terminal:
docker compose exec -T db psql -U admin -d student_course_db -f /dev/stdin < src/db/init.sql

What does this command do?

  • docker compose exec runs a command inside a running container.
  • -T disables interactive terminal mode so stdin can be piped from a file.
  • db is the service name from your docker-compose.yml.
  • < src/db/init.sql pipes your local SQL file into the psql command running inside the container.

If the command succeeds, you will see CREATE TABLE printed in your terminal. No other output means success.

Seeing an error?

  • If you see a message about the container not running, start it with docker compose up -d and try again.
  • If you see an authentication error, check that the -U admin and -d student_course_db values match what is in your docker-compose.yml environment section.
  • If you see a message about the input device not being a TTY, make sure you included the -T flag after exec.

Still stuck? Help me troubleshoot this docker compose exec error.

Verify the table and Bun connection

Two things need to be true before you move on: the students table exists in PostgreSQL, and your Bun app can connect to the database through the pool.

  • Before you run the next command, predict what you expect to see. Do you think the students table will be listed?
  • Check that the students table exists by running:
docker compose exec db psql -U admin -d student_course_db -c "\dt"

You should see a table listing that includes students in the public schema.

Don't see the students table?

  • Make sure you ran the init.sql command in the previous substep and saw CREATE TABLE output.
  • Run the init.sql command again. The IF NOT EXISTS clause makes it safe to run multiple times.

Help me figure out why my students table is not appearing.

Now verify that your application can connect to the database through the pool.

  • In server.ts, find the line that says import config from './src/config/index.js'; and add these lines directly below it:
// Temporary connection test - remove after verifying
import pool from './src/db/pool.js';

const result = await pool.query('SELECT NOW()');
console.log('Database connected:', result.rows[0].now);
  • Save server.ts.
  • Switch to the terminal where bun run dev is running. Bun detects the change and restarts automatically.

You should see Database connected: followed by a timestamp in your terminal output. This proves your pool is talking to PostgreSQL.

Seeing a connection refused error?

  • Make sure your Docker container is still running. Run docker compose ps to check its status.
  • Check that the values in your .env file (DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME) match the environment variables in docker-compose.yml.
  • Confirm the container is exposing port 5432 on your machine. The ports mapping "5432:5432" in docker-compose.yml handles this.

Help me debug my Bun PostgreSQL connection.

  • Now that the connection is verified, remove the temporary test code from server.ts. Delete the import pool line, the pool.query call, and the comment above them.
  • Save server.ts. It should be back to its original state with just the app and config imports plus app.listen.

Your database is connected and your schema is ready. Next up, you will build the CRUD endpoints that read and write student records through the pool you just created.

Build the Student CRUD Endpoints

Your server runs, your database is ready, and the health check proves the foundation is solid. Now it is time to build the actual feature your API exists for: managing student records.

CRUD operations (Create, Read, Update, Delete) are the backbone of any REST API. You will build them through three separate layers: a service that talks to the database, a controller that handles HTTP concerns, and a router that maps URLs to controller functions. This separation means each layer can change independently without breaking the others.

In this step, get ready to:
  • Create a service layer with five database functions using parameterized queries.
  • Create a controller layer that validates input and returns proper HTTP status codes.
  • Wire everything together with routes and test all five CRUD operations with curl.
Create the student service

The service layer is where your database queries live. Each function in this file does exactly one thing: run a SQL query and return the result.

  • Create a new file called src/services/student.service.ts in VS Code.
  • Add the read operations by pasting this code:
import pool from '../db/pool.js';

// Fetch all students, newest first
export const getAllStudents = async () => {
  const result = await pool.query(
    'SELECT * FROM students ORDER BY created_at DESC'
  );
  return result.rows;
};

// Fetch a single student by their database ID
export const getStudentById = async (id: string) => {
  const result = await pool.query('SELECT * FROM students WHERE id = $1', [id]);
  return result.rows[0] || null;
};

What does this code do?

These two functions handle the "Read" part of CRUD.

  • pool.query() grabs a connection from the pool, runs the SQL, and returns the connection automatically.
  • $1 is a parameterized placeholder. The actual value is passed in the array as the second argument. This prevents SQL injection because PostgreSQL treats it as data, never as executable SQL.
  • result.rows[0] || null returns the first match or null if no student has that ID.
  • Add the create and update operations directly below what you just wrote:
// Insert a new student and return the created record
export const createStudent = async (studentData: {
  first_name: string;
  last_name: string;
  email: string;
  date_of_birth?: string;
}) => {
  const { first_name, last_name, email, date_of_birth } = studentData;
  const result = await pool.query(
    `INSERT INTO students (first_name, last_name, email, date_of_birth)
     VALUES ($1, $2, $3, $4)
     RETURNING *`,
    [first_name, last_name, email, date_of_birth || null]
  );
  return result.rows[0];
};

// Update an existing student's fields
export const updateStudent = async (id: string, studentData: {
  first_name: string;
  last_name: string;
  email: string;
  date_of_birth?: string;
}) => {
  const { first_name, last_name, email, date_of_birth } = studentData;
  const result = await pool.query(
    `UPDATE students
     SET first_name = $1, last_name = $2, email = $3, date_of_birth = $4, updated_at = CURRENT_TIMESTAMP
     WHERE id = $5
     RETURNING *`,
    [first_name, last_name, email, date_of_birth || null, id]
  );
  return result.rows[0] || null;
};

What does RETURNING * do?

RETURNING * is a PostgreSQL feature that sends back the full row after an INSERT or UPDATE. Without it, you would need a separate SELECT query to get the record you just modified.

The date_of_birth || null pattern means: if no date of birth was provided, store null instead of undefined (which PostgreSQL would reject).

  • Add the delete function and the module exports to finish the file:
// Remove a student and return the deleted record
export const deleteStudent = async (id: string) => {
  const result = await pool.query(
    'DELETE FROM students WHERE id = $1 RETURNING *',
    [id]
  );
  return result.rows[0] || null;
};

Why return the deleted record?

Returning the deleted row lets the controller confirm that the student existed before deletion. If result.rows[0] is null, no row matched that ID and the delete did nothing.

  • Save src/services/student.service.ts.
  • Verify the file exports correctly by running this in a second terminal:
bun -e "import * as s from './src/services/student.service'; console.log(Object.keys(s))"

You should see the five function names printed as an array:

[ 'getAllStudents', 'getStudentById', 'createStudent', 'updateStudent', 'deleteStudent' ]

Seeing a "Cannot find module" error?

  • Make sure you are running the command from your project root folder (the one with package.json).
  • Check that the file is saved at exactly src/services/student.service.ts with no typos in the path.
  • help me fix my student.service.ts import

✔️ Awesome, I've got everything!

Great. Double check you have saved src/services/student.service.ts and move on to the controller.

ⓧ I'd like to double check the full code

Your src/services/student.service.ts should contain these sections in order. Scroll up and confirm each one is present:

  • The pool import statement at the top.
  • getAllStudents and getStudentById functions (read operations).
  • createStudent and updateStudent functions (write operations).
  • deleteStudent function exported at the bottom of the file.
Create the controller and routes

The controller sits between the HTTP request and the service layer. It reads request data, validates input, calls the right service function, and sends back a response with the correct status code.

  • Create a new file called src/controllers/student.controller.ts.
  • Add the import and the read operation handlers:
import type { Request, Response, NextFunction } from 'express';
import * as studentService from '../services/student.service.js';

// Handler: get all students
export const getAllStudents = async (req: Request, res: Response, next: NextFunction) => {
  try {
    const students = await studentService.getAllStudents();
    res.json({ data: students });
  } catch (err) {
    next(err);
  }
};

// Handler: get one student by ID from the URL
export const getStudentById = async (req: Request, res: Response, next: NextFunction) => {
  try {
    const student = await studentService.getStudentById(req.params.id);
    if (!student) {
      return res.status(404).json({ error: 'Student not found' });
    }
    res.json({ data: student });
  } catch (err) {
    next(err);
  }
};

What is the controller pattern here?

  • Each handler receives req, res, and next from Express.
  • next(err) forwards any unexpected error to the error-handling middleware instead of crashing the server.
  • The controller decides HTTP details (status 404, response shape) while the service handles only SQL.
  • Add the create handler below the read handlers:
// Handler: create a new student with validation
export const createStudent = async (req: Request, res: Response, next: NextFunction) => {
  try {
    const { first_name, last_name, email } = req.body;
    if (!first_name || !last_name || !email) {
      return res.status(400).json({
        error: 'Missing required fields: first_name, last_name, email',
      });
    }
    const student = await studentService.createStudent(req.body);
    res.status(201).json({ data: student });
  } catch (err) {
    next(err);
  }
};

Why validate in the controller?

Checking for required fields before calling the service means you return a clear 400 error immediately. Without this check, a missing email would reach the database and trigger a cryptic NOT NULL violation.

  • Add the update handler:
// Handler: update a student's fields
export const updateStudent = async (req: Request, res: Response, next: NextFunction) => {
  try {
    const { first_name, last_name, email } = req.body;
    if (!first_name || !last_name || !email) {
      return res.status(400).json({
        error: 'Missing required fields: first_name, last_name, email',
      });
    }
    const student = await studentService.updateStudent(req.params.id, req.body);
    if (!student) {
      return res.status(404).json({ error: 'Student not found' });
    }
    res.json({ data: student });
  } catch (err) {
    next(err);
  }
};

Why check for null after the update?

If updateStudent returns null, no row with that ID exists. Returning 404 tells the client the resource they tried to update does not exist.

  • Add the delete handler and the module exports to finish the controller:
// Handler: delete a student by ID
export const deleteStudent = async (req: Request, res: Response, next: NextFunction) => {
  try {
    const student = await studentService.deleteStudent(req.params.id);
    if (!student) {
      return res.status(404).json({ error: 'Student not found' });
    }
    res.status(204).send();
  } catch (err) {
    next(err);
  }
};

Why 204 for delete?

HTTP 204 means "No Content." The request succeeded, but there is nothing to send back. This is the standard response for a successful deletion.

  • Save src/controllers/student.controller.ts.

✔️ Awesome, I've got everything!

Great. Confirm you have saved the file and move on to the routes.

ⓧ I'd like to double check the full code

Your src/controllers/student.controller.ts should contain these sections in order. Scroll up and confirm each is present:

  • The studentService import statement at the top.
  • getAllStudents and getStudentById handlers (read operations with 404 check on getById).
  • createStudent handler (validates required fields, returns 201).
  • updateStudent handler (validates fields, checks for 404).
  • deleteStudent handler (checks for 404, returns 204).

Now you need a router to map HTTP verbs and URL paths to these controller functions.

  • Create a new file called src/routes/student.routes.ts and paste this code:
import { Router } from 'express';
import * as studentController from '../controllers/student.controller.js';

const router = Router();

// Map HTTP verbs to controller functions
router.get('/', studentController.getAllStudents);
router.get('/:id', studentController.getStudentById);
router.post('/', studentController.createStudent);
router.put('/:id', studentController.updateStudent);
router.delete('/:id', studentController.deleteStudent);

export default router;

What does this router do?

Each line maps a specific HTTP method and path to the matching controller function. The /:id part is a route parameter. Express extracts whatever value is in that URL segment and puts it in req.params.id.

  • Save src/routes/student.routes.ts.
Wire the routes and test with curl

Your src/app.ts already mounts a router at /api/v1 using import routes from './routes/index.js'. You need to update that index file to aggregate your student routes under that path.

  • Open src/routes/index.ts (the placeholder you created in Step 2) and replace its entire contents with:
import { Router } from 'express';
import studentRoutes from './student.routes.js';

const router = Router();

// Mount student endpoints under /students
router.use('/students', studentRoutes);

export default router;

How does the URL path build up?

src/app.ts mounts this router at /api/v1. This index mounts student routes at /students. So the full path becomes /api/v1/students.

This nesting pattern lets you add more resource routes later (courses, enrollments) without touching app.ts.

  • Save src/routes/index.ts.
  • Check your terminal where bun run dev is running. Bun should restart and print Server running on http://localhost:3000 with no errors.

Seeing a crash in Bun?

  • If the error mentions student.service or student.controller, double-check that both files are saved in the correct folders.
  • If it mentions student.routes, make sure src/routes/student.routes.ts exists (not studentRoutes.ts or students.routes.ts).
  • help me fix my Bun server crash

Time to test your API. Before you run the next command, what do you think will come back when you POST a new student to the database?

  • Create your first student by running this curl command in a second terminal:
curl -X POST http://localhost:3000/api/v1/students -H "Content-Type: application/json" -d '{"first_name":"Ada","last_name":"Lovelace","email":"ada@example.com"}'

You should see the created student returned with a database-generated id, created_at timestamp, and all the fields you sent:

{"data":{"id":1,"first_name":"Ada","last_name":"Lovelace","email":"ada@example.com","date_of_birth":null,"created_at":"...","updated_at":"..."}}
  • Fetch all students to confirm the record is stored:
curl http://localhost:3000/api/v1/students

You should see a data array containing Ada's record.

  • Fetch a single student by ID:
curl http://localhost:3000/api/v1/students/1

You should see the same record, this time as a single object inside data.

  • Update the student's name by running:
curl -X PUT http://localhost:3000/api/v1/students/1 -H "Content-Type: application/json" -d '{"first_name":"Augusta Ada","last_name":"Lovelace","email":"ada@example.com"}'

The response shows the updated record with first_name now set to Augusta Ada and a fresh updated_at timestamp.

  • Delete the student:
curl -X DELETE http://localhost:3000/api/v1/students/1 -w "\nHTTP Status: %{http_code}\n"

You should see HTTP Status: 204 with no response body. That confirms the deletion succeeded.

  • Confirm the student is gone by fetching all students again:
curl http://localhost:3000/api/v1/students

You should see an empty data array: {"data":[]}.

Getting an empty response or a 404?

  • If curl returns nothing, make sure bun run dev is still running in the other terminal and shows no errors.
  • If you see a 404, check that src/routes/index.ts uses router.use('/students', studentRoutes) (not /student singular).
  • If the POST returns a database error, confirm your Docker container is running with docker compose ps.
  • help me debug my CRUD endpoints

💡 Why separate layers instead of putting SQL in the route handler?

Right now the separation might feel like extra files for no reason. The payoff comes when your project grows.

Need to swap PostgreSQL for MongoDB? Change only the service. Need to add authentication checks? Add middleware before the controller. Need to reuse the same database logic from a different route? Import the service. Each layer has exactly one job, and changes stay contained.

All five CRUD operations work. Your API can create, read, update, and delete student records from PostgreSQL. Next up, you will make it handle errors gracefully so duplicate emails and invalid IDs return clean messages instead of raw database crashes.

Handle Errors Gracefully

Your CRUD endpoints work, but they have a serious gap. If something goes wrong at the database level, your API dumps raw PostgreSQL error details and stack traces directly to the client.

Production APIs never expose internal errors. They translate database-level problems into clean, predictable JSON responses with appropriate HTTP status codes. In this step, you will create a centralized error handler that catches database errors and returns friendly messages instead of crash dumps.

In this step, get ready to:
  • Trigger raw database errors and observe the ugly default response.
  • Create a centralized error-handling middleware that maps PostgreSQL error codes to clean HTTP responses.
  • Verify that duplicate emails and invalid IDs now return proper JSON errors.
Trigger the raw error responses

Your src/app.ts already imports an error handler from ./middleware/errorHandler.js. You created a placeholder in Step 2, but now it is time to replace it with the real implementation.

  • Check your terminal where bun run dev is running. You should see an error mentioning that the ./middleware/errorHandler module cannot be found.

To get the server running so you can observe how Express handles database errors by default, you will create a temporary pass-through version of this file first.

  • Create src/middleware/errorHandler.ts with this temporary pass-through code:
import type { Request, Response, NextFunction } from 'express';

// Temporary pass-through: lets errors fall to Express's default handler
const errorHandler = (err: Error, req: Request, res: Response, next: NextFunction) => {
  next(err);
};

export default errorHandler;

What does this code do?

This is a placeholder. It receives every error that controllers pass via next(err) and immediately forwards it to Express's built-in default error handler.

Express's default handler responds with a 500 status and the full error stack trace. That is the raw dump you are about to see.

  • Save src/middleware/errorHandler.ts.
  • Check your terminal. Bun should detect the new file and restart, printing Server running on http://localhost:3000.

Server still crashing?

Make sure the file is saved at exactly src/middleware/errorHandler.ts (inside a middleware folder, not middlewares). The path must match what src/app.ts imports.

help me fix a Cannot find module error in my Express app

Now trigger the designed obstacle. If you deleted all students earlier, create one first so you can test the duplicate email scenario.

  • In a second terminal, create a student by running:
curl -X POST http://localhost:3000/api/v1/students -H "Content-Type: application/json" -d '{"first_name":"Ada","last_name":"Lovelace","email":"ada@example.com"}'

You should see the student created successfully with an id and all fields returned.

Before you run the next command, what do you think will happen if you try to create a second student with the exact same email?

  • Attempt to create a duplicate student by running the same command again:
curl -X POST http://localhost:3000/api/v1/students -H "Content-Type: application/json" -d '{"first_name":"Ada","last_name":"Lovelace","email":"ada@example.com"}'

You see a wall of text containing a stack trace and internal PostgreSQL details. The response is a 500 with the full error object exposed to the client. This is the problem.

  • Now try requesting a student with a non-numeric ID by running:
curl http://localhost:3000/api/v1/students/abc

Another ugly error dump. PostgreSQL cannot cast abc to an integer, and the raw database error spills into the response.

Why is this a problem?

Exposing stack traces reveals your file paths, library versions, and database schema to anyone who sends a bad request. It is both a security risk and a poor user experience.

A production API should return a short, predictable JSON error with the right HTTP status code. The client gets enough information to fix their request without seeing your internals.

Create the error-handling middleware

Now you will replace the pass-through placeholder with a real error handler. This middleware inspects the error object, checks for known PostgreSQL error codes, and returns a clean JSON response with the correct status code.

  • In src/middleware/errorHandler.ts, replace the entire file contents with:
import type { Request, Response, NextFunction } from 'express';

const errorHandler = (err: any, req: Request, res: Response, next: NextFunction) => {
  console.error('Error:', err.message);

  // PostgreSQL unique constraint violation (e.g. duplicate email)
  if (err.code === '23505') {
    return res.status(409).json({
      error: 'A student with this email already exists',
    });
  }

  // PostgreSQL invalid input (e.g. non-integer for id column)
  if (err.code === '22P02') {
    return res.status(400).json({
      error: 'Invalid input format',
    });
  }

  // Catch-all for unknown errors
  res.status(500).json({
    error: 'Internal server error',
  });
};

export default errorHandler;

What does this code do?

  • The function signature (err, req, res, next) with four parameters tells Express this is error-handling middleware, not a regular middleware.
  • err.code === '23505' catches PostgreSQL's unique constraint violation. The email column has a UNIQUE constraint, so inserting a duplicate triggers this code.
  • err.code === '22P02' catches invalid text representation errors. PostgreSQL throws this when it cannot cast a value to the expected type (like casting abc to an integer).
  • The catch-all 500 response ensures unknown errors never leak internal details to the client.
  • Save src/middleware/errorHandler.ts. Bun restarts the server automatically.

Seeing a syntax error in the terminal?

Check that you have matching curly braces for each if block and that the export default errorHandler line is outside the function body.

help me fix a syntax error in my Express error handler middleware

Verify the clean error responses

The error handler is in place and the server is running. Time to confirm that both error cases now return clean JSON instead of stack traces.

Before you run the next commands, what do you expect the response to look like now that your custom error handler is active?

  • Test the duplicate email error by running:
curl -s -w "\nHTTP Status: %{http_code}\n" -X POST http://localhost:3000/api/v1/students -H "Content-Type: application/json" -d '{"first_name":"Ada","last_name":"Lovelace","email":"ada@example.com"}'

You should see a clean JSON body with status 409:

{"error":"A student with this email already exists"}
HTTP Status: 409

Why 409 and not 400?

HTTP 409 Conflict means the request conflicts with existing data. The email already belongs to another student, so the conflict is with the current state of the database.

HTTP 400 Bad Request means the client sent something malformed. A duplicate email is valid data that happens to conflict, so 409 is the correct choice.

  • Test the invalid ID format error by running:
curl -s -w "\nHTTP Status: %{http_code}\n" http://localhost:3000/api/v1/students/abc

You should see:

{"error":"Invalid input format"}
HTTP Status: 400
  • Also verify that the input validation in your controllers catches missing fields by running:
curl -s -w "\nHTTP Status: %{http_code}\n" -X POST http://localhost:3000/api/v1/students -H "Content-Type: application/json" -d '{"first_name":"Grace"}'

You should see a 400 response with the message Missing required fields: first_name, last_name, email. This validation runs in the controller before the request even reaches the database.

Two layers of defense

Your controllers validate required fields upfront, returning 400 before any database call happens. The error handler middleware is the safety net that catches unexpected database errors that slip through.

This layered approach means clients get fast, specific feedback for known issues and a clean generic response for anything unexpected.

🙋‍♀️ Still seeing a stack trace instead of JSON?

Make sure your error handler has exactly four parameters in the function signature: (err, req, res, next). Express only treats a middleware as an error handler if it has four arguments.

Also confirm the error handler is registered after all routes in src/app.ts. The line app.use(errorHandler) must come after app.use('/api/v1', routes).

help me debug why my Express error handler middleware is not catching errors

✔️ Awesome, I've got everything!

Great. Double check you have saved src/middleware/errorHandler.ts and that your server is running without errors in the terminal.

ⓧ I'd like to double check the full code

Here is the complete src/middleware/errorHandler.ts:

import type { Request, Response, NextFunction } from 'express';

const errorHandler = (err: any, req: Request, res: Response, next: NextFunction) => {
  console.error('Error:', err.message);

  if (err.code === '23505') {
    return res.status(409).json({
      error: 'A student with this email already exists',
    });
  }

  if (err.code === '22P02') {
    return res.status(400).json({
      error: 'Invalid input format',
    });
  }

  res.status(500).json({
    error: 'Internal server error',
  });
};

export default errorHandler;

And here is your src/app.ts which was already wired up from the previous step:

import express from 'express';
import routes from './routes/index.js';
import errorHandler from './middleware/errorHandler.js';

const app = express();

app.use(express.json());

app.get('/health', (req, res) => {
  res.json({ status: 'ok', timestamp: new Date().toISOString() });
});

app.use('/api/v1', routes);

app.use(errorHandler);

export default app;

Your API now handles errors like a production service. Clients get clear, actionable messages without ever seeing your database internals or file paths. This error-handling pattern scales to any new error type you encounter in the future.

Secret mission

Add Pagination and Search

Your GET /students endpoint returns every record at once. Real APIs let clients page through results and search by name or email. Can you build dynamic SQL that paginates and filters safely?

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally with Docker, so there are no ongoing cloud costs.

Resources you used:

  • Docker container student_course_db running PostgreSQL (uses minimal disk, no cost).
  • Docker volume pgdata persisting your database data.
  • Local project folder student-course-api on your Desktop.

Keep everything running

No action needed. Choose this if you are still actively building or plan to extend this API soon.

  • Your Docker container continues running in the background using negligible CPU and memory.
  • Your project folder and all source files remain on your Desktop.
  • You can resume development anytime by opening the folder in VS Code and running bun run dev.

Pause - I'll come back to this later

Shut down running processes to free up memory, but keep your files and data intact so you can pick up where you left off.

  • In the terminal where bun run dev is running, press Ctrl+C to stop the server.
  • Stop the PostgreSQL container by running:
docker compose stop
  • Your database data is preserved in the pgdata volume. When you are ready to resume, run docker compose up -d from your project folder to start the container again.

Delete - I don't want to use this again

Remove all project resources and start fresh. This deletes your database data permanently.

  • In the terminal where bun run dev is running, press Ctrl+C to stop the server.
  • Remove the Docker container and its data volume by running:
docker compose down -v
  • Delete the project folder from your Desktop. On macOS or Linux, run:
cd ~/Desktop
rm -rf student-course-api

On Windows, run:

cd ~\Desktop
rmdir /s /q student-course-api
  • Confirm the folder is gone by running ls (macOS/Linux) or dir (Windows) on your Desktop. You should no longer see student-course-api listed.

Nice Work!

Nice Work!

Nice work! You just built a fully functional REST API from scratch using Express, PostgreSQL, Bun, and TypeScript, complete with a production-ready project structure and centralized error handling.

You've learned how to:

  • Design a layered backend architecture with separated routes, controllers, services, config, middleware, and database layers that can evolve independently.
  • Run PostgreSQL in Docker and implement full CRUD operations using parameterized SQL queries that prevent SQL injection.
  • Build centralized error handling middleware that maps raw database errors to clean, structured HTTP responses for your API consumers.
  • Secret Mission: Added pagination and search filtering with dynamic SQL that builds queries safely using parameterized ILIKE clauses and LIMIT/OFFSET.

Ready to quiz yourself?