What SQLite is

SQLite runs on your phone, in your browser, in your desktop apps — and almost no one who uses it knows it's there. That's exactly the point.

This article explains what SQLite is, why you'd choose it over a server database, and what the maker's NoSQL plans did and didn't deliver. For those considering using SQLite, or just wanting to understand what's under the hood.

Requirements

• SQLite 3.x — the version that has been the standard since 2004. The current release is 3.53.x.
• The `sqlite3` command-line tool. On Debian/Ubuntu you install it with `sudo apt install sqlite3`.
• Basic knowledge of SQL. Anyone who has never seen tables and queries should first read an introduction elsewhere.

What SQLite is

SQLite is a relational database engine that doesn't run a server. No separate process, no network port, no configuration directory. The entire database is a single file on disk, and your application talks to it directly via an embedded library.

That differs fundamentally from MySQL or PostgreSQL. Those run as standalone server processes that client applications connect to. SQLite isn't a client-server system — it's a library you link to your program. The database engine runs in the same process as your application.

The origin lies with D. Richard Hipp, who designed the engine in the spring of 2000 while working on contract for the United States Navy. The first version, SQLite 1.0, appeared on August 17, 2000 as an extension for the Tcl scripting language. The first release of the 3.x line followed on June 18, 2004 with a revised file format, better Unicode support, and more efficient storage. Since then, 3.x has been the only version that still matters.

It's written in C and runs on almost any platform — from microcontrollers to servers. You don't need to install or configure anything. You link the library and you have a working SQL database.

Why you'd want SQLite

The big advantage is the absence of management. No database server that has to run, no user accounts, no network configuration, no backup procedure for a separate service. The database is a single file. Copy that file and you have a backup. Move it and you've moved your database.

That makes SQLite the right choice for local storage: desktop applications, mobile apps, embedded systems, development tools. Anywhere where one process at a time needs access to the data, and where you don't want to maintain a server.

It's not the right choice for web applications with many concurrent writers. SQLite does support multiple readers at once, but writing is globally locked — one writer at a time. For a busy web application with many concurrent writes, you need a server database. For a single-user desktop app, SQLite is faster and simpler than any server database.

A common rule of thumb: if your application runs on one machine and one process uses the database, SQLite is often the better choice. If multiple machines or processes need to write at the same time, then not.

Working with sqlite3

The `sqlite3` tool gives you an interactive session. Open or create a database with:

sqlite3 mydata.db

This command creates `mydata.db` if the file doesn't exist yet, and opens it otherwise. You're now in a SQL session. Create a table and add data:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO users (name) VALUES ('alice'), ('bob');
SELECT * FROM users;

No server running, no connection string, no port. The file is the database.

You can also run a query directly without an interactive session:

sqlite3 mydata.db "SELECT * FROM users;"

That's handy for scripts and cronjobs.

ACID without a server

SQLite supports the core of SQL: tables, indexes, transactions, triggers, views. And it guarantees ACID — atomicity, consistency, isolation, and durability. Even if power fails mid-transaction, the database stays consistent. That's built into the engine itself, not dependent on a server being shut down cleanly.

That reliability is the reason SQLite is in financial and medical software. An example: AloxBook, a Linux desktop app for personal financial bookkeeping, stores everything in SQLite. Without a password, the book is an unencrypted SQLite file. With a password, AloxBook uses SQLCipher — an encrypted variant of SQLite with AES-256 encryption. The same engine, two security levels.

Utilities

Besides `sqlite3`, SQLite includes `sqldiff`. That shows the differences between two SQLite databases and generates a SQL script that transforms one database into the other. Handy for migrations or checking changes between development and production environments.

sqldiff --changeset file1.db file2.db

The NoSQL plans that never became reality

In 2011, D. Richard Hipp announced a NoSQL interface for SQLite. At the same time, he introduced UnQL, a functional superset of SQL for document-oriented databases. The background was clear: NoSQL databases like MongoDB and CouchDB were gaining ground, and those fit semi-structured or hierarchical data better than a rigid relational model.

It is important to emphasize what did not happen. SQLite did not become a NoSQL database. There is no evidence that the announced NoSQL interface was ever fully implemented or released in the official codebase. UnQL remained a concept. The sources point to plans and ideas, not a functional NoSQL version.

What did happen: the `json1` extension. With it, you can store, search, and manipulate JSON data within SQLite's relational context. Instead of building a NoSQL engine, SQLite integrated document functionality into the relational model.

An example of JSON usage in SQLite:

CREATE TABLE events (id INTEGER PRIMARY KEY, payload JSON);
INSERT INTO events (payload) VALUES ('{"type": "click", "page": "/home"}');
SELECT payload ->> '$.type' FROM events WHERE payload ->> '$.page' = '/home';

You get document flexibility without giving up the relational foundation. That does cost something: you lose the ability to index on nested JSON fields the way you would on columns. Anyone running many queries on JSON fields will notice that.

Maintenance and tools

SQLite is not a dormant project. Releases are publicly dated and appear regularly. Version 3.53.0 is dated April 9, 2026, 3.53.1 on May 5, 2026, 3.53.2 on June 3, 2026, and 3.53.4 on July 24, 2026. The developers keep improving the engine, even though it often looks the same from the outside.

For those who do not want to work with the command line, there are graphical tools. Air SQLite Manager, developed by gesturedevelop, lets you browse tables, edit rows, and run queries without a terminal. It is available via Flatpak, which makes installation on various Linux distributions easy:

flatpak install flathub io.github.gesturedevelop.AirSQLiteManager

Such tools lower the barrier, but they change nothing about what SQLite is: an embedded database that requires no server.

Verification

Check whether SQLite works correctly and which version you have:

sqlite3 --version

This shows the version number, for example `3.53.4 2026-07-24`. That confirms the tool is installed and which functionality you have at your disposal.

Frequently asked problems

Database is locked

SQLite allows only one writer at a time. If another process holds the database for writing, you get this error. Solution: make sure your application keeps transactions short, use `busy_timeout` to wait for a release, or reconsider SQLite if you expect many concurrent writes.

Database file is corrupt after a crash

SQLite is designed to survive crashes, but a database on a faulty disk or an interrupted copy operation can become damaged. Check with:

sqlite3 mydata.db "PRAGMA integrity_check;"

This command returns `ok` if the database is intact. In case of damage, you can often rescue data with `.dump` and rebuild a new database — but that only works if the damage is limited.

JSON queries do not work

The `json1` extension is enabled by default in most SQLite builds, but not in all. Check with:

sqlite3 mydata.db "SELECT json_valid('{}');"

If this returns `1`, the extension is available. If it returns an error, you need a build with `json1` enabled.

The choice between SQLite and a server database is not a matter of better or worse. It is the question of whether you want to manage a server. SQLite lets you avoid that question — until the moment your application grows and you need a server after all. By then, you will have a database file that you can easily export to PostgreSQL or MySQL. That is not a coincidence, but by design.