Inside SQLite: Naming files

Hello, I'm Maneshwar. I'm working on FreeDevTools online currently building **one place for all dev tools, cheat codes, and TLDRs* — a free, open-source hub where developers can quickly find and use tools without any hassle of searching all over the internet.*

This chapter zooms all the way down to SQLite’s lowest level—where bytes on disk become pages, pages become trees, and durability is enforced through journaling.

Instead of thinking in terms of tables and rows, we look at how SQLite physically organizes database and journal files, how those files are named, and how different database connections relate to one another.

If you’ve ever wondered what actually happens when you call sqlite3_open(), this is where the story becomes concrete.

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 ("")
• 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.

Instead, you get two completely independent databases living in the process address space.

Just like temporary databases, these 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 temp 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.

However, 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.

My experiments and hands-on executions related to SQLite will live here: lovestaco/sqlite

References:

SQLite Database System: Design and Implementation. N.p.: Sibsankar Haldar, (n.d.).

👉 Check out: FreeDevTools

Any feedback or contributors are welcome!

It’s online, open-source, and ready for anyone to use.

⭐ Star it on GitHub: freedevtools