QueryForge

Everyone uses a database; almost nobody knows what is happening inside one. You type SELECT * FROM emp WHERE eid = 10 and rows come back — but where do those rows physically live? Why does adding an index make that lookup 28× faster? What actually happens when a ROLLBACK undoes an hour of work? The usual advice is to go read the Postgres source, which is over a million lines and assumes you already know the answers.

Most build-your-own-database tutorials quietly cheat. They store rows in a JSON file, or they wrap SQLite and call it a database. Nothing is learned about pages, buffer pools, B+ trees or crash recovery — which are precisely the parts that make a database a database.

QueryForge is the honest version: a SQL engine written from scratch in C++17 with **no SQLite and no embedded database library**. Every layer is mine — the 4 KB page format on disk, the LRU buffer pool, the B+ tree, the SQL parser, foreign-key enforcement, snapshot transactions, and user auth. And because a database's internals are invisible from a terminal, I wrapped the engine in an HTTP API and a VS Code-style web studio, so you can watch the schema tree redraw, the ER diagram grow foreign-key arrows, and a GROUP BY turn into a chart as you type SQL.

Solo Engineer & Architect — I designed and built both the engine and the studio.

**Engine (C++17, header-only):** the storage layer (4 KB pages, used/recycled block chains, an LRU buffer pool with dirty tracking), the B+ tree index manager (build, insert, delete, search, with swap-delete pointer fix-ups), the catalog manager (databases, tables, columns, FKs, views — persisted with Boost.Serialization), the record manager (insert/select/update/delete over pages, nested-loop join, FK checks), a tokenizer plus one parser class per SQL statement, the interpreter that classifies and dispatches, snapshot transactions with automatic crash recovery, a user manager with salted FNV-1a password hashes, and MySQL-style boxed output.

**Interfaces:** a readline CLI REPL, and a minimal HTTP server (/health, /query, /schema) with CORS and per-client X-Session tokens so every browser tab keeps its own login and current database.

**Studio (Next.js 15 + React 19):** the entire IDE — activity bar, explorer, tabs, breadcrumbs, status bar and 7 themes; a SQL editor with syntax highlighting and schema-aware autocomplete; smart run (statement under cursor, whole file, or selection); a live schema tree with PK/FK pills; an ER diagram; a sortable result grid with CSV export; bar/line/pie charts from any result set; a command palette; a problems panel; a SQL formatter; query history; and a browser-persisted workspace with 18 guided sample files.

**Ops:** CMake build, a 37-scenario / 110-assertion end-to-end suite that drives the real CLI binary, and GitHub Actions CI that runs that suite twice — once normally, once under AddressSanitizer + UBSan — plus the frontend build.

QueryForge is a **layered engine** — each layer knows only the one beneath it — with two front doors (CLI and HTTP) on top.

1$            Interpreter          tokenize → classify → dispatch
2$                 │
3$           QueryForgeAPI         auth · transactions · views · schema JSON
4$                 │
5$   Record ── Index ── Catalog     heap files · B+ tree · schema metadata
6$      │        │
7$        Buffer Manager           4K pages · LRU eviction · dirty tracking
8$                 │
9$             DB files

**Storage.** Every table is a .records file made of 4 KB blocks. A 12-byte header holds the prev/next block numbers and the record count; fixed-length records fill the rest. Each table keeps two chains — a *used* chain and a *recycled* chain. When a block empties it moves onto the recycled chain and gets reused before the file is allowed to grow, so a delete-heavy workload does not leak disk.

**Buffer pool.** Nothing touches the disk directly. Reads go through an LRU-managed page cache; writes mark pages dirty and flush on statement completion. This is the layer that makes repeated lookups fast — and the layer that makes ROLLBACK possible, because undoing a transaction is largely just discarding dirty pages.

**B+ tree.** Index files store nodes as pages; leaf entries pack (block, offset) pointers straight at the records. Deletes swap the last record into the hole (O(1) instead of shifting), which means the moved record's index entry has to be re-pointed — miss that and lookups silently chase a stale pointer.

**Transactions.** BEGIN snapshots the data directory into .txn_backup; ROLLBACK discards dirty pages and restores the snapshot; COMMIT flushes and deletes it. If the process dies mid-transaction, the leftover snapshot is detected on next start and rolled back automatically.

**SQL surface.** DDL for databases, tables, indexes, views and users; INSERT / UPDATE / DELETE; SELECT with column projections, DISTINCT, WHERE (=, <>, <, >, <=, >=, LIKE, IN over a list *or* a subquery, BETWEEN, combined with AND / OR), GROUP BY … HAVING, ORDER BY, LIMIT, aggregates (COUNT, MIN, MAX, SUM, AVG), inner and LEFT JOIN, and DESCRIBE.

