Technical Deft

Build a Database Server by Chris Zetter cover

Build a Database Server

A step-by-step guide to building your own database server. Learn about SQL and improve your programming skills.

Get a 150 page PDF and web-based book.

Buy now Or get a free preview.

Already purchased? Sign in to access.

Build a language from scratch

Create an interpreter that can parse, type check and run SQL queries. Start with running simple queries and build up to more complex ones.

SELECT 1;
SELECT
    order_no,
    SUM(items.price) AS total,
    COUNT(order_no)
FROM orders
INNER JOIN items ON orders.id = items.order_id
WHERE dispatched
GROUP BY order_no
ORDER BY total DESC;
def run_select
  load_table
  apply_joins
  filter_rows
  apply_grouping
  apply_projections
  order_rows
  apply_offset
  apply_limit
  build_result
end

Learn SQL (the fun way)

Learning by creating your own database server will help you gain a deeper understanding of SQL.

You'll cover the order of execution of queries, how SQL uses ternary logic and the techniques that databases use to join and group data efficiently.

Compare your implementation with databases such as PostgreSQL, MySQL, SQLite and DuckDB.

Improve your skills

The perfect project to practice how to structure and refactor code as you grow the feature set of your database server. The 200+ included test cases provide a safety net for refactoring your code, giving you freedom to experiment.

A hands-on approach to learning computer science topics including creating a recursive decent parser, building a type checker and implementing iterators.

You choose what language to complete the project in, so pick a language you want to learn or get more practice with.

$ ./run-tests                                      
....................F
A test has failed in 5_expressions.sql on line 193:
  Can use expression in select to transform values

  Expected statement to return rows (in any order):
    42
  
  Got rows:
    41

  Queries sent:
    CREATE TABLE t1(a INTEGER, b INTEGER);
    INSERT INTO t1 VALUES(7, 6);
    SELECT a * b FROM t1;
An example structure of a SELECT statement showing a SELECT node in the middle, surrounded by a node to represent a table name, nodes for projections, nodes for join tables and expressions, a node for a WHERE expression, nodes for grouping expressions, nodes for ORDER BY expressions, a node for a HAVING expression, a node for a LIMIT expression and a node for an OFFSET expression.

Get help with the tricky bits

200+ test cases and 13 chapter guide will help you build your solution step by step. Full of hints and diagrams to help you plan your implementation as well as a full sample solution. Access to a Discord discussion group to share solutions and ask for help when stuck.

Tools to help you:

Book

13 chapter guide to help create your implementation and ideas for extensions. Available on web or as a downloadable PDF.

Sample solution

A sample solution for the project in Ruby.

Test suite

200+ test cases to help guide your implementation and provide a safety net for refactoring.

Support

Access to a Discord server to share solutions and ask for help.

Book contents

Preface

What Makes SQL Special

Why SQL is the dominant way to communicate with databases.

Your Implementation

Introducing rgSQL and the test suite.

Running SQL

1. The Server

Create a server process that can communicate with a client using TCP.

2. Returning Values

Start building your parser and implementation to handle table-less SELECT queries.

3. Tables

Create tables so that you can persist data between statements.

4. Resilient Parsing

Handle the different statement formats by using tokenization.

5. Expressions

Handle mathematical and logical operators in queries and introduce functions.

6. Finding Errors

Build a type system that will warn you if there are problems with queries before running them.

7. Null

Make sure sure that your functions and operators can deal with null.

8. Filtering, Ordering and Limiting

Create more complex queries with WHERE, LIMIT and ORDER BY.

9. Query Plans

Build a query plan made of iterators to efficiently evaluate SELECT queries.

10. Qualified References

Validate and resolve different kinds of references in queries.

11. Joins

Implement different kinds of joins to query across multiple tables.

12. Grouping

Use GROUP BY to split results into groups.

13. Aggregate Functions

Be able to run and type check aggregate functions such as SUM and COUNT.

Appendix

The Test Suite

How to work with and extend the included test suite.

More Extensions

More interesting ways to extend your implementation.

About the author

Photo of Chris Zetter

Hi, I'm Chris Zetter. I'm a software engineer and author of this guide. My favourite thing to do is to mentor other engineers and help build their skills.

I've been working with SQL databases throughout my whole career and started building my own database server as a learning project. I love practical projects that let you 'learn by doing' and enjoyed creating my own database so much that I wanted to share it with others.

Build a Database Server by Chris Zetter cover

Start your database adventure today

Get access to the 150 page PDF and web based guide.

Find it's not for you? No-questions asked refunds available for 14 days after purchase.

Questions

Who is this book for?

The book is for software engineers with any amount of experience.

It is a great project for junior engineers to learn how to build a programming language. It explains how to parse, type check and execute SQL.

More experienced engineers will gain a deeper understanding of databases. They will get to grow and refactor a complex project as they work through the guide.

The most experienced engineers can challenge themselves by implementing the extensions at the end of chapters and dig deeper into the database research that interests them most.

What are the system requirements?

The guide is designed for a MacOS or Linux based system. You can also use the Windows Subsystem for Linux (WSL) to run the code on Windows. You will need to have Python 3.6 or later installed to run the test suite.

What will I be building?

This is a practical book. As you go through it, you will be building a database server (also known as a Database Management System or DBMS).

Your database server will store data in memory and be able to parse, type and run a variety of SQL statements including implementing SELECT with WHERE, ORDER BY, GROUP BY, LIMIT and JOIN clauses.

What programming language can I use?

You can use almost any programming language to complete the project, including Ruby, JavaScript, TypeScript, Python, Java and many more.

Unless you are looking for a challenge, there’s a couple of features that your language should have in its standard library or installable packages: support for regular expressions to help parsing SQL and the ability to start a TCP server to communicate to clients.

Pick the language you want to work in. You can use the project as an opportunity to learn a new language or explore different ways of writing code in a language you already know.

Can I buy the book for my team or group?

Bulk discounts for teams, companies and book groups are available on request. Contact Chris for more information.

What if I find the book isn't for me?

If you find the book isn't what you expected, you can get a full refund within 14 days of purchase.

Got another question? I'd love to hear from you.