From Postgres or MySQL to SQLite and D1: what breaks

Most SQL written for Postgres or MySQL gets through the SQLite parser without complaint, and that is exactly the danger. SQLite is lenient about type names and about words it does not recognise in a column definition, so a dump from another engine often creates every table and then quietly stores, checks or calculates something different from the original. The statements that fail loudly are the lucky ones. This guide lists the conversions that succeed and still go wrong, why each happens according to the engines' own documentation, and what to write instead for SQLite and Cloudflare D1, which runs SQLite underneath.

Why nothing complains: types are advice

Other engines attach the type to the column and enforce it. SQLite attaches a type to each stored value, and a column's declared type only sets a preference, called affinity. The SQLite documentation describes this as a more general dynamic type system, and notes that strict tables, which enforce types rigidly, exist only from version 3.37.0 onward (SQLite datatypes).

The affinity is chosen by looking for fragments in the declared name: a name containing the letters for integer gets integer affinity, one containing the letters for char or text gets text affinity, and anything unrecognised ends up numeric. The same page says that the length in VARCHAR(255) is ignored (SQLite datatypes). So a made-up type name is accepted, a length limit is not a limit, and a column typed as a custom enum accepts any string at all. Everything below is a consequence of that leniency.

Auto-generated keys that turn out empty

In Postgres, serial and bigserial are shorthand that creates a sequence and a default value (PostgreSQL numeric types). In MySQL the same job is done by AUTO_INCREMENT. Neither word means anything to SQLite as a generator.

SQLite fills a key automatically only when the column is an INTEGER PRIMARY KEY, which is an alias for the internal row id; if no value is given on insert, an unused integer is chosen (SQLite autoincrement). A column declared BIGINT PRIMARY KEY or INT PRIMARY KEY has integer affinity but is not that alias, and the usual consequence is a key that stays NULL for rows inserted without an id. This is general SQLite behaviour that follows from the rule above rather than a sentence in the cited page, so confirm it with one test insert.

The fix is to declare the column exactly as INTEGER PRIMARY KEY, with AUTOINCREMENT only if row ids must never be reused, which the same page says costs extra work and is usually unnecessary.

Enums that stop enforcing anything

Postgres has real enumerated types, a fixed and ordered set of allowed values (PostgreSQL enumerated types). MySQL has an inline enum list in the column definition. SQLite has neither. A column whose declared type is an unknown name gets numeric affinity and accepts any value, so a typo such as shiped is stored happily.

Rewrite the column as TEXT with a CHECK (status IN ('new', 'paid')) listing the same values, and delete the separate type statement. Then try inserting a value that does not belong; if that insert succeeds, the check is not in force.

Defaults that break at creation or at the first row

Writing now() or gen_random_uuid() as a column default stops the CREATE TABLE statement with a syntax error, because SQLite accepts there only a literal, a keyword such as CURRENT_TIMESTAMP, or an expression in brackets (checked for this guide on a current SQLite build). The same call wrapped in brackets is accepted and then fails with the first insert, with an unknown-function error. Write the CURRENT_TIMESTAMP keyword instead, or wrap a strftime call over 'now' in brackets, and for random identifiers build the value from randomblob and hex.

Dates need a decision, because SQLite has no date storage class. It stores dates as text in ISO 8601 form, as a Julian day number, or as Unix seconds (SQLite datatypes). Text in a fixed year-first layout is the choice that sorts and compares correctly as plain text, and timestamps with a zone should be normalised to UTC before they are stored. Date functions such as date_trunc, EXTRACT, DATEDIFF and interval arithmetic have no direct twin; they become date, strftime and julianday expressions with modifiers.

Timestamps and constraints added later

MySQL's ON UPDATE CURRENT_TIMESTAMP is a MySQL feature tied to its timestamp and datetime types (MySQL timestamp properties). In SQLite it is a syntax error. The replacement is a small trigger that runs after an update and sets the column for that row.

Foreign keys have a related problem. Database dumps often add them afterwards with an ALTER TABLE statement, but SQLite's ALTER TABLE has no way to attach a constraint to a table that already exists. Its documentation names new FOREIGN KEY, CHECK and NOT NULL constraints, a different datatype for a column, and a changed primary key among the edits that need the full rebuild procedure (SQLite ALTER TABLE). For a fresh migration the practical answer is to move the constraint into the child table's CREATE TABLE and create parents first. Two tables that reference each other cannot be ordered this way, and that case needs a human decision.

