Sqlite is a seriously cool technology. It allows you to put a database in a file, and essentially let your filesystem server act as the database server; there’s no need for a separate database process.
It’s also a pioneer in publishing its code to the public domain (cool).
It comes preinstalled on macOS. If you’re on a debian-based OS:
sudo apt install sqlite3
sqlite comes with a command line interface called sqlite3.
If you run it with no arguments it’ll connect to an in-memory database:
$ sqlite3
SQLite version 3.49.0 2025-02-06 11:55:18
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
As it says, you can connect to a persistent database with .open:
sqlite> .open test.db
Or just invoke it with a database argument:
$ sqlite3 test.db
SQLite version 3.49.0 2025-02-06 11:55:18
Enter ".help" for usage hints.
sqlite>
The cool thing about sqlite is that the entire database lives in 1 file. That means copying a database to a new host is as easy as copying the file to another host, deleting a database is as easy as removing a file, and connecting to a database is as easy as opening a file.
When I was getting started with sql, it helped to have a spreadsheet analogy:
This analogy works really well for sqlite since the database is a single file, just like the spreadsheet!
Back to the usage, let’s create a simple table to store some data. Since sqlite doesn’t enforce types on columns, we can set up a simple key-value datastore using sqlite:
sqlite> create table keyvalue(key text primary key, value anything);
Add a record:
sqlite> insert into keyvalue(key, value) values ("a", 3);
And retrieve the record:
sqlite> select * from keyvalue;
a|3
Now when you exit sqlite (control+d or .q) you’ll see your test.db file:
$ ls
test.db
And you can run queries against your file by putting the sql statement as an argument:
$ sqlite3 test.db 'select value from keyvalue where key = "a"'
3
More common than querying the database from the commandline is querying it from an application, such as a python web application.
# db.py
import sqlite3
db = sqlite3.connect('test.db')
cursor = db.cursor()
query = cursor.execute('select value from keyvalue where key = ?', 'a')
print(query.fetchone())
Result:
$ python db.py
(3,)
sqlite source code is managed using fossil and is mirrored on Github:
https://github.com/sqlite/sqlite