Build a REST API for Your Lab Notebook

Build a full CRUD REST API with Express, TypeScript, and PostgreSQL.

Introduction

30 Second Summary

Scientists track every experiment in lab notebooks. Every hypothesis, every procedure, every result needs a reliable system that stores and retrieves records on demand.

In this project, you will build a REST API that stores and manages lab experiment records in a database, complete with input validation and proper HTTP endpoints.

What You'll Build

A TypeScript REST API using Express 5, PostgreSQL, Drizzle ORM, and Zod validation that provides full CRUD operations for lab experiment records.

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

  • An Express 5 server with JSON middleware and error handling that serves your API.
  • A PostgreSQL database table defined entirely in TypeScript using Drizzle ORM, with no separate schema language or code generation step.
  • Reusable Zod validation middleware that rejects bad data before it reaches your database.
  • Five API endpoints covering list, get, create, update, and delete operations with proper HTTP status codes.
  • Secret Mission: Add status filtering and title search so researchers can find experiments by state or keyword.

Are there any prerequisites?

This project assumes you have Node.js 18+, TypeScript, and PostgreSQL installed locally. You should be comfortable with TypeScript fundamentals like type annotations and interfaces.

Set Up the Project

Every API project starts with a solid foundation. Before you write any route handlers or database queries, you need the right packages installed, TypeScript configured, and a folder structure that keeps your code organized.

In this step, you'll initialize a new Node.js project, install Express 5, Drizzle ORM, and Zod, configure TypeScript for modern ESM modules, and create your local PostgreSQL database.

In this step, get ready to:
  • Initialize a Node.js project with all production and development dependencies.
  • Configure TypeScript with strict mode and modern module resolution.
  • Create a local PostgreSQL database for your experiment data.
Create the project and install dependencies
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir lab-notebook-api
cd lab-notebook-api
  • Initialize a new Node.js project by running:
npm init -y
  • Confirm the project was initialized by running:
ls

You should see package.json in the output.

  • Install the production dependencies by running:
npm install express drizzle-orm pg dotenv zod
  • Install the development dependencies by running:
npm install -D typescript @types/express @types/node @types/pg drizzle-kit tsx

You should see a message confirming packages were added. Your package.json now has dependencies and devDependencies sections filled in.

Seeing permission errors or network failures?

Make sure you have a working internet connection. If you see EACCES permission errors on macOS/Linux, do not use sudo. Instead, fix your npm permissions by following the npm docs.

If a specific package fails to resolve, double-check that you typed the package name exactly as shown above. help me fix npm install errors

Now you need to configure package.json with ESM module support and two scripts the project needs.

  • In your code editor, click File in the top menu bar.
  • Click Open Folder.
  • Select the lab-notebook-api folder on your Desktop, then click Open.
  • Open package.json from the file explorer sidebar.
  • Replace the entire contents of package.json with the following:
{
  "name": "lab-notebook-api",
  "version": "1.0.0",
  "type": "module",
  "scripts": {
    "dev": "tsx watch src/index.ts",
    "db:push": "drizzle-kit push"
  },
  "dependencies": {
    "dotenv": "^16.4.7",
    "drizzle-orm": "^0.45.2",
    "express": "^5.2.1",
    "pg": "^8.13.3",
    "zod": "^4.4.3"
  },
  "devDependencies": {
    "@types/express": "^5.0.6",
    "@types/node": "^22.15.3",
    "@types/pg": "^8.11.11",
    "drizzle-kit": "^0.31.10",
    "tsx": "^4.22.4",
    "typescript": "^5.8.3"
  }
}

What does this configuration do?

  • "type": "module" tells Node.js to treat .js files as ES modules (import/export syntax) instead of CommonJS (require).
  • The dev script uses tsx watch to run your TypeScript server and automatically restart it whenever you save changes.
  • The db:push script uses drizzle-kit push to apply your TypeScript schema directly to the database without generating migration files.

Version numbers may differ slightly

Your version numbers in dependencies and devDependencies might be slightly newer than what's shown here. That's fine. The important parts are the type field and the scripts block.

  • Save package.json.
Configure TypeScript and create the folder structure

TypeScript needs a configuration file to know how strictly to check your code and how to resolve module imports.

  • Create a new file called tsconfig.json in the root of your lab-notebook-api folder.
  • Paste the following configuration into tsconfig.json:
{
  "compilerOptions": {
    "target": "ES2022",
    "module": "ESNext",
    "moduleResolution": "bundler",
    "esModuleInterop": true,
    "strict": true,
    "outDir": "./dist",
    "rootDir": "./src",
    "skipLibCheck": true,
    "noEmit": true
  },
  "include": ["src/**/*.ts", "drizzle.config.ts"],
  "exclude": ["node_modules"]
}

