Working with SQLite?

SQLite locking up or corrupt? We fix it.

SQLite is quiet and fast until the day the file is locked, the disk is full, or a shutdown leaves the database malformed. We find what the engine is actually complaining about and fix that, with your data intact.

  • Since 2015Fixing technology
  • FreeEstimates
  • US teamReal engineers

sqlite.log

still failing

What it is

SQLite at a glance

SQLite is an embedded SQL database written in C by D. Richard Hipp and first released in August 2000. The whole engine lives inside the application that uses it, so there is no separate server process to install or run. The entire database sits in one ordinary file on disk.

It is public domain and almost certainly the most widely deployed database in the world. Every iOS and Android device ships with it, every major browser uses it, macOS relies on it, and the Python standard library includes it. Countless embedded systems and desktop tools keep their state in a SQLite file without the user ever knowing.

The WAL journal mode that landed in 2010 made concurrent readers and a single writer work cleanly together, which pushed SQLite into far more serious production use. Current releases are in the 3.46 series. Companion projects fill in the gaps: Litestream streams the database to object storage for near continuous backup, rqlite wraps it in Raft for distributed consensus, and LiteFS from Fly.io replicates it across a cluster.

Type
Embedded relational database
First released
2000
License
Public domain
Maintained by
D. Richard Hipp and the SQLite Consortium
Typically used for
Mobile apps, desktop apps, config and state storage, CLI tools, prototypes, edge and serverless databases, small production web apps with Litestream

The honest version

SQLite pros and cons

No technology is all upside. This is where SQLite earns its place and where it tends to cause trouble.

Where SQLite is strong

  • Nothing to install or runThere is no server process, no port to open, no accounts to create. The database is a file your application opens directly.
  • One of the most tested codebases anywhereThe SQLite project ships with millions of lines of test code against a comparatively small engine, which is a large part of why it is so reliable in the field.
  • Fast reads and an honest file formatReads are quick, concurrent readers do not block each other in WAL mode, and the file format is stable and documented for decades.
  • Great for CLI tools, mobile and edgeA single file database travels with the app, which suits mobile apps, CLI utilities, prototypes and edge functions where a full server would be overkill.
  • Replication when you need itLitestream gives streaming backup and read replicas against object storage, and rqlite or LiteFS add distributed high availability on top of the same engine.
  • Full-text search built inThe FTS5 extension provides capable full-text search without pulling in a second system to run alongside the database.

Where SQLite bites

  • One writer at a timeEven in WAL mode SQLite serialises writes. Workloads that fan out heavy writes across many threads or processes hit that wall quickly.
  • Corruption is rare but realSudden power loss, a crash mid-write, or a database file placed on a network filesystem can leave the file malformed. Recovery is sometimes possible but never automatic.
  • Not built for high-concurrency multi-user web appsIt is a terrific library and a modest server. Large multi-tenant web apps with heavy concurrent writes usually outgrow it and belong on Postgres or MySQL.
  • Backups during writes are delicateCopying the file while a writer is active produces an inconsistent snapshot. Safe backups need the online backup API, a VACUUM INTO, or a tool such as Litestream.
  • No real users or rolesAccess control sits in the application, not the database. There are no GRANT statements and no network authentication to lean on.
  • Schema changes and migrations need careDROP COLUMN only arrived in 3.35, and shipping schema changes across mobile devices or edge nodes needs a tested migration path or they will diverge.

Sound familiar?

Common SQLite problems we fix

The SQLite problems that bring people to us, and what you have once each one is fixed.

SQLITE_BUSY: database is locked

Writes are timing out under load

The application is in DELETE journal mode, or many writers are competing for the same file through short-lived connections.

WAL mode on, writes serialised cleanly

database disk image is malformed

The file is corrupt after a crash

A power cut, a kill on the writer, or a database stored on a network mount that lies about fsync.

Recovered where possible, moved off the bad surface

database or disk is full

The disk filled and writes stopped

