Create a SQLite database file

Opening a new path does not immediately write a database file. SQLite creates the real file when you write its first schema or data.

This design is deliberate. It lets programs open a database “just in case” without littering the disk with empty files. But it means the moment of creation is the first write, not the open.

Open the database:

sqlite3 notes.db

Create its first table:

CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
) STRICT;

The statement returns silently. In SQLite’s shell, no output means success — errors are always printed.

Run .databases to see the full path, then leave with .quit. notes.db now contains a valid SQLite header and schema.

Verify what you created

Back in your terminal, look at the file:

ls -l notes.db
# -rw-r--r--  1 flavio  staff  12288 Aug  3 10:12 notes.db
file notes.db
# notes.db: SQLite 3.x database, ...

The file command recognizes the SQLite header in the first 16 bytes. A real database is never zero bytes: the smallest one is a few kilobytes, because SQLite allocates whole pages (4096 bytes by default).

You can also confirm the schema survived by reopening and running .schema, which prints the CREATE TABLE statement back to you.

Two things not to do

Do not use touch to create a database. It only creates a zero-byte placeholder. SQLite will accept a zero-byte file and treat it as an empty database, but any tooling that checks the header sees an invalid file, and you gain nothing — SQLite creates the file properly on its own.

Also, do not treat an ordinary file copy as a safe backup while the database is active. A cp that runs mid-write can capture a half-applied transaction, and in WAL mode part of your committed data lives in a sidecar file next to the main one. We will make a consistent backup later in the course with tools built for exactly that.

One habit worth forming now: keep the database file in a directory your application owns, like a data/ folder, not in whatever directory you happened to launch the shell from. Relative paths are the main way people end up with three half-filled copies of the same database.