What do these settings do?

  • "strict": true enables all of TypeScript's strictness checks. This catches bugs at compile time instead of at runtime.
  • "moduleResolution": "bundler" lets you write imports without file extensions (like import { db } from '../db/index') because tsx uses esbuild under the hood, which resolves imports like a bundler.
  • "noEmit": true means TypeScript only checks your code for errors. The actual compilation is handled by tsx at runtime.
  • Save tsconfig.json.

Now create the folder structure that will organize your source code into logical sections.

  • Create the project folders by running this command in your terminal:
mkdir -p src/db src/routes src/middleware
  • Confirm the folders were created by running:
ls src

You should see db, middleware, and routes in the output.

Why this folder structure?

  • src/db/ holds your database connection and schema definitions.
  • src/routes/ holds your API endpoint handlers.
  • src/middleware/ holds reusable request-processing functions like validation.

Terminal showing 'No such file or directory'?

Make sure your terminal is inside the lab-notebook-api folder. Run pwd to check your current location. If you're not in the right folder, run cd ~/Desktop/lab-notebook-api to get back.

help me fix my terminal directory issue

Create the environment file and database

Your API needs to know where its database lives. Environment variables keep configuration separate from code so you can change settings without editing source files.

  • Create a new file called .env in the root of your lab-notebook-api folder (the same level as package.json).
  • Paste the following into .env:
DATABASE_URL=postgresql://localhost:5432/lab_notebook
PORT=3000

What is DATABASE_URL?

This connection string tells your app where PostgreSQL is running. localhost:5432 means your local machine on PostgreSQL's default port. lab_notebook is the name of the database you're about to create.

  • Save .env.

Now create the actual PostgreSQL database that your API will connect to.

  • Create the local database by running this command in your terminal:
createdb lab_notebook

If the command completes without any output, it succeeded. No news is good news.

Seeing a connection error from createdb?

If you see a message about not being able to connect, your PostgreSQL server is likely not running. Start it with brew services start postgresql (macOS with Homebrew) or check your system's PostgreSQL service manager, then try again.

If you see a 'database already exists' error, that's fine. It means you already have a database with this name from a previous attempt.

help me get PostgreSQL running

Time for a quick sanity check to confirm everything is wired up correctly.

  • Create a temporary test file by running:
echo "console.log('hello')" > src/index.ts
  • Run the test file to verify TypeScript execution works:
npx tsx src/index.ts

You should see hello printed in your terminal. This confirms that tsx can run TypeScript files without errors.

Not seeing 'hello' in the output?

If you see a TypeScript or module error, check that your tsconfig.json has "module": "ESNext" and your package.json has "type": "module". Both are required for ESM imports to work.

If the command is not found, make sure you installed the dev dependencies (the npm install -D command from earlier). Run ls node_modules/.bin/tsx to check if tsx is available.

help me debug my tsx setup

This test file is temporary

You'll replace src/index.ts with your real Express server code in a later step. For now, it just proves your TypeScript toolchain is working.

✔️ Awesome, I've got everything!

Great. Make sure you've saved all your files before moving on.

ⓧ I'd like to double check the full code

Here's what each file should look like at this point.

package.json:

{
  "name": "lab-notebook-api",
  "version": "1.0.0",
  "type": "module",
  "scripts": {
    "dev": "tsx watch src/index.ts",
    "db:push": "drizzle-kit push"
  },
  "dependencies": {
    "dotenv": "^16.4.7",
    "drizzle-orm": "^0.45.2",
    "express": "^5.2.1",
    "pg": "^8.13.3",
    "zod": "^4.4.3"
  },
  "devDependencies": {
    "@types/express": "^5.0.6",
    "@types/node": "^22.15.3",
    "@types/pg": "^8.11.11",
    "drizzle-kit": "^0.31.10",
    "tsx": "^4.22.4",
    "typescript": "^5.8.3"
  }
}

tsconfig.json:

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "ESNext",
    "moduleResolution": "bundler",
    "esModuleInterop": true,
    "strict": true,
    "outDir": "./dist",
    "rootDir": "./src",
    "skipLibCheck": true,
    "noEmit": true
  },
  "include": ["src/**/*.ts", "drizzle.config.ts"],
  "exclude": ["node_modules"]
}

.env:

DATABASE_URL=postgresql://localhost:5432/lab_notebook
PORT=3000

Your project foundation is locked in. You have all your dependencies, TypeScript is configured, and your database is ready to accept data. Next up, you'll define the experiments table using Drizzle's TypeScript schema API.

Define the Database Schema

Your project folder is ready with all dependencies installed and TypeScript configured. Now it's time to define what data your API will store.

In most backend projects, you define your database tables in one language and your application code in another. Drizzle ORM flips that pattern. Your TypeScript schema file IS the database schema. No separate SQL files, no code generation step.

