Row Storage — Storing Rows as Tuples

Every table you've built so far identifies rows only by their position in a list — fine for a table scan, but fragile once rows are deleted or reordered, and it gives you no stable way to refer to "this exact row" from outside. Real engines solve this with a hidden, auto-incrementing integer key attached to every row: SQLite calls it rowid, and it's the mechanism this lesson asks you to build.

What rowid is (and isn't)

rowid is metadata the engine maintains, not something the user declares in CREATE TABLE. Even though CREATE TABLE users (name TEXT, age INTEGER) never mentions it, every row gets one automatically, starting at 1 and incrementing with each INSERT:

CREATE TABLE users (name TEXT, age INTEGER)
INSERT INTO users VALUES ('Alice', 30)   -- gets rowid 1
INSERT INTO users VALUES ('Bob', 25)     -- gets rowid 2

Implementation approach

Attach a per-table counter alongside the row list, and store each row with its rowid rather than as a bare value tuple:

class Table:
    def __init__(self, name, columns):
        self.name = name
        self.columns = columns      # [(name, type), ...]
        self.rows = []              # list of (rowid, [values...])
        self.next_rowid = 1

def insert(table, values):
    table.rows.append((table.next_rowid, values))
    table.next_rowid += 1

Note that next_rowid only ever increases — even if you later add DELETE, a deleted row's id is not reused. This matters: if rowid were reassigned after deletion, an external reference to "row 2" could silently start pointing at different data. Auto-increment counters in real databases behave the same way for exactly this reason.

Exposing rowid in queries

rowid needs to participate in the same projection and filtering machinery you built for SELECT, but it isn't one of the schema's declared columns — it's an implicit extra column that sits "before" column 0:

• SELECT rowid, * FROM users should project rowid first, then every schema column in order.
• SELECT rowid FROM users should work as its own projection, without needing *.
• WHERE rowid = 1 needs to be evaluated by comparing against the stored id directly, not by looking it up through find_column_index against the schema (since it isn't in the schema).

A clean way to implement this: special-case rowid in your column-resolution step — when resolving a name in the SELECT list or WHERE clause, check for the literal name rowid first, before falling back to schema column lookup.

def resolve(schema, row, name):
    if name == "rowid":
        return row_id_of(row)
    idx = find_column_index(schema, name)
    return row_values_of(row)[idx]

Edge cases

• rowid lookups (WHERE rowid = 1) should still work with a linear scan for now — you're not required to build direct indexed access by rowid yet; that comes with the B-tree lessons.
• A table with zero rows should still report next_rowid == 1 for the next insert; don't let an empty table's counter start anywhere else.
• Make sure .dump and other introspection commands you built earlier don't accidentally start printing rowid unless asked — it's implicit, not part of the schema's declared columns, so default output (SELECT *, .schema) shouldn't include it unless explicitly requested.

This hidden integer key is exactly what B-tree indices (a later chapter) will be built on — the primary key B-tree in SQLite is literally a tree keyed by rowid, so getting this abstraction right now sets up the indexing chapter cleanly.