What Is SQLite? The Embedded SQL Database Explained

SQLite Is a Database Inside Your Program

Most databases you've heard of — MySQL, Postgres, SQL Server — run as a separate program. You start a server, it listens on a port, and your application connects over the network to ask it questions. SQLite throws that whole model out.

SQLite is a library. You link it into your program, and it gives you a SQL database that lives in a single file on disk. There's no server, no port, no daemon, no pg_ctl start. Your application opens a file, and that file is the database.

sqlite3 mydata.db

That command opens (or creates) a file called mydata.db and gives you a SQL prompt. Everything you do — tables, rows, indexes — gets stored in that one file. Copy it to another machine and the database goes with it. Delete it and the database is gone.

A Quick Tour of the SQL

The SQL itself looks like SQL anywhere else. If you've used another database, almost everything will feel familiar:

CREATE TABLE books (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    author TEXT NOT NULL,
    year   INTEGER
);

INSERT INTO books (title, author, year) VALUES
    ('The Left Hand of Darkness', 'Ursula K. Le Guin', 1969),
    ('Project Hail Mary',         'Andy Weir',         2021),
    ('Piranesi',                  'Susanna Clarke',    2020);

SELECT title, year FROM books ORDER BY year DESC;

Standard SQL — CREATE TABLE, INSERT, SELECT, ORDER BY. SQLite supports the parts of the SQL standard you actually use day-to-day, plus modern niceties like CTEs, window functions, and JSON functions. The differences from Postgres or MySQL are mostly around types and a few syntax quirks, which we'll cover later.

What "Embedded" and "Serverless" Actually Mean

Two words come up constantly with SQLite. They're worth getting straight.

Embedded means SQLite runs in the same process as your application. There's no separate database process. When your Python script calls sqlite3.connect("data.db"), the SQL engine is running inside your Python process, reading and writing the file directly.

Serverless means there's no server to install, configure, run, secure, or back up. Compare the steps to start using a database:

• Postgres: install Postgres, start the service, create a user, create a database, configure pg_hba.conf, connect over TCP.
• SQLite: open a file.

That's the whole difference. Less power, much less ceremony.

The Whole Database Is One File

This is the bit that surprises people. The file format is documented and stable — a .db (or .sqlite, or .sqlite3, the extension is just convention) file you can:

• Email to a coworker.
• Commit to git (small ones, anyway).
• Copy with cp for an instant backup.
• Open in any SQLite tool on any operating system.

ls -lh mydata.db
# -rw-r--r--  1 you  staff   28K  Apr 23 14:02 mydata.db

That single file holds your tables, your indexes, your schema, and your data. SQLite databases on disk are byte-for-byte identical across Windows, macOS, Linux, iOS, and Android. The format is so stable that the U.S. Library of Congress recommends it for long-term data preservation.

Where You're Already Using SQLite

You almost certainly have hundreds of SQLite databases on the device you're reading this on. It powers:

• iOS and Android system storage and many apps on both platforms.
• Firefox, Chrome, and Safari (history, bookmarks, cookies).
• macOS (Mail, Photos, the dock).
• Most Linux desktop apps that need local storage.
• Skype, WhatsApp, Signal — chat history.
• Adobe Lightroom catalogs, Dropbox metadata, Steam libraries.

It's been called the most widely deployed database engine in the world, and that's probably true. The reason is simple: when an application needs structured local storage, SQLite is the path of least resistance.

What SQLite Is Not

It's not a client-server database. Two programs on different machines can't both connect to a SQLite database over the network — there's no network layer to connect to. If you need that, you want Postgres or MySQL.

It's not built for high write concurrency. SQLite uses file-level locking (with some clever optimizations in WAL mode), so while many readers can work in parallel, only one writer at a time gets to commit. For a single-user app or a low-traffic website, this is a non-issue. For a multi-tenant SaaS taking thousands of writes per second, it's the wrong tool.

It doesn't manage users or permissions. Database access is file access — whoever can read the file can read the data. That's fine for an app that owns its own database file. It's not fine for a shared multi-tenant setup.

Why You'd Choose It

The trade is simplicity for some scaling headroom you may never need:

• Zero setup. No service to run. No version mismatch between dev and prod. No "the database is down" because there is no database server.
• Fast. For most workloads, especially read-heavy ones, SQLite is faster than a network database — there's no socket round-trip on every query.
• Reliable. It's tested obsessively. The SQLite project has more test code than source code by a wide margin, and the format has been stable since 2004.
• Public domain. Free for any use, including commercial. No license to read.
• Portable. One file, every platform.

For local apps, prototypes, embedded devices, CLI tools, test suites, data analysis scripts, and small-to-medium websites, SQLite is often the correct default — not a stepping stone to a "real" database.

Next: SQLite vs MySQL

A natural next question is how SQLite stacks up against the database servers you've probably heard more about. We'll start with MySQL — where each one wins, and how to tell which fits the project in front of you.