What Is SQLite? Benefits, Limits, and Practical Use of a Serverless File-Based Database

Summary

SQLite is a serverless, file-based relational database engine that runs inside your application process. It is easy to describe SQLite as “lightweight,” but that does not mean it is only a cache or a toy database. It supports transactions, indexes, joins, WAL mode, foreign keys, and strict tables, and it is widely used in mobile apps, desktop apps, embedded systems, tests, and small services.

The practical question is not “Is SQLite powerful enough?” but “Is this data local to one application, one device, or one file?” If the answer is yes, SQLite can remove a lot of installation and operations work. If several servers need to write heavily to one shared central database, PostgreSQL or MySQL is usually the safer choice.

In this article

What SQLite is

SQLite is an embedded SQL database engine. The sqlite3 library is linked into the application, and the application reads and writes a database file directly. Unlike MySQL or PostgreSQL, there is no separate database server process to connect to over TCP.

The official SQLite documentation describes it as a “self-contained, serverless, zero-configuration, transactional SQL database engine.” In practical terms, that usually means this:

Feature	Meaning	Practical benefit
Serverless	No separate database server	Less setup, deployment, and operations work
Self-contained	Most functionality lives inside the library	Easy to embed in mobile, desktop, CLI, and device software
Zero-configuration	Very little server-side setup	Good for tests, local development, sample apps, and small tools
Transactional	ACID transaction support	Atomic changes are possible even though the database is a file
Public domain	Public-domain license	Low licensing friction for personal and commercial projects

SQLite also appears behind other libraries and frameworks. In the previously published WatermelonDB article, for example, SQLite is often the actual local storage layer. Android Room and the SQLite backend used by iOS Core Data are similar examples: developers use a higher-level API, but the data is stored in an SQLite database file.

When SQLite fits, and when it does not

The first decision criterion is simple: does the data mainly belong to one application instance or one device? SQLite is strong when the database is local and file-centered. It is not designed to be a central database that receives heavy concurrent writes from many server instances.

When SQLite is a good fit

• Local storage for mobile apps
• Settings and state storage for desktop apps, Electron apps, and CLI tools
• Local data logging for IoT and embedded devices
• Test databases, demo apps, and local development environments
• Small services with mostly reads or low write contention
• Application file formats where data should move as a single file

Offline-first apps are a common example. The app must continue reading and writing data when the network disappears. SQLite works well in that structure because there is no server dependency and the data is stored close to the application.

When to be careful with SQLite

• Several server instances need to write to the same database file at the same time
• The service constantly receives many write transactions per second
• You need database-server-level users, permissions, access control, and network management
• You want to place the database file on a network filesystem such as NFS or SMB
• Replication, failover, and operational monitoring are central requirements

This is a role problem, not a “small database” problem. SQLite is very capable as a local file database. But if you force it into the role normally handled by PostgreSQL or MySQL, locking, backups, permissions, and concurrency become the first places where trouble appears.

Files, transactions, types, and WAL

One database file

An SQLite database is usually stored as a single .sqlite or .db file. Tables, indexes, triggers, and views live inside that file. This makes the database easy to move, but it also means backup needs more care than a simple file copy when the database is in use.

For a safe backup while the database may be active, prefer the SQLite CLI .backup command or the SQLite Online Backup API.

sqlite3 app.db ".backup 'backup-$(date +%Y%m%d).db'"

Transactions and concurrency

SQLite supports transactions. You can group multiple SQL statements into one unit of work and roll them back if something fails.

BEGIN IMMEDIATE;

UPDATE accounts
SET balance = balance - 10000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 10000
WHERE id = 2;

COMMIT;

The important concurrency point is this: many readers can read at the same time, but writes to the same database file are coordinated around one writer at a time. WAL mode can reduce reader/writer blocking, but it does not make highly contended writes unlimited.

WAL mode

Write-Ahead Logging, or WAL, records changes in a separate WAL file first. The official documentation explains that WAL is faster in many scenarios and improves concurrency because readers do not block writers and writers do not block readers in the same way as the rollback journal mode.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;

These settings are common in application development, but they are not a universal answer. WAL can create app.db-wal and app.db-shm next to the main database file, and it needs extra caution on network filesystems or unusual multi-process deployments.

Flexible typing and STRICT tables

