I Have No Idea How a SQL Engine Works — So I’m Building One | Stage -1

Hello everyone, back to Medium after a looong break… and I’m not coming back empty handed. I’m coming back with a new project. A big one.

It’s called sql-engine — and no, I don’t fully know how it works yet. That’s kind of the point.

I’m learning and building it simultaneously. No course. No tutorial. Just me, C++, and a lot of confused staring at the screen. I’m documenting everything as I go — the wins, the bugs, the moments where I had absolutely no idea what I was doing.

You can follow along everywhere:

• 🎥 YouTube — https://youtu.be/A9iKONyU0yU?si=O592VJJSCR695r2a
• 🐦 X — https://x.com/aryanmh0
• 💻 GitHub — https://github.com/zoolpher/sql-engine

So what even is a SQL engine?

When you write something like:

SELECT name FROM users WHERE age > 20;

Something has to read that. Something has to understand it, validate it, figure out the cheapest way to execute it, and then actually go fetch the data.

That something is a SQL engine. And it’s not magic — it’s a pipeline. A series of components, each doing one job, handing off to the next.

Here’s what the full pipeline looks like:

Raw SQL string
     ↓
Lexer          → breaks string into tokens
     ↓
Parser         → builds an Abstract Syntax Tree (AST)
     ↓
Semantic Analyzer → validates tables, columns, types
     ↓
Query Planner  → converts AST into a logical execution plan
     ↓
Optimizer      → picks the cheapest way to execute
     ↓
Executor       → fetches data, returns results

I’m building every single one of these from scratch. In C++. No shortcuts.

Stage 1 — The Lexer

Stage 1 is complete and it’s the Lexer — also called a Tokenizer.

The lexer’s job is simple to explain and surprisingly fun to build: take a raw SQL string and break it into a list of tokens. A token is just a pair — a type and a value.

So this:

SELECT name FROM users WHERE age >= 18;

Becomes this:

[SELECT]      => "SELECT"
[IDENTIFIER]  => "name"
[FROM]        => "FROM"
[IDENTIFIER]  => "users"
[WHERE]       => "WHERE"
[IDENTIFIER]  => "age"
[GTE]         => ">="
[INTEGER]     => "18"
[SEMICOLON]   => ";"

Every keyword, every operator, every identifier, every literal — all classified and labeled before the parser ever sees them.

The interesting parts

A few things in the lexer that I found genuinely interesting to implement:

Get zoolpher’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

The >= lookahead — when the lexer hits a > character, it can't just immediately emit a GT token. It has to peek at the next character first. If it's =, the token is GTE. This peek-ahead pattern is a classic lexer technique and shows up everywhere.

The i-- trick — the lexer uses a for loop with i++ at the end. But when scanning a multi-character token like a keyword or number, the inner while loop already advances i. So before returning control to the for loop, you have to i-- to avoid skipping a character. Miss this once and you'll spend an hour debugging disappearing characters.

Integer vs Float detection — when scanning a number, the lexer checks for a . character mid-scan. If it finds one, the token type flips from INTEGER to FLOAT. Simple, but satisfying.

A Bug I Found — and Why It Matters

While writing tests for the lexer, I found a silent bug that I think is worth talking about.

Take this broken query:

SELECT name FROM users WHERE city = 'Delhi

No closing quote. The lexer hit the opening ', started collecting characters, and just... kept going until it ran out of input. Then it quietly emitted a STRING token with value Delhi and moved on like nothing happened.

No error. No warning. Nothing.

The problem? Everything downstream — the parser, the semantic analyzer, the planner — receives what looks like a perfectly valid token stream. They have no idea the original query was broken. The error surfaces deep inside the pipeline, far from where it actually happened, which makes debugging a nightmare.

The fix is one line — after the string scanning loop, check why the loop exited:

if (i >= input.length()) {
    throw std::runtime_error("Lexer error: unterminated string literal");
}

If the loop ran out of input before finding a closing quote, throw immediately. Fail loudly and early.

A lexer that silently swallows malformed input makes debugging hell for every stage that comes after it.

This is a real principle — Postgres, SQLite, and MySQL all catch this at the lexer level for exactly this reason.

What’s Next

Stage 2 is the Parser.

The lexer hands off a flat list of tokens. The parser’s job is to take that list and build an Abstract Syntax Tree — a nested structure that actually represents the meaning of the query, not just its characters.

That’s where things get genuinely hard. And genuinely interesting.

I’ll be documenting it the same way — building in public, sharing everything, bugs included.

If you want to follow along, the GitHub repo has the full source with stage-by-stage documentation:

See you in Stage 2.

Google Machines Geekologi™ Microsoft + Open Source FAANG Master

Built by [zoolpher](https://github.com/zoolpher/sql-engine.git) — B.Tech CS, systems engineering track.