heymegabyte/agent-skills / 05-architecture-and-stack
architecture-and-stack
VerifiedCloudflare-first platform selection. Workers, D1, R2, KV, DO, Queues, Vectorize, Containers, Workflows, native Go CLIs and measured Rust/WASM acceleration. Default stack, override conditions, auth, data patterns and reliability.
drizzle-orm-and-migrations.md
View on GitHub---
name: "Drizzle ORM and Migrations"
description: "Drizzle ORM v1.0 (pre-release — v1.0.0-rc.1 as of Apr 2026; stable line is still 0.45.x — pin the rc explicitly) as the database abstraction layer for D1 (SQLite) and Neon (PostgreSQL). RQBv2, 10x faster introspection, schema-first design with auto-generated migrations, type-safe queries, and the Drizzle → D1/Neon setup pattern. Covers schema conventions, relation patterns, migration workflow, and seed data."
updated: "2026-04-23"
---
# Drizzle ORM and Migrations
## Why Drizzle (v1.0.0-rc.1 — pre-release; stable line still 0.45.x; passed Prisma in downloads)
- Type-safe queries, 5KB bundle (vs Prisma 40KB+), zero-overhead SQL
- RQBv2 — 363 commits, 9K+ tests, relational query builder rewrite
- 10x schema introspection speed (10s → <1s)
- Schema defined in TypeScript (single source of truth)
- Auto-generated migrations via `drizzle-kit`
- Works with D1 (SQLite), Neon (PostgreSQL), MSSQL (new)
- Edge runtime compatible — perfect for Workers
## Setup
### Install
```bash
npm install drizzle-orm
npm install -D drizzle-kit
```
### drizzle.config.ts
```typescript
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
schema: './src/db/schema.ts',
out: './drizzle',
dialect: 'sqlite', // or 'postgresql' for Neon
driver: 'd1-http',
});
```
### Worker Binding
```typescript
import { drizzle } from 'drizzle-orm/d1';
import * as schema from './db/schema';
app.use('*', async (c, next) => {
c.set('db', drizzle(c.env.DB, { schema }));
await next();
});
```
## Schema Conventions
### Standard Columns (Every Table)
```typescript
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
import { sql } from 'drizzle-orm';
// ULID for ordered primary keys (better D1 insert performance)
// See: https://github.com/ulid/spec
export const users = sqliteTable('users', {
id: text('id').primaryKey(), // ULID
email: text('email').notNull().unique(),
name: text('name').notNull(),
role: text('role', { enum: ['user', 'admin'] }).default('user').notNull(),
createdAt: text('created_at').default(sql`(datetime('now'))`).notNull(),
updatedAt: text('updated_at').default(sql`(datetime('now'))`).notNull(),
deletedAt: text('deleted_at'), // Soft delete — null means active
});
```
### Naming Rules
- **Tables** — plural snake_case (`users`, `blog_posts`, `donation_records`)
- **Columns** — snake_case (`created_at`, `stripe_customer_id`)
- **TypeScript** — camelCase (`createdAt`, `stripeCustomerId`) — Drizzle maps automatically
- **Indexes** — `idx_{table}_{column}` (`idx_users_email`)
- **Foreign keys** — `{referenced_table}_id` (`user_id`, `post_id`)
### Relations
```typescript
import { relations } from 'drizzle-orm';
export const posts = sqliteTable('posts', {
id: text('id').primaryKey(),
title: text('title').notNull(),
slug: text('slug').notNull().unique(),
content: text('content').notNull(),
authorId: text('author_id').notNull().references(() => users.id),
publishedAt: text('published_at'),
createdAt: text('created_at').default(sql`(datetime('now'))`).notNull(),
});
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts),
}));
export const postsRelations = relations(posts, ({ one }) => ({
author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));
```
### Indexes
```typescript
import { index, uniqueIndex } from 'drizzle-orm/sqlite-core';
export const posts = sqliteTable('posts', {
// ... columns
}, (table) => ({
slugIdx: uniqueIndex('idx_posts_slug').on(table.slug),
authorIdx: index('idx_posts_author').on(table.authorId),
publishedIdx: index('idx_posts_published').on(table.publishedAt),
}));
```
## Migration Workflow
### Generate Migration
```bash
npx drizzle-kit generate
# Creates: drizzle/0001_initial.sql
```
### Apply to D1
```bash
npx wrangler d1 migrations apply DB --local # Test locally first
npx wrangler d1 migrations apply DB # Apply to production
```
### Migration Best Practices
- Always generate, never hand-write SQL
- Test locally before applying to production
- Never rename columns in production (add new, migrate data, drop old)
- Add indexes AFTER initial data load for speed
- Use `PRAGMA optimize` after bulk writes
## Common Query Patterns
### Select with Relations
```typescript
const postsWithAuthor = await db.query.posts.findMany({
with: { author: true },
where: isNotNull(posts.publishedAt),
orderBy: [desc(posts.publishedAt)],
limit: 10,
});
```
### Insert
```typescript
import { ulid } from 'ulid';
await db.insert(users).values({
id: ulid(),
email: parsed.email,
name: parsed.name,
});
```
### Update
```typescript
await db.update(users)
.set({ name: parsed.name, updatedAt: sql`datetime('now')` })
.where(eq(users.id, userId));
```
### Soft Delete
```typescript
await db.update(users)
.set({ deletedAt: sql`datetime('now')` })
.where(eq(users.id, userId));
// Always filter out soft-deleted in queries
const activeUsers = await db.query.users.findMany({
where: isNull(users.deletedAt),
});
```
### Transaction
```typescript
await db.batch([
db.insert(donations).values({ id: ulid(), amount: 5000, userId }),
db.update(campaigns).set({ raised: sql`raised + 5000` }).where(eq(campaigns.id, campaignId)),
]);
```
## Seed Data
```typescript
// src/db/seed.ts — run after migrations
async function seed(db: DrizzleD1Database) {
await db.insert(users).values([
{ id: ulid(), email: 'brian@megabyte.space', name: 'Brian Zalewski', role: 'admin' },
]);
}
```
## Neon (PostgreSQL) Variant
When D1 isn't enough (complex joins, full-text search, >1TB, RLS):
```typescript
import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';
const sql = neon(env.DATABASE_URL);
const db = drizzle(sql, { schema });
```
Schema uses `pgTable` instead of `sqliteTable`, identity columns (not serial) for IDs, and `timestamp` instead of `text` for dates. Use `$inferSelect`/`$inferInsert` for type derivation. Zod integration via `createInsertSchema`/`createSelectSchema`.
## D1 Notes (2026)
- Global read replication (beta) — routes reads to nearest replica, reduces latency 40-60%
- Storage — 1TB per account (paid), Time Travel 30-day PIT recovery
- PRAGMA optimize support for query performance
- Does NOT support BEGIN transactions — use batch API instead
- Prepared statements for repeated queries
- `node:fs` and Web File System APIs now available in Workers
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack." | Stars: 198k | Updated | Claude CodeCodex | Verified | Install |
| ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |