database-migrations

Name: database-migrations
Author: skateddu

27stars4forks

How to install database-migrations?

skateddu/claude-code-python-setup/database-migrations

$npx -y skills add skateddu/claude-code-python-setup --skill database-migrations

Installs into the current project.

Summary

Database migration best practices for schema changes, data migrations, rollbacks, and zero-downtime deployments across PostgreSQL, MySQL, and common ORMs (Prisma, Drizzle, Django, TypeORM, golang-migrate).

Files · 1

View on GitHub

SKILL.md

1	# Database Migration Patterns
2
3	Safe, reversible database schema changes for production systems.
4
5	## When to Activate
6
7	- Creating or altering database tables
8	- Adding/removing columns or indexes
9	- Running data migrations (backfill, transform)
10	- Planning zero-downtime schema changes
11	- Setting up migration tooling for a new project
12
13	## Core Principles
14
15	1. Every change is a migration — never alter production databases manually
16	2. Migrations are forward-only in production — rollbacks use new forward migrations
17	3. Schema and data migrations are separate — never mix DDL and DML in one migration
18	4. Test migrations against production-sized data — a migration that works on 100 rows may lock on 10M
19	5. Migrations are immutable once deployed — never edit a migration that has run in production
20
21	## Migration Safety Checklist
22
23	Before applying any migration:
24
25	- [ ] Migration has both UP and DOWN (or is explicitly marked irreversible)
26	- [ ] No full table locks on large tables (use concurrent operations)
27	- [ ] New columns have defaults or are nullable (never add NOT NULL without default)
28	- [ ] Indexes created concurrently (not inline with CREATE TABLE for existing tables)
29	- [ ] Data backfill is a separate migration from schema change
30	- [ ] Tested against a copy of production data
31	- [ ] Rollback plan documented
32
33	## PostgreSQL Patterns
34
35	### Adding a Column Safely
36
37	```sql
38	-- GOOD: Nullable column, no lock
39	ALTER TABLE users ADD COLUMN avatar_url TEXT;
40
41	-- GOOD: Column with default (Postgres 11+ is instant, no rewrite)
42	ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;
43
44	-- BAD: NOT NULL without default on existing table (requires full rewrite)
45	ALTER TABLE users ADD COLUMN role TEXT NOT NULL;
46	-- This locks the table and rewrites every row
47	```
48
49	### Adding an Index Without Downtime
50
51	```sql
52	-- BAD: Blocks writes on large tables
53	CREATE INDEX idx_users_email ON users (email);
54
55	-- GOOD: Non-blocking, allows concurrent writes
56	CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
57
58	-- Note: CONCURRENTLY cannot run inside a transaction block
59	-- Most migration tools need special handling for this
60	```
61
62	### Renaming a Column (Zero-Downtime)
63
64	Never rename directly in production. Use the expand-contract pattern:
65
66	```sql
67	-- Step 1: Add new column (migration 001)
68	ALTER TABLE users ADD COLUMN display_name TEXT;
69
70	-- Step 2: Backfill data (migration 002, data migration)
71	UPDATE users SET display_name = username WHERE display_name IS NULL;
72
73	-- Step 3: Update application code to read/write both columns
74	-- Deploy application changes
75
76	-- Step 4: Stop writing to old column, drop it (migration 003)
77	ALTER TABLE users DROP COLUMN username;
78	```
79
80	### Removing a Column Safely
81
82	```sql
83	-- Step 1: Remove all application references to the column
84	-- Step 2: Deploy application without the column reference
85	-- Step 3: Drop column in next migration
86	ALTER TABLE orders DROP COLUMN legacy_status;
87
88	-- For Django: use SeparateDatabaseAndState to remove from model
89	-- without generating DROP COLUMN (then drop in next migration)
90	```
91
92	### Large Data Migrations
93
94	```sql
95	-- BAD: Updates all rows in one transaction (locks table)
96	UPDATE users SET normalized_email = LOWER(email);
97
98	-- GOOD: Batch update with progress
99	DO $$
100	DECLARE
101	batch_size INT := 10000;
102	rows_updated INT;
103	BEGIN
104	LOOP
105	UPDATE users
106	SET normalized_email = LOWER(email)
107	WHERE id IN (
108	SELECT id FROM users
109	WHERE normalized_email IS NULL
110	LIMIT batch_size
111	FOR UPDATE SKIP LOCKED
112	);
113	GET DIAGNOSTICS rows_updated = ROW_COUNT;
114	RAISE NOTICE 'Updated % rows', rows_updated;
115	EXIT WHEN rows_updated = 0;
116	COMMIT;
117	END LOOP;
118	END $$;
119	```
120
121	## Prisma (TypeScript/Node.js)
122
123	### Workflow
124
125	```bash
126	# Create migration from schema changes
127	npx prisma migrate dev --name add_user_avatar
128
129	# Apply pending migrations in production
130	npx prisma migrate deploy
131
132	# Reset database (dev only)
133	npx prisma migrate reset
134
135	# Generate client after schema changes
136	npx prisma generate
137	```
138
139	### Schema Example
140
141	```prisma
142	model User {
143	id String @id @default(cuid())
144	email String @unique
145	name String?
146	avatarUrl String? @map("avatar_url")
147	createdAt DateTime @default(now()) @map("created_at")
148	updatedAt DateTime @updatedAt @map("updated_at")
149	orders Order[]
150
151	@@map("users")
152	@@index([email])
153	}
154	```
155
156	### Custom SQL Migration
157
158	For operations Prisma cannot express (concurrent indexes, data backfills):
159
160	```bash
161	# Create empty migration, then edit the SQL manually
162	npx prisma migrate dev --create-only --name add_email_index
163	```
164
165	```sql
166	-- migrations/20240115_add_email_index/migration.sql
167	-- Prisma cannot generate CONCURRENTLY, so we write it manually
168	CREATE INDEX CONCURRENTLY IF NOT EX

People who liked this also viewed

6 picks

Type

database-migrations

27stars4forks

View on GitHub

How to install database-migrations?

skateddu/claude-code-python-setup/database-migrations

$npx -y skills add skateddu/claude-code-python-setup --skill database-migrations

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Database Migration Patterns
2
3	Safe, reversible database schema changes for production systems.
4
5	## When to Activate
6
7	- Creating or altering database tables
8	- Adding/removing columns or indexes
9	- Running data migrations (backfill, transform)
10	- Planning zero-downtime schema changes
11	- Setting up migration tooling for a new project
12
13	## Core Principles
14
15	1. Every change is a migration — never alter production databases manually
16	2. Migrations are forward-only in production — rollbacks use new forward migrations
17	3. Schema and data migrations are separate — never mix DDL and DML in one migration
18	4. Test migrations against production-sized data — a migration that works on 100 rows may lock on 10M
19	5. Migrations are immutable once deployed — never edit a migration that has run in production
20
21	## Migration Safety Checklist
22
23	Before applying any migration:
24
25	- [ ] Migration has both UP and DOWN (or is explicitly marked irreversible)
26	- [ ] No full table locks on large tables (use concurrent operations)
27	- [ ] New columns have defaults or are nullable (never add NOT NULL without default)
28	- [ ] Indexes created concurrently (not inline with CREATE TABLE for existing tables)
29	- [ ] Data backfill is a separate migration from schema change
30	- [ ] Tested against a copy of production data
31	- [ ] Rollback plan documented
32
33	## PostgreSQL Patterns
34
35	### Adding a Column Safely
36
37	```sql
38	-- GOOD: Nullable column, no lock
39	ALTER TABLE users ADD COLUMN avatar_url TEXT;
40
41	-- GOOD: Column with default (Postgres 11+ is instant, no rewrite)
42	ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;
43
44	-- BAD: NOT NULL without default on existing table (requires full rewrite)
45	ALTER TABLE users ADD COLUMN role TEXT NOT NULL;
46	-- This locks the table and rewrites every row
47	```
48
49	### Adding an Index Without Downtime
50
51	```sql
52	-- BAD: Blocks writes on large tables
53	CREATE INDEX idx_users_email ON users (email);
54
55	-- GOOD: Non-blocking, allows concurrent writes
56	CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
57
58	-- Note: CONCURRENTLY cannot run inside a transaction block
59	-- Most migration tools need special handling for this
60	```
61
62	### Renaming a Column (Zero-Downtime)
63
64	Never rename directly in production. Use the expand-contract pattern:
65
66	```sql
67	-- Step 1: Add new column (migration 001)
68	ALTER TABLE users ADD COLUMN display_name TEXT;
69
70	-- Step 2: Backfill data (migration 002, data migration)
71	UPDATE users SET display_name = username WHERE display_name IS NULL;
72
73	-- Step 3: Update application code to read/write both columns
74	-- Deploy application changes
75
76	-- Step 4: Stop writing to old column, drop it (migration 003)
77	ALTER TABLE users DROP COLUMN username;
78	```
79
80	### Removing a Column Safely
81
82	```sql
83	-- Step 1: Remove all application references to the column
84	-- Step 2: Deploy application without the column reference
85	-- Step 3: Drop column in next migration
86	ALTER TABLE orders DROP COLUMN legacy_status;
87
88	-- For Django: use SeparateDatabaseAndState to remove from model
89	-- without generating DROP COLUMN (then drop in next migration)
90	```
91
92	### Large Data Migrations
93
94	```sql
95	-- BAD: Updates all rows in one transaction (locks table)
96	UPDATE users SET normalized_email = LOWER(email);
97
98	-- GOOD: Batch update with progress
99	DO $$
100	DECLARE
101	batch_size INT := 10000;
102	rows_updated INT;
103	BEGIN
104	LOOP
105	UPDATE users
106	SET normalized_email = LOWER(email)
107	WHERE id IN (
108	SELECT id FROM users
109	WHERE normalized_email IS NULL
110	LIMIT batch_size
111	FOR UPDATE SKIP LOCKED
112	);
113	GET DIAGNOSTICS rows_updated = ROW_COUNT;
114	RAISE NOTICE 'Updated % rows', rows_updated;
115	EXIT WHEN rows_updated = 0;
116	COMMIT;
117	END LOOP;
118	END $$;
119	```
120
121	## Prisma (TypeScript/Node.js)
122
123	### Workflow
124
125	```bash
126	# Create migration from schema changes
127	npx prisma migrate dev --name add_user_avatar
128
129	# Apply pending migrations in production
130	npx prisma migrate deploy
131
132	# Reset database (dev only)
133	npx prisma migrate reset
134
135	# Generate client after schema changes
136	npx prisma generate
137	```
138
139	### Schema Example
140
141	```prisma
142	model User {
143	id String @id @default(cuid())
144	email String @unique
145	name String?
146	avatarUrl String? @map("avatar_url")
147	createdAt DateTime @default(now()) @map("created_at")
148	updatedAt DateTime @updatedAt @map("updated_at")
149	orders Order[]
150
151	@@map("users")
152	@@index([email])
153	}
154	```
155
156	### Custom SQL Migration
157
158	For operations Prisma cannot express (concurrent indexes, data backfills):
159
160	```bash
161	# Create empty migration, then edit the SQL manually
162	npx prisma migrate dev --create-only --name add_email_index
163	```
164
165	```sql
166	-- migrations/20240115_add_email_index/migration.sql
167	-- Prisma cannot generate CONCURRENTLY, so we write it manually
168	CREATE INDEX CONCURRENTLY IF NOT EX

People who liked this also viewed

6 picks

Type