Skip to content

An SQL database, written as a learning project.

License

Notifications You must be signed in to change notification settings

i-sevostyanov/NanoDB

Repository files navigation

CI Go Report Card codecov GitHub license

NanoDB

SQL database, written as a learning project to better understand the internals of a database.

Features

  • A good starting point to dive into database internals
  • Modular and extendable architecture
  • Implemented widely usable SQL statements (SELECT, INSERT, UPDATE, DELETE, and more)
  • Interactive terminal for easy commands execution and experiments

Documentation

Shell

The NanoDB command line provides an SQL shell that can be used to select, insert, delete, and modify data.

Run locally

git clone https://github.com/i-sevostyanov/NanoDB.git
cd NanoDB
go run ./cmd/shell/main.go
#> \import <path to project>/testdata/demo.sql

Examples:

Imagine that we have a table with the following definition:

CREATE TABLE aircrafts
(
    id    INTEGER PRIMARY KEY,
    code  TEXT    NOT NULL,
    model TEXT    NOT NULL,
    range INTEGER NOT NULL
);

SELECT

Select all columns:

demo #> SELECT * FROM aircrafts
+----+------+---------------------+-------+
| id | code |        model        | range |
+----+------+---------------------+-------+
| 1  | 773  | Boeing 777-300      | 11100 |
| 2  | 763  | Boeing 767-300      | 7900  |
| 3  | SU9  | Sukhoi Superjet-100 | 3000  |
| 4  | 320  | Airbus A320-200     | 5700  |
| 5  | 321  | Airbus A321-200     | 5600  |
| 6  | 319  | Airbus A319-100     | 6700  |
| 7  | 733  | Boeing 737-300      | 4200  |
| 8  | CN1  | Cessna 208 Caravan  | 1200  |
| 9  | CR2  | Bombardier CRJ-200  | 2700  |
+----+------+---------------------+-------+
(9 rows)

Select specified columns with order, limit and offset:

demo #> SELECT id, model, range FROM aircrafts WHERE range < 5000 ORDER BY range ASC LIMIT 5 OFFSET 2
+----+---------------------+-------+
| id |        model        | range |
+----+---------------------+-------+
| 3  | Sukhoi Superjet-100 | 3000  |
| 7  | Boeing 737-300      | 4200  |
+----+---------------------+-------+
(2 rows)

Select expression:

demo #> SELECT 6+(2^3)*5-3+4/(10-2)%3
+----------------------------------------------------+
| (((6 + ((2 ^ 3) * 5)) - 3) + ((4 / (10 - 2)) % 3)) |
+----------------------------------------------------+
| 43                                                 |
+----------------------------------------------------+
(1 rows)

INSERT

demo #> INSERT INTO aircrafts (code, model, range) VALUES ('773', 'Boeing 777-300', 11100);
Query OK, 1 row affected

UPDATE

demo #> UPDATE airports SET code = 'MRN' WHERE id = 2
Query OK, 1 row affected

DELETE

demo #> DELETE FROM airports WHERE id > 5
Query OK, 4 rows affected

Roadmap

  • Indices
  • Joins
  • Subqueries
  • Aggregations (count, sum, avg, max, min)
  • Explain and query optimization
  • Transactions (ACID, MVCC, WAL)
  • Constraints (unique key, check, foreign key)
  • Built-in functions (sin, cos, abs, floor, etc)

Contributing

Contributions are welcome!

See CONTRIBUTING.md for more details.