Moving Our Laravel App from SQLite to MariaDB

On October 7, 2026 we moved this website's database from SQLite to MariaDB. The site is a Laravel 13 application on PHP 8.5, and its database supports both the parts visitors use and our own internal operations. It was small, about a megabyte.

This is how the move went: why we made it, what had to change in the code first, how we checked that nothing was lost, and the two times the process stopped itself.

Why we moved off SQLite

SQLite was a sensible choice for this site, and we'd make it again for a lot of small Laravel applications. There's no database server to run, a backup is a file, and in WAL mode readers don't block the writer. The limit that matters is in the same document: there can only be one writer at a time. Every write waits for the one ahead of it.

For most of the site's life that didn't matter. Writes came from visitors filling in forms, a few times a week. Over 2026 the application picked up more work that writes on its own schedule, not just when a visitor does something, and more of that was planned. Nothing was failing and nothing was slow. We moved because we could see where things were heading, and a database migration is a lot calmer when it isn't happening during an incident.

Choosing MariaDB 11.8

We installed MariaDB 11.8, an LTS release, from our Linux distribution's own packages. It gets security updates through the same channel as everything else on the server, and there's no third-party repository to keep track of.

Two minimum versions mattered. Laravel's mariadb driver creates a native UUID column for $table->uuid(), which needs MariaDB 10.7 or later. We also wanted explicit_defaults_for_timestamp on by default, which it is from 10.10. More on that one below.

The rule we cared about more than the version number was using the same version everywhere. Our local development environment had been running MariaDB 10.6, which is too old to match. We moved local development and CI to 11.8 so every test ran against what production would run.

If you're moving to MySQL instead, most of what follows still applies. The UUID column is the main difference: Laravel's MySQL grammar stores it as char(36).

Where SQLite had been forgiving

The query builder hides most of the syntax differences between the two databases. What it can't hide is that they disagree about what counts as a valid write.

Strings longer than their columns

SQLite doesn't enforce varchar lengths. Declare string('title', 180), insert 400 characters, and SQLite stores all 400. MariaDB in strict mode, which Laravel turns on by default, rejects the insert with an error.

We found this in code that stores values taken from incoming URLs alongside a form submission. Those values were capped at 500 characters, well above their column widths. On MariaDB, somebody arriving from a link with a very long query string would have had their form submission fail with a server error. We'd have lost the message without knowing it existed.

The fix trims each value to its column when the row is written. One field got different treatment. It holds an identifier that's only useful if it matches exactly, so a truncated one is worse than none, and an over-long one is dropped. We added tests that submit a 600-character value and check the submission is saved.

Timestamp columns that update themselves

When explicit_defaults_for_timestamp is off, MariaDB gives the first TIMESTAMP NOT NULL column in a table an implicit DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. Any update to the row, for any reason, resets that column to the current time.

Several of our columns would have been affected, including expiry times and effective dates, exactly the kind of value that must never move on its own. Nothing errors when this happens. The values are just wrong from then on.

The default has been ON since MariaDB 10.10, so 11.8 behaves correctly as installed. We set it explicitly in the server configuration anyway, so a future change can't quietly turn it off, and our post-copy checks look for any ON UPDATE clause that shouldn't be there.

The time zone needed the same care. MariaDB converts TIMESTAMP values through the session time zone when storing and reading them, so a server left on local time would shift every stored date. The application runs in UTC, and we pinned the connection to match. Both of these are standard options on a Laravel database connection:

'timezone' => '+00:00',
'isolation_level' => 'READ COMMITTED',

Row locks instead of one writer at a time

The transactions that change important state in our application start with a guarded write: an UPDATE that bumps a version column, but only if the row is still in the state the code expects. If no row matches, something else got there first and the transaction stops. It's a common optimistic concurrency pattern.

On SQLite, that first write did more than we'd asked of it. SQLite starts transactions in DEFERRED mode by default, so a transaction takes no lock until it needs one, and a transaction that has only read so far can still be overtaken by another connection's write. Ours wrote first. Each one took the database's single write lock with its opening statement and held it until commit, so everything after that ran with no other writer anywhere in the database.

