SQLite or MySQL, how I decide on small projects

Not everything needs a separate database server. Here are the criteria I use before setting up a MySQL instance for a project.

For a long time, “database” was pretty much synonymous with MySQL for me, without giving it much thought. It’s what I knew how to set up, what WordPress hosting came with by default, what every tutorial taught. So every time a client needed to store a handful of data, off I went to create a user, a database, configure wp-config.php or whatever .env was in play…

Projects like Facturapp changed my mind. There’s no server there: it’s a desktop and mobile app that runs locally, so a MySQL instance made no sense at all — I needed something that could live inside the app itself. SQLite was the obvious answer, and since then I’ve also used it on small web projects where before I would have reached for MySQL without a second thought.

When I choose SQLite

If the project isn’t going to have more than a handful of concurrent writes — an internal panel for a single client, a tool I use myself, the desktop app itself — SQLite wins almost every time. It’s a file. You back it up by copying that file. There are no users, permissions, or a separate process to manage on the server. And for read queries, at this project size, the performance difference with MySQL is irrelevant.

When I still reach for MySQL

As soon as WordPress or PrestaShop enter the picture, there’s not much to decide: they already assume MySQL (or MariaDB), and fighting that isn’t worth it. Same if the project is going to have several genuinely simultaneous writes — a backend with real traffic where multiple processes write at once, that’s where SQLite starts to show its limits, especially around write locks.

What I don’t do

I don’t decide this based on trends or whatever I read on Twitter that week. I’ve watched the pendulum swing from “SQLite is only for testing” to “SQLite can handle almost anything” in the space of a couple of years, and both statements are exaggerations. The real question is boring: how many processes are going to write at once, and do I actually need a separate server for that? Most of the time, for what I do, the answer is no.