In this step, get ready to:
  • Define the experiments table using Drizzle's TypeScript-native schema API.
  • Configure Drizzle Kit and create the database connection module.
  • Push your schema to the local PostgreSQL database and verify it exists.
Define the experiments table schema

Every experiment in your lab notebook has structured fields: a title, a hypothesis, materials, and so on. You'll express this structure as a pgTable definition that Drizzle can read and push directly to PostgreSQL.

  • Create a new file called src/db/schema.ts inside your lab-notebook-api project, then add the following code:
import { integer, pgTable, varchar, text, timestamp } from 'drizzle-orm/pg-core';

// Define the experiments table with all fields a lab experiment needs
export const experiments = pgTable('experiments', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  title: varchar({ length: 255 }).notNull(),
  hypothesis: text(),
  materials: text(),
  protocol: text(),
  observations: text(),
  results: text(),
  status: varchar({ length: 20 }).notNull().default('planned'),
  createdAt: timestamp().notNull().defaultNow(),
  updatedAt: timestamp().notNull().defaultNow(),
});

What does this code do?

  • pgTable('experiments', {...}) declares a PostgreSQL table named experiments with the columns you define inside the object.
  • generatedAlwaysAsIdentity() tells PostgreSQL to auto-generate a unique ID for every new row.
  • notNull() and default('planned') set constraints. The title and status are required, but fields like hypothesis and materials are optional.
  • defaultNow() on createdAt and updatedAt automatically stamps rows with the current time.

Why Drizzle over Prisma?

Prisma uses its own schema language (a .prisma file) and requires a code generation step before you can use it. Drizzle keeps everything in TypeScript. Your schema is regular TypeScript code that you import directly.

Drizzle's query syntax also mirrors SQL closely. As you build queries later, you'll recognize the SQL patterns underneath. This teaches you what the database is actually doing, rather than hiding it behind abstractions.

  • Save src/db/schema.ts.
  • You won't see a visible change yet. The next substep configures Drizzle Kit to use this schema and push it to your database.

Seeing red underlines in your editor?

  • Check that you imported from drizzle-orm/pg-core (not drizzle-orm alone). The table and column helpers live in the pg-core subpackage.
  • Confirm your node_modules folder exists. If npm install didn't finish in Step 1, TypeScript can't find the Drizzle types.

Why is my schema file showing TypeScript errors?

✔️ Awesome, I've got everything!

Great. Double check you've saved src/db/schema.ts before moving on.

ⓧ I'd like to double check the full code

import { integer, pgTable, varchar, text, timestamp } from 'drizzle-orm/pg-core';

export const experiments = pgTable('experiments', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  title: varchar({ length: 255 }).notNull(),
  hypothesis: text(),
  materials: text(),
  protocol: text(),
  observations: text(),
  results: text(),
  status: varchar({ length: 20 }).notNull().default('planned'),
  createdAt: timestamp().notNull().defaultNow(),
  updatedAt: timestamp().notNull().defaultNow(),
});
Connect Drizzle to your database

Drizzle Kit needs a configuration file to know where your schema lives and which database to target. You also need a connection module that the rest of your app will import when it queries the database.

  • Create a new file called drizzle.config.ts at the root of your lab-notebook-api folder (next to package.json), then add the following code:
import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

// Tell Drizzle Kit where to find the schema and where to connect
export default defineConfig({
  out: './drizzle',
  schema: './src/db/schema.ts',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

What does this config do?

  • schema points Drizzle Kit at the file you just created so it knows what tables to create.
  • dialect: 'postgresql' tells Drizzle Kit you're targeting a PostgreSQL database.
  • dbCredentials.url reads the connection string from your .env file via dotenv/config.
  • out: './drizzle' sets the output directory for any generated migration files. You won't use migrations in this project, but the config requires it.
  • Save drizzle.config.ts.

✔️ Awesome, I've got everything!

Great. Make sure you saved drizzle.config.ts at the project root, not inside src/.

ⓧ I'd like to double check the full code

import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  out: './drizzle',
  schema: './src/db/schema.ts',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});
  • Now create src/db/index.ts (in the same folder as schema.ts) and add the following code:
import 'dotenv/config';
import { drizzle } from 'drizzle-orm/node-postgres';

// Create and export the database connection for use across the app
export const db = drizzle(process.env.DATABASE_URL!);

What does this file do?

This module creates a single database connection instance and exports it as db. Every route handler in your API will import db from this file to run queries. The dotenv/config import ensures your DATABASE_URL environment variable is loaded before the connection is created.

  • Save src/db/index.ts.
  • You won't see a change yet. The next substep uses both files to push your schema to the database.

✔️ Awesome, I've got everything!

Great. You should now have both drizzle.config.ts at the root and src/db/index.ts saved.

ⓧ I'd like to double check the full code

import 'dotenv/config';
import { drizzle } from 'drizzle-orm/node-postgres';

export const db = drizzle(process.env.DATABASE_URL!);
Push the schema to PostgreSQL

Your schema exists in TypeScript, but your database doesn't know about it yet. The drizzle-kit push command reads your schema file and applies it directly to your local PostgreSQL.

  • Push your schema to the database by running this command in your terminal:
npm run db:push

What about the confirmation prompts?

Drizzle Kit will show you a summary of what it plans to create and ask for confirmation. Type yes when prompted to apply the changes. You should see a message confirming that the experiments table was created.

  • Verify the table exists by running this command:
psql lab_notebook -c '\d experiments'

You should see a table listing all your columns with their types. The id column shows as integer, the title column shows as character varying(255), and the timestamp columns show as timestamp without time zone.

Connection refused or database not found?

  • Make sure PostgreSQL is running. On macOS with Homebrew, run brew services start postgresql to start it.
  • Check that you created the lab_notebook database in Step 1. Run createdb lab_notebook if you skipped it.
  • Verify your .env file has DATABASE_URL=postgresql://localhost:5432/lab_notebook with no extra spaces or quotes.

Help me debug my drizzle-kit push connection error.

Your database now has a fully defined experiments table, created entirely from TypeScript. Next up, you'll build the Express server and wire up endpoints that read from and write to this table.

Build the Server and Create/Read Endpoints

Your database schema is defined and pushed to PostgreSQL. Now you need a way for the outside world to interact with that data over HTTP.

In this step, you'll build the full Express server from scratch. You'll define Zod validation schemas that guard your data, create a reusable validation middleware, wire up your first three API endpoints, and test them with curl.

In this step, get ready to:
  • Create Zod validation schemas and a reusable validation middleware.
  • Build an Express router with list, get-by-ID, and create endpoints.
  • Wire up the Express server with JSON parsing, routing, and error handling.
Create the validation schemas and middleware

Before any data reaches your database, it should pass through validation. Zod lets you define the exact shape of valid request bodies in TypeScript. If a request doesn't match, your API rejects it with a clear error before touching the database.

  • Create a new file at src/schemas/experiment.ts and add the following code:
import { z } from 'zod';

// Define allowed status values as an enum
const statusEnum = z.enum(['planned', 'in-progress', 'completed']);

// Schema for creating a new experiment
export const createExperimentSchema = z.object({
  title: z.string().min(1).max(255),
  hypothesis: z.string().optional(),
  materials: z.string().optional(),
  protocol: z.string().optional(),
  observations: z.string().optional(),
  results: z.string().optional(),
  status: statusEnum.optional().default('planned'),
});

// Schema for updating an existing experiment (all fields optional)
export const updateExperimentSchema = z.object({
  title: z.string().min(1).max(255).optional(),
  hypothesis: z.string().optional(),
  materials: z.string().optional(),
  protocol: z.string().optional(),
  observations: z.string().optional(),
  results: z.string().optional(),
  status: statusEnum.optional(),
});

// Infer TypeScript types from the schemas
export type CreateExperiment = z.infer<typeof createExperimentSchema>;
export type UpdateExperiment = z.infer<typeof updateExperimentSchema>;

What does this code do?

You've defined two schemas here. createExperimentSchema enforces that every new experiment has a non-empty title (max 255 characters) and an optional status that defaults to 'planned'.

The updateExperimentSchema makes every field optional so partial updates work. The z.infer lines generate TypeScript types directly from the schemas, keeping your types and validation in sync automatically.

  • Save src/schemas/experiment.ts.
  • You won't see a change yet. The next file uses these schemas as middleware.

✔️ Awesome, I've got everything!

Great. Make sure you've saved the file before moving on.

ⓧ I'd like to double check the full code

import { z } from 'zod';

const statusEnum = z.enum(['planned', 'in-progress', 'completed']);

export const createExperimentSchema = z.object({
  title: z.string().min(1).max(255),
  hypothesis: z.string().optional(),
  materials: z.string().optional(),
  protocol: z.string().optional(),
  observations: z.string().optional(),
  results: z.string().optional(),
  status: statusEnum.optional().default('planned'),
});

export const updateExperimentSchema = z.object({
  title: z.string().min(1).max(255).optional(),
  hypothesis: z.string().optional(),
  materials: z.string().optional(),
  protocol: z.string().optional(),
  observations: z.string().optional(),
  results: z.string().optional(),
  status: statusEnum.optional(),
});

export type CreateExperiment = z.infer<typeof createExperimentSchema>;
export type UpdateExperiment = z.infer<typeof updateExperimentSchema>;

Now you need a middleware function that applies any Zod schema to incoming request bodies. This keeps validation logic out of your route handlers.

  • Create a new file at src/middleware/validate.ts and add the following code:
