achreftlili/drizzle-starter

Drizzle Starter

Scaffold a Drizzle ORM project with TypeScript, schema definition (pgTable/mysqlTable), migrations via drizzle-kit, query builder, relations, Drizzle Studio, and type-safe queries.

Prerequisites

• Node.js >= 20.x
• TypeScript >= 5.3
• PostgreSQL, MySQL, or SQLite database
• npm or pnpm

Scaffold Command

# PostgreSQL
npm install drizzle-orm postgres
npm install -D drizzle-kit typescript @types/node tsx

# MySQL
npm install drizzle-orm mysql2
npm install -D drizzle-kit

# SQLite (libsql / Turso)
npm install drizzle-orm @libsql/client
npm install -D drizzle-kit

# Create config file
touch drizzle.config.ts

Database Initialization

# Push schema to database (development)
npx drizzle-kit push

# Generate migration files
npx drizzle-kit generate

# Apply migrations (production)
npx drizzle-kit migrate

# Open Drizzle Studio to browse data
npx drizzle-kit studio

Project Structure

src/
  db/
    index.ts                      # Database connection + Drizzle instance
    schema/
      index.ts                    # Re-export all schemas
      users.ts                    # User table definition
      posts.ts                    # Post table definition
      tags.ts                     # Tag table + junction table
      relations.ts                # Relation definitions
  repositories/
    user.repository.ts
    post.repository.ts
drizzle/
  migrations/                     # Auto-generated migration SQL files
    0000_initial.sql
    meta/
drizzle.config.ts                 # Drizzle Kit configuration

Key Conventions

• Schema is defined in TypeScript using pgTable, mysqlTable, or sqliteTable functions.
• Relations are defined separately from table schemas using relations().
• One file per table/entity under src/db/schema/. Export everything from a barrel index.ts.
• Use drizzle-kit for migrations: generate creates SQL from schema diffs, migrate applies them.
• Drizzle provides both a SQL-like query builder and a relational query API. Use relational API for reads with joins, SQL-like API for inserts/updates/deletes.
• All queries are type-safe at compile time. No need for extra code generation steps.

Essential Patterns

Drizzle Kit Config (drizzle.config.ts)

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/db/schema/index.ts",
  out: "./drizzle/migrations",
  dbCredentials: {
    url: process.env.DATABASE_URL ?? "postgresql://app:secret@localhost:5432/myapp",
  },
  verbose: true,
  strict: true,
});

Database Connection (src/db/index.ts)

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const connectionString = process.env.DATABASE_URL ?? "postgresql://app:secret@localhost:5432/myapp";

// Connection pool for queries
const client = postgres(connectionString, { max: 10 });

export const db = drizzle(client, { schema });
export type Database = typeof db;

Users Schema (src/db/schema/users.ts)

import { pgTable, uuid, varchar, text, timestamp, pgEnum } from "drizzle-orm/pg-core";

export const userRoleEnum = pgEnum("user_role", ["user", "admin"]);

export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  name: varchar("name", { length: 255 }).notNull(),
  password: varchar("password", { length: 255 }).notNull(),
  role: userRoleEnum("role").default("user").notNull(),
  bio: text("bio"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

Posts Schema (src/db/schema/posts.ts)

import { pgTable, uuid, varchar, text, timestamp, pgEnum, index } from "drizzle-orm/pg-core";
import { users } from "./users";

export const postStatusEnum = pgEnum("post_status", ["draft", "published", "archived"]);

export const posts = pgTable(
  "posts",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    title: varchar("title", { length: 500 }).notNull(),
    content: text("content"),
    status: postStatusEnum("status").default("draft").notNull(),
    authorId: uuid("author_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    publishedAt: timestamp("published_at"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => [
    index("posts_author_idx").on(table.authorId),
    index("posts_status_idx").on(table.status),
  ]
);

export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;

Tags and Junction Table (src/db/schema/tags.ts)

import { pgTable, uuid, varchar, primaryKey } from "drizzle-orm/pg-core";
import { posts } from "./posts";

export const tags = pgTable("tags", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: varchar("name", { length: 100 }).notNull().unique(),
});

export const postsTags = pgTable(
  "posts_tags",
  {
    postId: uuid("post_id")
      .notNull()
      .references(() => posts.id, { onDelete: "cascade" }),
    tagId: uuid("tag_id")
      .notNull()
      .references(() => tags.id, { onDelete: "cascade" }),
  },
  (table) => [primaryKey({ columns: [table.postId, table.tagId] })]
);

export type Tag = typeof tags.$inferSelect;
export type NewTag = typeof tags.$inferInsert;

