sqlite
mariadb

Migrate SQLite to MariaDB

Certified — live-tested path

The 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.

Start SQLite → MariaDB Migration

Free tier · no credit card

SQLite vs MariaDB

sqlite

SQLite

the embedded database that ships inside everything.

mariadb

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.

SQLiteMariaDB
INTEGERbigint
REALdouble
TEXTLONGTEXT
BLOBLONGBLOB
NUMERICdouble
BOOLEANtinyint(1)
DATETIMEdatetime
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

Ready to move SQLite to MariaDB?

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

Start Free Migration