On InnoDB the same UPDATE locks one row, and other requests are free to change any other row in the meantime. We reviewed the transactions that read some state and then acted on it, and found two places where the code had been leaning on that database-wide lock.

In one, a transaction locked a parent record and then checked and changed several related records. Under InnoDB those related rows weren't covered, so another request could change one of them between the check and the update. The fix applies the same guarded update to the related rows as well, in a single statement, and treats a short affected-row count as a conflict.

The other was a deadlock. Generating sequential numbers used an INSERT IGNORE to create a counter row if it was missing, followed by an UPDATE to increment it. When the row already exists, InnoDB's ignored insert still takes a shared lock on it, and the update then needs an exclusive one. Two requests doing this at the same moment each hold the shared lock the other is waiting for, and InnoDB rolls one of them back. A single INSERT ... ON DUPLICATE KEY UPDATE, which is what Laravel's upsert() generates on MariaDB, takes the exclusive lock straight away, so the second request waits instead.

The isolation level fixed a third, quieter issue. Under InnoDB's default REPEATABLE READ, a transaction's plain reads all come from the snapshot taken at its first read. Laravel's createOrFirst() tries an insert, and on a unique-key collision it looks up the existing row. If another request committed that row after the snapshot was taken, the lookup can't see it. Under READ COMMITTED each read sees the latest committed data, so the lookup finds the row it collided with.

Collation and case-sensitive identifiers

Laravel's default collation for MariaDB is utf8mb4_unicode_ci, which compares strings without regard to case or accents. That's what you want for things like email addresses. It's wrong for identifiers issued by external services, which are often case-sensitive. Under that collation evt_1AbC and evt_1abc are equal, and a unique index would refuse a genuinely different record as a duplicate.

Given how most providers generate IDs a collision is unlikely, but the fix is one collation, so we changed those columns to utf8mb4_bin. We did it in a new migration that skips SQLite, which already compares strings byte for byte:

// Illustrative. SQLite already compares bytes, so only MariaDB and MySQL change.
if (in_array(DB::connection()->getDriverName(), ['mariadb', 'mysql'], true)) {
    Schema::table('external_references', function (Blueprint $table) {
        $table->string('external_id', 64)->collation('utf8mb4_bin')->change();
    });
}

That driver check is how every schema fix in this project was done. We never edited a migration that had already run. Each fix went into a new migration, and those migrations shipped to production while it was still on SQLite, where they changed nothing. Production recorded them as run. So when we copied the migrations table across, it matched the code exactly, and the schema we built on MariaDB from the same files already included every fix.

The tests were running on the wrong database

Some of our tables enforce their rules in the database itself, with CHECK constraints and triggers. SQLite and MariaDB express those differently, so the migration that creates them has a branch for each driver. Our whole test suite ran on SQLite in memory, which meant the MariaDB branch had never been executed.

Every problem described above would also have passed a SQLite test run. So the suite moved to MariaDB, locally and in CI, and the MariaDB run is the one that has to pass. We kept a SQLite run alongside it for the time being, because SQLite was our rollback target and had to keep working.

Backups and schema changes

Our backup command only knew SQLite and refused anything else. After the switch that would have meant no backups, so it learned MariaDB. It uses mariadb-dump --single-transaction, which reads InnoDB tables from one consistent snapshot without locking the site out while the dump runs.

A backup isn't proven until it's been restored, so restoring one and checking it against the live data is part of the routine.

MariaDB also changed how we handle schema migrations during deployment. Unlike SQLite, MariaDB doesn't support rolling back most schema changes as part of a transaction. If a migration fails halfway through, changes that already succeeded can remain in place.

To account for that, we updated our deployment process to take a database backup whenever there are pending migrations. That gives us a recovery point if something goes wrong.