import { Request, Response, NextFunction, RequestHandler } from 'express';
import { ZodSchema } from 'zod';

// Generic middleware factory that validates req.body against any Zod schema
export const validateBody = <T>(schema: ZodSchema<T>): RequestHandler => {
  return (req: Request, res: Response, next: NextFunction) => {
    const result = schema.safeParse(req.body);
    if (!result.success) {
      // Return 400 with detailed error messages
      res.status(400).json({
        error: 'Validation failed',
        details: result.error.issues,
      });
      return;
    }
    // Replace req.body with parsed (and coerced) data
    req.body = result.data;
    next();
  };
};

What does this code do?

The validateBody function is a middleware factory. You pass it a Zod schema, and it returns an Express middleware function that parses req.body against that schema.

If parsing fails, it sends a 400 response with the specific validation errors. If parsing succeeds, it replaces req.body with the validated data (stripping unknown fields) and calls next() to continue to the route handler.

  • Save src/middleware/validate.ts.
  • You won't see a change yet. The router you build next will use this middleware.

✔️ Awesome, I've got everything!

Great. Make sure you've saved the file before moving on.

ⓧ I'd like to double check the full code

import { Request, Response, NextFunction, RequestHandler } from 'express';
import { ZodSchema } from 'zod';

export const validateBody = <T>(schema: ZodSchema<T>): RequestHandler => {
  return (req: Request, res: Response, next: NextFunction) => {
    const result = schema.safeParse(req.body);
    if (!result.success) {
      res.status(400).json({
        error: 'Validation failed',
        details: result.error.issues,
      });
      return;
    }
    req.body = result.data;
    next();
  };
};
Build the experiments router

The router is where your API endpoints live. Each endpoint maps an HTTP method and path to a handler function that reads or writes from the database.

  • Create a new file at src/routes/experiments.ts and add the following code:
import { Router, Request, Response } from 'express';
import { eq } from 'drizzle-orm';
import { db } from '../db/index';
import { experiments } from '../db/schema';
import { createExperimentSchema, updateExperimentSchema } from '../schemas/experiment';
import { validateBody } from '../middleware/validate';

const router = Router();

// GET / - List all experiments
router.get('/', async (req: Request, res: Response) => {
  const allExperiments = await db.select().from(experiments);
  res.json(allExperiments);
});

// GET /:id - Get a single experiment by ID
router.get('/:id', async (req: Request, res: Response) => {
  const id = parseInt(req.params.id);
  if (isNaN(id)) {
    res.status(400).json({ error: 'Invalid ID' });
    return;
  }

  const [experiment] = await db
    .select()
    .from(experiments)
    .where(eq(experiments.id, id));

  if (!experiment) {
    res.status(404).json({ error: 'Experiment not found' });
    return;
  }

  res.json(experiment);
});

// POST / - Create a new experiment (validated by Zod middleware)
router.post('/', validateBody(createExperimentSchema), async (req: Request, res: Response) => {
  const [experiment] = await db
    .insert(experiments)
    .values(req.body)
    .returning();

  res.status(201).json(experiment);
});

export default router;

What does this code do?

This file defines three endpoints on an Express Router:

  • GET / queries all experiments from the database using Drizzle's db.select().from() syntax and returns them as JSON.
  • GET /:id parses the ID from the URL, queries a single experiment using the eq comparator, and returns 404 if nothing matches.
  • POST / runs the Zod validation middleware first, then inserts the validated body into the database. The .returning() method returns the newly created row (a PostgreSQL feature) so you can send it back with a 201 status.

Why no try/catch in the route handlers?

Express 5 automatically catches errors from async route handlers. If a database query throws, Express forwards the error to your error-handling middleware without you needing to wrap every handler in try/catch.

  • Save src/routes/experiments.ts.
  • You won't see a change yet. The server entry point you create next mounts this router.

✔️ Awesome, I've got everything!

Great. Double check you've saved the file before moving on.

ⓧ I'd like to double check the full code

import { Router, Request, Response } from 'express';
import { eq } from 'drizzle-orm';
import { db } from '../db/index';
import { experiments } from '../db/schema';
import { createExperimentSchema, updateExperimentSchema } from '../schemas/experiment';
import { validateBody } from '../middleware/validate';

const router = Router();

router.get('/', async (req: Request, res: Response) => {
  const allExperiments = await db.select().from(experiments);
  res.json(allExperiments);
});

router.get('/:id', async (req: Request, res: Response) => {
  const id = parseInt(req.params.id);
  if (isNaN(id)) {
    res.status(400).json({ error: 'Invalid ID' });
    return;
  }

  const [experiment] = await db
    .select()
    .from(experiments)
    .where(eq(experiments.id, id));

  if (!experiment) {
    res.status(404).json({ error: 'Experiment not found' });
    return;
  }

  res.json(experiment);
});

