DailyRNG
Login
Basic

The file that is not a file

I opened a database expecting to see a folder of records. I saw one file. The whole database, every table and every row, lived inside that single file, and the query tool was what made it readable.

What you will see

id|name 1|Ada sqlite>

About 2 min of reading

Try It

Turn a plain file into a database, draw one table, and prove the data really landed inside it.

1

Start the prompt on a file name you choose. If the file is missing sqlite makes an empty one, so there is nothing to set up first.

sqlite3 shop.db

Before you run it, say out loud what it will print. Then open the answer.

What it prints
SQLite version 3.53.4 2025-01-01 Enter ".help" for usage hints. sqlite>
2

Confirm the database is empty. A fresh file has no tables, so the dot command prints nothing at all. That blank is the honest answer.

.tables

Before you run it, say out loud what it will print. Then open the answer.

What it prints
sqlite>
3

Draw your first table. A table needs a name and columns, each with a type. INTEGER for whole numbers, TEXT for words. This is the box, not the contents yet.

CREATE TABLE users (id INTEGER, name TEXT);

Before you run it, say out loud what it will print. Then open the answer.

What it prints
sqlite>
4

Now the table exists but holds zero rows. List the tables again and you see the name appear. The box is there, the box is empty.

.tables

Before you run it, say out loud what it will print. Then open the answer.

What it prints
users sqlite>
5

Put one row in. INSERT names the table and the values in column order. This is the first real data inside the file.

INSERT INTO users (id, name) VALUES (1, "Ada");

Before you run it, say out loud what it will print. Then open the answer.

What it prints
sqlite>
6

Read it back. SELECT asks the database a question. This one asks for everything in users, and your one row comes back under its column names.

SELECT * FROM users;

Before you run it, say out loud what it will print. Then open the answer.

What it prints
id|name 1|Ada sqlite>

Data Flow

A single file on disk becomes a database the moment a tool opens it, and every table you draw lives inside that file.
1 You have a .db file on disk
↓
2 sqlite3 opens it as a database
↓
3 You create tables inside it
↓
4 Rows are stored in the file
Why this works
One file, many tables

A database is not a folder of files. It is one file that holds many named tables inside it. Each table is a grid of rows and columns. sqlite keeps them all in the one .db file and knows which rows belong to which table. When you delete that file you delete the whole database at once.

Two kinds of command

At the sqlite prompt there are two things you can type, and mixing them up is the first confusion.

  • SQL statements start with a keyword like SELECT or CREATE and end with a semicolon.
  • Dot commands start with a dot like .tables or .quit and control the tool itself.

.tables lists what exists. SELECT asks a question about the data. They are different languages and the dot is the whole difference.

Examples

A database is one file

sqlite3 shop.db

That command opens a prompt. It does not create a folder, does not pick a location, does not ask for a format. shop.db is a single file on disk, and if it does not exist yet sqlite creates an empty one. Everything you store from here goes inside that file.

Ask it what is inside

.tables

This is a dot command, not SQL. It asks the tool, not the database. On a brand new file it prints nothing, because there are no tables yet. A table is the box you put rows into, and you have not drawn one.

Leave without deleting

.quit

Quitting the prompt never touches the file. The data stays exactly where it was. You reopen it later with the same command and the same contents. The file is the database, the prompt is just a window into it.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

Next Lesson
Asking a question
Asking a question →