Moving a mysqldump or pg_dump file into SQLite and D1

A dump file is a script written for the engine that made it, and SQLite is a different engine that happens to read most of the same words. Part of a MySQL or Postgres dump fails at once with a syntax error, which is the good outcome. The rest is accepted and loaded, and some of that rest means something else on the other side: a backslash that was an escape becomes a plain character, a hexadecimal number becomes an integer, a marker for missing data becomes a two-letter string. This guide walks through what each dump format contains, which parts SQLite and Cloudflare D1 refuse, which parts they swallow wrongly, and how to prove a load kept the rows. The table definitions and queries that need changing are a separate subject, covered in our guide to dialect differences between these engines, so this one stays on the file itself.

What D1 says about the file

D1 imports plain SQL text. The documented route is to save the statements in a .sql file and run wrangler d1 execute against the database with the --file option, and the page gives a limit of 5 GiB for such a file, after which the data has to be split (D1 import and export). The same page states plainly that nothing exported from MySQL or Postgres can be loaded as it comes, because the types and the syntax are not directly compatible. So a dump is raw material for a conversion, not a finished import file.

The page also lists what goes wrong in practice. A transaction wrapper in the file, meaning BEGIN TRANSACTION and COMMIT, makes the load fail with an error about opening a transaction inside another one, so both lines have to go. A table that is referenced before it exists breaks a foreign key, so parent tables must be created first, and PRAGMA defer_foreign_keys = true can postpone the check while data is loaded. A single statement that is too long gets refused with a "Statement too long" error, and the fix is to split one big insert into several smaller ones (D1 import and export). The limits page gives the ceiling for one statement as 100,000 bytes, which also applies to each statement inside a batch (D1 limits). Keep those four facts in mind, because the sections below keep running into them.

What a mysqldump file contains

According to the mysqldump manual, --opt is on unless switched off, and it stands for a group of switches including --add-locks, --disable-keys, --extended-insert and --set-charset (MySQL mysqldump). Each switch leaves a mark in the file, so a default dump has a recognisable shape:

• table locks written around each table's data, as LOCK TABLES and UNLOCK TABLES;
• a statement that switches keys off and on around the inserts;
• a SET NAMES line with the character set;
• one INSERT per table that carries many rows at once, each in its own brackets;
• table options after the closing bracket of every definition, such as the storage engine and the default character set.

The manual describes the first three of these directly. The rest of what follows was confirmed by feeding fragments to a current SQLite build for this guide, and nothing here is a statement from the manual unless it is linked.

SQLite refuses LOCK TABLES, UNLOCK TABLES and SET with a syntax error at the first word, and it refuses a table option such as the engine clause with a syntax error at the equals sign. A KEY line inside a table definition fails the same way, because SQLite wants indexes as separate statements after the table. All of these stop the load immediately, which means they cost a few minutes and cannot produce a wrong value.

Two things in the file are accepted and deserve a closer look. The first is the quoting of names. The SQLite keyword page says that a keyword in grave accents is an identifier, a mechanism it includes for compatibility with MySQL (SQLite keywords), so the backticks around table and column names are harmless. The second is the version-conditional comment. MySQL runs the code inside a comment that opens with a slash, an asterisk and an exclamation mark, optionally followed by a version number, whenever the server is new enough, and expects other servers to treat it as a plain comment (MySQL comments). SQLite does exactly that, since its parser handles comments as whitespace (SQLite comments). In my test, a line that wrapped a SET statement in such a comment loaded without a complaint and did nothing, which is the right result for session settings, and a conditional comment around a real statement, like the key-disabling one, is skipped just as quietly.

The backslash problem

This is the one that decides whether a dump loads correctly. Inside a MySQL string, a backslash begins an escape sequence: the manual lists sequences for the quote characters, newline, carriage return, tab, backspace, the NUL character, the backslash itself and a few more, and it says that a backslash before any other character is ignored, so that the character stands for itself (MySQL string literals). Two of them are special: the sequences for the percent sign and the underscore keep their backslash when they appear outside a pattern-matching context. The same page says a quote may also be doubled inside a string.

The SQLite expression documentation takes the opposite position. A string is in single quotes, a quote inside it is written twice, and C-style escapes with the backslash are not supported because they are not standard SQL (SQLite expressions). So the same bytes have two readings, and a dump written by mysqldump uses the first one. Two cases show how that plays out, both run for this guide:

The second row is the dangerous one, because nothing fails. The rows are all there, the counts match, and every multi-line text field has lost its line breaks. The repair has to decode each string by the MySQL rules and write it back by the SQLite rules: a doubled quote for a quote, and an actual newline character, or a call to the char function joined in with the concatenation operator, in place of the escape. Values that contain the percent and underscore sequences keep their backslash, per the manual, so they must not be decoded.

Postgres is different again, and mixing the two up is its own error. The COPY documentation covers backslash sequences for its data lines, and pg_dump writes ordinary strings with only the quote doubled, so a backslash in a plain Postgres string is just a backslash. Decoding those with the MySQL table would damage them.

Hex values and other quiet conversions

With the --hex-blob option mysqldump writes binary columns as hexadecimal, and the option exists in the manual's list (MySQL mysqldump). The notation is the 0x prefix. SQLite reads that prefix as a hexadecimal integer, not as bytes: the expression page says a hexadecimal integer literal is interpreted as a 64-bit integer, and that a blob literal starts with X and holds the digits inside single quotes (SQLite expressions). In a test with a text column, 0x4142 was stored as the text 16706, and with the X'4142' form the stored type was a blob. The same switch of notation is needed for every binary column, and no error appears if it is forgotten.