router.post('/', validateBody(createExperimentSchema), async (req: Request, res: Response) => {
  const [experiment] = await db
    .insert(experiments)
    .values(req.body)
    .returning();

  res.status(201).json(experiment);
});

export default router;
Wire up the Express server

The server entry point ties everything together. It creates the Express app, enables JSON parsing, mounts your router, and adds a catch-all error handler.

  • Create a new file at src/index.ts and add the following code:
import 'dotenv/config';
import express from 'express';
import experimentsRouter from './routes/experiments';

const app = express();
const port = process.env.PORT || 3000;

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

// Health check endpoint
app.get('/health', (req, res) => {
  res.json({ status: 'ok' });
});

// Mount the experiments router at /api/experiments
app.use('/api/experiments', experimentsRouter);

// Global error handling middleware (must have 4 parameters)
app.use((err: Error, req: express.Request, res: express.Response, next: express.NextFunction) => {
  console.error(err.stack);
  res.status(500).json({ error: 'Internal server error' });
});

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

What does this code do?

  • express.json() parses incoming JSON request bodies so req.body is a JavaScript object.
  • The /health endpoint gives you a quick way to check if the server is alive.
  • app.use('/api/experiments', experimentsRouter) mounts your router so all its routes are prefixed with /api/experiments.
  • The error middleware has four parameters. Express uses the 4-parameter signature to identify it as an error handler. Any unhandled errors from async routes land here.
  • Save src/index.ts.
  • Start the development server by running this command in your terminal:
npm run dev

You should see output like this:

Server running on http://localhost:3000

Server not starting?

  • If you see a "Cannot find module" error, double check that all four new files are saved in the correct paths: src/schemas/experiment.ts, src/middleware/validate.ts, src/routes/experiments.ts, and src/index.ts.
  • If you see a connection error mentioning PostgreSQL, make sure your PostgreSQL service is running and the DATABASE_URL in .env matches your local setup.
  • If port 3000 is already in use, stop the other process or change PORT in your .env file.

Help me debug my Express server startup error.

Now test your endpoints. Open a second terminal window and run:

  • Test the list endpoint by running this command:
curl http://localhost:3000/api/experiments

You should see an empty array: []. The database has no experiments yet.

  • Create an experiment by running this command:
curl -X POST http://localhost:3000/api/experiments \
  -H "Content-Type: application/json" \
  -d '{"title": "PCR Amplification Test", "hypothesis": "DNA fragment will amplify at 60C annealing temp"}'

You should see a 201 response with the full experiment object, including the auto-generated id, createdAt, and updatedAt fields.

  • Test validation by sending an invalid request with an empty title:
curl -X POST http://localhost:3000/api/experiments \
  -H "Content-Type: application/json" \
  -d '{"title": ""}'

You should see a 400 response with validation error details explaining the title must have at least 1 character.

What should I see?

The successful POST returns a JSON object with all your experiment fields plus database-generated values like id and timestamps. The failed POST returns a JSON object with an error field and a details array listing exactly which fields failed validation and why.

✔️ Awesome, I've got everything!

Great. Your API is accepting valid data and rejecting invalid data. Keep the server running for the next step.

ⓧ I'd like to double check the full code

import 'dotenv/config';
import express from 'express';
import experimentsRouter from './routes/experiments';

const app = express();
const port = process.env.PORT || 3000;

app.use(express.json());

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

app.use('/api/experiments', experimentsRouter);

app.use((err: Error, req: express.Request, res: express.Response, next: express.NextFunction) => {
  console.error(err.stack);
  res.status(500).json({ error: 'Internal server error' });
});

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

Your API can now create and read experiments with full input validation. Next up, you'll add the ability to update and delete experiments to complete the CRUD cycle.

Add Update, Delete, and Test the Full CRUD Cycle

Your API can create experiments and retrieve them. Those are the C and R in CRUD.

In this step, you will add the remaining two operations: update (PATCH) and delete (DELETE). Then you will test the full lifecycle end-to-end with curl to prove every endpoint works together.

In this step, get ready to:
  • Add a PATCH endpoint that updates experiment fields and refreshes the timestamp.
  • Add a DELETE endpoint that removes an experiment and returns it for confirmation.
  • Run through a full create-read-update-delete cycle using curl.
Add the PATCH endpoint

Experiments change as researchers run them. They start as planned, move to in-progress, and eventually reach completed. The PATCH endpoint lets you update any subset of fields without replacing the entire record.

  • In src/routes/experiments.ts, find the router.post route near the bottom of the file.
  • Add the following PATCH route below it, before export default router:
