Build a Database from Zero (Part 2): Turning "How to Query" into "What You Want"

Database

PostgreSQL

Build from Zero

2 hours ago1250 words·7 min read

What You'll Get Out of This

Upgrade Part 1's hardcoded loop into a declaration; from now on, changing a condition no longer means rewriting code;
Build a "query function" into a "description + engine" yourself, and see how to split "what you want" from "how to find it";
Plant the crucial seed for all later speedups: once "how to find" is freed from the code, it leaves room for the engine to optimize.

Before We Start

Know everything from Part 1 (array = table, loop = query);
Be comfortable passing a function as an argument (won't feel strange with callbacks / higher-order functions).

Starting From "Problem 1" From Last Time

At the end of Part 1 we said: the query is hardcoded; change a condition and you rewrite a chunk. Let's make that pain concrete. Now you want three different results, so you write three nearly-identical blocks:

// find price under 50
function byPrice(rows) {
  return rows.filter(r => r.price < 50);
}

// find id === 2
function byId(rows) {
  return rows.filter(r => r.id === 2);
}

// find title containing "Database"
function byTitle(rows) {
  return rows.filter(r => r.title.includes("Database"));
}

Three blocks, and the only difference is that one line r.xxx === xxx. That's "query logic hardcoded" — every new way to search is one more copied loop.

Step 1: Pull the "Condition" Out

Look at those three — what actually changes is the condition itself; the rest (take an array, check each item, gather results) is all boilerplate.

So can we turn "condition" into a parameter? JS naturally supports this — a condition is "a function that takes a row and returns true/false."

// one function serves all: the condition is up to the caller
function query(rows, predicate) {
  const result = [];
  for (const row of rows) {
    if (predicate(row)) result.push(row);
  }
  return result;
}

// now the three searches only write the condition, no loop:
const cheap   = query(books, r => r.price < 50);
const theTwo  = query(books, r => r.id === 2);
const dbTitle = query(books, r => r.title.includes("Database"));

See what this little step did: "how to query (the loop)" and "what to query (the condition)" parted ways.

"What to query" — the predicate supplied by the caller (what you want);
"How to query" — the fixed loop inside query (how to find).

You've already written a miniature query engine. It has one "engine" but serves countless queries.

This step is what a real database calls the split between "declarative" and "procedural." You just made the first crossing yourself: from "I tell you how to do it" to "I only tell you what I want."

Step 2: Declare the Table Schema Too

Above, one layer is still missing. predicate is written on the fly by the caller, but "which columns does this table have, and what types" is currently buried in the data (relying on the object's own fields).

A real database declares the table schema first, so queries can know "these columns, these types." Let's do the same — first define the "table" object:

// our "table": a name + column definitions + data
function createTable(name, columns) {
  return {
    name,
    columns,      // e.g. [{ name: "id", type: "number" }, ...]
    rows: []
  };
}

// declare a books table
const books = createTable("books", [
  { name: "id",    type: "number" },
  { name: "title", type: "string" },
  { name: "price", type: "number" }
]);

With column definitions, the query engine can do "smart" things — like return only the columns you want (projection), rather than dumping whole rows at you:

// query engine: supports chosen columns (projection) + condition (predicate)
function select(table, wantedColumns, predicate) {
  const result = [];
  for (const row of table.rows) {
    if (!predicate || predicate(row)) {
      const out = {};
      for (const col of wantedColumns) out[col] = row[col];
      result.push(out);
    }
  }
  return result;
}

select(books, ["title", "price"], r => r.price < 50);
// [{ title: "Database Internals", price: 59 }, { title: "A Philosophy...", price: 39 }]

Note the payoff of this layer isn't just "less code," it's that the engine now "knows" the table structure — which lays the groundwork for "jumping by index later" (the engine must hold the schema to decide how to walk).

Step 3: Make "What You Want" Look a Little More Like SQL

In the real world you write SELECT title, price FROM books WHERE price < 50. Our engine can already answer that — just add a thin "shell" to line up that description with the call:

function queryTable(table, { columns, where }) {
  return select(table, columns, where);
}

// "SELECT title, price FROM books WHERE price < 50"
const result = queryTable(books, {
  columns: ["title", "price"],
  where:   r => r.price < 50
});

You've now "built" an engine that understands "which columns, what condition." The remaining hard part — the theme of the next few articles — is: can the "how to find" part stop being so dumb?

And So "Problem 2" Surfaces: The Engine Is Too Naive

We worked hard to free "how to find" from the code, so that the engine gets a chance to pick a smarter search. But right now the engine's "how to find" is still that honest loop — it scans the whole table every time.

There it is — Part 1's "Problem 2," now caught. Because you've separated "what you want" from the code, you can — and eventually will — swap the implementation inside select without the caller changing a single line.

Remember this: decoupling "how to find" from "what you want" is the precondition for every database optimization. Only when decoupled can the engine secretly replace the "naive loop" with "hop along the index," while your query stays identical.

Starting next article, we really swap out that "naive loop."

Hands-On Practice

Add a new table: use createTable to declare a users table (id, name, age), and use queryTable to find "people older than 30, returning only name and age."
Give the engine an aggregate: implement count(table, where) that returns the count of matching rows. Think about how much code it shares with select.
Prove "decoupling" wins: now change the select implementation to "scan in a smarter order" (e.g. skip some rows in the existing array), and observe that the caller (outer queryTable) doesn't change at all.

Summary and Keywords

Lock "the loop" inside the engine, hand "the condition" to the caller → from procedural to declarative;
The engine begins to "know" the table structure (columns, types), so it can project — and later, walk an index;
Decoupling "what you want" from "how to find it" is the prerequisite for all later optimizations — the caller stays the same while the engine silently upgrades.

Keywords: declarative、predicate、projection、schema、query engine、decoupling

What's Next

We can already say "what we want," and the engine is dutifully naive. Next, we invite the first real structure — a hash table — to solve "I know the exact id, give me that row" type key lookups. You'll build O(1) yourself, but immediately hit an awkward wall: it can find "equal to," but can't find "less than 50." That awkwardness will push you to the turning point of Part 4.