ReefDB

ReefDB is a minimalistic, in-memory and on-disk database management system written in Rust, implementing basic SQL query capabilities and full-text search.

Features

In-Memory or On-Disk storage options
Basic SQL statements (CREATE TABLE, INSERT, SELECT, UPDATE, DELETE)
INNER JOIN support
Full-Text Search using Inverted Index
Custom data types (INTEGER, TEXT, FTS_TEXT)

Dependencies

nom for SQL parsing
serde for serialization
bincode for encoding

Usage

To use ReefDB, you can choose between an in-memory storage (InMemoryReefDB) or on-disk storage (OnDiskReefDB).

In-Memory ReefDB Example

use reefdb::InMemoryReefDB;

fn main() {
    let mut db = InMemoryReefDB::new();

    let queries = vec![
        "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)",
        "INSERT INTO users VALUES (1, 'Alice')",
        "INSERT INTO users VALUES (2, 'Bob')",
        "SELECT * FROM users WHERE id = 1",
    ];

    for query in queries {
        let result = db.query(query);
        println!("Result: {:?}", result);
    }
}

On-Disk ReefDB Example

use reefdb::OnDiskReefDB;

fn main() {
    let kv_path = "kv.db";
    let index = "index.bin";
    let mut db = OnDiskReefDB::new(kv_path.to_string(), index.to_string());

    let queries = vec![
        "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)",
        "INSERT INTO users VALUES (1, 'Alice')",
        "INSERT INTO users VALUES (2, 'Bob')",
        "SELECT * FROM users WHERE id = 1",
    ];

    for query in queries {
        let result = db.query(query);
        println!("Result: {:?}", result);
    }
}

Full-Text Search Example

use reefdb::InMemoryReefDB;

fn main() {
    let mut db = InMemoryReefDB::new();

    let queries = vec![
        "CREATE TABLE books (title TEXT, author TEXT, description FTS_TEXT)",
        "INSERT INTO books VALUES ('Book 1', 'Author 1', 'A book about the history of computer science.')",
        "INSERT INTO books VALUES ('Book 2', 'Author 2', 'A book about modern programming languages.')",
        "INSERT INTO books VALUES ('Book 3', 'Author 3', 'A book about the future of artificial intelligence.')",
        "SELECT title, author FROM books WHERE description MATCH 'computer science'",
    ];

    for query in queries {
        let result = db.query(query);
        println!("Result: {:?}", result);
    }
}

DELETE Example

use reefdb::InMemoryReefDB;

fn main() {
    let mut db = InMemoryReefDB::new();

    let queries = vec![
        "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)",
        "INSERT INTO users VALUES (1, 'Alice')",
        "INSERT INTO users VALUES (2, 'Bob')",
        "DELETE FROM users WHERE id = 1",
        "SELECT * FROM users",
    ];

    for query in queries {
        let result = db.query(query);
        println!("Result: {:?}", result);
    }
}

UPDATE Example

use reefdb::InMemoryReefDB;

fn main() {
    let mut db = InMemoryReefDB::new();

    let queries = vec![
        "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)",
        "INSERT INTO users VALUES (1, 'Alice')",
        "INSERT INTO users VALUES (2, 'Bob')",
        "UPDATE users SET name = 'Charlie' WHERE id = 1",
        "SELECT * FROM users",
    ];

    for query in queries {
        let result = db.query(query);
        println!("Result: {:?}", result);
    }
}

INNER JOIN Example

use reefdb::InMemoryReefDB;

fn main() {
    let mut db = InMemoryReefDB::new();

    let queries = vec![
        "CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT)",
        "CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER)",
        "INSERT INTO authors VALUES (1, 'Alice')",
        "INSERT INTO authors VALUES (2, 'Bob')",
        "INSERT INTO books VALUES (1, 'Book 1', 1)",
        "INSERT INTO books VALUES (2, 'Book 2', 2)",
        "SELECT authors.name, books.title FROM authors INNER JOIN books ON authors.id = books.author_id",
    ];

    for query in queries {
        let result = db.query(query);
        println!("Result: {:?}", result);
    }
}

Future Improvements (TODOs)

