drizzle-migrations

✅ USE this skill for:

• Adding new tables or modifying existing columns
• Generating and running database migrations
• Drizzle-specific query patterns and relations
• SQLite schema best practices with Drizzle
• Setting up Drizzle configuration

❌ DO NOT use for:

• Supabase/PostgreSQL → use supabase-admin skill
• Raw SQL without Drizzle → use standard SQL resources
• Prisma ORM → different syntax and patterns
• General database design theory → use database architecture resources

Configuration: drizzle.config.ts

```typescript

import { defineConfig } from 'drizzle-kit';

export default defineConfig({

schema: './src/db/schema.ts',

out: './drizzle',

dialect: 'sqlite',

dbCredentials: {

url: './data/app.db',

},

});

```

Commands:

```bash

npm run db:generate # Generate migration files

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

npm run db:studio # Open Drizzle Studio GUI

```

Location: src/db/schema.ts

Table Definition

```typescript

import { sqliteTable, text, integer, real, blob } from 'drizzle-orm/sqlite-core';

import { relations } from 'drizzle-orm';

// Basic table

export const users = sqliteTable('users', {

id: text('id').primaryKey(),

email: text('email').notNull().unique(),

username: text('username').notNull(),

passwordHash: text('password_hash'),

createdAt: text('created_at').notNull().default(sqlCURRENT_TIMESTAMP),

updatedAt: text('updated_at'),

});

// Table with foreign key

export const checkIns = sqliteTable('check_ins', {

id: text('id').primaryKey(),

userId: text('user_id').notNull().references(() => users.id, {

onDelete: 'cascade',

}),

mood: integer('mood').notNull(),

cravingLevel: integer('craving_level').notNull(),

sleepHours: real('sleep_hours'),

notes: text('notes'),

createdAt: text('created_at').notNull().default(sqlCURRENT_TIMESTAMP),

});

// Table with composite index

export const auditLog = sqliteTable('audit_log', {

id: text('id').primaryKey(),

userId: text('user_id').notNull(),

action: text('action').notNull(),

targetType: text('target_type'),

targetId: text('target_id'),

details: text('details'), // JSON string

createdAt: text('created_at').notNull().default(sqlCURRENT_TIMESTAMP),

}, (table) => ({

userActionIdx: index('idx_audit_user_action').on(table.userId, table.action),

createdAtIdx: index('idx_audit_created').on(table.createdAt),

}));

```

Relations

```typescript

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

checkIns: many(checkIns),

sessions: many(sessions),

journalEntries: many(journalEntries),

}));

export const checkInsRelations = relations(checkIns, ({ one }) => ({

user: one(users, {

fields: [checkIns.userId],

references: [users.id],

}),

}));

```

SQLite Types in Drizzle

```typescript

import {

sqliteTable,

text, // TEXT - strings, JSON, dates

integer, // INTEGER - numbers, booleans (0/1)

real, // REAL - floating point

blob, // BLOB - binary data

} from 'drizzle-orm/sqlite-core';

const examples = sqliteTable('examples', {

// Strings

name: text('name').notNull(),

description: text('description'),

// Numbers

count: integer('count').notNull().default(0),

rating: real('rating'),

// Booleans (stored as 0/1)

isActive: integer('is_active', { mode: 'boolean' }).default(true),

// Dates (stored as ISO strings)

createdAt: text('created_at').notNull().default(sqlCURRENT_TIMESTAMP),

expiresAt: text('expires_at'),

// JSON (stored as TEXT)

metadata: text('metadata', { mode: 'json' }),

// Enums (stored as TEXT)

status: text('status', { enum: ['pending', 'active', 'archived'] }),

});

```

Strategy 1: Push (Development Only)

```bash

npm run db:push

```

• Directly applies schema changes
• Fast for development
• Never use in production

Strategy 2: Generate & Migrate (Production)

```bash

# 1. Generate migration file

npm run db:generate

# 2. Review generated SQL in /drizzle folder

# 3. Apply migration (in code or manually)

```

Applying Migrations in Code

```typescript

import { drizzle } from 'drizzle-orm/better-sqlite3';

import { migrate } from 'drizzle-orm/better-sqlite3/migrator';

import Database from 'better-sqlite3';

const sqlite = new Database('./data/app.db');

const db = drizzle(sqlite);

// Run migrations

migrate(db, { migrationsFolder: './drizzle' });

```

Adding a New Table

