Creating & Opening Databases

Prev Next

If you've used MySQL or PostgreSQL before, you're used to running a CREATE DATABASE statement before you can do anything else. SQLite has no such statement, because a SQLite "database" isn't a thing the engine manages internally — it's just a file. Understanding exactly when that file comes into being clears up a lot of confusion for people new to SQLite.

A Database Is Created By Connecting to It

When you point the sqlite3 CLI (or a library) at a filename that doesn't exist yet, SQLite doesn't create the file immediately — it creates it the first time something is actually written to it, such as the first CREATE TABLE:

sqlite3 recipebox.db

-- recipebox.db does not exist on disk yet at this point --
CREATE TABLE recipes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);
-- ...and now it does. The file was created the moment we wrote to it.

There's no separate provisioning step, no database name to register anywhere, and no permissions to grant on a fresh database — the filesystem's own file permissions are all that apply.

Note: If you only ever open a database and run read-only queries against a filename that doesn't exist, SQLite may still leave no file behind, since nothing was ever written. The file truly only appears once data (or at least a schema object) is written.

The :memory: In-Memory Database, Revisited

The special filename :memory:, introduced in the last lesson, tells SQLite to skip the disk entirely and keep the whole database in RAM for the lifetime of that one connection. It's genuinely useful beyond just experimenting at the CLI — automated test suites commonly open a fresh :memory: RecipeBox database for every test run, so each test starts from a perfectly clean, empty schema with zero disk cleanup required afterward:

sqlite3 :memory:

Once that connection closes, everything written to it is gone — nothing was ever on disk to begin with.

A Database You Can Just... Copy

Because the entire database is one file, backing it up, moving it, or duplicating it for a test environment can be as simple as copying that file:

cp recipebox.db recipebox-backup.db

This works safely whenever nothing is actively writing to the database at the moment you copy it. If the app might be writing concurrently, a plain file copy risks grabbing the database mid-write and producing a corrupt or inconsistent copy — SQLite provides proper mechanisms for that case (the .backup dot-command and the Online Backup API), covered in full in Backup, VACUUM & Maintenance later in this track. For now, just remember: "it's one file" is what makes SQLite databases so easy to move around in the first place.

Checking What You're Connected To

Two dot-commands are handy for confirming exactly which file (or memory database) you're currently working with and what it contains:

.databases   -- shows the file path of the currently open database
.tables      -- lists the tables that exist in it so far

Next up: Now that you know how the database file itself comes to exist, Data Types & Type Affinity covers what actually goes inside it — and one of SQLite's most distinctive behaviors.

Prev Next