SQLite does not enforce column types in the same way PostgreSQL or MySQL usually do. The official documentation describes SQLite as using a dynamic type system: the type belongs to the value itself. For example, a column declared as INTEGER can still contain a different type of value in some situations.

This flexibility can be convenient, but in production it can also create the “why is there a string in a numeric column?” bug. If you need stricter checking, consider STRICT tables, introduced in SQLite 3.37.0.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  age INTEGER CHECK (age >= 0),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;

Turn on foreign keys for each connection

SQLite supports foreign key constraints, but you should not blindly assume they are active. A safe habit is to run PRAGMA foreign_keys = ON; when each connection is created.

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

Check this in Node.js, Python, React Native, Android, or any ORM/wrapper you use. If the connection option or initialization hook is missing, foreign keys may behave differently from what the schema suggests.

A runnable SQLite example

macOS and many Linux distributions already include the sqlite3 CLI or can install it through a package manager.

sqlite3 blog.db

Create a small table and insert one row:

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS notes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;

INSERT INTO notes (title, body)
VALUES ('SQLite test', 'A local database example stored in one file without a server.');

SELECT id, title, created_at
FROM notes
ORDER BY id DESC;

You can also run the same example from the shell:

sqlite3 blog.db <<'SQL'
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS notes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;

INSERT INTO notes (title, body)
VALUES ('SQLite test', 'A local database example stored in one file without a server.');

SELECT id, title, created_at
FROM notes
ORDER BY id DESC;
SQL

In Python, the standard library sqlite3 module is enough for a small example.

import sqlite3

