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.

In the previous section, we explored individual pieces of SQLite’s internal architecture—the sqlite3 structure, schema objects, tables, indexes, and execution engine.

Now, it’s time to zoom out and see how all of these components actually interact in a real system.

This is where things start to feel less like isolated structures and more like a living system.

The Big Picture: Application ↔ SQLite

At the highest level, an application interacts with SQLite through two primary handles:

• sqlite3* → Represents a database connection
• sqlite3_stmt* → Represents a compiled SQL statement

Every API call, whether it’s preparing a query, executing it, or fetching results—flows through these two interfaces.

SQLite doesn’t just passively execute queries either.

It allows applications to register custom callback functions.

When needed, SQLite invokes these callbacks, enabling logic to run inside the application’s space.

This is how features like custom functions, collations, or hooks are implemented.

Multiple Connections, Multiple Databases

One subtle but powerful feature of SQLite is that an application is not limited to a single connection.

An application can:

• Open multiple sqlite3* connections
• Connect each one to different databases—or even the same database

Within a single connection, multiple databases can coexist:

• Main database
• Temporary database
• Attached databases

Each of these is represented internally by an entry in the Db array.

What’s important here is that each database is not just a logical entity—it has its own underlying storage machinery.

B-Tree and Pager: The Storage Backbone

Every database is accessed through a B-tree (or B+ tree) structure.

This is the core storage engine of SQLite.

Each database connection uses:

• A dedicated Btree object → Handles structural access to data
• A Pager object → Manages disk-level concerns

The Pager is where things get very real. It is responsible for:

• Tracking database file state
• Managing the journal (for transactions and rollback)
• Handling locks
• Maintaining the page cache

In shared cache mode, multiple B-tree instances can reuse the same Pager through a shared structure (BtShared).

This allows efficient memory usage when multiple connections access the same database file.

Transactions: Controlled and Isolated

Even though a connection may have multiple databases attached, SQLite enforces an important rule:

A single sqlite3 connection can have only one active transaction at a time.

However, that transaction can span multiple databases within the same connection.

SQLite dynamically starts transactions on individual databases depending on what the SQL statement requires.

This keeps operations efficient while still ensuring consistency.

From SQL to Execution: The Role of sqlite3_stmt

Every SQL statement you write is compiled into a bytecode program.

That compiled form is represented by a sqlite3_stmt*, backed internally by a VDBE (Virtual Database Engine) program.

When executed:

1. The VDBE interprets the bytecode
2. It opens cursors to access data
3. It performs operations like scan, insert, update, or delete

Cursors: Navigating the Data

Cursors are the bridge between execution and storage.

Each SQL statement maintains a set of VdbeCursor objects.

These are runtime constructs used by the VM to interact with data.

Here’s how they work:

• A VdbeCursor maps to a BtCursor
• A BtCursor operates on a single B-tree (table or index)
• It allows iteration over records

This design enables:

• Multiple cursors on the same table or index
• Independent traversal without interference

So even within a single query, SQLite can efficiently scan, filter, and join data using multiple cursors simultaneously.

Putting the Flow Together

Let’s trace a typical execution flow:

1. The application prepares a query → gets a sqlite3_stmt*
2. SQLite compiles it into VDBE bytecode
3. On execution:
   • VDBE opens cursors
   • Cursors access B-tree structures
   • B-tree interacts with Pager
   • Pager reads/writes pages from disk
4. Results flow back up to the application

All of this is coordinated through the sqlite3 connection object.

*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: