MariaDB to SQLite migration guide

IntoSQL reads a MariaDB dump and writes one SQLite script with the same tables, columns, keys, indexes, foreign keys, defaults, counters, sequences, comments and rows. It reads the dump itself, without a database server, and lists in the report every value SQLite can't hold as it is and every object it doesn't carry. The script loads on SQLite 3.31 and later.

Convert in the browser: MariaDB to SQLite opens the converter set up for this pair.

Dump for the best result#

Any dump from mariadb-dump, mysqldump, phpMyAdmin or Adminer from MariaDB 10.3 on works, as .sql or .sql.gz. These options keep more of your data:

mariadb-dump --default-character-set=utf8mb4 --hex-blob \
  --routines --triggers --events --single-transaction \
  shop | gzip > shop.sql.gz

• --default-character-set=utf8mb4 keeps emoji and other 4-byte characters. A dump made with utf8mb3 has already lost them, and the report says so (dump_lost_four_byte_chars).
• --hex-blob writes binary columns and VECTOR values as hexadecimal, which survives copying between systems.
• --routines --triggers --events add those objects to the dump, so the report can list them by name.

A dump made with --no-create-info still converts: the types are read from the values, and the report says the schema was missing (no_schema). MariaDB's JSON columns, which the dump writes as longtext with a json_valid check, are read as JSON again.

Convert#

In the browser, open MariaDB to SQLite or the converter on the home page, drop the dump and choose SQLite. It reads the file from your disk in pieces, twice, and writes the script as it goes, so the dump is never uploaded and its size isn't limited by memory. The desktop app does the same with the file on disk.

With the command line:

intosql inspect shop.sql.gz
intosql convert shop.sql.gz --to sqlite --output shop.sqlite.sql --report shop.report.json

inspect lists the tables, their rows and what won't be converted, without writing anything. convert writes the script and, with --report, the report as JSON.

Through the API, a dump converts like any other file: open a conversion with its name ending in .sql or .sql.gz and "output": {"dialect": "sqlite"}.

Load into SQLite#

sqlite3 -bail shop.sqlite < shop.sqlite.sql

The script creates each table with its keys, copies the rows and adds indexes, counters and triggers, all in one transaction with syncing off, so it runs at disk speed and a load that stops leaves no partial tables. Foreign keys are declared in each table and checked once the rows are in: the script ends with PRAGMA foreign_key_check, which lists any row that points to a missing row instead of failing the load. Turn foreign keys on in each connection that writes (PRAGMA foreign_keys = ON), as SQLite expects.

Or load it straight in#

intosql load and the desktop app load the dump into SQLite without a script, with the same tables, keys and indexes the script creates:

intosql load shop.sql.gz --into shop.db

It stops before writing anything when one of the dump's tables already exists. It loads in one transaction, and a file the load created is removed if it fails. Loading into SQLite is free. See Loading.

What isn't converted#

Views, triggers, stored procedures, functions and events are written in MariaDB's own language. They are listed in the report by kind and name, never dropped silently, so you can recreate them in SQLite.

Data types#

MariaDB to SQLite data types lists each type that changes in the script: what it becomes, why, and the report line.

Every other rule#

Generated from the engine, where each rule is decided.