router.patch('/:id', validateBody(updateExperimentSchema), async (req: Request, res: Response) => {
  const id = parseInt(req.params.id);
  if (isNaN(id)) {
    res.status(400).json({ error: 'Invalid ID' });
    return;
  }

  // Update only the provided fields, plus refresh the updatedAt timestamp
  const [experiment] = await db
    .update(experiments)
    .set({ ...req.body, updatedAt: new Date() })
    .where(eq(experiments.id, id))
    .returning();

  if (!experiment) {
    res.status(404).json({ error: 'Experiment not found' });
    return;
  }

  res.json(experiment);
});

What does this code do?

This route validates the request body against updateExperimentSchema (all fields optional) before reaching the handler.

  • The spread { ...req.body, updatedAt: new Date() } merges whatever fields the client sent with a fresh timestamp. Only the provided fields change in the database.
  • The .returning() method is a PostgreSQL-specific feature. It gives you the updated row back without needing a separate SELECT query.
  • If no row matches the ID, experiment is undefined and the route returns a 404.
  • Save src/routes/experiments.ts.
  • Check your terminal where tsx watch is running. It should recompile without errors.

Seeing a TypeScript error?

Make sure you placed the PATCH route after the POST route and before export default router. A route defined outside the router scope causes a compile error.

Also confirm that updateExperimentSchema is imported at the top of the file. It should already be there from the previous step.

Help me fix a TypeScript error in my PATCH route.

Add the DELETE endpoint

Researchers need to remove experiments they no longer want to track. The DELETE endpoint removes a record and returns it as confirmation of what was deleted.

  • In the same file, add the following DELETE route below your new PATCH route and before export default router:
router.delete('/:id', async (req: Request, res: Response) => {
  const id = parseInt(req.params.id);
  if (isNaN(id)) {
    res.status(400).json({ error: 'Invalid ID' });
    return;
  }

  // Delete the experiment and return it for confirmation
  const [experiment] = await db
    .delete(experiments)
    .where(eq(experiments.id, id))
    .returning();

  if (!experiment) {
    res.status(404).json({ error: 'Experiment not found' });
    return;
  }

  res.json({ message: 'Experiment deleted', experiment });
});

What does this code do?

  • Unlike PATCH, this route has no validateBody middleware. A DELETE request needs only the ID from the URL. There is no request body to validate.
  • The response wraps the deleted record in an object with a message field. This gives API consumers a clear signal that the deletion succeeded plus a copy of what was removed.
  • The same 404 pattern applies: if the ID does not match any row, the endpoint tells the client so.
  • Save src/routes/experiments.ts.
  • Confirm tsx watch recompiles cleanly in your terminal.

Server crashed after saving?

Check that you have a closing }); for the router.delete callback. A missing closing brace crashes the TypeScript compiler.

Help me fix my DELETE route.

✔️ Awesome, I've got everything!

Great. Double check you have saved src/routes/experiments.ts and that your server is still running.

ⓧ I'd like to double check the full code

import { Router, Request, Response } from 'express';
import { eq } from 'drizzle-orm';
import { db } from '../db/index';
import { experiments } from '../db/schema';
import { createExperimentSchema, updateExperimentSchema } from '../schemas/experiment';
import { validateBody } from '../middleware/validate';

const router = Router();

router.get('/', async (req: Request, res: Response) => {
  const allExperiments = await db.select().from(experiments);
  res.json(allExperiments);
});

router.get('/:id', async (req: Request, res: Response) => {
  const id = parseInt(req.params.id);
  if (isNaN(id)) {
    res.status(400).json({ error: 'Invalid ID' });
    return;
  }

  const [experiment] = await db
    .select()
    .from(experiments)
    .where(eq(experiments.id, id));

  if (!experiment) {
    res.status(404).json({ error: 'Experiment not found' });
    return;
  }

  res.json(experiment);
});

router.post('/', validateBody(createExperimentSchema), async (req: Request, res: Response) => {
  const [experiment] = await db
    .insert(experiments)
    .values(req.body)
    .returning();

  res.status(201).json(experiment);
});

router.patch('/:id', validateBody(updateExperimentSchema), async (req: Request, res: Response) => {
  const id = parseInt(req.params.id);
  if (isNaN(id)) {
    res.status(400).json({ error: 'Invalid ID' });
    return;
  }

  const [experiment] = await db
    .update(experiments)
    .set({ ...req.body, updatedAt: new Date() })
    .where(eq(experiments.id, id))
    .returning();

  if (!experiment) {
    res.status(404).json({ error: 'Experiment not found' });
    return;
  }

  res.json(experiment);
});

