Why We Moved from SQLite to MySQL

ChatCubie started on SQLite. That was the right call, and it stopped being the right call about four months later. This is where the line was.

Why SQLite first

There is no server to run, no credentials to manage, and no connection pool to tune. The database is a file. For the first weeks of a project, that removes an entire category of problems while you are still deciding what you are even building.

For a single-user app, or a read-heavy one, it can stay the right answer indefinitely. Plenty of production software runs on it happily.

Where it broke down

Concurrent writes

SQLite serialises writes. One writer at a time, and everyone else waits. In a chat application, writes are the main event — every message, every receipt, every presence update. Under real concurrent use we started seeing lock timeouts, and the failure was not graceful: a message that should have taken milliseconds would occasionally take seconds or fail outright.

The filesystem was not ours

This was the decisive one. On managed hosting, a redeploy can rebuild the directory the application lives in. A database that is a file in that directory is a database that can disappear. We had already been bitten by exactly this with uploaded images, and a chat history is considerably harder to shrug off than a lost avatar.

What the migration cost

The schema translation was the easy half — a day of adjusting types and adding the indexes we had been getting away without.

The expensive half was the code. The SQLite driver we were using was synchronous, and the MySQL driver is not. That meant converting roughly two hundred database calls from callback style to async/await, and then finding every place where the surrounding code had quietly assumed the query had already finished.

Migrating a database is a schema problem for one day and a control-flow problem for the rest of the week.

What we would do differently

1. Write the data layer as async from day one, even on SQLite. Wrapping synchronous calls in promises costs almost nothing early and makes the driver swappable later.
2. Keep queries behind a thin module rather than scattering them through route handlers. Every query we had inlined into a route was a query we had to find by hand.
3. Add the indexes before you need them. SQLite forgave missing indexes at small scale, which meant we did not discover them until the data was bigger and the queries were slower.

Was it worth it?

Yes, and the reason is not raw speed. It is that the database is now a service with its own lifecycle, backups, and credentials — independent of whatever the deploy pipeline does to the application directory. That separation is what made the rest of the products possible.