How to Migrate a PHP App from SQLite to MySQL

Check you need to move first

SQLite serves a lot of single-server apps well, and moving costs you a separate service to run, patch and back up. Good reasons to switch are measured ones: lock waits that remain after you shorten write transactions, a second app server that must write the same data, or a host that gives you managed MySQL with failover. Our SQLite in production guide lists the checks to run before deciding.

If you do need to move, plan it as a short, rehearsed job rather than a live experiment. Run the whole thing against a copy first.

Why a straight dump doesn't import

The long-running Stack Overflow question "Quick easy way to migrate SQLite3 to MySQL?" is mostly answers explaining why sqlite3 app.db .dump | mysql fails. SQLite's dump contains statements MySQL rejects, such as BEGIN TRANSACTION, PRAGMA lines and double-quoted identifiers, and its loose typing lets a column declared INTEGER hold text. Sed scripts can patch the syntax, but they don't fix the types, and the result needs checking line by line. For an app you own, porting the schema deliberately and copying the data with a script is more reliable, and you can repeat it.

Step 1: port the schema

• INTEGER PRIMARY KEY AUTOINCREMENT becomes BIGINT AUTO_INCREMENT PRIMARY KEY.
• Any TEXT column that is unique or indexed (emails, token hashes, external IDs) must become VARCHAR(n); MySQL can't index unbounded TEXT without a prefix length.
• Timestamps stored as Y-m-d H:i:s UTC strings map cleanly to DATETIME.
• Create every table with ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci, so foreign keys work and emoji don't break inserts.
• Keep every index. Check foreign keys point at InnoDB tables, and decide whether you want ON DELETE CASCADE or deletes handled in code, as before.

Collation is the quiet change. SQLite compares text case-sensitively by default; utf8mb4_unicode_ci doesn't. A unique email column that held both Sam@x.com and sam@x.com in SQLite will reject the second row. Look for those duplicates before you copy.

Step 2: translate the SQLite-only SQL

Prepared statements that use plain SELECT, INSERT, UPDATE and DELETE usually work unchanged. Search your code for the few constructs that don't:

-- Ignore duplicates
INSERT OR IGNORE INTO t ...          →  INSERT IGNORE INTO t ...

-- Upsert
ON CONFLICT(key) DO UPDATE
  SET n = excluded.n                 →  ON DUPLICATE KEY UPDATE n = VALUES(n)

-- Current time (store UTC)
datetime('now')                      →  UTC_TIMESTAMP()
datetime('now', '-1 day')            →  UTC_TIMESTAMP() - INTERVAL 1 DAY

-- Write lock
BEGIN IMMEDIATE                      →  START TRANSACTION + SELECT ... FOR UPDATE

Also remove PRAGMA calls from the connection code, and connect with charset=utf8mb4 in the DSN and a strict sql_mode, so MySQL rejects bad data instead of silently truncating it.

Step 3: copy the data with PDO

$src = new PDO('sqlite:storage/app.db');
$dst = new PDO('mysql:host=127.0.0.1;dbname=app;charset=utf8mb4', $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$dst->exec('SET FOREIGN_KEY_CHECKS = 0');

foreach ($tables as $table) {                 // parents before children
    $rows = $src->query("SELECT * FROM $table");
    $dst->beginTransaction();
    $insert = null;
    foreach ($rows as $row) {
        $row = array_filter($row, 'is_string', ARRAY_FILTER_USE_KEY);
        $insert ??= $dst->prepare(sprintf(
            'INSERT INTO %s (%s) VALUES (%s)', $table,
            implode(', ', array_keys($row)),
            implode(', ', array_fill(0, count($row), '?'))
        ));
        $insert->execute(array_values($row));
    }
    $dst->commit();
}
$dst->exec('SET FOREIGN_KEY_CHECKS = 1');

Name columns explicitly rather than relying on order, as one developer who moved a Doctrine app found: SQLite's column order can differ from the order your code assumes. Copy IDs as they are so foreign keys still line up, then set each table's AUTO_INCREMENT above its highest ID. Run the copy from a backup of the SQLite file, not the live one.

Step 4: verify, switch and keep a way back

1. Compare row counts for every table, and spot-check a few accounts, payments and anything with money or credits.
2. Run your test suite and click through sign-in, a payment and an admin action against MySQL on a staging copy.
3. For the real move, stop writes (maintenance mode or stop PHP-FPM for a minute), take a final SQLite backup, run the copy, switch the connection setting, and start again.
4. Keep the final SQLite file untouched for a few days. If something is wrong, switch the setting back.
5. Change your backups: a file copy no longer works, so schedule mysqldump --single-transaction or your provider's snapshots, and test a restore.

Kanboard's forum thread on the same move shows the common snags in practice: dump syntax, character sets and getting the schema to match what the app expects.

Sources: Stack Overflow: Quick easy way to migrate SQLite3 to MySQL? · Kanboard forum: How to migrate SQLite to MySQL · Danny van Kooten: Migrating a Doctrine database from SQLite to MySQL · MySQL manual: INSERT ... ON DUPLICATE KEY UPDATE · SQLite: datatypes in SQLite