**Foreign keys** are enforced with RESTRICT: a child insert or update must reference an existing parent, a referenced parent row cannot be deleted, and a referenced parent table cannot be dropped.

**Views** are stored SELECT statements, re-expanded at query time, and may be built on other views up to depth 8.

**HTTP + sessions.** queryforge_server exposes /health, /query and /schema. Every response carries an X-Session token; send it back and your login and current database persist across calls. Different tokens are fully isolated sessions — that is what makes a shared, hosted studio usable by more than one person at a time.

**Studio.** The Next.js front end consumes a single /schema payload to drive three separate features — the schema tree, the editor's autocomplete, and the ER diagram — so they can never disagree with each other or with the engine.

QueryForge sits in the **learn-by-building / systems-credibility** wedge. It is not trying to replace Postgres — it is trying to answer the question *do you actually understand what a database does?* with a repository instead of an opinion.

**Who it is for:** students in a DBMS course who want to *see* pages and B+ trees rather than memorise them; engineers preparing for systems-design and internals interviews; and, frankly, recruiters — a from-scratch storage engine with a published benchmark, an ASan-clean CI pipeline and a polished web studio is a far stronger signal than another CRUD app.

**The hook:** open the live studio, log in as root / root, and run the 18 guided sample files (01_database.sql → 17_cleanup.sql) in order. They build a company database step by step and walk through every feature — including an intentional foreign-key violation, so you can watch the engine refuse it.

**Go-to-market:** live studio at queryforge-one.vercel.app → GitHub with a design deep-dive, benchmarks and a CI badge → DBMS course communities and interview-prep circles. MIT-licensed, so it can be forked as coursework.

**Proof over claims:** the benchmark is the argument. Anyone can assert that indexes are faster; the numbers show the full scan degrading from 5.6 ms to 22.0 ms as the table grows from 3K to 10K rows while the B+ tree stays flat at roughly 0.8 ms — O(n) against O(log n), measured rather than asserted.

Live studio at queryforge-one.vercel.app with source at github.com/subhm2004/Self_SQL_Server.

**Shipped (engine):** 4 KB page storage with used/recycled block chains · LRU buffer pool with dirty tracking · B+ tree indexes with swap-delete pointer fix-ups · SQL tokenizer, parser and interpreter · full DDL (databases, tables, indexes, views, users) · INSERT / UPDATE / DELETE · SELECT with projections, DISTINCT, LIKE, IN (list *and* subquery), BETWEEN, AND/OR, ORDER BY, LIMIT · GROUP BY … HAVING · aggregates (COUNT, MIN, MAX, SUM, AVG) · inner and LEFT JOIN · foreign keys with RESTRICT · views over views (depth ≤ 8) · snapshot transactions with automatic crash recovery · salted-hash auth with a root superuser · thread-safe engine-level mutex · MySQL-style boxed output.

**Shipped (studio):** VS Code-style shell — activity bar, explorer, tabs, breadcrumbs, status bar — with **7 themes** · SQL editor with syntax highlighting and schema-aware autocomplete · smart run (statement under cursor, whole file, or selection) · **live schema tree** with PK/FK pills that auto-refreshes after DDL · **ER diagram** with foreign-key arrows · sortable result grid with CSV export · **bar / line / pie charts** from any result set · command palette · problems panel that jumps to the failing statement · one-click SQL formatter · query history (last 100) · browser-persisted workspace with 18 guided sample files.

**Shipped (platform):** readline CLI REPL · HTTP API (/health, /query, /schema) with CORS and per-client X-Session isolation · CMake build · a **37-scenario / 110-assertion** end-to-end suite that drives the real binary against a throwaway data directory · **GitHub Actions CI** running that suite normally *and* under AddressSanitizer + UBSan, plus the frontend build.

**Measured:** B+ tree lookups run **~7× faster at 3,000 rows and ~28× faster at 10,000 rows** than a full scan (5.6 ms → 0.84 ms, and 22.0 ms → 0.78 ms). Because the scan is O(n) while the index is O(log n), the gap *widens* as the table grows.

Snapshot transactions are correct but coarse — copying the whole data directory on every BEGIN will not scale past toy datasets. A proper write-ahead log with per-page undo/redo is the honest next step, and it would also let me replace the single engine-wide mutex with real concurrency. Joins are the biggest functional gap: single-column equality, SELECT * only, and a nested loop at that — projections and WHERE on joins, plus a hash join, are the obvious follow-ups. The catalog allows one index per table (primary key only); supporting multiple indexes would force me to write a real, if simple, query planner to choose between them. Fixed-length records waste space on short strings, and the standard fix is a slotted page layout with variable-length records. Finally, the parser is hand-rolled per statement, which is fine right up until you want parentheses in a WHERE clause — a proper recursive-descent expression parser would unlock that and correlated subqueries in one go.