Migrate SQLite to MariaDB
Certified — live-tested pathThe converter maps 8 SQLite column types to MariaDB. One of them changes kind on the way (NUMERIC becomes double); 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
SQLite vs MariaDB
SQLite
the embedded database that ships inside everything.
MariaDB
the community-driven MySQL fork.
SQLite to MariaDB data type mapping
Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.
| SQLite | MariaDB |
|---|---|
| INTEGER | bigint |
| REAL | double |
| TEXT | LONGTEXT |
| BLOB | LONGBLOB |
| NUMERIC | double |
| BOOLEAN | tinyint(1) |
| DATETIME | datetime |
| VARCHAR(255) | varchar(255) |
Example: SQLite to MariaDB schema conversion
SQLite
CREATE TABLE orders ( id INTEGER PRIMARY KEY AUTOINCREMENT, customer_id INTEGER NOT NULL, total NUMERIC NOT NULL, is_gift BOOLEAN NOT NULL DEFAULT 0, notes TEXT, placed_at DATETIME NOT NULL );
MariaDB
CREATE TABLE IF NOT EXISTS `orders` (
`id` bigint NOT NULL AUTO_INCREMENT,
`customer_id` bigint NOT NULL,
`total` double NOT NULL,
`is_gift` tinyint(1) NOT NULL DEFAULT 0,
`notes` LONGTEXT,
`placed_at` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;SQLite dates can be text, numbers or both
SQLite has no date type, and its date functions accept three representations: ISO-8601 text, a Julian day number stored as REAL, and Unix seconds stored as INTEGER. A single DATETIME column can contain all three if different code paths wrote it. MariaDB's datetime accepts only the first.
SELECT typeof(created_at), count(*) FROM t GROUP BY 1 shows which you have. Numeric rows can be converted in SQLite before the move, with datetime(created_at, 'unixepoch') for Unix seconds, so every row reaches MariaDB as text it can parse.
MariaDB is strict where SQLite was not
Since 10.2.4, MariaDB runs in strict mode by default, so a value that does not fit its column is an error rather than a warning. SQLite never enforced VARCHAR(255) or INTEGER, and a database that has been written to for years usually holds a few rows that break both. DBShifts flags mixed-type columns during analysis.
Over-length strings are the common case. SELECT max(length(col)) FROM t on each VARCHAR column shows which declarations the data has outgrown, and widening them before the load is cheaper than finding them one failed batch at a time.
BOOLEAN was only ever a number
A SQLite BOOLEAN column has numeric affinity and no constraint, so it can hold 0 and 1, but also 2, 'true', 't' or 'yes', depending on what wrote it. It becomes tinyint(1) in MariaDB, which stores small numbers and nothing else.
Look before the load: SELECT DISTINCT is_active FROM t. Map the text spellings to 0 and 1 in SQLite first, and the column arrives meaning what everyone assumed it meant.
SQLite → MariaDB conversion notes
Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.
- INTEGER columns are 64-bit — DBShifts maps them to BIGINT so large IDs don't overflow 32-bit INT targets.
- Typeless and mixed-affinity columns (SQLite's dynamic typing) are inferred, stringified safely, and flagged.
- NUMERIC affinity values are read exactly, not through float round-trips.
- Literal 'NULL' defaults reported by MariaDB's information schema are cleaned instead of becoming DEFAULT 'NULL' strings.
- AUTO_INCREMENT reseed after bulk load, same as MySQL.
Frequently asked questions
Is SQLite to MariaDB migration production-ready in DBShifts?
Yes. SQLite to MariaDB is a certified pair: it passes the live certification suite on real SQLite and MariaDB servers, which checks schema fidelity, row counts, type-aware checksums and paging across batches.
What happens to SQLite types that MariaDB doesn't have?
Each gets the nearest MariaDB type the converter treats as safe: NUMERIC becomes double. Every such decision is in the migration plan before anything runs, and any mapping can be overridden per column.
Can I keep SQLite and MariaDB in sync after the initial migration?
Not continuously: SQLite keeps no change log to read. Incremental sync re-runs copy only the rows that changed, using a per-table sync key.
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 SQLite to MariaDB?
Set up in under two minutes. Validation and rollback included.
Start Free Migration