Setting Up Fastify + PostgreSQL with Drizzle ORM

Introduction

Learn to create a type-safe database layer by integrating Fastify with PostgreSQL via Drizzle ORM.

Prerequisites

Node.js >=16
PostgreSQL instance (e.g., running locally or via Docker)

Step 1: Initialize Project

mkdir fastify-drizzle
cd fastify-drizzle
npm init -y
npm install fastify @drizzle/orm drizzle-orm postgres

Step 2: Configure Database Connection

Create src/db.ts:

import { Drizzle } from "drizzle-orm";
import { Pool } from "postgres";

const pool = new Pool({
  connectionString:
    process.env.DATABASE_URL || "postgres://user:password@localhost:5432/db",
});

export const db = Drizzle(pool);

Step 3: Define Drizzle Schema

Create src/schema.ts:

import { pgTable, serial, text, varchar } from "drizzle-orm";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  username: varchar("username", { length: 50 }).notNull(),
  email: text("email").notNull(),
});

Step 4: Set Up Fastify Server

Create src/server.ts:

import Fastify from "fastify";
import { db } from "./db";
import { users } from "./schema";

async function buildServer() {
  const app = Fastify({ logger: true });

  app.get("/users", async () => {
    const list = await db.select().from(users);
    return list;
  });

  app.post("/users", async (request, reply) => {
    const { username, email } = request.body as any;
    const result = await db
      .insert(users)
      .values({ username, email })
      .returning();
    reply.code(201).send(result);
  });

  return app;
}

if (require.main === module) {
  buildServer().then(app => app.listen({ port: 3000 }));
}

export default buildServer;

Step 5: Run Migrations

Use your preferred migration tool (e.g., drizzle-kit) to create the users table:

npx drizzle-kit generate:migration
npx drizzle-kit migrate

Step 6: Test Endpoints

# Create a user
curl -X POST http://localhost:3000/users \
  -H "Content-Type: application/json" \
  -d '{"username":"bob","email":"bob@example.com"}'

# List users
curl http://localhost:3000/users

Summary

This setup provides a type-safe, scalable stack with Fastify, PostgreSQL, and Drizzle ORM, enabling rapid API development with confidence.