A backup doesn't make a migration reversible, though. Restoring one returns the whole database to the moment the backup was taken, and anything written after that is lost, such as a form somebody submitted a minute after the deploy. We treat a restore as the last resort. The real protection is writing migrations that are safe to run against live data, and knowing how we'd recover before a schema change starts.

Copying the data

There are tools that convert a SQLite dump into MySQL syntax. We didn't use one. The schema we wanted on MariaDB was the one our migrations build there, with MariaDB's version of the triggers, the binary collations and native UUID columns. A converted dump gives you SQLite's schema translated into another dialect, which isn't the same thing.

So we ran Laravel's migrations against an empty MariaDB database and then copied only the rows, using three artisan commands we wrote for the job.

The first is a preflight. It reads the source and reports anything that would make the copy fail or alter the data: strings longer than their target column, values that would collide in a unique index under the new collation, invalid JSON, timestamps that won't parse, decimals with more places than their column allows, and a migration history that doesn't match the code. It prints table names, column names and counts, never a value from a row, so its output is safe to paste anywhere.

The second does the copy. It won't run if the preflight fails, and won't write into the database the site is running on. It inserts parent tables before child tables, so triggers that look up a parent row find it. It keeps every ID and sets each auto-increment counter past the highest ID SQLite ever issued, so IDs of deleted rows are never reused.

The third is the proof. It reduces every row on both sides to a canonical form, hashes it, and compares the results table by table, along with auto-increment counters, foreign keys, unique indexes, the database-level rules and the server settings the schema depends on. Any difference exits non-zero, and the cutover couldn't continue past a non-zero exit.

The tool runs in both directions, SQLite to MariaDB and MariaDB back into a fresh SQLite database. That second direction was our rollback.

What building it taught us

Compare in application code, not in SQL. With utf8mb4_unicode_ci, MariaDB ignores trailing spaces when comparing strings, so 'abc' and 'abc ' are equal. A comparison done in SQL would pass a row that had lost a trailing space. Ours hashes each row in PHP, sorts the row hashes, and hashes the sorted list. Sorting makes the result independent of row order, which matters because a string primary key can sort differently under the two collations. A test changes one byte in one row and checks that the comparison fails.

SQLite's DATE columns weren't holding dates. Eloquent's date cast writes a full date and time, so SQLite held 2026-10-07 00:00:00 in columns MariaDB defines as DATE. The copy reduces a midnight time to the date and refuses any other time of day, which would otherwise be silently thrown away.

A freshly migrated database isn't empty. One of our migrations inserts seed data, so the copy clears every row in the target first, not only the migration history.

Rehearsing and planning the cutover

All of the work above shipped while production was still on SQLite and changed nothing the live site relied on. The cutover itself was planned as a separate event.

We rehearsed the copy repeatedly in local development, then against a fresh snapshot of the real production data. That data includes personal information people have sent us, so it never left production for practice runs, and each rehearsal's copy was deleted afterwards.

Database privileges were split by responsibility, so the account the application runs under can read and write data but can't change the schema.

We wrote a go/no-go list before the day: server settings confirmed, the full suite passing on both databases, a MariaDB backup restored and verified, a clean preflight against a production snapshot, and the rehearsal timed.

The cutover had this shape: put the site in maintenance mode and pause anything that writes, snapshot SQLite, copy and compare, switch the application's configuration to MariaDB, make sure nothing could keep writing to the old database, check the site while it was still closed to visitors, take and verify a fresh backup, then reopen. We also accounted for incoming webhooks during maintenance, including how their providers handle failed delivery attempts.

How we'd roll back depended on timing. Until the site reopened, nothing had been written to MariaDB, so going back meant restoring the old configuration. After it reopened, MariaDB would hold data the SQLite snapshot didn't have, and switching back would lose it. From then on the plan was to fix forward, and if we couldn't, copy MariaDB back into SQLite with the same tool and compare it the same way.

We planned for 10 to 15 minutes of maintenance mode, most of it for checks.

What went wrong

A rehearsal stopped on decimal precision

The first rehearsal against production data stopped at the preflight.