WAL and temporary files growing without bound, or an application that never checkpoints under pressure.

Space reclaimed, checkpoints tuned

backup: snapshot inconsistent

Backups are not restorable

A cron job that copies the raw file while writes are still happening, so half the backups fail to open.

Online backups that actually restore

Litestream: replica fell behind

Replication stopped quietly

The streaming replica stalled behind the primary, often because of credentials, clock drift, or object storage limits, and nobody noticed.

Replication caught up and monitored

SELECT took 12.4s

A once-fast query crawls

A missing index on a column that only started mattering once the table grew, or ANALYZE that has never been run.

Indexed and measured

schema mismatch on upgrade

A mobile app crashes after release

A migration that worked in the office but fails on real devices with older data in them.

Migrations tested against real data

cross-process locking failing on NFS

It never worked on this mount

The database file is on a network filesystem whose locking is not reliable, so SQLite protections quietly fail.

Moved to local disk, replicated off-box

What we do

SQLite help, from rescue to upkeep

Call us for one locked database, or hand us the whole stack.

Rescue
Corrupt file, locked database, or an app that will not start. We get it opening again, then find out why it stopped.
Performance
Journal mode, pragmas, indexes and query work, with numbers before and after.
Backups and replication
Online backups, point-in-time recovery with Litestream, and restores that have actually been run.
Production review
An honest look at whether SQLite is the right fit for your workload, or whether it is time to move.
Migrations
Moving between SQLite and Postgres or MySQL in either direction, with the application tested against the result.
Managed Tech
Backup verification, integrity_check on a schedule, Litestream replica watch, and an engineer who understands your write patterns when load changes.

How it works

From first message to fixed

  1. Look Free estimate

    Tell us what's going wrong. We look at it, then tell you plainly what's wrong and what it will cost.

  2. Fix

    We fix the cause, not the symptom. If something is down, getting it back up comes first.

  3. Harden

    Backups, monitoring, updates and access, so the same problem can't quietly come back.

  4. Hand back or stay on

    You get a plain-language write-up. Or we stay on for hosting, upkeep and new work.

Often found together

Running SQLite alongside something else?

We work on the whole stack, so the problem can be wherever it actually is.

Questions

SQLite questions

Something else? Call+1 737 727 7327

Is SQLite safe to use in production?

For the right workload it is a sound choice in production. Single-node apps with modest write concurrency run on SQLite for years without drama, especially paired with Litestream for backup and read replicas. Where it struggles is high-concurrency multi-writer web apps, and that is where it is worth considering Postgres or MySQL instead.

Can you repair a corrupt SQLite database?

In most cases some or all of the data can be recovered. The .recover command in a recent SQLite build extracts what it can from a malformed file, and the integrity_check pragma reports exactly what is wrong. The important thing is not to overwrite or reinitialise the file before someone has looked at it.

Why is our SQLite database locking under load?

The common cause is running in the default DELETE journal mode while several writers share the file. Turning on WAL mode and a sensible busy_timeout clears up most of these cases. Writes placed on a network filesystem or inside a container with a slow bind mount are the other usual suspects.

Can you move from SQLite to Postgres (or the other way)?

Yes, and we do it in both directions regularly. We map the schema, convert the data, retest the application against the new engine, and run the two side by side long enough to be confident before switching the traffic over.

Can you set up Litestream for SQLite backups and replication?

Yes, that is a common piece of work for us. We put Litestream in front of the database, point it at S3 compatible storage, and verify that a restore actually produces a working database. After that you get continuous backup and the option of read replicas, without changing the application.

How much does SQLite help cost?

Estimates are free. Hand us the database file or a schema export, and we come back with a quote before anything on your end is changed.

Free estimate

SQLite giving you trouble?

Tell us what you're running and what's going wrong. A person reads it and comes back with an estimate.

  • No charge to find out what it will take
  • If it's urgent, say so

Replies come from a person. By sending, you authorize us to email you back. No marketing.