Migrate MariaDB to SQLite

Beta — live-tested path

Migrating from MariaDB (the community-driven MySQL fork) to SQLite (the embedded database that ships inside everything) means every table, column type, key, index and row has to survive two engines' different opinions about data. This is a cross-model migration — rows and relations are preserved as structured documents — and the conversion rules are explicit, not guessed. DBShifts converts the schema with deterministic rules, transfers data in parallel batches, and validates the result with per-table row counts and type-aware checksums.

Start MariaDB → SQLite Migration

Free tier · no credit card

MariaDB vs SQLite

MariaDB

the community-driven MySQL fork.

SQLite

the embedded database that ships inside everything.

How the migration works

Connect

Point DBShifts at your source and target. Credentials stay encrypted; SSH tunnels supported for private databases.

Analyze

Automatic schema introspection produces a migration plan: what converts automatically, what migrates with warnings, what needs human review.

Migrate

Schema is created on the target, data transfers in parallel batches with constraints deferred, indexes rebuilt after load.

Validate

Per-table row counts, type-aware checksums on both sides, FK integrity — plus a fidelity report listing anything lossy.

MariaDB to SQLite data type mapping

Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.

MariaDB	SQLite	Note
INT	INTEGER
BIGINT	INTEGER
SMALLINT	INTEGER
TINYINT(1)	INTEGER	MySQL and MariaDB store booleans here. Plain TINYINT is numeric — the width is the only thing distinguishing them.
DECIMAL(10,2)	NUMERIC
FLOAT	REAL	Binary floating point, same rounding caveat as DOUBLE.
DOUBLE	REAL	Binary floating point. Money stored in a DOUBLE will round; use DECIMAL end to end.
VARCHAR(255)	TEXT
TEXT	TEXT
LONGTEXT	TEXT
CHAR(10)	TEXT
DATE	TEXT
DATETIME	TEXT
TIMESTAMP	TEXT	Timezone behaviour differs per engine — a naive target shifts the instant rather than failing.
TIME	TEXT	MySQL TIME is a duration spanning -838:59:59 to 838:59:59, not a clock time. Targets with a 24-hour TIME cannot hold the full range.
YEAR	INTEGER	MySQL-only type. Widened on every other engine.
BLOB	BLOB
JSON	TEXT	JSON is stored and indexed very differently per engine; queries against it will need rewriting.
ENUM('a','b')	TEXT CHECK (<column> IN ('a','b'))	Value list is kept as a CHECK constraint where the target supports one.
SET('a','b')	TEXT	No equivalent outside MySQL and MariaDB. The value list is preserved but the constraint is not.

Example: MariaDB to SQLite schema conversion

MariaDB

CREATE TABLE orders (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  customer_id   INT NOT NULL,
  status        ENUM('new','paid','shipped') NOT NULL DEFAULT 'new',
  total         DECIMAL(10,2) NOT NULL,
  is_gift       TINYINT(1) NOT NULL DEFAULT 0,
  notes         TEXT,
  metadata      JSON,
  placed_at     DATETIME NOT NULL
);

SQLite

CREATE TABLE IF NOT EXISTS "orders" (
    "id" INTEGER,
    "customer_id" INTEGER NOT NULL,
    "status" TEXT CHECK ("status" IN ('a','b')),
    "total" NUMERIC NOT NULL,
    "is_gift" INTEGER,
    "notes" TEXT,
    "metadata" TEXT,
    "placed_at" TEXT NOT NULL,
    PRIMARY KEY ("id")
);

One file, one writer

SQLite allows many concurrent readers and one writer at a time. A MariaDB application that relies on row-level locking and concurrent writes will serialise on SQLite, and under load that appears as SQLITE_BUSY rather than as slowness. Write-ahead logging improves reader concurrency but does not change the single-writer rule.

That makes this migration a good fit for embedded, offline and test uses, and a poor fit for a multi-user write workload. It is worth being explicit about which of those is intended before evaluating the result.

What MariaDB enforces that SQLite will not