Some of our reporting data contained decimal values with more precision than their MariaDB columns allowed. They're averages calculated elsewhere, and SQLite had stored them at full precision: a value like 4.333333 in a column declared decimal(6,2). In the worst table about a third of the values had more decimal places than the column allows.

We checked what MariaDB 11.8 does with that in strict mode. Extra decimal places are rounded to the column's scale, half away from zero, and the insert succeeds with a note (Data truncated), not an error. A value too large for the column is different: strict mode rejects it with an out-of-range error, where non-strict mode would quietly clip it to the column's maximum. Our values were the first kind, so the live application would never have failed on them. The question was only what the copy should do with values already stored, and the preflight was right to stop and make us decide.

We didn't relax the check across the board. The copy tool now has a short, explicit list of columns that hold measurements. For those, it rounds the way MariaDB would and the preflight reports how many values it rounded. Money isn't on that list. A monetary value with more precision than its column still stops the copy, and any decimal column added later stays exact unless someone deliberately adds it to the list.

Before shipping that change we checked, read-only against production, whether any rounded value crossed a threshold our reports compare against. None did, and the largest change was 0.005. The next rehearsal passed with 69 values rounded and every table matching.

The first cutover rolled itself back

We ran the cutover from a script that would roll everything back if any step failed. On the first attempt every database step passed. The copy and comparison passed, the new backup restored and verified, and the pages we checked through maintenance mode all loaded. Then the script's own smoke test failed, and the rollback ran.

The failing line chose a page to test by reading the sitemap through a pipeline that used head to keep the first match. This is a simplified version, not the script itself:

set -euo pipefail
url=$(curl -s "$SITE/sitemap.xml" | grep -o '<loc>[^<]*/blog/[^<]*' | head -n 1)

head exits as soon as it has its one line. If grep is still writing when that happens, it's killed by SIGPIPE and exits with status 141. Without pipefail, the pipeline's status would be that of its last command, which succeeded. With it, a non-zero status anywhere in the pipeline fails the whole thing, set -e stops the script, and in our case the rollback ran.

The database was fine. The bug was in the validation script checking it. The rollback put the old configuration and the SQLite file back and reopened the site on SQLite, exactly as designed, so our pre-reopening rollback had now been tested for real.

If you write scripts with pipefail, make sure no command in a pipeline stops reading while something before it is still writing. sed -n 1p and awk 'NR == 1' read all of their input, so either one can stand in for head here. Swapping head for grep -m 1 doesn't fix it, because grep then exits early itself and the command feeding it can be killed by the same signal. Saving the output to a variable or a file first and filtering that avoids the problem completely.

The second attempt

We ran it again two minutes later and it passed. Maintenance mode lasted about a minute and a half, well under the 10 to 15 we'd planned for, mostly because the database was small and every check was scripted. Copying and comparing took a few seconds, and every table matched.

Before reopening we confirmed the application could write, a fresh MariaDB backup restored cleanly, and the logs showed no new errors. After reopening, our end-to-end test suite passed all 53 tests against the live site, and the web server logged no server errors.

How it turned out

The site has been on MariaDB since October 7. SQLite was the right database for it for a long time, and we'd still pick it for a small Laravel application today. What changed was how much of the application writes at the same time, and that's where MariaDB fits better.

Most of the work went into finding the places where the code had quietly relied on SQLite's behavior: strings it never cut short, a lock that covered more than we'd asked for, unique indexes that would have started ignoring case. Reviewing the code against MariaDB's behavior found most of them before any data moved, and moving the test suite to MariaDB meant the fixes were exercised from then on. Building the transfer tool and rehearsing with production data caught the remaining data differences. The final comparison matched every table, with the measurement columns checked after the same rounding we'd approved beforehand.

The one surprise on the day came from our own validation script, not the database. The rollback built for a database failure handled it without any fuss, and that was the first time it had run against production. If you're planning a similar move for a Laravel application, it's the kind of work we do as part of Laravel development and ongoing application maintenance.