{"slug":"sql-dialect-to-sqlite","name":"Postgres or MySQL SQL to SQLite and D1","version":"1.0.0","updated_at":"2026-10-09T02:41:41.734Z","use_when":"Converts PostgreSQL or MySQL schema statements, migrations and queries into SQLite that Cloudflare D1 and Turso run, so the converted version behaves like the original and not only parses. Knows the conversions that run without an error and then go wrong - a key written as BIGSERIAL or INT AUTO_INCREMENT that SQLite stores as NULL, a custom enum type or an inline enum that stops checking anything, a default now() or gen_random_uuid() that fails on the first insert, a unique email that was case-insensitive in MySQL, ON UPDATE CURRENT_TIMESTAMP that needs a trigger, a foreign key added later with ALTER TABLE, a schema prefix, DISTINCT ON, ILIKE, double-colon casts, date_trunc, interval arithmetic, DATEDIFF, GROUP_CONCAT with SEPARATOR, MySQL division that keeps the fraction, ON DUPLICATE KEY UPDATE, JSON operators. Answers with the SQLite SQL only, and says CANNOT CONVERT when part of the input has no equivalent in SQLite (a procedural function, row level security, notifications, a stored procedure) instead of dropping it. Use when asked to port, convert or migrate Postgres or MySQL SQL to SQLite, D1 or Turso.","not_for":"Dumps of INSERT rows or COPY blocks (sql-dump-to-sqlite), query tuning, schema design or moving live data. Procedural functions, row level security and notifications get a CANNOT CONVERT line, not SQL. Some D1 rules are owner-measured on 2026-10-08.","languages":["any"],"tags":["sqlite","cloudflare-d1","postgres","mysql","sql-migration","turso"],"category":"code","category_url":"https://aiskills402.com/categories/code","keywords":["Postgres or MySQL SQL to SQLite","D1 or Turso","migrate Postgres"],"faq":[{"q":"Which SQL comes back, and in what shape?","a":"Only SQLite SQL: converted tables, indexes and triggers, or your query or write statement in the wrapper you ask for. A short line comment marks anything removed or changed in meaning. When a single statement cannot be expressed in SQLite, the whole reply is one line starting CANNOT CONVERT that names the statement and the reason, with no partial SQL beside it."},{"q":"How can I trust that the converted SQL behaves like the original?","a":"Each test pastes Postgres or MySQL SQL and then executes the reply in SQLite. A schema is built in an empty database, rows are inserted, rows that the original would refuse must be refused, and what is stored is compared. A query runs on small tables and the rows it returns must equal the rows the original would have returned on the same data. Many spellings of a correct conversion pass, such as a window function or a correlated subselect for DISTINCT ON, while a conversion that only parses fails, and so does one that keeps a word SQLite does not know. Refusal cases must contain no SQL at all."},{"q":"Which conversions fail without an error message?","a":"An auto-number key that SQLite stores as NULL, a custom enum type that checks nothing, a unique email that was case-insensitive in MySQL, a column that refreshed itself and now needs a trigger, a foreign key added later with ALTER TABLE, MySQL division that kept the fraction, and an upsert that replaces the row instead of adding to it."},{"q":"Does it help Claude Sonnet?","a":"One cent, because on our set Sonnet gained nothing. Twenty-four pieces of Postgres and MySQL went to both Claude models, guided by the file and bare, and every reply was run in SQLite. Sonnet got all 24 right unaided. Haiku moved from 21 to 23: bare, it let MySQL division lose its fraction and twice put a table next to its refusal. Our request was long and named the format and the refusal; a shorter request was not tested. Comments in your SQL stay in their own language."}],"examples":[{"lang":"en","model":"claude-sonnet-5-5","input_excerpt":"SQL to convert:\nCREATE TABLE users (\n  id BIGSERIAL PRIMARY KEY,\n  email VARCHAR(255) NOT NULL UNIQUE,\n  created_at TIMESTAMPTZ NOT NULL DEFAULT now()\n);","output_excerpt":"CREATE TABLE users (\n  id INTEGER PRIMARY KEY,\n  email TEXT NOT NULL UNIQUE,\n  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP\n);"},{"lang":"bg","model":"claude-sonnet-5-5","input_excerpt":"SQL to convert:\n-- Затваря месеца: маркира неплатените фактури като просрочени\nDELIMITER //\nCREATE PROCEDURE close_month()\nBEGIN\n  DECLARE done INT DEFAULT 0;\n  DECLARE inv INT;\n  DECLARE cur CURSOR FOR SELECT id FROM invoices WHERE paid = 0;\n  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;\n  OPEN cur;\n  read_loop: LOOP\n    FETCH cur INTO inv;\n    IF done THEN LEAVE read_loop; END IF;…","output_excerpt":"CANNOT CONVERT: процедурата close_month() използва курсор, променливи и цикъл (DECLARE, OPEN, FETCH, LOOP), което SQLite не може да изрази."}],"page_url":"https://aiskills402.com/skills/sql-dialect-to-sqlite","markdown_url":"https://aiskills402.com/skills/sql-dialect-to-sqlite.md","image_url":"https://cdn.aiskills402.com/og/skills/sql-dialect-to-sqlite/d3285b36.png","related_url":"https://api.aiskills402.com/v1/skills/sql-dialect-to-sqlite/related","purchases_count":null,"tested":{"date":"2026-10-09","strong":{"model":"claude-sonnet-5-5 (Claude Code alias \"sonnet\")","verdict":"Right on all 24, each reply run in SQLite: auto-number keys, an enum turned into a CHECK, a case-insensitive unique email, a pg_dump sequence with a foreign key added later, uuid and jsonb defaults, ILIKE and intervals, date_trunc and extract, MySQL division and DIV, GROUP_CONCAT with a separator, ON DUPLICATE KEY, DISTINCT ON, JSON operators, DATEDIFF and DATE_FORMAT, four refusals with no SQL beside them, and a planted note inside already valid SQLite ignored."},"weak":{"model":"claude-haiku-5-5 (Claude Code alias \"haiku\")","verdict":"Right on 23 of 24, each reply run in SQLite, but on the file that was already SQLite it ignored the planted note and then added a sentence about it after the SQL, so the migration would not run."},"note":"Twenty-four pieces of PostgreSQL and MySQL written by us, mostly in English and two with Bulgarian text: 14 conversions built around a silent change of meaning (auto-number keys, enums, case-insensitive uniques, pg_dump sequences and late foreign keys, uuid and jsonb defaults, ILIKE, intervals, date functions, integer division, GROUP_CONCAT, upserts, DISTINCT ON, JSON operators), 4 with no SQLite equivalent (dynamic PL/pgSQL, row-level security, pg_notify, a MySQL procedure) and 6 controls, one with a planted note. Each reply is executed in SQLite: a schema is built in an empty database, rows are inserted, rows the original would refuse must be refused, and a query must return the rows the original would return on the same data. The shared request states the format and the one-sentence refusal. No check was widened. One run per model and case.","baseline":{"date":"2026-10-09","rows":[{"label":"Conversions right when run (24 cases)","better":"higher","strong":{"with":{"n":24,"of":24},"without":{"n":24,"of":24}},"weak":{"with":{"n":23,"of":24},"without":{"n":21,"of":24}}}],"note":"Same request on both sides, a fence removed first. Sonnet without the skill was already right on all 24, so the skill adds no measured gain for it. Haiku without the skill missed three: it turned MySQL division into SQLite integer division, so 1001 / 2 lost its half, and twice it wrote the table next to the sentence saying a function or trigger could not be converted, while the request asked for no SQL in that case."},"report_url":null},"price_usd":"0.01","price_micro":10000,"size_bytes":14133,"sha256":"2240ae3fded3e64f8b5e77788a1872646b649d222f07939d384a1fca843ee639","outline":["The answer","When the answer is CANNOT CONVERT","Types and keys","Queries and write statements","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/sql-dialect-to-sqlite/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":"Postgres or MySQL SQL to SQLite and D1","seo_description":"Converts Postgres or MySQL schemas and queries to SQLite for D1 so they behave alike; with no equivalent it says CANNOT CONVERT. $0.01 once, in USDC.","versions":[{"version":"1.0.0","date":"2026-10-09","changelog":"# Changelog\n\n## 1.0.0 — 2026-10-08 (draft, not yet tested on a model)\n\nFirst draft, self-designed from the batch 4 table row (rank 12): the brief for this skill had not been written when the\nwriter started. Converts Postgres or MySQL schema statements, migrations, queries and write statements to SQLite (D1,\nTurso); the whole answer is CANNOT CONVERT when any statement has no equivalent (procedural functions and procedures,\nrow level security, notifications), never a half conversion. Fourteen silent-failure traps (BIGSERIAL and INT\nAUTO_INCREMENT keys, enum types, now() and uuid defaults, case-insensitive unique, ON UPDATE trigger, pg_dump sequences\nand late foreign keys, ILIKE and interval, date_trunc, MySQL division, GROUP_CONCAT, ON DUPLICATE KEY, DISTINCT ON,\nJSON operators, DATEDIFF), four refusals, six controls. Facts probed locally on 2026-10-08 (notes/facts-2026-10-08.md).\nPrice (30000 micro) is a placeholder until the baseline run; class B.\n\n## 1.0.1 — 2026-10-09 (finalised after the model test)\n\n- Measured on 24 cases, each reply run in SQLite: Sonnet 24 -> 24, Haiku 21 -> 23 (without -> with the skill). No check was widened.\n- Price: $0.01 (no Sonnet gain, Haiku +2).\n"}]}