Ask most developers what database to use for a new project, and the reflex answer is usually PostgreSQL or MySQL. Both are excellent, and for good reason, they're covered constantly. But there's a database that quietly runs more software than either of them, and it never even shows up in the conversation: SQLite.

It's on your phone, in your browser, inside countless desktop apps, embedded in cars and appliances, and it's a genuinely reasonable choice for a lot of self-hosted projects that people default to a full database server for instead.

What is SQLite?

SQLite is a relational database that doesn't run as a separate server process at all. Instead of connecting over a network to something like mysqld or postgres, your application reads and writes directly to a single file on disk. There's no username, no password, no port to open, and nothing to configure before you can start running SQL queries.

The entire database engine is a library linked directly into your application, which is why the SQLite project describes itself as "serverless," a database with no server at all rather than a database running on someone else's server.

Despite that simplicity, SQLite supports the vast majority of standard SQL: transactions, joins, indexes, triggers, views, and full ACID compliance (atomicity, consistency, isolation, durability). It's not a stripped-down toy database; it's a fully capable one that just happens to skip the client-server architecture entirely. It's also one of the most widely deployed pieces of software in existence, since it ships inside every major web browser, most mobile operating systems, and a huge share of desktop applications, almost always invisibly.

How is SQLite different from PostgreSQL or MySQL?

The core difference is architectural, not really about SQL feature support:

No server process. PostgreSQL and MySQL run as background services that your application connects to, usually over TCP even for local connections. SQLite is embedded directly into the application itself, reading and writing to one file through a C library that gets linked at compile or runtime.

No network layer. Because there's no server to connect to, there's no authentication, no network configuration, and no separate process to keep running and monitor. This also means there's no network-based attack surface to secure, since there's nothing listening on a port.

Concurrency. This is SQLite's real limitation. It handles many simultaneous readers well, especially in WAL (write-ahead logging) mode, but only one writer at a time can modify the database, which is fine for most small-to-medium workloads but becomes a bottleneck for applications with heavy concurrent write traffic.

Portability. A SQLite database is one file. Backing it up means copying that file; moving it to another server means copying that file there too, no export/import step required in between.

Data types. SQLite uses "type affinity" rather than strict typing, meaning columns will generally accept values of any type unless you've added explicit constraints. This is more flexible than PostgreSQL's strict typing but can occasionally surprise developers coming from a stricter database background.

Understanding WAL mode

By default, older versions of SQLite used a rollback journal for transactions, which locks the entire database file during writes and blocks readers at the same time. Modern SQLite deployments almost always enable WAL (write-ahead logging) mode instead, which writes changes to a separate log file first and lets readers continue accessing the main database file without blocking.

This single setting change is responsible for most of the concurrency improvements that have made SQLite viable for a much wider range of production use cases than it was a decade ago. Enabling it is a single command:

PRAGMA journal_mode=WAL;

Most modern frameworks and self-hosted applications that use SQLite enable this by default, but it's worth confirming if you're setting up a database connection manually.

When SQLite is genuinely enough

A surprising number of self-hosted applications and small projects fit comfortably within what SQLite handles well:

Personal and small-team tools. Self-hosted apps used by one person or a small team rarely generate enough concurrent write traffic to hit SQLite's single-writer limit. Many popular self-hosted apps, including several widely used note-taking, bookmarking, and monitoring tools, default to SQLite for exactly this reason, and some don't offer any other option at all.

Read-heavy websites and blogs. If your application reads far more often than it writes, which describes most content sites, SQLite's read concurrency in WAL mode is more than sufficient, and static site generators or lightweight CMS platforms built around SQLite can comfortably handle significant traffic.

Local development and testing. Spinning up a full database server just to run tests adds friction and CI pipeline time. SQLite lets you test against something that behaves like a real relational database with zero setup, often using an in-memory database (:memory:) that disappears the moment the test finishes.

Embedded and edge deployments. Anywhere you don't want to manage a separate database service, on a small VPS, in a container, on a Raspberry Pi, SQLite removes an entire moving part from your infrastructure, which also means one fewer service to monitor, patch, and back up separately.

Prototypes and MVPs. If you're not sure a project will get real traffic yet, starting with SQLite avoids over-provisioning infrastructure for a database server you might not end up needing, and it keeps local development frictionless while the schema is still changing frequently.

When you should still reach for a full database server

SQLite isn't the right choice for everything, and knowing the boundary matters:

High concurrent write volume. If multiple processes or many simultaneous users are writing to the database constantly, SQLite's single-writer model will eventually queue those writes and slow things down, even in WAL mode.

Multiple applications sharing one database. SQLite is built around a single application accessing its own file. If you need several separate services connecting to the same database over a network, you need a real database server that can manage concurrent client connections properly.

Very large datasets with complex querying. SQLite can handle databases in the tens of gigabytes without issue, and there are documented cases of it managing far larger datasets, but if you're running complex analytical queries against a genuinely large dataset with heavy joins, PostgreSQL's more sophisticated query planner and indexing options will scale further.

Built-in replication needs. If you need standard master-replica replication for redundancy or read scaling, that's native to PostgreSQL and MySQL in a way it isn't for SQLite, which relies on file-level backup or third-party tools for anything resembling replication.

Network access from remote clients. If something needs to query the database from a different machine over the network, SQLite isn't built for that use case at all; it assumes local file access.

Migrating from SQLite later isn't a dead end

One of the more persistent myths about SQLite is that choosing it locks you in. In practice, most frameworks that support SQLite also support PostgreSQL or MySQL through the same abstraction layer, meaning you can start on SQLite and migrate later if your write concurrency actually grows past what it can handle.

The migration itself is usually a matter of exporting the schema and data (most frameworks include tooling for this, or a straightforward .dump and reimport works for simpler schemas) and updating a connection string.

Starting with the simpler option and moving up when you have evidence you need to is a reasonable default, not a mistake, and it avoids paying the operational cost of a full database server before you actually need one.

Backing up a SQLite database properly

Because the whole database is a single file, it's tempting to just copy it with cp, but that risks grabbing the file mid-write and ending up with a corrupted copy. SQLite's own backup mechanisms handle this safely:

sqlite3 mydb.sqlite ".backup 'backup.sqlite'"

This uses SQLite's online backup API, which safely copies the database even while it's actively being written to, and is the recommended approach over a raw file copy for anything beyond a quick manual snapshot.

Wrapping up

SQLite gets overlooked because it doesn't look like a "real" database in the way a server process does, but that's exactly what makes it useful. For self-hosted tools, small applications, and anything that doesn't need heavy concurrent writes, it removes an entire service you'd otherwise need to install, secure, and maintain, while still giving you the full expressive power of SQL underneath.

Thanks for reading! Whether your app stays on SQLite or eventually grows into a full database server, it still needs somewhere solid to run. V.PS offers scalable, production-ready NVMe-powered VPS hosting that handles SQLite-backed apps just as well as it runs PostgreSQL, MySQL, or MariaDB, and xTom provides enterprise-grade dedicated servers, colocation, and more for workloads, SQLite or otherwise, that need dedicated resources.

Frequently asked questions about SQLite

Is SQLite good enough for a production website?

For many small-to-medium sites, yes, particularly ones that are read-heavy. Several popular content management systems and self-hosted apps support SQLite in production for exactly this reason, especially once WAL mode is enabled.

Can multiple people use a SQLite database at the same time?

Multiple readers can access a SQLite database simultaneously without issue, particularly in WAL mode. Writes are serialized, meaning only one write happens at a time, which is fine for lighter traffic but can become a bottleneck under heavy concurrent writes.

How do I back up a SQLite database?

Since the entire database is a single file, backing it up is as simple as copying that file, ideally using SQLite's own .backup command or online backup API to avoid copying it mid-write.

Does SQLite support the same SQL as PostgreSQL or MySQL?

It supports the core of standard SQL, including transactions and joins, but some advanced features and strict data typing differ. Most everyday queries work the same across all three.

Is it hard to migrate from SQLite to PostgreSQL later?

It's usually manageable, especially if your application was built using a database abstraction layer or ORM. The SQL itself may need minor adjustments, but the underlying data model typically transfers with minimal changes.

What is WAL mode, and should I enable it?

Write-ahead logging (WAL) mode is a journaling mode that lets readers keep accessing the database while a write is in progress, instead of locking the whole file. It's a significant concurrency improvement over SQLite's older default journal mode, and most self-hosted apps that use SQLite enable it automatically.

Does SQLite work well for multi-user web applications?

It can, particularly for smaller teams or read-heavy applications, but it's not designed for high-concurrency, multi-writer workloads the way a dedicated database server is. Test your actual usage pattern before assuming either way.