```typescript

// 1. Add to schema.ts

export const newFeature = sqliteTable('new_feature', {

id: text('id').primaryKey(),

userId: text('user_id').notNull().references(() => users.id),

name: text('name').notNull(),

createdAt: text('created_at').notNull().default(sqlCURRENT_TIMESTAMP),

});

// 2. Add relations

export const newFeatureRelations = relations(newFeature, ({ one }) => ({

user: one(users, {

fields: [newFeature.userId],

references: [users.id],

}),

}));

// 3. Generate migration

// npm run db:generate

```

Adding a Column

```typescript

// In schema.ts, add the new column

export const users = sqliteTable('users', {

// existing columns...

newColumn: text('new_column'), // Add this

});

// Generate migration

// npm run db:generate

```

Adding an Index

```typescript

export const messages = sqliteTable('messages', {

id: text('id').primaryKey(),

conversationId: text('conversation_id').notNull(),

createdAt: text('created_at').notNull(),

}, (table) => ({

// Add index

convCreatedIdx: index('idx_messages_conv_created')

.on(table.conversationId, table.createdAt),

}));

```

Renaming (Requires Manual SQL)

SQLite doesn't support direct column renames in older versions. For complex changes:

```sql

-- drizzle/XXXX_rename_column.sql

-- Manual migration for column rename

-- 1. Create new table with desired schema

CREATE TABLE users_new (

id TEXT PRIMARY KEY,

email TEXT NOT NULL UNIQUE,

display_name TEXT NOT NULL, -- renamed from username

created_at TEXT NOT NULL

);

-- 2. Copy data

INSERT INTO users_new SELECT id, email, username, created_at FROM users;

-- 3. Drop old table

DROP TABLE users;

-- 4. Rename new table

ALTER TABLE users_new RENAME TO users;

```

Basic Queries

```typescript

import { db } from '@/db';

import { eq, and, or, desc, asc, like, gte, lte } from 'drizzle-orm';

import { users, checkIns } from '@/db/schema';

// Select all

const allUsers = await db.select().from(users);

// Select with conditions

const activeUsers = await db

.select()

.from(users)

.where(eq(users.isActive, true));

// Select specific columns

const userEmails = await db

.select({ id: users.id, email: users.email })

.from(users);

// Complex where clause

const results = await db

.select()

.from(checkIns)

.where(

and(

eq(checkIns.userId, userId),

gte(checkIns.createdAt, startDate),

lte(checkIns.createdAt, endDate)

)

)

.orderBy(desc(checkIns.createdAt))

.limit(30);

```

Insert

```typescript

// Single insert

const [newUser] = await db

.insert(users)

.values({

id: generateId(),

email: 'user@example.com',

username: 'newuser',

})

.returning();

// Bulk insert

await db.insert(checkIns).values([

{ id: '1', userId, mood: 7, cravingLevel: 2 },

{ id: '2', userId, mood: 8, cravingLevel: 1 },

]);

// Upsert (insert or update)

await db

.insert(users)

.values({ id: 'user-1', email: 'new@example.com' })

.onConflictDoUpdate({

target: users.id,

set: { email: 'new@example.com' },

});

```

Update

```typescript

await db

.update(users)

.set({ username: 'newname', updatedAt: new Date().toISOString() })

.where(eq(users.id, userId));

```

Delete

```typescript

// Always use WHERE clause!

await db

.delete(checkIns)

.where(eq(checkIns.id, checkInId));

// Delete with multiple conditions

await db

.delete(sessions)

.where(

and(

eq(sessions.userId, userId),

lte(sessions.expiresAt, new Date().toISOString())

)

);

```

Joins

```typescript

const userWithCheckIns = await db

.select({

user: users,

checkIn: checkIns,

})

.from(users)

.leftJoin(checkIns, eq(users.id, checkIns.userId))

.where(eq(users.id, userId));

```

Aggregations

```typescript

import { count, avg, sum, max, min } from 'drizzle-orm';

const stats = await db

.select({

totalCheckIns: count(),

avgMood: avg(checkIns.mood),

maxStreak: max(checkIns.streak),

})

.from(checkIns)

.where(eq(checkIns.userId, userId));

```

1. Always use transactions for related changes

```typescript

await db.transaction(async (tx) => {

await tx.insert(users).values(userData);

await tx.insert(profiles).values(profileData);

});

```

1. Always include WHERE on DELETE/UPDATE
2. Use indexes for frequently queried columns
3. Store dates as ISO strings for SQLite
4. Use returning() to get inserted/updated rows
5. Generate migrations, don't push to production

• [Drizzle ORM Docs](https://orm.drizzle.team/docs/overview)
• [Drizzle SQLite](https://orm.drizzle.team/docs/get-started-sqlite)
• [Drizzle Migrations](https://orm.drizzle.team/docs/migrations)