Row Storage — Storing Rows as Tuples

Now we move from parsing to storage. How should rows be organized in memory? This lesson introduces the fundamental concept of row-based storage with automatic row identifiers.

What Is a Tuple?

In database terminology, a tuple is a single row of data. Each tuple contains one value per column, stored in column order:

Table: users (id INTEGER, name TEXT, age INTEGER)
Tuple: (1, 'Alice', 30)

Most relational databases store data as tuples — this is called row-oriented (or N-ary) storage. The alternative is column-oriented storage (used by analytics databases like ClickHouse), where each column is stored separately.

The rowid Concept

Every table needs a way to uniquely identify each row. SQLite solves this with an implicit rowid — a hidden 64-bit integer that auto-increments:

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

The rowid is always available even though it's not in the CREATE TABLE definition:

SELECT rowid, * FROM users
-- rowid|name|age
-- 1|Alice|30
-- 2|Bob|25

Why rowid Matters

The rowid serves as the primary key for the table's internal B-tree (which we'll build in Chapter 3). It provides:

1. Unique identification: Every row has a distinct integer
2. Fast lookup: B-tree search by rowid is O(log n)
3. Stable references: Indices map column values to rowids

Auto-Increment Implementation

Keep a counter per table that starts at 1 and increments after each INSERT:

How SQLite Does It

SQLite stores the largest rowid ever used and increments from there. If you delete rowid=5 and insert again, you get rowid=6, not rowid=5. The AUTOINCREMENT keyword adds even stricter guarantees (never reuses rowids), but the default behavior already avoids most issues.

In PostgreSQL, the equivalent is a SEQUENCE — a separate object that generates unique integers. Sequences survive transaction rollbacks (if you roll back an INSERT, that sequence number is "burned").

Your Task

Add automatic rowid to every table. Make it accessible via SELECT rowid, * and filterable via WHERE rowid = N.