SQLite Parser

This project is a SQLite .db file parser implemented in Rust, based on the CodeCrafters "Build Your Own SQLite" Challenge. CodeCrafters breaks down the challenge into multiple stages and provides tests to validate solutions, but this is otherwise a self-directed project.

Original challenge description:

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

Features

• Inspect database metadata with .dbinfo and .tables.
• Execute SELECT COUNT(*) queries to count rows.
• Execute SELECT queries on specific columns with optional WHERE clauses.

Syntax:

cargo run -- <path-to-db-file> "<SQL-or-dot-command>"

Key Concepts Implemented

• Parse database file header and sqlite_schema table to retrieve database metadata and schema.
• Locate and parse B-tree pages (interior and leaf).
• Decode variable-length integers.
• Deserialize raw bytes into SQLite data types.

Limitations

• Only supports a subset of SQL commands.
• Uses ad-hoc regex parsing instead of a full grammar-based parser, since this project focuses on learning database internals rather than parser theory.
• Assumes that all data can fit in memory (no page caching).
• Assumes tables fit within a single page (no multi-page tables).

Planned Features

• SELECT variations: COUNT(*) WHERE, SELECT *, DISTINCT, ORDER BY, LIMIT
• Scan indices
• Support multi-page tables
• INSERT, UPDATE, DELETE

Sample Databases

CodeCrafters provides test databases to make it easy to test queries locally.

There is a small sample database in the root of this repository: sample.db. It contains two tables: apples & oranges.

There are two other databases that you can use:

1. superheroes.db:
   • This is a small version of the test database used in the table-scan stage.
   • It contains one table: superheroes.
   • It is ~1MB in size.
2. companies.db:
   • This is a small version of the test database used in the index-scan stage.
   • It contains one table: companies, and one index: idx_companies_country
   • It is ~7MB in size.

These aren't included in the repository because they're large in size. You can download them by running this script:

./download_sample_databases.sh

If the script doesn't work for some reason, you can download the databases directly from codecrafters-io/sample-sqlite-databases.

Example Queries

Get database info from the sample database:

cargo run -- sample.db ".dbinfo"

List all tables:

cargo run -- sample.db ".tables"

Count rows:

cargo run -- sample.db "SELECT COUNT(*) FROM apples"

Select columns:

cargo run -- sample.db "SELECT name, description FROM oranges"

Select columns with a WHERE clause:

cargo run -- sample.db "SELECT name, color FROM apples WHERE color = Red"