Technical Deft

Build a Database Server

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

A message from Chris

👋 Hi, I'm writing this guide. Before finishing it, I want to make sure that at least 100 people would consider buying it.

I plan to make it available early 2025 for £40/$50/€50.

I'll only contact you about this guide, see full privacy statement.

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_having
  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 how the different kinds of join can be implemented.

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

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.

You'll cover a broad set of computer science topics including parsing, concurrency, data structures and serialization.

$ ./run-tests                                      
....................F
A test has failed at ./2_minimal_database_spec.rb:136
  Chapter 2: A Minimal Database
    INSERT
      treats columns that haven't been set as NULL

  Queries run:
    CREATE TABLE t2(a INTEGER, b INTEGER);
    INSERT INTO t2 VALUES(1);
    SELECT a, b FROM t2;
  
  Expected last query to return rows (in any order):
    1, NULL
  
  Actual:
    1, 2        

Get help with the tricky bits

200+ test cases and 12 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 server to share solutions to ask for help on when stuck.

What's included:

Book

12 chapter guide to help create your implementation and ideas for extensions. Available on web or as a download.

Sample solutions

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.

Chapter list

PART I - Running SQL

1. The Server

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

2. A Minimal Database

Start building your parser and implementation to handle simple SELECT queries as well as managing tables and inserting records.

3. Functions and Expressions

Handle mathematical and logical operators in queries and introduce functions.

4. Types

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

5. Filtering and sorting

Create more complex queries with WHERE, LIMIT and ORDER.

6. Joining Tables

Implement different kinds of joins to query across multiple tables.

7. Aggregate Functions

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

8. Grouping

Use GROUP BY and HAVING on query results..

PART II - A Real Server

9. Persistence

Store your data on disk so it isn't lost if the server restarts.

10. Indexes

Make reading data faster by creating indexes and storing them on disk.

11. Concurrency

Adapt your server to be able to run queries concurrently across multiple database connections.

12. Transactions

Support transactions that will let you apply changes atomically and rollback after errors.

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.

And my big ask...

I'm about halfway through writing this guide but will only finish if 100 people let me know they would buy it.

I plan to make it available early 2025 for £40/$50/€50.

I'll only contact you about this guide, see full privacy statement.