What Is SQLite?

What Is SQLite?

SQLite is a C-language library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. Unlike traditional database systems, there is no separate server process; the engine links directly into your application as a library. An entire database, including tables, indexes, schema definitions, and data, is stored in a single cross-platform file on disk.

The project was first released in 2000 by D. Richard Hipp and has been actively developed ever since. Its source code is in the public domain, and according to its official documentation it uses a stable, backward-compatible file format that works across platforms. Today mobile operating systems, browsers, and countless desktop applications store data with SQLite behind the scenes, which makes it one of the most widely deployed database engines in the world.

To see where SQLite fits within the wider database landscape, take a look at our guide to database types.

How the Serverless Architecture Works

Most databases follow a client-server model: a server process runs continuously in the background, and applications connect over a network or socket to send queries and receive results. SQLite inverts this approach. There is no separate server; your application calls the SQLite library inside its own process and reads and writes the database file directly.

This design removes network latency, the burden of running a separate service, and many configuration steps. In return, SQLite is positioned to run on a single machine, usually alongside a single application; multi-user access over a network is not its primary goal.

In practice, this means your application compiles in the SQLite library or pulls it in as a dependency. Queries are not sent to a separate process; they are handled in the same process through direct function calls. That keeps distribution simple: you are left with a single executable and a database file beside it. This simplicity makes SQLite especially attractive for software that runs on the end user's own machine.

One File, Zero Configuration

A SQLite database is a single, operating-system-independent file. Copying, moving, or emailing that file effectively moves the whole database. There is no administrator account to set up, no port to open, and no service to start; you just add the library to your program and point it at the file path.

• Portability — The file format is cross-platform; the same file opens on Windows, Linux, and macOS.
• Simple backups — When the database is idle, copying the file is often enough to back it up.
• Easy distribution — You can ship a ready-made data file along with your application.
• Low maintenance — There is no separate server, user, or connection pool to manage.

Storage Classes and Type Affinity

Unlike most SQL databases, SQLite uses dynamic typing. The type you assign to a column is not a strict constraint but an "affinity"; values are stored in their own storage classes. SQLite has five storage classes.

When you declare a column type, SQLite assigns an affinity such as TEXT, INTEGER, REAL, NUMERIC, or BLOB. For example, if you insert a numeric string into a column with INTEGER affinity, SQLite tries to convert it to an integer. For teams that want stricter behavior, SQLite also offers STRICT tables, which enforce column types.

Basic SQL Usage

SQLite supports a large portion of standard SQL. The example below shows creating a table, inserting a row, and running a query.

-- Create a table
CREATE TABLE articles (
  id       INTEGER PRIMARY KEY,
  title    TEXT NOT NULL,
  views    INTEGER DEFAULT 0,
  published TEXT   -- ISO 8601 date string
);

-- Insert data
INSERT INTO articles (title, published)
VALUES ('What Is SQLite?', '2026-09-28');

-- Query
SELECT id, title, views
FROM   articles
WHERE  views > 100
ORDER  BY published DESC;

SQLite also supports advanced features such as common table expressions (CTEs), window functions, JSON processing functions, full-text search (FTS5), and spatial indexing extensions like R-Tree. In some languages, such as Python, SQLite even ships as part of the standard library, so you can start storing data without installing an extra driver.

For quick work from the terminal, there is the official sqlite3 shell tool. With it you can open a database, list tables, run queries, and import or export data. There are also ready-made binding libraries for nearly every popular programming language, which makes SQLite easy to integrate into many different technology stacks.

Transactions and ACID Guarantees

SQLite is ACID compliant: transactions are atomic, consistent, isolated, and durable. This means a group of changes is either applied in full or not at all. Even in the event of a power loss or crash, the database returns to a consistent state.

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- Both updates persist together
-- On error: ROLLBACK; undoes everything

Concurrency and WAL Mode

By default SQLite uses a rollback journal. As an alternative, you can enable Write-Ahead Logging (WAL) mode. In WAL mode, readers can keep reading the database even while a writer is active, which improves concurrency for read-heavy workloads with occasional writes.

-- Enable WAL mode (once per database)
PRAGMA journal_mode = WAL;

-- Tune the durability/performance balance
PRAGMA synchronous = NORMAL;

When Is SQLite the Right Choice?

SQLite's own documentation positions it not as a replacement for a network database but as an alternative to direct file reads and writes (much like fopen). It is a strong choice in the following scenarios.

• Mobile and desktop apps — Local, on-device data storage.
• Embedded systems and IoT — Lightweight storage on resource-constrained devices.
• Application file format — Storing complex documents in a single structured file.
• Testing and development — Temporary or in-memory databases that need fast setup.
• Caching and the edge — Read-heavy, low-latency local data access.
• Small to medium sites — Projects whose traffic is mostly reads.

When to Choose a Server-Based Database

If your project requires multi-user access over a network, a high volume of concurrent writes, fine-grained user and role management, or horizontal scaling, a client-server database is a better fit. This is where engines like PostgreSQL or MySQL come in. An application can start with SQLite and migrate to a server-based database as it grows; most of your SQL knowledge carries over during that transition.

Licensing: Public Domain

SQLite's core source code has been released into the public domain, free of copyright, so anyone may use it freely for commercial or private purposes. For legal assurance, some organizations can purchase an optional, paid "warranty of title." The SQLite ecosystem also includes some optional, commercially licensed extensions, such as encryption.

Backup and Maintenance

Because SQLite is a single file, backups are usually simple. Copying the file while the database is idle may be enough; however, if the database is in active use, it is safer to use the command-line tools or online backup mechanisms to capture a consistent backup.

# Dump the whole database to a SQL script
sqlite3 data.db ".dump" > backup.sql

# Create an optimized copy (VACUUM INTO)
sqlite3 data.db "VACUUM INTO 'backup.db';"

Limitations and Things to Watch For

No database is ideal for every job. SQLite's deliberate design choices become limits in certain scenarios.

• Concurrent writes — One writer at a time; not suited to write-heavy, multi-user systems.
• Network access — Designed for local file access; sharing over network file systems can cause problems.
• User/role management — There is no built-in fine-grained access control like server databases; security lives in the application layer.
• Very large scale — Although the documented theoretical size limit is high, server-based engines are more appropriate for very large, multi-user systems.

If you are looking for a different approach, such as key-value oriented embedded storage, you might also evaluate alternatives like Berkeley DB.