Installing & Using SQLite

sqlite1-1 claimed there's no server to install. This chapter makes that claim concrete.

There's No Server to Install

mysql1 walked through installing a MySQL server package, starting the service, and then connecting a client to it. postgres1-2 walked through installing Postgres, running initdb, starting the service, and connecting via psql. SQLite has no equivalent first step at all — the sqlite3 command-line tool doesn't connect to anything running; it opens or creates a file directly.

The sqlite3 CLI

sqlite3 mydata.db

That single command opens mydata.db if it already exists, or creates a brand-new, empty database at that path if it doesn't — and immediately drops into an interactive prompt. No authentication step, no host or port to specify.

Basic meta-commands, deliberately echoing postgres1-2's own backslash-prefixed commands but using a dot prefix instead:

Creating and Populating a Database

sqlite3 notes.db
CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('First real note');
SELECT * FROM notes;

That's the entire workflow, start to finish — no separate "create the database" step distinct from creating the file itself.

In-Memory Databases

A genuinely useful, distinct mode: sqlite3 :memory: (or the special string ":memory:" passed in application code) creates a database that exists only in RAM, never touching disk at all, and disappears completely the moment the connection closes.

This matters practically for two real reasons: it's extremely fast, with no disk I/O at all, and it's a genuinely common real-world pattern in automated testing — spinning up a fresh, empty in-memory database for each test run guarantees complete isolation between runs with zero cleanup required, since the "database" simply ceases to exist the moment the test process ends. This mode obviously can't help where persistence across restarts actually matters — a limitation worth naming now and covered properly in sqlite1-9.

Opening a Database From Application Code

Most language bindings open a SQLite database with nothing more than a file path string — Python's sqlite3.connect('mydata.db') is a representative example (covered more fully in sqlite1-8). No host, no port, no username, no password, no connection-pool configuration — a direct, concrete fulfillment of sqlite1-1's own "near-instant, no handshake" claim.

Side-by-Side — The Full Workflow Compared

Hands-On Exercises