- The fopen() line that settles most of this
- What if the project grows?
- Side-by-side
- Choose the server when people share the data
- Choose the file when one app owns the data
- What they share
- Who looks after it?
- Concurrency and access, in plain English
- Red flags that you've picked the wrong one
- A five-minute architecture test
- Frequently asked questions
Use MySQL when several users or services need to share one central database over a network, with lots of connections at once. Use SQLite when one application or device needs local storage in a single file with no server to run, which covers many low to medium traffic websites and app file formats.
This is for developers deciding whether a project needs a networked database server or an embedded engine that lives in a single file. It's one of the earliest decisions in any build, and getting it wrong usually means a painful rewrite later on, so it's worth five minutes of thought now rather than five weeks of refactoring next year.
The fopen() line that settles most of this
Here's the insight most comparisons miss. SQLite's own documentation says it isn't directly comparable with client/server databases such as MySQL, because it solves a different problem. Its makers say the thing it really competes with is fopen(), the humble function for opening a file on disk, not a shared enterprise system.
That reframes the whole question. You're not hunting for the database with the longest feature list. You're choosing an architecture: a serverless file that lives inside your app, or a network server that many clients connect to at once. Once you know which shape your project is, the answer is usually obvious, and the remaining questions are about details.
What if the project grows?
A common worry with the single-file route is outgrowing it. It's a fair worry, but it's often overstated. The file engine's makers say it works well for low to medium traffic websites, and many apps never get beyond that. If yours does, moving to a server is a known path: the SQL is familiar, so plan the connection and deployment changes early. Writing standard SQL from day one, and avoiding engine-specific tricks, keeps that door open.
If you're running a content site or a small shop, check what your platform expects before you choose, and go with its recommendation unless you have a strong reason not to.
Side-by-side
| Question | MySQL | SQLite |
|---|---|---|
| Shape | Client/server | In-process library |
| Server to run | Yes | No |
| Storage | Tables in schemas | One disk file |
| Transactions | Full ACID | Transactional |
| Many connections | High volume | Unstated |
| Licence | Open source | Public domain |
| JSON | Supported | Unstated |
| Websites | Unstated | Low to medium traffic |
Choose the server when people share the data
If more than one user, service or machine needs to read and write the same records at the same time, you want a server. Oracle's description of MySQL stresses full ACID transactions and coping with a high volume of simultaneous connections, and that's exactly the job it was built for.
Think of a customer database behind a web app, an order system that a dozen staff use at once, or a back end that three separate services all write to. Those are server-shaped problems.
Choose the file when one app owns the data
If one application owns its data, a server is overhead you don't need. SQLite is a library that runs inside your program: nothing to install, no separate service to keep alive at three in the morning. The whole database lives in one disk file that you can copy, back up or ship to a user.
That makes it a natural fit for desktop and mobile apps, small internal tools, test suites and app file formats, anywhere the data belongs to one program rather than to a crowd of users.
What they share
Both are relational and both speak SQL, so the queries you write look familiar either way. Both are transactional. And both are free to use in the everyday sense: one is open source and the other's code has been placed in the public domain. For the server's licence terms in more detail, see whether MySQL is free.
Who looks after it?
A server is something you run. It needs installing, patching, backing up, monitoring and securing, and somebody needs to know how. If you're a solo developer or a tiny team, that's real time taken away from building your product. A managed service removes some of that work, in exchange for another account and another supplier to manage. The file engine asks almost none of it of you, though you'll still want to check the right way to copy the file safely while your app is running.
Even teams that run a server in production often reach for the single-file engine in tests and prototypes, because there's nothing to set up. That's handy, but be careful: if production is the server, run at least some of your tests against the server too, so differences between the two don't surprise you on launch day.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Concurrency and access, in plain English
The word you'll hear is concurrency: lots of people writing at once. A shop with a handful of orders an hour isn't a concurrency problem. A busy booking system where hundreds of people grab the same seats is. Be honest about which one you are, because over-building is as real a risk as under-building, and both waste weeks.
Access works differently too. A network server is something other machines can reach, which means accounts, passwords and network rules to get right. A file inside your app is only as exposed as the app and the device it sits on. Neither is safe or unsafe by default, but they ask for different habits: with a server, lock down who can connect; with a file, think about who can copy it.
Red flags that you've picked the wrong one
- You're running a server so one app can store settings.
- Several services pass one file around to write to it.
- Users need to save their data as one file, but it sits on a server.
- Your team queries production data from laptops over the network.
If two or more of those sound familiar, spend an afternoon sketching the other architecture before the project grows any further. Moving a small project is a chore; moving a large one can swallow a quarter, and the longer you wait, the more code leans on the old shape.
A five-minute architecture test
Answer these in order and stop at the first yes.
- Will more than one machine write at once? Use a server.
- Does one app on one device own the data? Use the file.
- Is it a modest website? The file engine may cope.
- Must the whole database travel as one file? Use the file.
- Still unsure? Start small and keep your SQL standard.
If Postgres is also on your shortlist, our guide to how SQLite compares with Postgres walks through that version of the choice with the same architecture-first approach.
Frequently asked questions
Can one SQLite file hold triggers and views?
Yes. Tables, indices, triggers and views all live together in the one disk file, according to its own documentation.
How does MySQL organise data?
In tables of rows and columns, organised into schemas, according to Oracle's description.
Is SQLite's code free to reuse?
Its source code sits in the public domain rather than under an open source licence, which is one reason it turns up inside so many other programs' files.
Does Oracle offer MySQL as a hosted service?
Yes, HeatWave. Oracle pitches it as a cloud service handling transactions and analytics together, so nobody on your team is patching or babysitting a server.
Does SQLite handle JSON?
Its own description doesn't mention JSON, so check its documentation before you design around it.
