d1-drizzle-schema

Name: d1-drizzle-schema
Author: jezweb

937stars96forks

How to install d1-drizzle-schema?

jezweb/claude-skills/d1-drizzle-schema

$npx -y skills add jezweb/claude-skills --skill d1-drizzle-schema

Installs into the current project.

Summary

Generate Drizzle ORM schemas for Cloudflare D1 databases with correct D1-specific patterns. Produces schema files, migration commands, type exports, and DATABASE_SCHEMA.md documentation. Handles D1 quirks: foreign keys always enforced, no native BOOLEAN/DATETIME types, 100 bound

Files · 1

View on GitHub

SKILL.md

1	# D1 Drizzle Schema
2
3	Generate correct Drizzle ORM schemas for Cloudflare D1. D1 is SQLite-based but has important differences that cause subtle bugs if you use standard SQLite patterns. This skill produces schemas that work correctly with D1's constraints.
4
5	## Critical D1 Differences
6
7	\| Feature \| Standard SQLite \| D1 \|
8	\|---------\|-----------------\|-----\|
9	\| Foreign keys \| OFF by default \| Always ON (cannot disable) \|
10	\| Boolean type \| No \| No — use `integer({ mode: 'boolean' })` \|
11	\| Datetime type \| No \| No — use `integer({ mode: 'timestamp' })` \|
12	\| Max bound params \| ~999 \| 100 (affects bulk inserts) \|
13	\| JSON support \| Extension \| Always available (json_extract, ->, ->>) \|
14	\| Concurrency \| Multi-writer \| Single-threaded (one query at a time) \|
15
16	## Workflow
17
18	### Step 1: Describe the Data Model
19
20	Gather requirements: what tables, what relationships, what needs indexing. If working from an existing description, infer the schema directly.
21
22	### Step 2: Generate Drizzle Schema
23
24	Create schema files using D1-correct column patterns:
25
26	```typescript
27	import { sqliteTable, text, integer, real, index, uniqueIndex } from 'drizzle-orm/sqlite-core'
28
29	export const users = sqliteTable('users', {
30	// UUID primary key (preferred for D1)
31	id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
32
33	// Text fields
34	name: text('name').notNull(),
35	email: text('email').notNull(),
36
37	// Enum (stored as TEXT, validated at schema level)
38	role: text('role', { enum: ['admin', 'editor', 'viewer'] }).notNull().default('viewer'),
39
40	// Boolean (D1 has no BOOL — stored as INTEGER 0/1)
41	emailVerified: integer('email_verified', { mode: 'boolean' }).notNull().default(false),
42
43	// Timestamp (D1 has no DATETIME — stored as unix seconds)
44	createdAt: integer('created_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
45	updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
46
47	// Typed JSON (stored as TEXT, Drizzle auto-serialises)
48	preferences: text('preferences', { mode: 'json' }).$type<UserPreferences>(),
49
50	// Foreign key (always enforced in D1)
51	organisationId: text('organisation_id').references(() => organisations.id, { onDelete: 'cascade' }),
52	}, (table) => ({
53	emailIdx: uniqueIndex('users_email_idx').on(table.email),
54	orgIdx: index('users_org_idx').on(table.organisationId),
55	}))
56	```
57
58	See [references/column-patterns.md](references/column-patterns.md) for the full type reference.
59
60	### Step 3: Add Relations
61
62	Drizzle relations are query builder helpers (separate from FK constraints):
63
64	```typescript
65	import { relations } from 'drizzle-orm'
66
67	export const usersRelations = relations(users, ({ one, many }) => ({
68	organisation: one(organisations, {
69	fields: [users.organisationId],
70	references: [organisations.id],
71	}),
72	posts: many(posts),
73	}))
74	```
75
76	### Step 4: Export Types
77
78	```typescript
79	export type User = typeof users.$inferSelect
80	export type NewUser = typeof users.$inferInsert
81	```
82
83	### Step 5: Set Up Drizzle Config
84
85	Copy [assets/drizzle-config-template.ts](assets/drizzle-config-template.ts) to `drizzle.config.ts` and update the schema path.
86
87	### Step 6: Add Migration Scripts
88
89	Add to `package.json`:
90	```json
91	{
92	"db:generate": "drizzle-kit generate",
93	"db:migrate:local": "wrangler d1 migrations apply DB --local",
94	"db:migrate:remote": "wrangler d1 migrations apply DB --remote"
95	}
96	```
97
98	Always run on BOTH local AND remote before testing.
99
100	### Step 7: Generate DATABASE_SCHEMA.md
101
102	Document the schema for future sessions:
103	- Tables with columns, types, and constraints
104	- Relationships and foreign keys
105	- Indexes and their purpose
106	- Migration workflow
107
108	## Bulk Insert Pattern
109
110	D1 limits bound parameters to 100. Calculate batch size:
111
112	```typescript
113	const BATCH_SIZE = Math.floor(100 / COLUMNS_PER_ROW)
114	for (let i = 0; i < rows.length; i += BATCH_SIZE) {
115	await db.insert(table).values(rows.slice(i, i + BATCH_SIZE))
116	}
117	```
118
119	## D1 Runtime Usage
120
121	```typescript
122	import { drizzle } from 'drizzle-orm/d1'
123	import * as schema from './schema'
124
125	// In Worker fetch handler:
126	const db = drizzle(env.DB, { schema })
127
128	// Query patterns
129	const all = await db.select().from(schema.users).all() // Array<User>
130	const one = await db.select().from(schema.users).where(eq(schema.users.id, id)).get() // User \| undefined
131	const count = await db.select({ count: sql`count(*)` }).from(schema.users).get()
132	```
133
134	## Reference Files
135
136	\| When \| Read \|
137	\|------\|------\|
138	\| D1 vs SQLite, JSON queries, limits \| [references/d1-specifi

People who liked this also viewed

6 picks

Type

d1-drizzle-schema

937stars96forks

View on GitHub

How to install d1-drizzle-schema?

jezweb/claude-skills/d1-drizzle-schema

$npx -y skills add jezweb/claude-skills --skill d1-drizzle-schema

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# D1 Drizzle Schema
2
3	Generate correct Drizzle ORM schemas for Cloudflare D1. D1 is SQLite-based but has important differences that cause subtle bugs if you use standard SQLite patterns. This skill produces schemas that work correctly with D1's constraints.
4
5	## Critical D1 Differences
6
7	\| Feature \| Standard SQLite \| D1 \|
8	\|---------\|-----------------\|-----\|
9	\| Foreign keys \| OFF by default \| Always ON (cannot disable) \|
10	\| Boolean type \| No \| No — use `integer({ mode: 'boolean' })` \|
11	\| Datetime type \| No \| No — use `integer({ mode: 'timestamp' })` \|
12	\| Max bound params \| ~999 \| 100 (affects bulk inserts) \|
13	\| JSON support \| Extension \| Always available (json_extract, ->, ->>) \|
14	\| Concurrency \| Multi-writer \| Single-threaded (one query at a time) \|
15
16	## Workflow
17
18	### Step 1: Describe the Data Model
19
20	Gather requirements: what tables, what relationships, what needs indexing. If working from an existing description, infer the schema directly.
21
22	### Step 2: Generate Drizzle Schema
23
24	Create schema files using D1-correct column patterns:
25
26	```typescript
27	import { sqliteTable, text, integer, real, index, uniqueIndex } from 'drizzle-orm/sqlite-core'
28
29	export const users = sqliteTable('users', {
30	// UUID primary key (preferred for D1)
31	id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
32
33	// Text fields
34	name: text('name').notNull(),
35	email: text('email').notNull(),
36
37	// Enum (stored as TEXT, validated at schema level)
38	role: text('role', { enum: ['admin', 'editor', 'viewer'] }).notNull().default('viewer'),
39
40	// Boolean (D1 has no BOOL — stored as INTEGER 0/1)
41	emailVerified: integer('email_verified', { mode: 'boolean' }).notNull().default(false),
42
43	// Timestamp (D1 has no DATETIME — stored as unix seconds)
44	createdAt: integer('created_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
45	updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
46
47	// Typed JSON (stored as TEXT, Drizzle auto-serialises)
48	preferences: text('preferences', { mode: 'json' }).$type<UserPreferences>(),
49
50	// Foreign key (always enforced in D1)
51	organisationId: text('organisation_id').references(() => organisations.id, { onDelete: 'cascade' }),
52	}, (table) => ({
53	emailIdx: uniqueIndex('users_email_idx').on(table.email),
54	orgIdx: index('users_org_idx').on(table.organisationId),
55	}))
56	```
57
58	See [references/column-patterns.md](references/column-patterns.md) for the full type reference.
59
60	### Step 3: Add Relations
61
62	Drizzle relations are query builder helpers (separate from FK constraints):
63
64	```typescript
65	import { relations } from 'drizzle-orm'
66
67	export const usersRelations = relations(users, ({ one, many }) => ({
68	organisation: one(organisations, {
69	fields: [users.organisationId],
70	references: [organisations.id],
71	}),
72	posts: many(posts),
73	}))
74	```
75
76	### Step 4: Export Types
77
78	```typescript
79	export type User = typeof users.$inferSelect
80	export type NewUser = typeof users.$inferInsert
81	```
82
83	### Step 5: Set Up Drizzle Config
84
85	Copy [assets/drizzle-config-template.ts](assets/drizzle-config-template.ts) to `drizzle.config.ts` and update the schema path.
86
87	### Step 6: Add Migration Scripts
88
89	Add to `package.json`:
90	```json
91	{
92	"db:generate": "drizzle-kit generate",
93	"db:migrate:local": "wrangler d1 migrations apply DB --local",
94	"db:migrate:remote": "wrangler d1 migrations apply DB --remote"
95	}
96	```
97
98	Always run on BOTH local AND remote before testing.
99
100	### Step 7: Generate DATABASE_SCHEMA.md
101
102	Document the schema for future sessions:
103	- Tables with columns, types, and constraints
104	- Relationships and foreign keys
105	- Indexes and their purpose
106	- Migration workflow
107
108	## Bulk Insert Pattern
109
110	D1 limits bound parameters to 100. Calculate batch size:
111
112	```typescript
113	const BATCH_SIZE = Math.floor(100 / COLUMNS_PER_ROW)
114	for (let i = 0; i < rows.length; i += BATCH_SIZE) {
115	await db.insert(table).values(rows.slice(i, i + BATCH_SIZE))
116	}
117	```
118
119	## D1 Runtime Usage
120
121	```typescript
122	import { drizzle } from 'drizzle-orm/d1'
123	import * as schema from './schema'
124
125	// In Worker fetch handler:
126	const db = drizzle(env.DB, { schema })
127
128	// Query patterns
129	const all = await db.select().from(schema.users).all() // Array<User>
130	const one = await db.select().from(schema.users).where(eq(schema.users.id, id)).get() // User \| undefined
131	const count = await db.select({ count: sql`count(*)` }).from(schema.users).get()
132	```
133
134	## Reference Files
135
136	\| When \| Read \|
137	\|------\|------\|
138	\| D1 vs SQLite, JSON queries, limits \| [references/d1-specifi

People who liked this also viewed

6 picks

Type