jezweb/claude-skills / archive/plugins/cloudflare/skills/d1-drizzle-schema
d1-drizzle-schema
VerifiedGenerate 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 parameter limit, JSON stored as TEXT. Use when creating a new database, adding tables, or scaffolding a D1 data layer.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 5.3 KB |
| assets/drizzle-config-template.ts | script | 388 B |
| assets/schema-template.ts | script | 2.3 KB |
| references/column-patterns.md | markdown | 5.4 KB |
| references/d1-specifics.md | markdown | 4.5 KB |
references/d1-specifics.md
View on GitHub# D1 Specifics
Reference for Cloudflare D1 behaviour that differs from standard SQLite. Load this when troubleshooting D1 issues or when you need to write raw SQL against D1.
## D1 vs Standard SQLite
| Feature | Standard SQLite | D1 |
|---------|-----------------|-----|
| Foreign keys default | OFF | **ON (always enforced)** |
| `PRAGMA foreign_keys` | Can toggle freely | **Blocked** — always on |
| `PRAGMA defer_foreign_keys` | Available | Available (for migration ordering) |
| Other PRAGMAs | Full access | Restricted (table_list, table_info, table_xinfo only) |
| Bound parameters per query | ~999 | **100** |
| Max database size | Filesystem | 10 GB (paid) / 500 MB (free) |
| Max columns per table | Unlimited | **100** |
| Max string/BLOB size | Unlimited | **2 MB** |
| Max SQL statement length | Unlimited | **100 KB** |
| Max queries per Worker invocation | N/A | 1000 (paid) / 50 (free) |
| Max concurrent D1 connections | N/A | **6 per Worker** |
| Max query duration | N/A | **30 seconds** |
| Concurrency model | Multi-writer | **Single-threaded** (Durable Object) |
| BigInt support | Yes | **No** (JS 52-bit limit) |
| Virtual tables (FTS5) | Yes | Yes, but **blocks `wrangler d1 export`** |
## JSON in D1
JSON functions are always available (no extension loading needed).
### Storage
JSON is stored as `TEXT` columns. Drizzle handles serialisation with `{ mode: 'json' }`.
### Extraction Functions
| Function | Returns | Example |
|----------|---------|---------|
| `json_extract(col, '$.path')` | SQL type matching JSON type | `json_extract(data, '$.name')` → `"Alice"` |
| `col -> '$.path'` | JSON representation | `data -> '$.score'` → `42` (as JSON) |
| `col ->> '$.path'` | SQL TEXT | `data ->> '$.score'` → `"42"` (as TEXT) |
| `json_each(value)` | Rows (top-level array) | Expand array into rows |
| `json_tree(value)` | Rows (full nested) | Expand entire structure |
### Type Coercion
| JSON type | D1 type |
|-----------|---------|
| `null` | `NULL` |
| number (integer) | `INTEGER` |
| number (decimal) | `REAL` |
| boolean | `INTEGER` (1 = true, 0 = false) |
| string | `TEXT` |
| object/array | `TEXT` |
### Generated Columns from JSON
D1 supports generated columns — extract JSON fields as indexable columns:
```sql
CREATE TABLE sensor_data (
raw_data TEXT,
location AS (json_extract(raw_data, '$.location')) STORED
);
CREATE INDEX idx_location ON sensor_data(location);
```
### JSON Gotcha
`json_extract()` throws `malformed JSON` (error 9015) if the column contains non-JSON text. Guard with `json_valid()`:
```sql
SELECT * FROM events
WHERE json_valid(metadata) AND json_extract(metadata, '$.country') = 'AU'
```
## Query Result Formats
### `.all<T>()` — Array of row objects
```typescript
const { results, success, meta } = await env.DB
.prepare("SELECT * FROM users WHERE role = ?")
.bind("admin")
.all<UserRow>()
// results: UserRow[]
// meta: { duration, rows_read, rows_written, last_row_id, changes, size_after }
```
### `.first()` — Single row or null
```typescript
const row = await env.DB.prepare("SELECT * FROM users WHERE id = ?").bind(id).first()
// row: Record<string, unknown> | null
// With column name — returns scalar:
const count = await env.DB.prepare("SELECT COUNT(*) as count FROM users").first('count')
// count: number | null
```
### `.run()` — Execute mutation (no rows returned)
```typescript
const result = await env.DB
.prepare("INSERT INTO users (id, name) VALUES (?, ?)")
.bind(id, name)
.run()
// result: { success, meta: { changes, last_row_id, ... } }
```
### `.raw()` — Array of arrays (no column names)
```typescript
const rows = await env.DB.prepare("SELECT id, name FROM users").raw()
// rows: [["abc", "Alice"], ["def", "Bob"]]
// With column names:
const rows = await env.DB.prepare("SELECT id, name FROM users").raw({ columnNames: true })
// rows: [["id", "name"], ["abc", "Alice"], ["def", "Bob"]]
```
### `.batch()` — Multiple statements in one transaction
```typescript
const [r1, r2] = await env.DB.batch([
env.DB.prepare("INSERT INTO users VALUES (?, ?)").bind(id1, name1),
env.DB.prepare("INSERT INTO users VALUES (?, ?)").bind(id2, name2),
])
// Returns: D1Result[] — one per statement, all in single transaction
```
## Batch Insert Calculation
D1's 100 parameter limit means: `max_rows_per_insert = Math.floor(100 / columns_per_row)`
| Columns | Max rows per INSERT |
|---------|-------------------|
| 5 | 20 |
| 10 | 10 |
| 15 | 6 |
| 20 | 5 |
Symptoms of hitting the limit: silent failure, partial data, or cryptic "Failed to insert" error.
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |