Overview

This project was built for the PesaPal Junior Developer Challenge 26 as a practical exercise in database internals, query execution, and systems design.

Rather than using an existing database engine, the project focused on implementing the mechanics of relational query processing directly in TypeScript. The system parses SQL text, transforms queries into executable structures, and evaluates them against an in-memory relational storage layer.

The final result is a lightweight relational query engine with two interfaces:

• a terminal REPL for direct query execution
• a browser-based SQL playground for interactive testing

Both interfaces operate on the same backend execution pipeline.

More than a CRUD application, the project became an exercise in compiler-style architecture, relational evaluation, and execution flow design.

Problem

Most developers interact with SQL databases through abstractions such as ORMs, managed services, or database drivers. While these tools simplify application development, they also hide the mechanics of how relational systems actually interpret and execute queries.

This project explored a more foundational question:

How does a relational database convert raw SQL text into executable operations against structured data?

The challenge was to build a small but functional relational engine capable of supporting realistic query workflows while remaining modular, understandable, and testable.

The focus was not full SQL compatibility. Instead, the goal was to understand the execution lifecycle behind relational systems:

• parsing
• AST generation
• query evaluation
• relational joins
• schema management
• state mutation

Approach

The system was designed around a staged query execution pipeline.

Rather than executing SQL directly from text input, queries move through several independent phases before evaluation:

raw SQL input

lexical tokenization

grammar parsing

AST generation

semantic validation

execution planning

relational evaluation

formatted output generation

This separation made the system easier to reason about and allowed each layer to evolve independently.

The project also intentionally separated interfaces from execution logic. Both the REPL and browser playground rely on the same backend engine, which ensured consistent behavior across environments and reduced duplication.

System Design

The architecture follows a three-layer structure:

Interface Layer

The system exposes two interfaces for interacting with the query engine.

Terminal REPL

The REPL provides direct SQL execution from the command line, making it useful for rapid testing and iterative query evaluation.

Browser SQL Playground

A lightweight browser interface built with LitElement allows interactive query execution in a more accessible environment.

Both interfaces share the same execution backend, ensuring identical parser and runtime behavior regardless of entry point.

Query Processing Layer

The query processing layer is the core of the system.

It is responsible for transforming raw SQL text into executable relational operations.

Lexical Analysis

The lexer, implemented with Moo, converts SQL text into structured tokens representing keywords, identifiers, operators, literals, and syntax boundaries.

Grammar Parsing

Nearley was used to define SQL grammar rules and generate parse trees for supported query structures.

This stage introduced some of the most complex engineering challenges in the project due to SQL ambiguity and nested syntax behavior.

AST Generation

Parsed queries are transformed into abstract syntax tree structures representing executable relational operations.

The AST layer acts as a boundary between syntax interpretation and execution behavior.

This made it possible to evolve execution logic independently from grammar handling.

Execution Engine

The executor evaluates AST nodes against relational table structures.

Responsibilities include:

• filtering
• ordering
• row mutation
• condition evaluation
• join execution
• schema inspection
• relational traversal

The execution layer coordinates query behavior while preserving predictable evaluation flow across operations.

Storage Layer

The storage engine manages relational data structures entirely in memory.

It handles:

• table definitions
• schema metadata
• row storage
• inserts and updates
• deletes
• schema mutations

The project intentionally avoided persistence layers in order to keep the focus on query execution mechanics and relational evaluation behavior.

Using in-memory storage also simplified debugging and made execution flow easier to observe during development.

Query Lifecycle

A SQL query passes through several stages before execution.

1. Query Input

The system receives raw SQL text from either the REPL or browser interface.

2. Tokenization

The lexer scans the query and converts it into structured tokens.

3. Parsing

Grammar rules validate syntax and construct parse structures representing the query.

4. AST Construction

The parser output is transformed into structured AST nodes representing executable operations.

5. Semantic Evaluation

The executor validates table references, conditions, operators, and schema compatibility before execution.

6. Relational Execution

The engine evaluates filters, joins, ordering, limits, and mutations against relational table data.

7. Output Formatting

The final result set is formatted and returned through the active interface.

Supported SQL Features

The engine supports a practical subset of relational operations, including:

• CREATE TABLE
• INSERT
• SELECT
• WHERE
• ORDER BY
• LIMIT
• UPDATE
• DELETE
• INNER JOIN
• LEFT JOIN
• ALTER TABLE
• DROP TABLE
• DESCRIBE
• SHOW TABLES

The goal was to support enough relational functionality to exercise realistic query execution behavior without attempting full SQL specification coverage.

Key Decisions

AST-Driven Execution

One of the most important architectural decisions was separating parsing from execution through AST structures.

Rather than coupling runtime logic directly to parser output, queries are first transformed into normalized execution nodes.

This created cleaner execution boundaries and made the system easier to extend as new operations were introduced.

Shared Execution Backend

The REPL and browser playground both rely on the same execution engine.

This avoided duplicated query logic and ensured behavioral consistency across interfaces.

Modular Pipeline Design

Lexing, parsing, execution, and storage were intentionally separated into independent layers.

This reduced coupling and made debugging significantly easier as parser complexity increased.

Limiting Scope to Core Relational Behavior

The project intentionally focused on core relational execution mechanics instead of broad SQL compatibility.

Features such as persistence, indexing, transactions, and optimization were deliberately excluded to keep the architecture manageable within the challenge scope.

Challenges and Tradeoffs

SQL Grammar Ambiguity

Handling SQL grammar proved significantly more difficult than expected.

Challenges included:

• nested query structures
• optional clause ordering
• operator precedence
• join condition parsing
• ambiguous grammar paths

Several parser iterations were required before AST generation became stable across supported query types.

Join Execution Complexity

Implementing joins manually introduced additional relational complexity.

The engine needed to:

• traverse relational row sets
• evaluate matching conditions
• preserve unmatched rows for left joins
• maintain predictable evaluation behavior

Even within a simplified relational engine, join execution quickly became stateful and difficult to reason about without clear execution boundaries.

Maintaining Execution Correctness

Query execution introduced edge cases around:

• filtering order
• update targeting
• mutation safety
• schema enforcement
• conditional evaluation

Automated testing became essential as parser and executor behavior evolved together.

Testing and Reliability

The project includes automated tests using Vitest to validate both parsing behavior and relational execution correctness.

Coverage includes:

• parser validation
• CRUD operations
• join execution
• schema mutations
• query consistency
• execution edge cases

Testing became especially important because parser and executor changes frequently affected each other indirectly.

The test suite helped prevent regressions while making architectural refactoring safer.

AI-Assisted Development Workflow

The project combined AI-assisted iteration with manual systems engineering work.

AI accelerated:

• boilerplate generation
• early grammar drafting
• repetitive scaffolding
• interface setup

Most of the critical engineering work still depended heavily on manual debugging and architectural control, particularly around:

• parser stability
• execution correctness
• relational edge cases
• join behavior
• system integration
• execution flow design

The final system reflects a hybrid workflow where AI improved iteration speed, but correctness depended on deliberate engineering decisions and repeated debugging cycles.

Outcome

The final result was a functioning relational query engine capable of parsing and executing meaningful SQL workflows through both terminal and browser interfaces.

More importantly, the project became a practical exercise in:

• compiler-style architecture
• AST-based execution systems
• relational query evaluation
• modular systems design
• stateful execution behavior
• interface abstraction

It also exposed how quickly seemingly simple SQL operations become complex once parsing, execution, joins, and schema management are implemented directly.

What I Learned

This project made database internals substantially more concrete.

Implementing a relational execution engine from scratch exposed how tightly connected parsing, execution, and state management become once higher-level abstractions disappear.

It also reinforced the importance of architecture boundaries in systems work. Separating lexing, parsing, AST generation, execution, and storage made the project significantly easier to reason about as complexity increased.

Most importantly, the project demonstrated that many systems problems are less about individual algorithms and more about designing clean execution boundaries between interconnected moving parts.