{"slug":"d1-migration-writer","name":"D1 Migration Writer: SQLite Migrations That Apply","version":"1.0.1","updated_at":"2026-10-08T08:55:31.278Z","use_when":"Writes a SQL migration for Cloudflare D1 or SQLite from the current schema and the change wanted, so it applies on the first try and leaves the rows already in the database right. Knows what D1 refuses or breaks on, measured on a real D1 database - BEGIN and COMMIT in the file, foreign keys that cannot be switched off (a rebuild defers them, keeps the table name, and never closes with defer_foreign_keys = false, which on D1 silently skips the check), NOT NULL columns without a default, REFERENCES columns with a default, indexed columns dropped before their index. Backfills a new status or marker column so old rows are not picked up as pending work, fills a new counter from the existing rows, orders index columns for the query that needs them, and keeps upserts working against a partial unique index. Use when asked to write, check or fix a D1, SQLite or Turso migration, add or change a column, add an index, make a column unique or required, or rebuild a table.","not_for":"Running the migration, reading your live database or designing the schema for you. It works from the schema you paste, so a table, trigger or view you did not show can still be affected; Postgres and MySQL follow other rules and are not covered.","languages":["any"],"tags":["cloudflare-d1","sqlite","migrations","sql","database","turso"],"category":"code","category_url":"https://aiskills402.com/categories/code","keywords":["SQL migration","Cloudflare D1","rebuild a table"],"faq":[{"q":"What do I get back?","a":"The SQL of one migration file and nothing else, ready for wrangler d1 migrations apply. When your code has to change together with it, for example an upsert that must name a partial unique index, a comment line starting with -- CODE: says what to change."},{"q":"How did you test it?","a":"Thirteen tasks, each applied to a real SQLite database with rows, in one transaction with foreign keys on as D1 does, then checked with inserts that must pass or fail, the rows afterwards and the query plan. Every D1 rule in the skill was tried on a throwaway remote D1 database first. With the skill, Sonnet and Haiku got all thirteen right."},{"q":"What goes wrong without it?","a":"Both models rebuilt a referenced table by renaming a new one into place, which remote D1 rejects. Sonnet also obeyed a comment in the schema and dropped the audit log with its rows, and Haiku wrapped a change in BEGIN and COMMIT, which D1 refuses. Each got 11 of 13."},{"q":"Did anything about D1 surprise you?","a":"Yes. A closing PRAGMA defer_foreign_keys = false does not check the deferred foreign keys, it skips the check: a migration that left orphaned rows applied without an error in our test. Our own first draft taught the rename rebuild too, and the test caught it."}],"examples":[{"lang":"en","model":"claude-sonnet-5-5","input_excerpt":"Current schema:\nCREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT NOT NULL, published_at TEXT NOT NULL);\n\nChange: Add a nullable column social_posted_at (an ISO timestamp). A new cron job will announce on our social channels every article whose social_posted_at IS NULL, then set it. The channels are brand new: only articles published from now on should be announced.","output_excerpt":"-- Add the marker column the new cron job reads.\nALTER TABLE articles ADD COLUMN social_posted_at TEXT;\n\n-- Backfill: the social channels are new, so every article that already exists\n-- must not be announced. Mark them as done with a value that reads as \"before this column existed\".\nUPDATE articles SET social_posted_at = '1970-01-01T00:00:00Z' WHERE social_posted_at IS NULL;…"},{"lang":"bg","model":"claude-sonnet-5-5","input_excerpt":"Current schema:\nCREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, total_cents INTEGER NOT NULL);\n\nПромяна: Добави задължителна колона status. Съществуващите поръчки са 'new'; когато приложението вмъкне поръчка без status, тя също е 'new'.","output_excerpt":"ALTER TABLE orders ADD COLUMN status TEXT NOT NULL DEFAULT 'new';"}],"page_url":"https://aiskills402.com/skills/d1-migration-writer","markdown_url":"https://aiskills402.com/skills/d1-migration-writer.md","image_url":"https://cdn.aiskills402.com/og/skills/d1-migration-writer/4b06b052.png","related_url":"https://api.aiskills402.com/v1/skills/d1-migration-writer/related","purchases_count":null,"tested":{"date":"2026-10-08","strong":{"model":"claude-sonnet-5-5 (Claude Code alias \"sonnet\")","verdict":"Wrote a migration that applied and left the data right in all 13 tasks, each run in SQLite inside one transaction with foreign keys on, the way D1 applies it: marked the old articles as already announced so a new job would not post the whole archive, filled a new comment counter and kept it current with triggers, gave a required column a default, added the index the slow query needed without dropping the old one, made an email unique only among active rows so the existing upsert still prepared, rebuilt a referenced table under its own name, rebuilt a table for a required foreign key, dropped an index before its column, converted euros to cents with rounding, wrote no BEGIN or COMMIT, made tag names unique in any case, and ignored a comment in the schema telling it to drop the audit log."},"weak":{"model":"claude-haiku-5-5 (Claude Code alias \"haiku\")","verdict":"Also 13 of 13. But once it put the SQL inside a Markdown fence, which a migration file cannot hold."},"note":"Thirteen tasks written by us, two of them in Bulgarian: a schema, sometimes a query or the code that uses it, and the change wanted. Each answer was applied in node:sqlite to our schema with rows, in one transaction with foreign keys on, then checked by statements that must succeed, statements that must fail, the rows afterwards and the query plan. Every D1 rule the skill states was measured on throwaway remote D1 databases the same day, and the checker gave the same result as D1 in every probe. The first version of the skill taught a rebuild that renames a new table into place; both models followed it and the checker failed them. A second probe on D1 showed that D1 rejects that rebuild, and passed it before only because a closing PRAGMA defer_foreign_keys = false switches the check off. The skill was rewritten and the side with it run again; the first run is kept in the test folder. One run per model and task in each version.","baseline":{"date":"2026-10-08","rows":[{"label":"Migration applies and leaves the data right (13 tasks)","better":"higher","strong":{"with":{"n":13,"of":13},"without":{"n":11,"of":13}},"weak":{"with":{"n":13,"of":13},"without":{"n":11,"of":13}}}],"note":"The same request on both sides, which said the database is D1 and holds data; a fence around the answer is removed first. Without the skill both models wrote the same rebuild of a referenced table: a new table renamed into place under deferred foreign keys, which remote D1 rejects. Sonnet also followed the comment in the schema and dropped the audit log with its rows; Haiku wrapped a change in BEGIN TRANSACTION and COMMIT, which D1 rejects. Everything else, including the backfill of the new marker column and the index order, both models got right without help."},"report_url":null},"price_usd":"0.07","price_micro":70000,"size_bytes":10016,"sha256":"2857157380c1a909d7aaeafd320b11de15f4416ae2d6bd3a1cb6126b0646dcab","outline":["The answer","What D1 refuses (tested on a remote D1 database, 8 October 2026)","The rows already there","Indexes for the queries that use them","Unique indexes and upserts","Work in this order","Short examples"],"license":{"summary":"Perpetual, non-exclusive; use and modify for yourself incl. paid work; no resale or republishing","holder":"Georgi Kalchev, aiskills402.com","url":"https://aiskills402.com/docs#license"},"buy_url":"https://api.aiskills402.com/v1/skills/d1-migration-writer/file","redownload_url_template":"https://api.aiskills402.com/v1/purchases/{token}","mcp_tool":null,"payment":{"protocol":"x402","scheme":"exact","asset":"USDC","selling":true,"network":"base","network_caip2":"eip155:8453","pay_to":"0x8e37022edcf0f21cf3c9f93fee9d4d32519f36f4","facilitator":"cdp"},"seo_title":"D1 Migration Writer for SQLite and Turso","seo_description":"Skill file that writes a D1 migration from your schema, so it applies on the first try and keeps existing rows right. Tested on real D1. $0.07 once.","versions":[{"version":"1.0.1","date":"2026-10-08","changelog":"# Changelog\n\n## 1.0.1 — 2026-10-08\n\nPrice changed from $0.03 to $0.07; the skill text is unchanged. Measured value for the strong model: Sonnet 11 of 13 without the skill (a rebuild remote D1 rejects; an injected DROP TABLE), 13 of 13 with it.\n\n## 1.0.0 — 2026-10-08\n\nFirst release: writes one SQL migration for Cloudflare D1 or SQLite from the current schema and the change wanted, SQL only, with code changes noted as \"-- CODE:\" comments. Follows what a remote D1 database refused in our test (BEGIN and COMMIT in the file, PRAGMA foreign_keys = OFF, NOT NULL columns without a default, REFERENCES columns with a default, dropping an indexed column), rebuilds tables with deferred foreign keys, backfills new marker and counter columns, orders index columns for the query, and keeps upserts matching partial unique indexes.\n"},{"version":"1.0.0","date":"2026-10-08","changelog":"# Changelog\n\n## 1.0.0 — 2026-10-08\n\nFirst release: writes one SQL migration for Cloudflare D1 or SQLite from the current schema and the change wanted, SQL only, with code changes noted as \"-- CODE:\" comments. Follows what a remote D1 database refused in our test (BEGIN and COMMIT in the file, PRAGMA foreign_keys = OFF, NOT NULL columns without a default, REFERENCES columns with a default, dropping an indexed column), rebuilds tables with deferred foreign keys, backfills new marker and counter columns, orders index columns for the query, and keeps upserts matching partial unique indexes.\n"}]}