Inside SQLite: Naming files

Source: Dev.to

One Database, One File

At its core, an SQLite database is exactly one file.

There is no directory full of metadata, no background daemon maintaining state elsewhere.

The database is the file and nothing more, nothing less.

When an application opens a database using sqlite3_open, it simply passes a file name:

• It can be a relative path (based on the current working directory), or
• An absolute path starting from the root of the filesystem.

Any file name that the underlying operating system accepts is valid. SQLite doesn’t impose its own naming rules, except in two special cases.

The Two Special Database Names

SQLite treats certain “file names” as signals rather than literal paths.

1. Temporary Databases (NULL or Empty Names)

If the file name passed to sqlite3_open is:

• a NULL pointer,
• an empty string (""), or
• a string consisting only of whitespace,

SQLite creates a temporary database. Internally it still uses a file, but it tries to keep as much data in memory as possible. Each such open call gets its own temporary database—no sharing, no reuse. These databases are ephemeral; once the connection is closed, SQLite deletes them automatically.

2. In-Memory Databases (:memory:)

If the file name is exactly ":memory:", SQLite creates a pure in‑memory database:

• No files on disk
• No persistence across connections

Opening ":memory:" twice does not give you two handles to the same database; you get two completely independent databases living in the process address space. Like temporary databases, they vanish the moment the connection is closed.

How SQLite Names Temporary Files

When SQLite does create temporary files, it chooses names randomly:

• Prefix: etilqs_
• Followed by 16 random alphanumeric characters
• No file extension

You can customize the prefix at compile time using the SQLITE_TEMP_FILE_PREFIX macro.

By default, SQLite searches for a temporary directory in this order:

1. /var/tmp
2. /usr/tmp
3. /tmp

You can override this behavior by setting the TMPDIR environment variable.

Temporary files are deleted when closed properly. If the application or system crashes, those files may linger around—harmless but orphaned.

Main and Temp Databases: Two Names You Should Know

Every SQLite connection has at least two internal databases:

• main – the primary database file you opened
• temp – a separate temporary database associated with that connection

These are internal names, not file names, and they matter in SQL. For example:

SELECT * FROM temp.table1;

queries table1 from the temporary database, not from the main database.

Each SQLite library connection gets its own temp database. Even if two connections open the same main database file, their temp databases are completely isolated.

The temp database is only materialized on disk when you first create a temporary object, such as:

CREATE TEMP TABLE table1 (...);

Once the connection closes, SQLite deletes the temp database file automatically. The structure of the temp database mirrors the structure of the main database (same page layout, same internal organization). Manually touching these temp files (renaming, deleting, editing) is a fast path to corruption—SQLite expects exclusive control.

References

• SQLite Database System: Design and Implementation. Sibsankar Haldar
• GitHub examples: