Hello, I'm Maneshwar. I'm building git-lrc, an AI code reviewer that runs on every commit. It is free, unlimited, and source-available on Github. Star Us to help devs discover the project. Do give it a try and share your feedback for improving the product.
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:
/var/tmp/usr/tmp/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.).
*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.
git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*
Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.
⭐ Star it on GitHub:
HexmosTech
/
git-lrc
Free, Unlimited AI Code Reviews That Run on Commit
git-lrc
Free, Unlimited AI Code Reviews That Run on Commit
AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.
git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.
See It In Action
See git-lrc catch serious security issues such as leaked credentials, expensive cloud operations, and sensitive material in log statements
git-lrc-intro-60s.mp4
Why
- 🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
- 🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
- 🔁 Build a habit, ship better code. Regular review → fewer bugs → more robust code → better results in your team.
- 🔗 Why git? Git is universal. Every editor, every IDE, every AI…
Top comments (0)