INSERT INTO — Adding Rows

With a schema catalog in place, it's time to actually store data. INSERT INTO is where your engine starts holding real rows in memory, and it's also where type checking against the schema first becomes necessary — the schema you built in the previous lesson stops being decorative and starts being enforced.

Row storage

The simplest representation of a table's data is a list of rows, where each row is itself a list (or tuple) of values in column order:

users.rows = [
  [1, "Alice", 30],
  [2, "Bob",   25],
]

Keeping rows as plain ordered lists — rather than dicts keyed by column name — mirrors how real storage engines lay out fixed-width or sequential records, and makes positional operations (like matching VALUES (...) against the column list) direct and cheap.

Parsing INSERT

INSERT INTO users VALUES (1, 'Alice', 30)

tokenizes to KEYWORD INSERT, KEYWORD INTO, IDENTIFIER users, KEYWORD VALUES, SYMBOL (, then a comma-separated list of literals, then SYMBOL ). Parsing walks: expect INSERT INTO <table> VALUES (, then repeatedly read a literal (number or string) followed by , or ).

Validation, in order

Before appending a row, run through these checks — order matters because each one assumes the previous passed:

1. Table exists. Look up the table name in the catalog; if absent, ERR unknown table: <name> and stop — don't attempt to validate values against a schema that doesn't exist.
2. Column count matches. The number of values in VALUES (...) must equal the number of columns in the schema. If not: ERR expected N values but got M, where N is the schema's column count and M is what was actually supplied.
3. Type check, column by column. Walk the schema's (name, type) list alongside the parsed values in lockstep. A TEXT column expects a STRING literal; an INTEGER column expects a NUMBER literal. On the first mismatch, report ERR type mismatch for column <name> using that specific column's name (not the table name), so the error pinpoints exactly what's wrong.

for (col_name, col_type), value in zip(schema.columns, values):
    if col_type == "INTEGER" and not isinstance(value, int):
        return f"ERR type mismatch for column {col_name}"
    if col_type == "TEXT" and not isinstance(value, str):
        return f"ERR type mismatch for column {col_name}"

Only after all values pass validation do you append the row — never partially insert a row and then fail, since that would leave the table in an inconsistent state.

Dumping data back out

.dump <table> is your window into what's actually stored — it should print every row, one per line, with values joined by |:

.dump users
→ 1|Alice|30
→ 2|Bob|25

This pipe-separated format will look familiar from SELECT's output format in the next lesson — keeping the two consistent means you can reuse the same row-formatting helper for both .dump and query results later.

Why this matters for the bigger picture

This is the first place your engine holds mutable state rather than just metadata. Every later feature — SELECT, WHERE, UPDATE, DELETE, indices — operates on the row list you build here, so getting validation and storage right now avoids subtle bugs (wrong column alignment, type confusion) surfacing many lessons later.