Adding a column has its own rules. The new column cannot be primary key or unique, a default of CURRENT_TIMESTAMP or any parenthesised expression is refused, and a NOT NULL column must come with some non-NULL default (SQLite ALTER TABLE). Dropping a column fails if it is indexed, part of a key, named in a constraint, a trigger or a view; drop the index first. What happens when a model attempts that rebuild on D1 is measured in a separate write-up on failed D1 rebuilds.

Arithmetic and text that quietly change

MySQL's slash operator gives a decimal result, so dividing three by five yields 0.60, while DIV is the operator that discards the fraction (MySQL arithmetic). SQLite performs integer arithmetic when both operands are integers, and integer division is truncated toward zero (SQLite expressions). A report ported from MySQL therefore returns zero where it used to return a fraction. Multiply one operand by 1.0 wherever the source had a bare slash, and leave a bare slash in place wherever the source wrote DIV. Postgres already truncates integer division, so nothing changes there.

Case handling is the second silent difference. MySQL's common collations ignore letter case when comparing text, so a unique email column refused an address repeated in different capitalisation. SQLite compares with a binary collation unless told otherwise, and offers NOCASE, which folds only the ASCII letters (SQLite datatypes). ILIKE should become LIKE, which according to the SQLite documentation understands upper and lower case only within ASCII, so a letter outside that range compares as different (SQLite expressions). With non-English data that gap matters.

Upserts, JSON and arrays

MySQL's ON DUPLICATE KEY UPDATE and INSERT IGNORE map to ON CONFLICT ... DO UPDATE and INSERT OR IGNORE. In the update, the incoming row is reached through the excluded prefix, and the conflict target has to name a uniqueness constraint that actually exists (SQLite upsert). A target that matches no unique index fails on every call, not only on conflict.

Postgres arrays have no SQLite type. The usual approach is a JSON array in a text column, queried with the json_each table function, and every operator that touched the array has to be rewritten with it. JSON containment operators and the Postgres JSON builder functions likewise have no equivalent; the SQLite JSON functions replace them one by one.

Limits that only D1 adds

D1 documents several hard limits that ordinary SQLite does not impose. A query may have at most 100 bound parameters, a LIKE or GLOB pattern may be at most 50 bytes, and a table may have at most 100 columns (Cloudflare D1 limits). Fifty bytes is only half as many Cyrillic letters, because each takes two bytes, which is arithmetic rather than something the page states.

Code that builds a long IN (...) list from user data will pass tests with a handful of rows and fail once the list grows, so pass such a list as one JSON parameter and expand it with json_each. Searching a phrase inside text with a long pattern is better done with instr.

A check that can fail

Converted SQL should be run, not only read. Create the tables in a scratch SQLite database, then try four things: insert a row without an id and confirm it gets one; insert a value that the source database would have refused; insert a unique value again in other capitalisation; and divide two integers whose result has a fraction. If all four behave like the old engine's answer, the conversion kept the meaning. If the first returns a NULL key or the second succeeds, an earlier section applies.

When this does not apply

Out of scope are data export, character sets and performance tuning; what is covered is schema statements and common queries. It does not cover stored procedures, row-level security, notifications and other features that SQLite lacks entirely; those cannot be translated and need a redesign. The MySQL and Postgres behaviour described is for the versions the cited pages document, and MariaDB or older releases can differ. SQLite versions differ too: D1 and Turso may be on a different version than the one a page describes, so features added recently, such as some join types, should be checked on the target. The empty key from a BIGINT PRIMARY KEY and the Cyrillic letter count are general knowledge and arithmetic, not statements from the cited pages. Apart from small tests of the default and empty-key behaviour on one SQLite build, this guide ran no experiments; it restates documentation.

Converting with a skill

For a quick conversion pass, the sql-dialect-to-sqlite skill takes Postgres or MySQL table definitions, migrations or queries and returns SQLite that D1 and Turso run. It rewrites generated keys, enums, defaults, dates, division, upserts and JSON functions, and if a piece of the input cannot be expressed in SQLite it gives a plain refusal that names that statement instead of dropping it.