with sqlite3.connect("blog.db") as conn:
    conn.execute("PRAGMA foreign_keys = ON")
    conn.execute("PRAGMA busy_timeout = 5000")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS notes (
            id INTEGER PRIMARY KEY,
            title TEXT NOT NULL,
            body TEXT NOT NULL,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        ) STRICT
    """)
    conn.execute(
        "INSERT INTO notes (title, body) VALUES (?, ?)",
        ("SQLite test", "Use parameter binding instead of building SQL strings directly.")
    )
    rows = conn.execute("SELECT id, title FROM notes ORDER BY id DESC").fetchall()

print(rows)

The key detail is parameter binding. Even if SQLite is local, SQL injection can still happen when user input is concatenated into SQL strings.

Practical best practices

1. Keep connection initialization SQL explicit

Many SQLite settings are connection-level settings. Put initialization SQL in one place when the app starts or when a connection is created.

PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA journal_mode = WAL;

One caveat: journal_mode is stored in the database file, and the returned value can vary by environment. Check the actual value rather than assuming it changed.

PRAGMA journal_mode;

2. Keep write transactions short

Long write transactions can make other write operations wait. Do not put slow work such as downloads, network calls, or image processing inside a write transaction. Keep the transaction around the minimum database changes.

3. Create indexes from real query patterns

SQLite also needs index design. Add indexes to columns that are frequently searched or sorted, then verify them with EXPLAIN QUERY PLAN.

CREATE INDEX IF NOT EXISTS idx_notes_created_at
ON notes (created_at DESC);

EXPLAIN QUERY PLAN
SELECT id, title
FROM notes
WHERE created_at >= '2026-01-01'
ORDER BY created_at DESC;

4. Decide the date/time format as a team

SQLite does not have a dedicated date type. Common choices are ISO-8601 text, Unix time integers, or Julian day numbers. If the application does not also define timezone rules, sorting, comparing, and displaying dates can become confusing.

In many app projects, storing UTC-based ISO-8601 text and converting to the user’s timezone at display time is easy to understand.

5. Prefer SQLite backup tools over raw file copying

If the application is using the database, a raw file copy can miss WAL files or checkpoint state. For an active database, check .backup or the SQLite Online Backup API first.

sqlite3 app.db ".backup backup.db"
sqlite3 backup.db "PRAGMA integrity_check;"

Common misconceptions and mistakes

“SQLite is only a toy database for tests”

No. SQLite is used in browsers, mobile operating systems, apps, embedded devices, and many other places. The point is purpose: it is strong for local, embedded, read-heavy, and single-file problems.

“Turning on WAL solves all concurrent write problems”

WAL helps reduce reader/writer conflicts. It does not remove all write contention on the same database file. If writes are highly contended, use a write queue, shorten transactions, or reconsider whether the system really needs a central server database.

“INTEGER PRIMARY KEY always needs AUTOINCREMENT”

The SQLite documentation says AUTOINCREMENT adds CPU, memory, disk, and I/O overhead and should be avoided unless it is strictly needed. In most cases, INTEGER PRIMARY KEY is enough. Review AUTOINCREMENT only when you specifically must prevent reuse of old ROWIDs.

-- Enough for most tables.
CREATE TABLE items (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

-- Use only when old ROWIDs must not be reused.
CREATE TABLE audit_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  message TEXT NOT NULL
);

“A declared foreign key is always checked automatically”

Treat declaration and runtime enforcement separately. Enable PRAGMA foreign_keys = ON; per connection, and test that invalid child rows are actually rejected.

Failure cases and fixes

Case 1. database is locked or SQLITE_BUSY keeps appearing

Symptoms: The app intermittently fails with database is locked or SQLITE_BUSY, especially when several threads or processes write at the same time.

Common causes:

• Long write transactions hold locks for too long.
• busy_timeout is missing or too short on the connection.
• Several writers access the same database file at once.
• Slow work such as a network call runs inside a transaction.
• The SQLite file is placed on a network filesystem.

Check first:

PRAGMA journal_mode;
PRAGMA busy_timeout;

# Example on macOS/Linux: see which process holds the DB file
lsof app.db

Fix direction:

PRAGMA busy_timeout = 5000;
PRAGMA journal_mode = WAL;

• Keep write transactions short.
• Use a write queue inside the application if writes need serialization.
• Do not place SQLite database files on network shared folders.
• If multiple servers need to share one database, consider PostgreSQL or MySQL instead.

Case 2. Foreign keys are declared, but invalid rows are inserted

Symptoms: A table has a FOREIGN KEY, but a row without a matching parent row is inserted.

Cause: Foreign key checking is probably not enabled for that SQLite connection. Based on the official documentation, foreign keys must be enabled at runtime with PRAGMA foreign_keys = ON;, and that applies per connection.

Check:

PRAGMA foreign_keys;
PRAGMA foreign_key_check;

Fix:

PRAGMA foreign_keys = ON;

Run this in the initialization routine every time a connection is created. If you use an ORM, check the SQLite connection options or event hooks.

Case 3. A string enters a numeric column and sorting looks wrong

Symptoms: A column declared as INTEGER contains string values, and sorting or comparisons no longer behave as expected.

Cause: SQLite’s default type system is flexible. A column declaration alone does not strongly prevent every type mismatch.

Check:

SELECT id, age, typeof(age)
FROM users
WHERE typeof(age) <> 'integer';

Fix direction:

• Consider STRICT tables for new or migrated tables.
• Add validation at the application input layer.
• Before migration, inspect existing rows with typeof().

CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  age INTEGER CHECK (age >= 0)
) STRICT;

Case 4. A copied backup is missing recent data

Symptoms: You copied only the .db file while the application was running, and the restored database does not contain recent data.

Cause: In WAL mode, recent changes may still be in the -wal file. Copying only the active main database file may not produce a consistent backup.

Fix:

sqlite3 app.db ".backup backup.db"
sqlite3 backup.db "PRAGMA integrity_check;"

Alternatively, use the SQLite Online Backup API in the application. If raw file copying is unavoidable, close database connections and understand the checkpoint state first.

Conclusion

SQLite is much more practical than the short phrase “a lightweight database in one file” suggests. For local apps, mobile apps, embedded devices, tests, and small tools where data should stay close to the application, it can greatly reduce setup and operations work.

On the other hand, if many servers need to write to a central database at the same time, do not stretch SQLite into the wrong role. Choose a server database such as PostgreSQL or MySQL. The key to using SQLite well is to define its role early, initialize PRAGMA settings consistently, keep write transactions short, understand WAL, enable foreign keys, and plan backups correctly.

References

• 🔗 About SQLite
• 🔗 Appropriate Uses For SQLite
• 🔗 Datatypes In SQLite
• 🔗 STRICT Tables
• 🔗 Foreign Key Support
• 🔗 Write-Ahead Logging
• 🔗 File Locking And Concurrency
• 🔗 SQLite Autoincrement
• 🔗 Release History
• 🔗 Representative SQLite database is locked discussion
• 🔗 Python sqlite3 and concurrency

Related Posts

• 🔗 React Native Nitro and TurboModule: criteria for selecting native modules in the New Architecture
• 🔗 What is WatermelonDB in React Native? Offline-first local DB usage compared with SQLite