Create, Open, Inspect, Close, and Reopen Your First Database File

Learning outcomes

This lesson turns the architecture from Lesson 1 into something you can observe. You will begin in an empty directory, open a named SQLite database, create one tiny table, insert rows, leave the CLI process, and reopen the same file. The goal is not to learn much SQL yet; the goal is to see where persistent state lives.

Start with an empty lab directory

Do not run the first persistence experiment inside Downloads, Desktop clutter, or a production project. Use an empty directory so every file that appears has an explanation. The commands below create a user-owned lab directory. Pick the command for your shell.

New-Item -ItemType Directory -Force "$HOME\Documents\SQLiteCourse\chapter01\first-db" | Out-NullSet-Location "$HOME\Documents\SQLiteCourse\chapter01\first-db"Get-ChildItem -Force

mkdir -p "$HOME/sqlite-course/chapter01/first-db"cd "$HOME/sqlite-course/chapter01/first-db"ls -la

Before opening SQLite, the directory should contain no course database. Keep this terminal available: after important steps, you will inspect the filesystem again instead of assuming what happened.

Open a named database from the command line

Run the sqlite3 executable you verified in Lesson 2 and give it a filename:

sqlite3 fieldnotes_first.db

The SQLite CLI opens a connection whose primary database is named main. If the named database did not exist, the CLI is allowed to create it. Filesystem visibility can depend on when the operating system and SQLite first need to materialize the file, so the robust checkpoint is not “I saw a file at exactly this instruction.” The robust checkpoint is to ask the open connection which database it is using and then make a durable schema change.

SQLite version 3.53.4 ...Enter ".help" for usage hints.sqlite>

Your prompt may include the database filename or different decoration in newer CLI builds. Do not treat the prompt text as the authoritative identity of the database.

Your first safety habit: verify the connected file

Before changing data, run .databases. This is a CLI dot-command that lists databases attached to the current connection, including the primary main database and its path.

.databases

The important output is the filename associated with main. The exact columns can vary by CLI version, but you should see that main points to the intended fieldnotes_first.db path. If it does not, stop before issuing CREATE, INSERT, UPDATE, or DELETE.

SQL statements and dot-commands live at different layers

The CLI accepts a mixed stream of input. Ordinary SQL is sent to the SQLite library. A line beginning with a dot at the left margin is normally intercepted by the CLI itself.

If you type .tables inside application code later, the core SQLite library will not understand it as SQL. Applications query SQLite's schema tables or use their driver's APIs instead.

Create one tiny table and a few rows

We will intentionally keep the schema small. The table represents field observations, not the final course schema. Type the following SQL at the CLI prompt:

CREATE TABLE note (    note_id INTEGER PRIMARY KEY,    title   TEXT NOT NULL,    status  TEXT NOT NULL);INSERT INTO note (title, status) VALUES    ('Inspect pump 7', 'open'),    ('Photograph valve plate', 'done'),    ('Record vibration reading', 'open');SELECT note_id, title, statusFROM noteORDER BY note_id;

You should get three rows with identifiers 1, 2, and 3. The exact presentation depends on your CLI output mode. The durable facts are the table definition and rows, not whether the CLI draws a box around them.

note_id | title                    | status1       | Inspect pump 7           | open2       | Photograph valve plate   | done3       | Record vibration reading | open

Why did SQLite assign 1, 2, and 3? For this ordinary table, INTEGER PRIMARY KEY is tied to SQLite's rowid behavior. We will study rowids carefully in a later chapter; for now, you only need to observe the generated identifiers.

Inspect schema state with the CLI

Now use shell conveniences to inspect the same database without writing additional SQL:

.tables.schema note.databases

.tables should include note. .schema note should display a CREATE TABLE note ... statement representing the durable schema. .databases should still identify the intended file.

These commands are useful shortcuts. They do not mean the schema exists “inside the CLI.” The schema is database state; the CLI is merely showing it.

Close the process, reopen the file, and prove persistence

Leave the CLI using either documented exit command:

.quit# or, in a separate run:.exit

Back in your operating-system shell, list the directory. You should now see fieldnotes_first.db. Its exact byte size depends on page size, filesystem allocation, and build details, so this course does not require one magic size.

Reopen the same filename:

sqlite3 fieldnotes_first.db

Verify the path again, then query the rows:

.databases.tablesSELECT COUNT(*) AS note_count FROM note;SELECT title, status FROM note ORDER BY note_id;

The count should be 3 and the same three rows should return. That proves the table and rows live in persistent database storage. The earlier CLI process is gone; a new process opened the file and reconstructed access to that state.

Use .open carefully

You can change databases without leaving the CLI by using .open. The current CLI closes the existing primary connection and opens the named target. If the target filename does not exist, ordinary .open filename can create a new empty database.

.open fieldnotes_first.db.databases.tables

This convenience creates a classic failure mode: one mistyped character can point the shell at a different file. Current CLI builds provide .open --ifexists filename when you intend to open only an already-existing file. That option is a useful guardrail for inspection work.

.open --ifexists fieldnotes_first.db.databases

Failure lab: the typo that looks like missing data

Exit the real database, then deliberately make this harmless typo in the disposable lab directory:

sqlite3 fieldnote_first.db

Inside that shell, run:

.databases.tables

You will likely see an empty database because fieldnote_first.db is not fieldnotes_first.db. The original data did not disappear; you opened or created a different file.

Exit and list the directory. Two similarly named files make the mistake visible. Reopen the correct file using --ifexists or the correct command-line filename, verify .databases, and confirm the three rows.

Why extra -journal, -wal, or -shm files can appear

When quiescent, an ordinary SQLite database is often represented by one main file. During transactions, SQLite's journaling mode can create companion files. In rollback-journal mode you may temporarily see a file such as fieldnotes_first.db-journal. In write-ahead logging (WAL) mode, later chapters may show fieldnotes_first.db-wal and fieldnotes_first.db-shm.

Do not delete companion files while a database is active just because they look temporary. They can participate in transaction recovery and concurrency. Likewise, do not teach yourself that “backup” always means copying only the visible .db file during live writes. Chapter 16 gives this topic the depth it deserves.

Checkpoint: what belongs to the file and what belongs to the session?

Clean up the disposable experiment

Keep the real course database you will create in Lesson 5, but this first-file experiment is disposable. Exit all SQLite connections first. Then remove the deliberate typo file. You may also remove fieldnotes_first.db if you want to repeat the lab from zero.

Remove-Item .\fieldnote_first.db -ErrorAction SilentlyContinue# Optional reset:# Remove-Item .\fieldnotes_first.db -ErrorAction SilentlyContinue

rm -f ./fieldnote_first.db# Optional reset:# rm -f ./fieldnotes_first.db

Never generalize a lab cleanup command into a wildcard that can touch valuable databases. Explicit filenames are intentionally boring.

Summary and next lesson

You created a named database, proved that committed schema/data survive the lifetime of a CLI process, and learned to separate SQL from shell dot-commands. You also established a production-quality beginner habit: verify the connected main path before modifying data.

Next we zoom in one layer. You will learn what a connection and a statement represent, what “autocommit” means, and why a command that changes CLI formatting is fundamentally different from SQL that changes the database.

References

• Command Line Shell For SQLite.
• CREATE TABLE.
• INSERT.
• Temporary Files Used By SQLite.