Skip to content

SQLite Is a Lot More Capable Than Its Reputation

3 min read2 views[Backend]
Cover image for "SQLite Is a Lot More Capable Than Its Reputation"

Why SQLite Is Better Than Its “Toy Database” Reputation

SQLite is easy to underestimate. There’s no database server to install, no port to expose, and no separate service to monitor. SQLite runs inside your application and stores its data in a file.

That makes it look less capable than databases like PostgreSQL or MySQL. But SQLite is a real relational database, with tables, SQL queries, indexes, constraints, and transactions. The difference is mostly how it’s deployed: a server database runs as its own service, while SQLite is part of the application that uses it.

Fewer services to manage

For a small service on a VPS, SQLite can remove a surprising amount of routine work. You have one fewer service to configure, update, and keep running. It’s also easier for someone else to run your project locally: they don’t need to install a database server or create a database account just to get started.

A database file also makes SQLite convenient for tools and applications that mostly run in one place. It works well for personal projects, command-line programs, small websites, and services where one application manages the data. If you need to inspect or move the data, the database is a file your usual tools can work with.

Transactions still matter

Transactions are another reason SQLite deserves more credit. Suppose an application needs to create an order and reduce the stock count for an item. Those changes belong together: if one succeeds and the other fails, the data is wrong. A transaction lets the application treat them as one unit, so both changes happen or neither does.

Performance depends on the workload

SQLite can be fast for workloads that fit its design. Since it doesn’t need to send every query over a network to a database server, a local query avoids that extra trip.

That doesn’t make SQLite automatically faster in every situation. The query, indexes, storage, and workload still matter. But for a single application accessing local data, a separate database server can add complexity without providing much benefit.

Know where the limits are

The main limitation to keep in mind is writing at the same time. SQLite supports many readers, but writes take turns. If several requests try to write concurrently, some may have to wait.

For a site with occasional updates, that’s often fine. For a service with a constant stream of concurrent writes, or several application servers writing to shared data, a client-server database is usually a better fit.

Backups need a plan

A database file is convenient, but it doesn’t make backups automatic. Copying the file while the application is actively changing it can produce a backup that doesn’t represent a consistent point in time.

For a live database, use SQLite’s backup facilities or arrange for writes to stop during the copy. And make sure you’ve actually tried restoring a backup; a backup you can’t restore is just an extra file.

Migrations still matter

SQLite also doesn’t remove the need to look after your data model. As an application changes, its tables may need to change too. Keep database migrations in your project and apply them deliberately, just as you would with a server database. It’s easier to manage that change than to discover later that production and development have different schemas.

When SQLite is a good fit

A useful rule of thumb is to start with SQLite when:

  • One application owns the data.
  • The amount of concurrent writing is manageable.
  • You value a simple deployment.

Move to PostgreSQL or another server database when you need frequent concurrent writes, access from multiple machines, or database features and operational controls that fit a server setup better.

SQLite isn’t the right choice for every workload, and “it’s just a file” shouldn’t be an excuse to skip backups or think about concurrency. But for plenty of small services, it gives you the database features you need without making you run a database server too. That’s a practical tradeoff, not a compromise by default.

# Comments (0)

No comments yet. Be the first.

0/1000