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

Overview

ReefDB

ReefDB logo

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

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)

  • Implement support for more SQL statements such as ALTER TABLE, DROP TABLE, and additional JOIN types (LEFT, RIGHT, OUTER).
  • Add support for indexing and query optimization to improve performance.
  • Implement transaction support and atomicity for database operations (there is a transaction struct but it's not in autocommit mode like in sqlite)
  • Add support for user-defined functions, aggregate functions (SUM, COUNT, AVG, MIN, MAX), Grouping and sorting (GROUP BY and ORDER BY), and stored procedures.
  • Improve error handling and reporting.
  • Enhance the full-text search capability with more advanced text processing techniques such as stemming, tokenization, and handling of synonyms.
  • Implement authentication and authorization mechanisms for secure access to the database.
  • Add support for replication and distributed database management (using raft-rs?)
  • Implement a command-line interface or GUI for interacting with the database.
  • Improve documentation and provide examples for using the database in various use cases.
  • Write benchmarks and performance tests to measure and optimize the database performance.
  • Enforce constraints such as unique, primary key, foreign key, and check constraints to maintain data integrity.
  • Implement multi-threading and concurrency control for improved performance and safe parallel access to the database
  • Add support for handling various data types (e.g., date and time, binary data) and user-defined data types.
  • Optimize memory management and caching mechanisms for efficient resource utilization.

License

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

You might also like...
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

A Toy Query Engine & SQL interface
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

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

A PostgreSQL inspired SQL database written in Rust.

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.

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

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

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

SQL database to read and write
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.

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.

Comments
  • `SELECT *` seems broken

    `SELECT *` seems broken

    🙏 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 
    opened by aguynamedben 1
Owner
Sacha Arbonel
Let's build some nice abstractions. Flutter by Day 💙, Rust by Night 🦀
Sacha Arbonel
A Rust SQL query builder with a pleasant fluent API closely imitating actual SQL

Scooby An SQL query builder with a pleasant fluent API closely imitating actual SQL. Meant to comfortably build dynamic queries with a little bit of s

Aleksei Voronov 100 Nov 11, 2022
Gh-sql - Query GitHub Projects (beta) with SQL

gh-sql: Query GitHub Projects (beta) with SQL Installation gh extension install KOBA789/gh-sql Features SELECT items DELETE items UPDATE item fields

Hidekazu Kobayashi 108 Dec 7, 2022
Learn Rust black magics by implementing basic types in database systems

Type Exercise in Rust (In Chinese) 数据库表达式执行的黑魔法:用 Rust 做类型体操 This is a short lecture on how to use the Rust type system to build necessary components

Alex Chi 996 Jan 3, 2023
An object-relational in-memory cache, supports queries with an SQL-like query language.

qlcache An object-relational in-memory cache, supports queries with an SQL-like query language. Warning This is a rather low-level library, and only p

null 3 Nov 14, 2021
Query system statistics with SQL.

pg_stat_sysinfo Collects system statistics. ---- CREATE EXTENSION pg_stat_sysinfo; CREATE EXTENSION ---- SELECT * FROM pg_stat_sysinfo_collect();

PostgresML 12 Jun 14, 2023
🧰 The Rust SQL Toolkit. An async, pure Rust SQL crate featuring compile-time checked queries without a DSL. Supports PostgreSQL, MySQL, SQLite, and MSSQL.

SQLx ?? The Rust SQL Toolkit Install | Usage | Docs Built with ❤️ by The LaunchBadge team SQLx is an async, pure Rust† SQL crate featuring compile-tim

launchbadge 7.6k Dec 31, 2022
SQL validator tool for BigQuery standard SQL.

bqvalid What bqvalid does bqvalid is the SQL validator tool for BigQuery standard SQL. bqvalid fails with error message if there's the expression that

null 10 Dec 25, 2022
Rust library to parse, deparse and normalize SQL queries using the PostgreSQL query parser

This Rust library uses the actual PostgreSQL server source to parse SQL queries and return the internal PostgreSQL parse tree.

pganalyze 37 Dec 18, 2022
Fully typed SQL query builder for Rust [deprecated]

What is Deuterium? Deuterium is a fancy SQL builder for Rust. It's designed to provide a DSL to easily build SQL queries in safe and typed way. Like R

Stanislav Panferov 169 Nov 20, 2022
Query LDAP and AD with SQL

SQLDAP Ever wanted to query AD or LDAP with SQL like queries ? I'm going to answer this question myself: yes ! Why ? Because I never could remember al

null 9 Nov 15, 2022