Build a Database Server
Build a Database Server
Learn how real databases work by following this practical guide to building your own
About the Book
Build a Database Server will guide you through building a database from scratch. You'll start by creating an interpreter that can parse and type check SQL statements, then go on to store data and build an engine to run complex queries.
You'll gain a deep knowledge about SQL and relational databases, from the execution order queries, to techniques that databases use to join and group data efficiently. Learn about database research and compare your implementation with real databases including PostgreSQL, MySQL, SQLite and DuckDB.
A hands-on approach to learning computer science topics including creating a recursive descent 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.
As well as guiding you through building your database, the book has:
- A test runner with 200+ test cases and test runner to help drive your implementation and provide a safety net for refactoring your code
- Specific prompts to refactor at the end of each chapter to help you reflect on and improve your code
- A sample solution that you can refer to if you get stuck and compare your approach to
- Over 30 ideas for extensions that you can choose to add to your implementation
- A link to join a discord server to share your solution and ask for help
Who is this book for?
The book is for software engineers with any amount of experience:
- It's 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 senior engineers can challenge themselves by implementing the extensions at the end of chapters and dig deeper into the database research that interests them most.
Can I get a preview?
You can sign to get the free community edition of the book which contains the first few chapters and lets you start building your database implementation. There is an adapted version of the first chapter 'What makes SQL special' available online.
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 as well as running aggregate functions.
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.
Team Discounts
Get a team discount on this book!
Table of Contents
-
Preface
- What Makes SQL Special
- Relational databases
- Before SQL
- Designed for everyone
- A declarative language
- Widespread implementations
- ISO/IEC 9075
- Your Implementation
- Test suite
- rgSQL
- Picking a programming language
- Getting help
- Sections in this Guide
- What Makes SQL Special
-
Running SQL
- Introduction
- 1. The Server
- How to talk to a database server
- TCP in depth
- Does every database have a server process?
- Implementation
- 2. Returning Values
- Tableless SELECT statements
- Parsing
- Running your statement
- Returning results
- How PostgreSQL sends data
- Implementation
- 3. Tables
- Managing tables
- Reading and writing data
- Do all databases store tables in the same way?
- Implementation
- 4. Resilient Parsing
- Elements of a statement
- Write it how you want
- Confusing keywords
- Managing parsing complexity
- Passing the parser
- Implementation
- 5. Expressions
- What is an expression
- Operators
- Functions
- How to specify a language
- Implementation
- 6. Finding Errors
- All or nothing
- Finding errors before running statements
- Flexible typing
- Errors when running statements
- Return codes
- Implementation
- 7. Null
- What is null?
- Working with the unknown
- Null propagation
- Type checking null
- Implementation
- 8. Filtering, Ordering and Limiting
- Filtering
- Ordering
- Limiting
- Evaluating SELECT queries
- Evaluating a different way
- Rows or columns?
- Implementation
- 9. Query Plans
- What is a query plan?
- Planning for success
- What are iterators?
- Advantages of iterators
- When one row at a time isn't fast enough
- Implementation
- 10. Qualified References
- The many forms of references
- Resolving references
- The portability of SQL
- Implementation
- 11. Joins
- The different kinds of joins
- How to join
- More join algorithms
- Implementation
- 12. Grouping
- Using GROUP BY
- Grouping by complex expressions
- Referring to ungrouped columns
- Metadata for grouped queries
- Validating expressions after grouping
- Evaluating grouped queries
- Implementation
- 13. Aggregate Functions
- Count
- Sum
- Aggregate functions rules
- Accumulating results
- Working with aggregate functions
- Implicit grouping
- Compiling queries
- Implementation
-
Appendix
- The Test Suite
- Debugging
- Configuring
- Server tests
- SQL test format
- Writing your own tests
- Changing the test runner
- Running with GitHub Actions
- More Extensions
- Additional types
- Set operators
- Subqueries
- Common table expressions
- Views
- More clients
- Query pipelines
- The Test Suite
The Leanpub 60 Day 100% Happiness Guarantee
Within 60 days of purchase you can get a 100% refund on any Leanpub purchase, in two clicks.
Now, this is technically risky for us, since you'll have the book or course files either way. But we're so confident in our products and services, and in our authors and readers, that we're happy to offer a full money back guarantee for everything we sell.
You can only find out how good something is by trying it, and because of our 100% money back guarantee there's literally no risk to do so!
So, there's no reason not to click the Add to Cart button, is there?
See full terms...
Earn $8 on a $10 Purchase, and $16 on a $20 Purchase
We pay 80% royalties on purchases of $7.99 or more, and 80% royalties minus a 50 cent flat fee on purchases between $0.99 and $7.98. You earn $8 on a $10 sale, and $16 on a $20 sale. So, if we sell 5000 non-refunded copies of your book for $20, you'll earn $80,000.
(Yes, some authors have already earned much more than that on Leanpub.)
In fact, authors have earnedover $14 millionwriting, publishing and selling on Leanpub.
Learn more about writing on Leanpub
Free Updates. DRM Free.
If you buy a Leanpub book, you get free updates for as long as the author updates the book! Many authors use Leanpub to publish their books in-progress, while they are writing them. All readers get free updates, regardless of when they bought the book or how much they paid (including free).
Most Leanpub books are available in PDF (for computers) and EPUB (for phones, tablets and Kindle). The formats that a book includes are shown at the top right corner of this page.
Finally, Leanpub books don't have any DRM copy-protection nonsense, so you can easily read them on any supported device.
Learn more about Leanpub's ebook formats and where to read them