SQLite: embedded database engine without server

sqlite3 --version

If you see a version number like `3.40.1`, SQLite is already installed and you can skip to step 3.

2. Install SQLite

On Debian/Ubuntu:

sudo apt update && sudo apt install sqlite3

On Fedora/RHEL:

sudo dnf install sqlite

This installs the `sqlite3` command-line tool including the underlying library.

3. Create a database

SQLite stores a complete database — tables, indexes, triggers, and data — in a single file. Create a new database with:

sqlite3 mydata.db

This command opens an interactive SQL shell and creates the file `mydata.db`. If the file already exists, SQLite opens the existing file.

4. Create a table and add data

Run the following SQL commands within the SQLite shell:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');

The first line creates a table with three columns. The next two lines add rows. SQLite supports standard SQL types: `INTEGER`, `TEXT`, `REAL`, `BLOB`, and `NULL`.

5. Exit the SQLite shell

Type `.exit` to close the interactive shell:

.exit

The database file `mydata.db` is now on disk. You can copy it, email it, or put it under Git version control — it is a single file.

6. Query the database without the interactive shell

You can also run SQL commands directly from the command line:

sqlite3 mydata.db "SELECT * FROM users;"

This displays all rows from the `users` table. This method is useful for scripts and automated tasks.

7. Use JSON extensions for document-like data

SQLite has supported JSON1 extensions since version 3.9.0. These allow you to store and query JSON data within relational tables:

sqlite3 mydata.db "CREATE TABLE documents (id INTEGER PRIMARY KEY, data JSON);"
sqlite3 mydata.db "INSERT INTO documents (data) VALUES ('{\"title\": \"Test\", \"tags\": [\"linux\", \"sqlite\"]}');"
sqlite3 mydata.db "SELECT json_extract(data, '$.title') FROM documents;"

This combines relational structure with the flexibility of document databases — without needing a separate NoSQL server.

8. Compare databases with sqldiff

SQLite includes a utility `sqldiff` that shows the difference between two database files:

sqldiff mydata.db mydata_backup.db

This generates an SQL script that transforms one database into the other. Useful for migrations and debugging schema changes.

Verification

Check that your database functions correctly:

sqlite3 mydata.db "PRAGMA integrity_check;"

This command should return `ok`. Additionally, check the table structure:

sqlite3 mydata.db ".schema"

This shows the CREATE TABLE statements for all tables in the database.

Common problems

1. Database is locked

SQLite allows only one writer at a time. If you encounter this, use WAL mode:

sqlite3 mydata.db "PRAGMA journal_mode=WAL;"

This allows concurrent reading and writing and reduces locking issues.

2. Version too old for JSON extensions

JSON1 extensions require SQLite 3.9.0 or newer. Upgrade on Debian/Ubuntu:

sudo apt update && sudo apt upgrade sqlite3

3. Flatpak installation of Air SQLite Manager does not work

If `flatpak install flathub com.gesturedevelop.AirSQLiteManager` fails, check whether Flatpak is configured:

flatpak remote-add --if-not-exists flathub https://flathub.org/repo/flathub.flatpakrepo

Then run the installation command again.

Background: why SQLite is not NoSQL

SQLite is relational and uses SQL. The term NoSQL first appeared in 1998, when Carlo Strozzi used it for a relational database without an SQL interface. In 2011, SQLite developer D. Richard Hipp announced plans for a NoSQL interface and UnQL, a superset of SQL for document databases. That interface never became mainstream in the official project. Instead, SQLite has offered JSON1 extensions since version 3.9.0, allowing you to store and query JSON documents within relational tables — a hybrid approach that suffices for most applications.

SQLite is written in C and runs in Firefox, Chrome, Android, iOS, and countless embedded systems. The first version appeared in August 2000, developed by D. Richard Hipp for the United States Navy. The storage layer evolved from gdbm to its own B-tree structure, making SQLite independent of external libraries. The brand name SQLite was registered in 2008. That combination of simplicity, backward compatibility, and a strict focus on stability explains why SQLite has endured for more than two decades — and why the NoSQL attempts of 2011, while not becoming a mainstream reality, raised questions about the boundary between relational and document-based that are still relevant for any developer choosing a database.

← Back to knowledge base