Booleans have the same flavour. A Postgres dump writes them as t and f, and SQLite has no boolean type. Loaded as text, neither value counts as true in a WHERE clause: in my test a filter on the column returned no rows at all, with both letters present. The conversion to 1 and 0 has to be made on purpose. Dates are the opposite case: they are best left exactly as dumped, because SQLite stores them as text anyway and re-formatting only adds risk.

What a pg_dump file contains

The pg_dump manual describes the default output as a plain-text SQL script, and says a script like this can reconstruct the database on another machine, and, with some modifications, even on another SQL product (PostgreSQL pg_dump). The modifications are the work. By default the script has SET statements at the top, ownership commands such as ALTER TABLE ... OWNER TO (the --no-owner option leaves them out), schema-qualified table names, constraints added after the data through ALTER TABLE ONLY ... ADD CONSTRAINT, calls such as SELECT pg_catalog.setval(...) to reposition sequences, and the data itself in COPY blocks.

I tested the pieces. The SET statements, the ALTER TABLE ONLY form and the setval call each stop SQLite with a syntax error, and a schema prefix such as public. fails with "no such table". These are loud. The constraint statements deserve attention for what they ask for: SQLite cannot add a key or a check to an existing table with ALTER TABLE, so they have to be folded into the table definitions, as the earlier guide on dialect differences explains.

COPY blocks

The block begins with COPY table (columns) FROM stdin;, then every row sits on its own line, and a line holding only a backslash and a period closes it. SQLite has no such statement, so the load stops. Turning the rows into INSERT statements is the real job, and the COPY text format has rules that the conversion must follow (PostgreSQL COPY):

• a tab character divides the columns, and a row with more or fewer columns than expected is an error;
• the null string defaults to a backslash followed by N, and it is matched before the backslashes are removed, so the real text \N is written with a doubled backslash;
• backslash sequences for backspace, form feed, newline, carriage return, tab and vertical tab are recognised, as are octal and hexadecimal forms, and any other escaped character stands for itself.

The null string carries the main trap. A converter that copies the field as a quoted string stores a two-letter text, not a missing value. In my test the row with the two-character text was not found by IS NULL, so a count of missing values was zero where the source had one. An empty field, on the other hand, is an empty string and not a missing value.

There is a way to avoid this class of problem, and the manual names it: --inserts and --column-inserts make pg_dump write INSERT commands instead of COPY, noting that this is mostly useful for loading into databases other than PostgreSQL, and --rows-per-insert sets how many rows go into each command (PostgreSQL pg_dump). That leaves the types and the escapes to handle but removes the COPY decoding, and the row limit gives you a way to keep statements short enough for D1. MySQL has the opposite switch: --skip-extended-insert writes one row per statement, and --complete-insert puts the column names in every statement (MySQL mysqldump).

Multi-row inserts and the D1 statement limit

SQLite accepts one INSERT with several rows after VALUES, and the insert documentation describes the form (SQLite INSERT). So the structure of a default mysqldump insert is fine. The size is the problem: that single line for a large table can be many megabytes, and the D1 limit for one statement is 100,000 bytes (D1 limits). The D1 page shows the cure, which is splitting one statement into several with the same column list (D1 import and export). The easiest place to split is at the source, with the dump options above, because splitting a statement afterwards means parsing quoted strings that may themselves contain ),(.

A check that can fail

Load the converted file into a scratch SQLite database first, then into D1, and compare against facts you wrote down from the source, not against the file you just produced. A small table makes the idea concrete. Say the source holds two people; the MySQL dump gives the first an apostrophe in the name, a two-line note and a flag of 1, and gives the second no note and a flag of 0. After conversion, the load should satisfy this query:

SELECT name, length(note) AS note_length, active
FROM people ORDER BY id;

Expected answer: the first row is O'Brien, 3, 1, and the second row is Ann, NULL, 0. A note stored with a backslash and a letter instead of a line break would give a length of 4, and a missing note stored as the text \N would give a length of 2 where the NULL should be. Add the row counts per table from the source, and one more query that counts rows whose text column is NULL. Then break it on purpose: leave one backslash escape in the converted file and make sure the length check turns red. A check that still passes after that edit measures nothing.

When this does not apply

This covers plain-text script dumps from mysqldump and pg_dump, loaded into SQLite or D1 through SQL files. Custom or directory formats from pg_restore, MySQL's tab-separated --tab output, CSV loaders and the .import command of the SQLite shell behave in other ways and stay out of scope. The syntax errors and silent conversions above were observed on one SQLite build with small fragments, not on a real production dump, and D1 may run another version, so a quirk can differ there; the table and constraint changes are in the other guide, and large objects, triggers, procedures and views are not covered at all. The MySQL and Postgres behaviour is the one the cited manuals describe for the versions they cover. Character sets deserve their own check, since the text in a dump has to arrive as the same characters, and this guide did not examine them.

Converting a dump with a skill

For a dump that has to be converted rather than read, the sql-dump-to-sqlite skill takes dump text from MySQL or Postgres and returns SQL that SQLite, D1 or Turso can run. It decodes every string with the rules of whichever engine wrote the file, writes binary values as real blobs, turns booleans into 1 and 0, reads COPY blocks including the null marker, removes locks, session settings and engine clauses, orders tables to satisfy foreign keys, and cuts long inserts into smaller ones. If a value has no exact SQLite equivalent without a design decision, or the pasted text is cut off partway through a row, it answers with a one-line refusal that names the place and does not guess.