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.

Quality

81%

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Securityby

Risky

Do not use without reviewing

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.

Repository: achreftlili/deep-dev-skills
Commit: 181fcbc

Last updated: about 1 month ago
Created: about 1 month ago

Is this your skill?

If you maintain this skill, you can claim it as your own. Once claimed, you can manage eval scenarios, bundle related skills, attach documentation or rules, and ensure cross-agent compatibility.