router.delete('/:id', async (req: Request, res: Response) => {
  const id = parseInt(req.params.id);
  if (isNaN(id)) {
    res.status(400).json({ error: 'Invalid ID' });
    return;
  }

  const [experiment] = await db
    .delete(experiments)
    .where(eq(experiments.id, id))
    .returning();

  if (!experiment) {
    res.status(404).json({ error: 'Experiment not found' });
    return;
  }

  res.json({ message: 'Experiment deleted', experiment });
});

export default router;
Test the full CRUD lifecycle

All five endpoints are wired up. Time to prove they work together by walking through a complete experiment lifecycle: create it, find it, update it, and delete it.

  • Open a new terminal window. Your dev server must stay running in the original terminal.
  • Create an experiment by running this command:
curl -X POST http://localhost:3000/api/experiments \
  -H "Content-Type: application/json" \
  -d '{"title": "PCR Amplification", "hypothesis": "DNA fragments will amplify at 95C", "status": "planned"}'

You should see a JSON response with status 201 and your new experiment including an id field. Note the id value for the next commands.

  • List all experiments to confirm it was stored by running:
curl http://localhost:3000/api/experiments

You should see an array containing your new experiment.

  • Get the experiment by its ID (replace 1 with the actual ID from your create response):
curl http://localhost:3000/api/experiments/1
  • Update the experiment status to in-progress by running:
curl -X PATCH http://localhost:3000/api/experiments/1 \
  -H "Content-Type: application/json" \
  -d '{"status": "in-progress"}'

The response shows the updated experiment. Compare the updatedAt timestamp with createdAt. They should now be different.

  • Update it again to completed and add results by running:
curl -X PATCH http://localhost:3000/api/experiments/1 \
  -H "Content-Type: application/json" \
  -d '{"status": "completed", "results": "Amplification successful at 35 cycles"}'
  • Delete the experiment by running:
curl -X DELETE http://localhost:3000/api/experiments/1

You should see a confirmation message with the deleted experiment data.

  • Confirm the list is empty again by running:
curl http://localhost:3000/api/experiments

The response should be []. Your full CRUD cycle is working end-to-end.

What status codes should I see?

  • POST returns 201 (Created).
  • GET and PATCH return 200 (OK).
  • DELETE returns 200 with the deleted record.
  • Any request for a non-existent ID returns 404 (Not Found).
  • A POST or PATCH with invalid data returns 400 (Bad Request) with Zod error details.

Getting connection refused?

Make sure your dev server is still running in the other terminal. If it crashed, run npm run dev again.

If you see an empty response or a database error, confirm PostgreSQL is running and the lab_notebook database exists.

Help me debug my CRUD test.

Secret mission

Add Status Filtering and Title Search

Your list endpoint returns every experiment in the database. As a researcher's notebook grows, that becomes overwhelming. Add query parameter support so researchers can filter by status and search by title keyword.

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 PostgreSQL, so there are no ongoing costs.

Resources you used:

  • A local PostgreSQL database named lab_notebook.
  • The lab-notebook-api project folder on your Desktop.
  • Node.js packages installed in node_modules.

Keep everything running

No action needed. Choose this if you're still actively building or plan to continue with the next project in this series.

  • Your PostgreSQL database costs nothing to keep running locally.
  • Your project files stay on your Desktop, ready for the next session.
  • The dev server is only running while your terminal is active. Closing the terminal stops it automatically.

Pause - I'll come back to this later

Stop the PostgreSQL service to free up system resources. Your data stays intact for when you return.

  • If you installed PostgreSQL with Homebrew on macOS, stop the service by running:
brew services stop postgresql
  • On Windows, open the Services app (search for "Services" in the Start menu), find the PostgreSQL service, and click Stop.
  • Your lab_notebook database and project files remain on disk. When you're ready to resume, restart PostgreSQL and run npm run dev from your project folder.

Delete - I don't want to use this again

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

  • Drop the PostgreSQL database by running:
dropdb lab_notebook
  • Delete the project folder. On macOS or Linux, run:
cd ~/Desktop
rm -rf lab-notebook-api
  • On Windows, run:
cd ~\Desktop
rmdir /s /q lab-notebook-api
  • Confirm the folder is gone by running ls (macOS/Linux) or dir (Windows) on your Desktop. You should no longer see lab-notebook-api in the output.

Nice Work!

Nice Work!

You just built a production-ready REST API from scratch using Express 5, TypeScript, Drizzle ORM, and PostgreSQL. This is the backbone that authentication, AI features, and a React frontend will plug into.

You've learned how to:

  • Define a PostgreSQL schema entirely in TypeScript using Drizzle ORM, with no separate schema language and no code generation step.
  • Create reusable validation middleware with Zod that rejects bad data before it reaches your database.
  • Implement full CRUD endpoints (Create, Read, Update, Delete) with proper HTTP status codes and .returning() for instant confirmation.
  • Secret Mission: Added composable query filters with status and title search using Drizzle's ilike and and operators.

Ready to quiz yourself?