Foreign keys exist in SQLite but are off by default, and every connection must issue PRAGMA foreign_keys = ON to enable them. Code that assumes the database rejects orphans will find that it does not, on any connection that forgot the pragma.

Column types are advisory. An INTEGER column accepts text, a VARCHAR(10) accepts a thousand characters, and no error is raised. Length limits, unsigned ranges and ENUM value lists all stop being enforced, so validation that lived in the schema now has to live in the application.

ENUM is converted to TEXT with a CHECK constraint where possible, which does preserve the restriction. CHECK constraints are enforced by SQLite, so this is one place where the guarantee survives the move.

Dates need a convention

SQLite has no date or time type. DATETIME columns become TEXT, and the value has to be written in a format that sorts correctly, which in practice means ISO-8601 with a consistent timezone. Storing dates in a local format sorts wrongly and compares wrongly.

MariaDB's zero date, 0000-00-00, has no meaning in any format and needs converting to NULL or a real date before the move. It is legal in MariaDB, so it is genuinely present in older schemas.

MariaDB → SQLite conversion notes

Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.

MariaDB's JSON columns (LONGTEXT + json_valid CHECK) are detected and mapped to real JSON types on targets that have them.
Engine-specific CHECK constraints like json_valid() are filtered on targets that don't support them — data still migrates.
Display-width unsigned types (bigint(20) unsigned) are recognized and widened correctly.
High-precision decimals land in TEXT columns on purpose — SQLite's NUMERIC affinity would silently convert them to lossy REAL.
Unsigned BIGINT values beyond the signed 64-bit range are stored as exact text rather than corrupted floats.
Dates and times are stored as ISO-8601 text, the SQLite convention.

Frequently asked questions

Is MariaDB to SQLite migration production-ready in DBShifts?

MariaDB to SQLite is a live-verified beta pair: it passed the same certification suite as every other pair — a real migration with edge-case data validated by row counts and checksums — and ships with a post-migration fidelity report you can audit before cutover.

How does DBShifts verify the MariaDB to SQLite migration was correct?

Three layers: per-table row counts on both sides, an order-independent type-aware checksum of row data (canonicalized so engine representation differences don't false-alarm), and FK integrity checks on the target. Rows that fail to insert are quarantined with the exact error — never silently dropped — and can be fixed and retried from the UI.

What happens to MariaDB types that SQLite doesn't have?

They convert by deterministic rules with a recorded decision: a native equivalent where one exists, a portable fallback (TEXT/JSON/DECIMAL) where one doesn't. Every lossy conversion appears in the migration plan before you run and in the fidelity report after. You can override any mapping per column.

Can I keep MariaDB and SQLite in sync after the initial migration?

Yes. DBShifts supports incremental sync (UPSERT-based delta transfers on a sync key) and, for supported sources, real-time change data capture so the target stays current until you cut over.

Do I need to write any SQL or scripts for MariaDB to SQLite?

No. Connect both databases, review the generated migration plan (what's automatic, what migrates with warnings, what needs manual review — typically triggers and stored procedures), and run. Procedural code is transpiled best-effort and queued for human review rather than auto-applied.

What's the hardest part of moving MariaDB to SQLite?

The data model itself differs (sql → embedded), so rows and relations are restructured into documents with explicit type rules. DBShifts surfaces every inference and lossy conversion in the migration plan before you run, so nothing is guessed silently.

Which MariaDB data types change when moving to SQLite?

9 of the mapped types convert to something that cannot represent exactly the same range or constraint. The clearest cases are TINYINT(1) to INTEGER, FLOAT to REAL, DOUBLE to REAL. The full table above lists every mapping the converter performs, and analysis flags each one on your actual schema before any data moves.

Go deeper

How We Live-Certified All 49 Migration Paths

The certification harness behind this page's claims.

Why Row Counts Aren't Enough

How type-aware checksum validation works.

Related migration paths

MySQLSQLite MariaDBMySQL MariaDBMariaDB MariaDBPostgreSQL MariaDBSQL Server MariaDBOracle

Ready to move MariaDB to SQLite?

Set up in under two minutes. Validation and rollback included.

Start Free Migration