Skip to content
WordPress Maintenance

SQLite Basics: Why Do Local Apps Choose a “Single-File Database”?

SQLite Basics: Why Do Local Apps Choose a “Single-File Database”?

Many browsers, mobile apps, and desktop tools store their data in SQLite. Unlike server-based databases such as MySQL or PostgreSQL, SQLite makes one file the database itself.

This article explains what SQLite is, using small experiments with Python’s standard sqlite3 module. It describes general properties, not the implementation of any specific product.

Note: The numbers come from running Python 3 (SQLite 3.45.3) on October 14, 2026. They will vary by environment.

What Is SQLite?

SQLite is a database engine embedded inside an application. There is no separate process to start and connect to; the app reads and writes the file directly.

Aspect Server-based (MySQL, etc.) SQLite
How it runs A resident separate process you connect to The app operates on the file inside its own process
Where data lives The server’s data area A single file
Setup Users, permissions, connection settings Just a file path
Fits when Many users write at the same time One app owns its own data

WordPress uses a server-based MySQL-family database because many visitors access it at once. For one app handling its own data on one machine, running a server is usually not worth the effort.

Experiment 1: Create a File

I created one table in a temporary folder and inserted data.

import sqlite3
c = sqlite3.connect("t.db")
c.execute("create table log(id integer primary key, msg text)")

After 1,000 rows, the folder contained only t.db, at 32,768 bytes (32 KB). Copying that file copies the database, which is why backup and transport are simple.

Copying a file mid-write can capture an inconsistent state. Copy while the app is idle, or use SQLite’s dedicated backup facility.

Experiment 2: How Does Commit Frequency Affect Time?

I inserted the same 1,000 rows in two ways.

Method Time
Commit after every row (1,000 commits) about 0.29 s
One transaction, one commit at the end about 0.0007 s

That is roughly a 400x difference. On every commit, SQLite confirms the data really reached the file, and that confirmation accounts for most of the time.

When inserting many rows, group them into one transaction. Code that commits per row works, but slowly.

Experiment 3: What Happens With Simultaneous Writes?

SQLite allows only one writing connection at a time. I held a write lock (begin immediate) on one connection and tried the same on a second one. After waiting 0.1 seconds it gave up:

OperationalError: database is locked

This is not a malfunction; it means “someone else is writing right now.” Ways to handle it:

  • Set a timeout — too short, and mild contention becomes an error
  • Keep write transactions short — the longer you hold the lock, the longer others wait
  • If you need frequent writes from many connections, consider a server-based database

This constraint is why SQLite suits “one app owning its data.”

Journal Modes and WAL

To survive a power loss mid-write, SQLite keeps a record of changes. This is the journal mode.

The default is delete; pragma journal_mode returned delete in the experiment. After switching to WAL mode, two extra files, t.db-wal and t.db-shm, appeared.

In WAL mode, readers don’t block the writer and the writer doesn’t block readers, which helps read-heavy apps. Keep in mind:

  • The database becomes a set of three files; copy or move all three together
  • WAL does not work on network-shared folders

Loose Typing

SQLite does not strictly enforce column types. Inserting the string 'abc' into an integer column raised no error and was stored as text.

That is flexible, and also a pitfall. Assume the app validates input. For stricter behavior, use STRICT tables or CHECK constraints.

Four Points to Watch

1. Don’t put it on a network share

Opening the same SQLite file from several PCs over a shared folder may break file locking and can cause corruption. One file is used by an app on one machine.

2. Batch your writes

As Experiment 2 showed, commit count directly drives speed.

3. Back up when idle, or use the dedicated feature

Copying is easy but avoid doing it mid-write; with WAL, keep all three files consistent. After copying, verifying with a hash gives extra confidence.

4. Choose another tool when many write at once

SQLite is excellent but not universal. Services with heavy concurrent writes fit a server-based database better.

Summary

  • SQLite is an embedded engine where one file is the database: little setup, simple copying and transport
  • 1,000 inserts took about 0.29 s with per-row commits and about 0.0007 s in one transaction. Group writes in transactions
  • Only one connection can write at a time; others get database is locked. Use timeouts and short transactions
  • WAL mode reduces waiting but makes the database three files
  • Types are not strictly enforced; validate in the app

Local apps choose a single-file database because it avoids running a server and makes data easy to carry. In exchange, you accept low write concurrency and care about how the file is handled. For another design topic about operations that stay safe when repeated, see What Is Idempotency?.