Build your own SQLite

Build your own SQLite

Learn about SQLite's file format, how indexed data is stored in B-trees and more.
Difficulty
Hard
Attempts
22
Supported Languages
Python
Go
JavaScript

Introduction

In this challenge, you’ll build a barebones SQLite implementation that supports basic SQL queries like SELECT. Along the way you’ll learn about SQLite’s file format, how indexed data is stored in B-trees and more.

Stages

Print number of tables
#1

In this stage, you’ll implement one of SQLite’s dot-commands: .dbinfo. This command prints metadata related a SQLite database, and you’ll implement one of these values: the number of tables. You’ll do this by parsing a file that uses the SQLite database file format.

22 completions
Print table names
#2

In this stage, you’ll implement another dot-command: .tables. Instead of just printing the count of tables like in the previous stage, you’ll print out the names of tables too.

12 completions
Count rows in a table
#3

Now that you’ve gotten your feet wet with the SQLite database file format, it’s time to move on to actual SQL! In this stage, your sqlite3 implementation will need to execute a SQL statement of this form: SELECT COUNT(*) FROM <table>.

5 completions
Read data from a single column
#4

In this stage, your sqlite3 implementation will need to execute a SQL statement of this form: SELECT <column> FROM <table>.

4 completions
Read data from multiple columns
#5

This stage is similar to the previous one, just that you’ll read data from multiple columns instead of just one. In this stage, your sqlite3 implementation will need to execute a SQL statement of this form: SELECT <column1>,<column2> FROM <table>.

4 completions
Filter data with a WHERE clause
#6

In this stage, you’ll filter records based on a WHERE clause. You’ll assume that the query can’t be served by an index, so you’ll visit all records in a table and then filter out the matching ones.

4 completions
Retrieve data using a full-table scan
#7

In this stage, you’ll filter records based on a WHERE clause. You’ll assume that the query can’t be served by an index, so you’ll visit all records in a table and then filter out the matching ones.

3 completions
Retrieve data using an index
#8

This stage is similar to the previous one, but focuses on enhancing query performance using an index. In this stage, your program will need to read through millions of rows in under 5 seconds.

2 completions