License

This project is licensed under the MIT License. See LICENSE for more information.

🙏 This is a sweet looking library. I wanted to use Tantivy is WASM but it's a pain, so I'm hoping I can get this working in WASM.

The in-memory example in the README doesn't work and I think it's because query parsing is broken for SELECT *. Changing SELECT * to SELECT users.name works fine.

use reefdb::InMemoryReefDB;

fn main() {
    test_readme_in_memory_example();
    test_readme_in_memory_example_FIXED();
    // test_inner_join();
    // test_fts_text_search();
}

// It seems that `SELECT *` isn't working. The _FIXED version of the function
// below does `SELECT users.name` and that works.
fn test_readme_in_memory_example() {
    let mut db = InMemoryReefDB::new();

    let queries = vec![
        "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)",
        "INSERT INTO users VALUES (1, 'Alice')",
        "INSERT INTO users VALUES (2, 'Bob')",
        "SELECT * FROM users WHERE id = 1",
    ];

    for query in queries {
        let result = db.query(query);
        println!("Result: {:?}", result);
    }
}

fn test_readme_in_memory_example_FIXED() {
    let mut db = InMemoryReefDB::new();

    let queries = vec![
        "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)",
        "INSERT INTO users VALUES (1, 'Alice')",
        "INSERT INTO users VALUES (2, 'Bob')",
        "SELECT users.name FROM users WHERE id = 1",
    ];

    for query in queries {
        let result = db.query(query);
        println!("Result: {:?}", result);
    }
}

Output:

Result: Ok(CreateTable)
Result: Ok(Insert(1))
Result: Ok(Insert(1))
Failed to parse statement: Parsing Error: Error { input: "SELECT * FROM users WHERE id = 1", code: Tag }
Result: Err(Other("Parsing Error: Error { input: \"SELECT * FROM users WHERE id = 1\", code: Tag }"))
Result: Ok(CreateTable)
Result: Ok(Insert(1))
Result: Ok(Insert(1))
Result: Ok(Select([(0, [Text("Alice")])]))

documentation enhancement

Type-safe SQL query wrappers

fnsql The fnsql crate provides simple type-safe optional wrappers around SQL queries. Instead of calling type-less .query() and .execute(), you call

9 Apr 29, 2022

A Toy Query Engine & SQL interface

Naive Query Engine (Toy for Learning) 😄 This is a Query Engine which support SQL interface. And it is only a Toy for learn query engine only. You can

45 Dec 21, 2022

FeOphant - A SQL database server written in Rust and inspired by PostreSQL.

A PostgreSQL inspired SQL database written in Rust.

27 Dec 7, 2022

GlueSQL is a SQL database library written in Rust

GlueSQL is a SQL database library written in Rust. It provides a parser (sqlparser-rs), execution layer, and optional storage (sled) packaged into a single library.

2.1k Jan 8, 2023

Distributed SQL database in Rust, written as a learning project

toyDB Distributed SQL database in Rust, written as a learning project. Most components are built from scratch, including: Raft-based distributed conse

4.6k Jan 8, 2023

A user crud written in Rust, designed to connect to a MySQL database with full integration test coverage.

SQLX User CRUD Purpose This application demonstrates the how to implement a common design for CRUDs in, potentially, a system of microservices. The de

78 Nov 27, 2022

Distributed, version controlled, SQL database with cryptographically verifiable storage, queries and results. Think git for postgres.

SDB - SignatureDB Distributed, version controlled, SQL database with cryptographically verifiable storage, queries and results. Think git for postgres

5 Apr 26, 2022

SQL database to read and write "discord"

GlueSQL Discord Storage After discussing how CI testing will be managed, we plan to move it upstream. Precautions for use discord ToS https://discord.

9 Feb 28, 2023

RisingWave is a cloud-native streaming database that uses SQL as the interface language.

RisingWave is a cloud-native streaming database that uses SQL as the interface language. It is designed to reduce the complexity and cost of building real-time applications. RisingWave consumes streaming data, performs continuous queries, and updates results dynamically. As a database system, RisingWave maintains results inside its own storage and allows users to access data efficiently.