Migrate MySQL to SQLite
Certified — live-tested pathThe converter maps 20 MySQL 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
Not ready to connect a database? Paste your CREATE TABLE statements and see the SQLite schema now
MySQL vs SQLite
MySQL
the world's most deployed open-source relational database.
SQLite
the embedded database that ships inside everything.
MySQL to SQLite data type mapping
Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.
| MySQL | 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: MySQL to SQLite schema conversion
MySQL
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
);SQLite does not enforce column types
SQLite treats a declared type as a preference. A column declared INTEGER will store the string 'abc' if an application asks it to. The table above maps MySQL's types onto SQLite's five storage classes, which is as close as SQLite goes, but after the move nothing stops a bad write landing in a numeric column.
STRICT tables, available from SQLite 3.37, reject values that do not match the declared type. The converter emits ordinary tables, so add STRICT yourself if the application relied on MySQL's strict mode to catch its mistakes.
DECIMAL becomes NUMERIC affinity
DECIMAL(10,2) becomes NUMERIC. SQLite stores a NUMERIC value as an integer when it is one and as an 8-byte float otherwise, which round-trips any value of up to 15 significant digits but is still binary floating point: SUM over many prices can drift by a fraction of a cent. Values too wide for that stay as exact text on purpose.
If the database holds money and SQLite will do arithmetic on it, store cents in an INTEGER column instead. It is the one representation SQLite keeps exact under every operation.
Dates and times become ISO-8601 text
SQLite has no date type. DATE, DATETIME, TIMESTAMP and TIME all become TEXT holding ISO-8601 strings, which sort correctly as text and work with date(), time() and strftime(). Comparisons between them are string comparisons, so every value in a column needs the same format and the same zone.
MySQL's TIMESTAMP is the one to watch: it is stored as UTC and converted to the session's time zone on every read, so the text written to SQLite records what the copying session saw. Check one row whose instant you know after the load.
MySQL → SQLite conversion notes
Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.
- TINYINT(1) is treated as a boolean; plain TINYINT stays numeric — converting both blindly corrupts ratings and counters.
- UNSIGNED integers are widened on targets without unsigned types, so values above the signed range survive.
- ENUM and SET columns are converted with their value lists preserved (native enums, CHECK constraints, or arrays depending on the target).
- TIME columns are durations (-838h..838h) at the driver level — DBShifts converts them per target instead of failing the batch.
- Zero dates (0000-00-00) are normalized deterministically and reported, never silently dropped.
- 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 MySQL to SQLite migration production-ready in DBShifts?
Yes. MySQL to SQLite is a certified pair: it passes the live certification suite on real MySQL and SQLite servers, which checks schema fidelity, row counts, type-aware checksums and paging across batches.
What happens to MySQL 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 MySQL 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 MySQL 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.
Zero-Downtime Migration with Change Data Capture
Snapshot, stream the changes made meanwhile, and stay in sync until cutover.
Reference
- Getting started
- Running a migration, step by step
- Full type-mapping reference
- All supported pairs and their tiers
- Known limitations
- Understanding data-loss warnings
- Going live with cutover
- Watching for drift after cutover
- Run this migration from CI/CD
- Rewrite MySQL queries for SQLite
- Convert your MySQL CREATE TABLE statements to SQLite
Compared with other tools
Errors you may hit
Ready to move MySQL to SQLite?
Set up in under two minutes. Validation and rollback included.
Start Free Migration