Relations (src/db/schema/relations.ts)

import { relations } from "drizzle-orm";
import { users } from "./users";
import { posts } from "./posts";
import { tags, postsTags } from "./tags";

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
  postsTags: many(postsTags),
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  postsTags: many(postsTags),
}));

export const postsTagsRelations = relations(postsTags, ({ one }) => ({
  post: one(posts, {
    fields: [postsTags.postId],
    references: [posts.id],
  }),
  tag: one(tags, {
    fields: [postsTags.tagId],
    references: [tags.id],
  }),
}));

Schema Barrel Export (src/db/schema/index.ts)

export * from "./users";
export * from "./posts";
export * from "./tags";
export * from "./relations";

Repository — SQL-like API (src/repositories/user.repository.ts)

import { eq, ilike, and, desc, sql, count } from "drizzle-orm";
import { db } from "../db";
import { users, type NewUser } from "../db/schema/users";

export async function createUser(data: NewUser) {
  const [user] = await db.insert(users).values(data).returning({
    id: users.id,
    email: users.email,
    name: users.name,
    role: users.role,
    createdAt: users.createdAt,
  });
  return user;
}

export async function findUserByEmail(email: string) {
  return db.query.users.findFirst({
    where: eq(users.email, email),
  });
}

export async function findUserWithPosts(userId: string) {
  return db.query.users.findFirst({
    where: eq(users.id, userId),
    with: {
      posts: {
        where: eq(posts.status, "published"),
        orderBy: [desc(posts.createdAt)],
        limit: 10,
      },
    },
  });
}

export async function listUsers(params: {
  page: number;
  limit: number;
  search?: string;
  role?: "user" | "admin";
}) {
  const conditions = [];
  if (params.search) {
    conditions.push(ilike(users.name, `%${params.search}%`));
  }
  if (params.role) {
    conditions.push(eq(users.role, params.role));
  }
  const where = conditions.length > 0 ? and(...conditions) : undefined;

  const [data, [{ total }]] = await Promise.all([
    db
      .select({
        id: users.id,
        email: users.email,
        name: users.name,
        role: users.role,
        createdAt: users.createdAt,
      })
      .from(users)
      .where(where)
      .orderBy(desc(users.createdAt))
      .offset((params.page - 1) * params.limit)
      .limit(params.limit),
    db.select({ total: count() }).from(users).where(where),
  ]);

  return { users: data, total, pages: Math.ceil(total / params.limit) };
}

export async function updateUser(id: string, data: Partial<NewUser>) {
  const [user] = await db
    .update(users)
    .set({ ...data, updatedAt: new Date() })
    .where(eq(users.id, id))
    .returning();
  return user;
}

export async function deleteUser(id: string) {
  await db.delete(users).where(eq(users.id, id));
}

Transaction Example

import { db } from "../db";
import { users } from "../db/schema/users";
import { posts } from "../db/schema/posts";

export async function createUserWithFirstPost(userData: NewUser, postTitle: string) {
  return db.transaction(async (tx) => {
    const [user] = await tx.insert(users).values(userData).returning();

    const [post] = await tx
      .insert(posts)
      .values({
        title: postTitle,
        authorId: user.id,
        status: "draft",
      })
      .returning();

    return { user, post };
  });
}

Migration Runner (Programmatic)

import { migrate } from "drizzle-orm/postgres-js/migrator";
import { db } from "./db";

async function runMigrations() {
  console.log("Running migrations...");
  await migrate(db, { migrationsFolder: "./drizzle/migrations" });
  console.log("Migrations complete");
}

runMigrations().catch(console.error);

Common Commands

# Generate migration from schema changes
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Push schema directly (dev only — no migration files)
npx drizzle-kit push

# Pull schema from existing database
npx drizzle-kit pull

# Open Drizzle Studio (visual database browser)
npx drizzle-kit studio

# Check schema for issues
npx drizzle-kit check

# Drop all tables (dev only)
npx drizzle-kit drop

Integration Notes

• NestJS / Express / Fastify: Export db from src/db/index.ts and import it in services. No special ORM adapter needed.
• NextAuth / Auth.js: Use @auth/drizzle-adapter for session/account storage. Pair with nextauth-skill.
• Testing: Use a test database. Run migrations before tests. Truncate tables between test suites. Drizzle's type-safe API makes test data builders straightforward.
• Docker: Pair with docker-compose-generator skill for the database service. Run npx drizzle-kit migrate in the app startup.
• Prisma migration: Drizzle and Prisma serve the same role. Pick one per project.