Migrate MariaDB to SQLite
Certified — live-tested pathThe converter maps 20 MariaDB column types to SQLite. 9 of them change kind on the way (TINYINT(1) becomes INTEGER, DATE becomes TEXT, DATETIME becomes TEXT, and more); the table below lists every one. DBShifts converts the schema, copies the rows in parallel batches and checks the result with row counts and checksums.
Free tier · no credit card
MariaDB vs SQLite
MariaDB
the community-driven MySQL fork.
SQLite
the embedded database that ships inside everything.
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 |
|---|---|
| INT | INTEGER |
| BIGINT | INTEGER |
| SMALLINT | INTEGER |
| TINYINT(1) | INTEGER |
| DECIMAL(10,2) | NUMERIC |
| FLOAT | REAL |
| DOUBLE | REAL |
| VARCHAR(255) | TEXT |
| TEXT | TEXT |
| LONGTEXT | TEXT |
| CHAR(10) | TEXT |
| DATE | TEXT |
| DATETIME | TEXT |
| TIMESTAMP | TEXT |
| TIME | TEXT |
| YEAR | INTEGER |
| BLOB | BLOB |
| JSON | TEXT |
| ENUM('a','b') | TEXT CHECK (<column> IN ('a','b')) |
| SET('a','b') | TEXT |
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 PRIMARY KEY AUTOINCREMENT,
"customer_id" INTEGER NOT NULL,
"status" TEXT NOT NULL DEFAULT 'new' CHECK ("status" IN ('new','paid','shipped')),
"total" NUMERIC NOT NULL,
"is_gift" INTEGER NOT NULL DEFAULT 0,
"notes" TEXT,
"metadata" TEXT,
"placed_at" TEXT NOT NULL
);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?
Yes. MariaDB to SQLite is a certified pair: it passes the live certification suite on real MariaDB and SQLite servers, which checks schema fidelity, row counts, type-aware checksums and paging across batches.
What happens to MariaDB types that SQLite doesn't have?
Each gets the nearest SQLite type the converter treats as safe: TINYINT(1) becomes INTEGER, DATE becomes TEXT, DATETIME becomes TEXT, TIMESTAMP becomes TEXT, TIME becomes TEXT. Every such decision is in the migration plan before anything runs, and any mapping can be overridden per column.
Can I keep MariaDB and SQLite in sync after the initial migration?
Yes, until you cut over. DBShifts reads the row-based binary log (binlog_format = ROW). With zero-downtime mode on, the log position is captured before the first row is copied, so writes made during the copy are replayed afterwards.
Which MariaDB data types need a second look when moving to SQLite?
The table above carries a note on 9 of them: TINYINT(1) (INTEGER), FLOAT (REAL), DOUBLE (REAL), TIMESTAMP (TEXT) and more. Analysis flags the same columns on your own schema before any data moves.
Go deeper
How We Live-Certified All 49 Migration Paths
The certification harness behind this page's claims.
Rehearse the Migration: Dry Runs, Quarantine, and Rollback
Rehearse, isolate failures, and keep the exit open.
Reference
Compared with other tools
Ready to move MariaDB to SQLite?
Set up in under two minutes. Validation and rollback included.
Start Free Migration