Part 8

Databases: inspect structured state before you write it

A database can feel more intimidating than a file because the state is hidden behind a driver. The cure is the same terminal rhythm you already know: connect to a controlled target, inspect structure, preview data, make one bounded change and verify it.

A database is another form of structured state

Files expose their source directly. A database exposes state through an engine: tables or collections, records, indexes and queries. Do not start with a write. First confirm which driver and database you are using, then inspect what already exists.

The same habit from configuration files applies here: address the logical object you mean — a table, row, key or document — instead of thinking about how the data happens to be stored.

`db` is a common mirror; the driver still matters

db can select an installed driver and forwards common actions such as connect, list, inspect, preview, search, export and focused writes. The direct commands — sqlite, mysql, postgresql, redis, mongodb — keep the driver-specific contract visible.

Think of the common layer as vocabulary, not as a claim that all databases are the same. SQL tables, Redis keys and MongoDB documents have different models and different native capabilities.

install md db sqlite

db

db sqlite

sqlite

SQLite is the safest place to learn the database rhythm

SQLite stores a database in one local file, so it is ideal for a disposable exercise. Create data/notes.sqlite, then create one simple table through native SQL. After that, -t should show the table you just made.

This is intentionally local and credential-free. You can delete the practice database later like any other file, which makes it a much better first experiment than connecting to a production MySQL server.

cd lil-playground

md data

sqlite -c data/notes.sqlite -f

sqlite : CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL, status TEXT NOT NULL)

sqlite -t

Inspect structure, then preview rows

sqlite -s notes shows the columns and types. sqlite -n notes 10 previews rows. On an empty table the empty result is useful information: your schema exists, but no data has been added yet.

Make this order habitual. Knowing the fields before a write is the database equivalent of checking a destination path before a file move.

sqlite -s notes

sqlite -n notes 10

Focused writes can use JSON instead of hand-building SQL

For everyday insert and update operations, the driver accepts a JSON object after :. This keeps data values separate from the command grammar and avoids making you quote an entire SQL statement for a simple row change.

After each write, preview or search again. -a inserts, -w updates matching rows, and -d exists for deliberate deletion. Do not turn a delete into a reflex just because it is short.

sqlite -a notes : {"title":"First terminal note","status":"draft"}

sqlite -a notes : {"title":"Archive project","status":"done"}

sqlite -n notes 10

sqlite -w notes title "First terminal note" : {"status":"reviewed"}

sqlite -f notes reviewed 20

Native SQL is there when the common surface is not enough

A grouped aggregate such as SELECT status, COUNT(*) ... is naturally expressed in SQL. The : native form gives you that escape hatch without pretending the compact common commands cover every database task.

This boundary is healthy: use the concise structured operations for common work, and use native database language when the problem itself is genuinely database-specific.

sqlite : SELECT status, COUNT(*) AS count FROM notes GROUP BY status

An export answers a data question; a dump protects a larger state

Exporting notes to JSON gives you portable row data that other tools can read. dump records schema and data in a form intended for restoration. These are related but different artifacts, so name and store them accordingly.

A backup routine is incomplete until you have inspected the output and know how you would load it. Later, test restoration into a disposable database rather than waiting for an emergency.

sqlite -o notes -j data/notes.json -f

sqlite dump data/notes.sql -f

stat data/notes.sql

Remote databases add credentials, permissions and network state

MySQL/MariaDB and PostgreSQL connections introduce a host, user, database, network path and password prompt. Redis and MongoDB have their own connection models. That is why a local SQLite exercise comes first: it separates database habits from connection complexity.

Never paste real passwords into reusable scripts or public examples. Let interactive password prompts or deliberately designed secret storage handle credentials, and make production writes only after you have confirmed the selected database and target rows.

SQLitelocal filePractice, embedded data, small local state

MySQL / MariaDBremote SQLCommon web-hosting applications

PostgreSQLremote SQLRelational applications and richer SQL workloads

Rediskeys / valuesCache, ephemeral and fast application state

MongoDBdocumentsDocument-oriented application data

db mysql

mysql -c localhost app shop

postgresql -c localhost app shop

Keep this as a script

Save this as database-checkpoint.lil. It opens the disposable SQLite file, ensures the practice table exists, inspects it and writes a dump. The script intentionally does not insert or delete application data: repeatable automation should begin with checks and backups before it contains business-specific writes.

#lil
@install md sqlite
cd lil-playground
md data
sqlite -c data/notes.sqlite -f
sqlite : CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL, status TEXT NOT NULL)
sqlite -t
sqlite -n notes 10
sqlite dump data/notes.sql -f