SKILL.md

--- frontmatter

name: agentic-jumpstart-database
description: Database patterns with Drizzle ORM and PostgreSQL including schema design, queries, migrations, transactions, and relationships. Use when working with database schemas, queries, migrations, indexes, joins, or when the user mentions database, Drizzle, PostgreSQL, SQL, or data access.

Database Patterns with Drizzle ORM

Schema Design

Table Definition

typescript

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

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  name: varchar("name", { length: 100 }),
  bio: text("bio"),
  isAdmin: boolean("is_admin").default(false).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// Type exports
export type User = typeof users.$inferSelect;
export type UserCreate = typeof users.$inferInsert;

Relationships

typescript

import { relations } from "drizzle-orm";

// One-to-many
export const modules = pgTable("modules", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  order: integer("order").default(0).notNull(),
});

export const segments = pgTable("segments", {
  id: serial("id").primaryKey(),
  moduleId: integer("module_id")
    .references(() => modules.id, { onDelete: "cascade" })
    .notNull(),
  title: varchar("title", { length: 255 }).notNull(),
  order: integer("order").default(0).notNull(),
});

// Define relations for query builder
export const modulesRelations = relations(modules, ({ many }) => ({
  segments: many(segments),
}));

export const segmentsRelations = relations(segments, ({ one }) => ({
  module: one(modules, {
    fields: [segments.moduleId],
    references: [modules.id],
  }),
}));

Indexes

typescript

import { pgTable, index, uniqueIndex } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
}, (table) => ({
  emailIdx: uniqueIndex("email_idx").on(table.email),
  createdAtIdx: index("created_at_idx").on(table.createdAt),
}));

Data Access Layer

Data access functions go in /src/data-access/. They contain pure database operations with no business logic.

Naming Convention

•Function name: verbNoun (e.g., createUser, getSegmentById)

Basic CRUD Operations

typescript

// src/data-access/users.ts
import { database } from "~/db";
import { users } from "~/db/schema";
import { eq } from "drizzle-orm";
import type { User, UserCreate } from "~/db/schema";

export async function getUsers() {
  return database.query.users.findMany();
}

export async function getUserById(id: number) {
  const result = await database
    .select()
    .from(users)
    .where(eq(users.id, id))
    .limit(1);
  return result[0];
}

export async function getUserByEmail(email: string) {
  const result = await database
    .select()
    .from(users)
    .where(eq(users.email, email))
    .limit(1);
  return result[0];
}

export async function createUser(user: UserCreate) {
  const result = await database.insert(users).values(user).returning();
  return result[0];
}

export async function updateUser(id: number, user: Partial<UserCreate>) {
  const result = await database
    .update(users)
    .set({ ...user, updatedAt: new Date() })
    .where(eq(users.id, id))
    .returning();
  return result[0];
}

export async function deleteUser(id: number) {
  const result = await database
    .delete(users)
    .where(eq(users.id, id))
    .returning();
  return result[0];
}

Query Patterns

Select Specific Columns

typescript

// Only select what you need
const users = await database
  .select({
    id: users.id,
    name: users.name,
    email: users.email,
  })
  .from(users);

Filtering

typescript

import { eq, ne, gt, lt, gte, lte, like, and, or, isNull, isNotNull, inArray } from "drizzle-orm";

// Equality
const user = await database
  .select()
  .from(users)
  .where(eq(users.email, email));

// Multiple conditions
const activeAdmins = await database
  .select()
  .from(users)
  .where(and(eq(users.isAdmin, true), isNotNull(users.lastLoginAt)));

// OR conditions
const results = await database
  .select()
  .from(users)
  .where(or(eq(users.role, "admin"), eq(users.role, "moderator")));

// IN clause
const selectedUsers = await database
  .select()
  .from(users)
  .where(inArray(users.id, [1, 2, 3]));

// LIKE pattern matching
const matchingUsers = await database
  .select()
  .from(users)
  .where(like(users.name, `%${searchTerm}%`));

Joins

typescript

import { eq } from "drizzle-orm";

// Inner join
const segmentsWithModules = await database
  .select({
    segment: segments,
    moduleTitle: modules.title,
  })
  .from(segments)
  .innerJoin(modules, eq(segments.moduleId, modules.id));

// Left join (optional relationship)
const usersWithProgress = await database
  .select()
  .from(users)
  .leftJoin(progress, eq(users.id, progress.userId));

Using Query Builder with Relations

typescript

// Get segments with their modules (using relations)
const result = await database.query.segments.findMany({
  with: {
    module: true,
  },
  orderBy: [segments.order],
});

// Nested relations
const modulesWithSegments = await database.query.modules.findMany({
  with: {
    segments: {
      with: {
        attachments: true,
      },
    },
  },
});

Ordering and Pagination

typescript

import { desc, asc } from "drizzle-orm";

const paginatedUsers = await database
  .select()
  .from(users)
  .orderBy(desc(users.createdAt))
  .limit(20)
  .offset(40);

Aggregations

typescript

import { sql, count } from "drizzle-orm";

// Count
const [{ total }] = await database
  .select({ total: count() })
  .from(users);

// Sum, avg, etc.
const [{ avgPrice }] = await database
  .select({ avgPrice: sql`avg(${products.price})` })
  .from(products);

Transactions

typescript

export async function reorderSegmentsUseCase(
  updates: { id: number; order: number }[]
) {
  return database.transaction(async (tx) => {
    const results = [];
    for (const update of updates) {
      const [result] = await tx
        .update(segments)
        .set({ order: update.order, updatedAt: new Date() })
        .where(eq(segments.id, update.id))
        .returning();
      results.push(result);
    }
    return results;
  });
}

Migration Commands

bash

# Generate migration from schema changes
npm run db:generate

# Run migrations
npm run db:migrate

# Push schema directly (development only)
npm run db:push

# Open Drizzle Studio
npm run db:studio

# Reset database (clear, migrate, seed)
npm run db:reset

Common Patterns

Soft Delete

typescript

export const users = pgTable("users", {
  // ...other fields
  deletedAt: timestamp("deleted_at"),
});

// Query only non-deleted
const activeUsers = await database
  .select()
  .from(users)
  .where(isNull(users.deletedAt));

// Soft delete
await database
  .update(users)
  .set({ deletedAt: new Date() })
  .where(eq(users.id, id));

Timestamp Management

typescript

// Always update updatedAt on modifications
export async function updateUser(id: number, data: Partial<UserCreate>) {
  const result = await database
    .update(users)
    .set({ ...data, updatedAt: new Date() })
    .where(eq(users.id, id))
    .returning();
  return result[0];
}

Check if Exists

typescript

export async function isEmailInUse(email: string): Promise<boolean> {
  const existing = await database
    .select({ id: users.id })
    .from(users)
    .where(eq(users.email, email))
    .limit(1);
  return existing.length > 0;
}

Database Patterns with Drizzle ORM

Schema Design

Table Definition

Relationships

Indexes

Data Access Layer

Naming Convention

Basic CRUD Operations

Query Patterns

Select Specific Columns

Filtering

Joins

Using Query Builder with Relations

Ordering and Pagination

Aggregations

Transactions

Migration Commands

Common Patterns

Soft Delete

Timestamp Management

Check if Exists

Database Checklist