Build the Course Lab Database and Adopt Safe Working Habits

Learning outcomes

The first four lessons built your mental model and toolchain. This final lesson turns them into a working course workspace that you can reuse. We will keep the domain small enough for beginners but realistic enough to support later lessons about types, constraints, transactions, indexes, WAL, backup, application integration, and migrations.

The course domain: FieldNotes device maintenance

FieldNotes is a fictional maintenance application used by technicians who inspect devices at customer sites. It gives the course a consistent vocabulary: sites contain devices; technicians create maintenance notes; devices have service status; later chapters can add tasks, measurements, tags, JSON metadata, search, audit history, migrations, and application access.

For Chapter 01, we intentionally keep the schema modest. You are not expected to understand every future SQLite-specific design choice yet. The point is to create durable relational state that can evolve with the course.

Create a safe course workspace

A clean directory structure reduces two common beginner risks: accidentally modifying the wrong database and mixing generated outputs with source scripts. Keep labs under a dedicated user-owned folder, not beside production databases.

sqlite-course/├── databases/│   └── fieldnotes.db├── sql/│   ├── schema.sql│   └── seed.sql├── migrations/│   └── 001_initial.sql├── exports/├── backups/└── scratch/

You do not have to create every file today. The naming convention is the important part: database files use a clear .db suffix; reusable SQL uses .sql; migrations start with an ordered numeric prefix; exports state their format; backups include purpose or timestamp when they become operational artifacts.

Build fieldnotes.db

Create the directories, then open the database from the databases directory or use an explicit path. Verify .databases before writing.

sqlite3 databases/fieldnotes.db

Inside the CLI:

.databasesCREATE TABLE site (    site_id   INTEGER PRIMARY KEY,    site_name TEXT NOT NULL);CREATE TABLE device (    device_id   INTEGER PRIMARY KEY,    site_id     INTEGER NOT NULL,    device_code TEXT NOT NULL,    device_name TEXT NOT NULL,    status      TEXT NOT NULL,    FOREIGN KEY (site_id) REFERENCES site(site_id));CREATE TABLE maintenance_note (    note_id     INTEGER PRIMARY KEY,    device_id   INTEGER NOT NULL,    noted_at    TEXT NOT NULL,    note_text   TEXT NOT NULL,    FOREIGN KEY (device_id) REFERENCES device(device_id));INSERT INTO site (site_name) VALUES    ('North Plant'),    ('Harbor Lab');INSERT INTO device (site_id, device_code, device_name, status) VALUES    (1, 'PUMP-007', 'Cooling Water Pump 7', 'active'),    (1, 'FAN-014',  'Exhaust Fan 14',       'inspection_due'),    (2, 'SENS-003', 'Vibration Sensor 3',   'active');INSERT INTO maintenance_note (device_id, noted_at, note_text) VALUES    (1, '2026-08-12T08:30:00Z', 'Minor seal seepage; inspect next visit.'),    (2, '2026-08-12T09:15:00Z', 'Guard secure; bearing noise requires follow-up.'),    (3, '2026-08-12T10:05:00Z', 'Baseline vibration reading recorded.');SELECT d.device_code, d.device_name, d.status, s.site_nameFROM device AS dJOIN site AS s ON s.site_id = d.site_idORDER BY d.device_code;

The expected logical result contains three devices: FAN-014 and PUMP-007 at North Plant, and SENS-003 at Harbor Lab. The sort order follows device_code, not insertion order.

Tables:  site              2 rows  device            3 rows  maintenance_note  3 rowsKey relationships:  every device row points to a site_id  every maintenance_note row points to a device_id

Later chapters will strengthen this schema. For example, Chapter 3 will revisit types and rowids, Chapter 5 will deepen constraints, Chapter 8 will formalize transactions, and Chapter 10 will add workload-justified indexes. Starting simple lets you understand each improvement instead of inheriting a mysterious “perfect” schema.

Verify the database instead of trusting the setup script

Successful-looking input is not verification. Ask the database and CLI independent questions:

.databases.tables.schema site.schema device.schema maintenance_noteSELECT COUNT(*) AS site_count FROM site;SELECT COUNT(*) AS device_count FROM device;SELECT COUNT(*) AS note_count FROM maintenance_note;SELECT sqlite_version();PRAGMA compile_options;

At this point the expected counts are 2 sites, 3 devices, and 3 maintenance notes. If a count differs, do not continue blindly. Determine whether you ran the seed twice, opened the wrong file, or partially executed the setup.

Use .help as your first CLI documentation tool

The sqlite3 shell has many dot-commands. Memorizing all of them is a poor learning target because command sets and formatting features evolve. Instead, memorize how to discover help:

.help.help open.help mode.help databases

Then use the official SQLite documentation for details that matter to correctness, version behavior, or destructive options. A strong workflow is:

1. Run .help or .help TOPIC in the exact CLI build you are using.
2. Check .version and SELECT sqlite_version();.
3. Consult the current page on sqlite.org for the command or engine behavior.
4. Test on a disposable database before applying destructive or unfamiliar operations to valuable data.

What about .show? Treat CLI features as versioned

Older SQLite CLI versions commonly included .show to print several shell settings, and the course-generation specification explicitly asks you to recognize it. However, the current official CLI documentation for the 3.52/3.53-era formatter overhaul no longer lists .show among the documented dot-commands. This is a useful real example of why the course does not ask you to memorize a frozen command list.

On your installed shell, check capability rather than assuming:

.help show.show

If your build supports .show, use it as an inspection convenience and compare its output with commands such as .mode. If your current build reports that .show is unknown, that is not a database failure. Use .help, .mode, .databases, .version, and other documented per-setting commands instead. The mandatory Chapter 01 lab does not depend on .show.

Real file versus :memory:

SQLite can create a database whose contents live only in memory for the life of a connection. The CLI can open one by starting without a filename or by explicitly using :memory:.

Use :memory: when persistence is irrelevant and isolation is desired. Use a real disposable file when the lesson concerns SQLite as a file-based embedded database. Many concurrency and WAL tests later in the course specifically require file-backed databases and multiple connections.

Safe copying, backups, and experiments

A closed, quiescent database file is straightforward to copy. A live database can be more subtle because active transaction state may involve rollback-journal or WAL companion files. Therefore, the Chapter 01 rule is conservative:

1. For a simple lab copy, first close the CLI/application and ensure no other process is writing that database.
2. Copy the database to the backups or scratch area using an explicit filename.
3. Perform risky experiments on the copy, not on your only valuable file.
4. Before later production-like work, use SQLite-aware backup mechanisms taught in Chapter 16 instead of assuming a live file copy is sufficient.

Copy-Item .\databases\fieldnotes.db .\backups\fieldnotes_before_experiment.db

cp ./databases/fieldnotes.db ./backups/fieldnotes_before_experiment.db

A backup you have never opened or restored is only an assumption. Later chapters add integrity checks and restore drills.

Failure cases to practice now

You accidentally run the seed twice. Because this beginner schema does not yet protect every natural identifier with uniqueness constraints, duplicate data may be inserted. That is intentional teaching material: compare counts, restore the clean lab copy, and later learn how constraints prevent entire classes of mistakes.

You copy a database while another process is actively writing it. Stop and use a safe backup method or quiesce/close the writer. Do not assume copying only the main file captures a consistent live state.

You use :memory: for a persistence lab and are surprised that data vanishes. That behavior is the point of an in-memory database. Repeat the lab with a named file.

You cannot find a remembered dot-command. Run .help in your actual shell and check current official documentation. Do not download an old CLI solely to match an outdated tutorial screenshot.

Your working directory is ambiguous. Use .databases and explicit paths. Relative paths are convenient only when you know which directory your process started in.

End-of-chapter verification checklist

Bridge to Chapter 2

Chapter 01 deliberately kept the CLI small: open a database, verify it, inspect tables/schema, get help, and exit. Chapter 2 turns the sqlite3 shell into a productive tool. You will learn current output modes, readable formatting, script execution with .read, batch behavior and exit status, parameters, import/export, and reproducible data exchange.

The database you just built gives those commands something meaningful to operate on.

References

• Command Line Shell For SQLite.
• Official SQLite downloads.
• In-Memory Databases.
• Temporary Files Used By SQLite.
• SQLite Online Backup API — preview for later backup lessons.