SQLite development for mobile apps, devices and small services

SQLite is a library that puts a complete SQL database in a single file inside your application, with nothing to install or administer. We use it for mobile and desktop apps, devices, local caches and small services, and we are clear about where it stops being the right tool.

Talk through your SQLite project Request an estimate

Local databases in mobile and desktop apps
Offline-first caches that sync later
WAL mode, backups and schema migrations done properly
A clear line to a client/server database when you outgrow it

When SQLite is the right choice

SQLite's own guidance on when to use it covers these cases:

Embedded devices and apps: it needs no administration, so it suits phones, appliances and devices with no expert support.
An application file format: the database file can be a desktop app's document, and changes save automatically.
A local or edge cache of data held elsewhere, so the app can keep working through a network outage.
Websites and services with low to medium traffic and few simultaneous writers, on a single machine.

When SQLite is not the right choice

SQLite's documentation is just as clear about its limits:

Many clients on different machines using one database over a network. Use a client/server database such as PostgreSQL or MySQL.
Many simultaneous writers. SQLite allows many readers but one writer at a time.
The database file on a network file system. Locking is unreliable on many of them, and the documentation advises against it.
Very large data sets. Everything lives in one file, and data approaching terabytes needs a system that spreads content over several files or volumes.

What we build with SQLite

Typical projects, described in general terms.

Mobile app storage

A local database inside an iOS or Android app for lists, history and settings that must work without a connection.

Offline-first sync

A local copy of the data that syncs to your server when the connection returns.

Desktop and device databases

A single-file database for a desktop tool or a device, with versioned schema changes.

Search

Full-text search with the built-in FTS5 module, so local search works without a separate service.

Backups

Safe backups of a live database using SQLite's online backup API instead of copying a file in use.

Moving up

Planning the step to a client/server database when writers, machines or data size outgrow SQLite.

What SQLite gives you

SQLite is a serverless, self-contained SQL engine in a library, and its code is in the public domain. A whole database, with its tables, indexes, triggers and views, is one disk file. Transactions are atomic even after an operating-system crash or power failure. Write-ahead logging (WAL) mode lets readers and a writer work without blocking each other. Built-in extras include FTS5 full-text search, JSON functions, an online backup API and run-time loadable extensions. STRICT tables enforce column types, against SQLite's default flexible typing.

What we get right when using it

Writers take turns: there is one writer at a time, even in WAL mode, so a blocked writer gets a busy error. We set a busy timeout and retry.
Types: a column's declared type is only a preference by default. We use STRICT tables where the data needs enforcement.
Foreign keys: they are off by default and must be switched on for each connection.
File location: WAL does not work over a network file system, and the WAL files are part of the database state and stay with the database file.
Backups: a plain copy of a file in use can be corrupted if something fails mid-copy. We use the backup API.
Schema changes: ALTER TABLE covers renames, adding and dropping columns with limits. Other changes use the documented create, copy, drop and rename procedure inside a transaction.

How we work

Check it fits first: one machine, few writers and data that fits comfortably in a file, or we say so and suggest another database
Set the pragmas on purpose: WAL mode, foreign keys and a busy timeout, written down and tested
Version the schema: every change is a scripted migration that runs in a transaction
Hand over properly: the migrations and backup routine in your repository, and a note on what to do when it outgrows SQLite

Frequently asked questions

Does SQLite need a database server?

No. It runs as a library inside your application and the database is a single file, so there is nothing separate to install or administer.

Can several users or processes use it at once?

Many readers can read at once, but only one writer at a time. WAL mode lets readers and a writer proceed together. It suits one machine, not many clients across a network.

Can the database file sit on a shared network drive?

SQLite's documentation advises against it. Locking and write ordering over network file systems can fail and corrupt data, and WAL mode does not work over them. Use a client/server database for multi-machine access.

Can we back it up while the app is running?

Yes, with the online backup API, which copies a live database. A plain file copy can block writers and can be corrupted by a failure during the copy.