Understanding the Core Opcodes in SQLite

Hello, I'm Maneshwar. I'm working on git-lrc: a Git hook for Checking AI generated code.

Yesterday we saw the structure of a bytecode program and how a simple SELECT * FROM t1 gets compiled into a linear array of instructions.

Today, instead of looking at the program as a whole, we slow down and examine the individual instructions that make it work.

What looks like a simple query is actually a choreography of small, extremely precise operations.

Each opcode has narrowly defined semantics. Together, they form the behavior of SQL execution. Let’s walk through them.

1. Trace

Trace is not about data; it is about visibility.

When tracing is enabled through sqlite3_trace, this opcode outputs the UTF-8 string stored in P4 to the trace callback. If tracing is disabled, it effectively does nothing.

It exists purely for introspection. The VM checks a global tracing flag, and if enabled, emits diagnostic output.

This is another example of SQLite’s philosophy: make the internal program observable when needed, but keep runtime lean when not.

2. Goto

Goto is the simplest control-flow primitive: an unconditional jump.

The program counter is set to the address specified by P2. Execution resumes from there immediately.

No conditions. No register checks. Just raw redirection of control flow.

3. OpenRead

Now we begin touching storage.

OpenRead opens a read-only cursor on a B-tree whose root page is identified by P2. The database file is determined by P3:

• 0 → main database
• 1 → temp database
• > 1 → attached databases

P1 becomes the cursor identifier. From this point onward, instructions refer to this cursor by that number.

P4 is interesting:

• If it is an integer, it specifies the number of columns in the table.
• If it is a pointer to a KeyInfo structure, it describes index layout and collations.

There is also locking behavior embedded here. If the database is not yet locked, executing OpenRead attempts to acquire a shared lock. If that fails, execution terminates with SQLITE_BUSY.

So a single opcode is not merely “open a cursor.” It also establishes read isolation guarantees.

4. Rewind

After opening a cursor, you need to position it.

Rewind resets cursor P1 to the first entry in the tree — the smallest key.

If the tree is empty and P2 > 0, execution jumps to P2 immediately. Otherwise, execution falls through to the next instruction.

This is how a SELECT loop typically begins:

• Open cursor
• Rewind
• If empty, jump to Halt
• Otherwise, process rows

This conditional jump embedded in Rewind is how SQLite avoids separate “is empty?” checks.

5. Column

Column extracts the P2-th column from the record currently pointed to by cursor P1.

The extracted value is written into register P3.

Internally, the VM interprets the raw record format (which was constructed earlier using MakeRecord). If the record does not have that many columns, it produces NULL — unless P4 contains a default MEM value to use instead.

This is where raw B-tree payload bytes are decoded into meaningful typed values stored in registers.

So every time you reference t1.x, a Column opcode is doing the real work.

6. MakeRecord

MakeRecord does the reverse of Column.

It takes P2 registers starting from register P1 and packs them into a single serialized record. That record becomes suitable:

• As a table row payload
• Or as an index key

This is one of the most critical opcodes in the VM because it bridges logical row values and the physical record format stored inside B-trees.

Under the hood, it:

• Encodes serial types
• Builds the record header
• Serializes values into a contiguous byte sequence

Without MakeRecord, there is no insertion.

7. ResultRow

This opcode is the handoff point between SQLite internals and your application.

Registers P1 through P1+P2−1 represent a complete row. When ResultRow executes, sqlite3_step() returns SQLITE_ROW.

The VM pauses at this boundary. It resumes execution only when sqlite3_step() is called again.

So ResultRow marks a breakpoint in execution — a cooperative yield back to the caller.

8. Next

Iteration happens here.

Next advances cursor P1 to the next entry in the tree. If another entry exists, execution jumps immediately to P2. If not, it falls through.

This opcode, together with Rewind and Goto, forms the loop structure of table scans.

Conceptually:

• Rewind
• Process row
• Next
• Jump back

That is how a full table scan is implemented.

9. Close

Close shuts down cursor P1.

If the cursor is already closed, the instruction does nothing.

SQLite keeps cursor management explicit. Cursors are not automatically freed until either Close executes or the program halts.

10. Halt

Halt stops execution.

Before stopping, it:

• Closes all open cursors
• Cleans up RowSet objects and other temporary structures

P1 becomes the result code returned by sqlite3_exec, sqlite3_reset, or sqlite3_finalize.

For normal completion:

P1 = SQLITE_OK (0)

For errors, P1 carries the error code.

If P1 ≠ 0, then P2 determines rollback behavior:

• OE_Fail → do not rollback
• OE_Rollback → rollback entire transaction
• OE_Abort → undo changes from this statement only

If P4 is non-null, it contains the error message string.

There is always an implicit Halt 0 0 0 0 0 appended to the end of every program. Jumping past the last instruction is equivalent to halting successfully.

11. Transaction

This opcode begins a transaction on database P1:

• 0 → main
• 1 → temp
• > 1 → attached

Locking behavior depends on P2:

• 0 → shared lock (read transaction)
• non-zero → RESERVED lock (write transaction)
• ≥2 → EXCLUSIVE lock

Starting a write transaction also creates a rollback journal.

Notice how transaction control is just another opcode. There is nothing “special” about BEGIN or COMMIT at the VM level — they are compiled into instruction sequences like everything else.

12. VerifyCookie

This opcode protects against schema drift.

It checks that:

• The schema version cookie matches P2
• The local generation counter matches P3

If the cookie has changed (for example, another connection modified the schema), the VM detects it and forces a schema reload.

But this check only works after a transaction or a cursor-opening opcode establishes at least a shared lock. That ensures we are reading consistent metadata.

It is a subtle but critical guardrail.

13. TableLock

TableLock acquires a lock on a specific table:

• P1 → database number
• P2 → root page
• P3 → 0 for read, 1 for write
• P4 → pointer to table name

This opcode exists primarily for multi-connection coordination and ensures table-level locking semantics are honored when required.

Seeing the Bigger Pattern

When you zoom out, something beautiful appears.

A simple SELECT becomes:

• Start transaction
• Verify schema
• OpenRead
• Rewind
• Column
• ResultRow
• Next
• Halt

An INSERT becomes:

• Start transaction
• OpenWrite
• MakeRecord
• Insert
• Halt

There is no hidden machinery. Just deterministic execution of small instructions.

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.

👉 Check out: git-lrc
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…

View on GitHub