FnckSQL

FnckSQL is a lightweight, LSM KV-based SQL Database Management System (DBMS) created by individual developers as a hobby project. The core objective of FnckSQL is to demonstrate that anyone can write a database, even if the primary author cannot find a job in the industry. The project name and its irreverent tone reflect this sentiment, symbolizing a defiant stance toward the "beautiful" database industry.

FnckSQL offers a variety of features typically found in more mature SQL databases, including:

FnckSQL utilizes a Log-Structured Merge (LSM) tree as its storage engine, providing efficient write performance and supporting high throughput for database operations.

FnckSQL supports a comprehensive SQL interface with capabilities such as projections, filters, joins, aggregates, and transactions. This makes it a fully functional SQL database, suitable for learning and experimentation.

FnckSQL includes Object-Relational Mapping (ORM) features, enabling the use of Rust structs directly within the database:

#[derive(Default, Debug, PartialEq)]
struct MyStruct {
  c1: i32,
  c2: String,
}
 
implement_from_tuple!(
    MyStruct, (
        c1: i32 => |inner: &mut MyStruct, value| {
            if let DataValue::Int32(Some(val)) = value {
                inner.c1 = val;
            }
        },
        c2: String => |inner: &mut MyStruct, value| {
            if let DataValue::Utf8(Some(val)) = value {
                inner.c2 = val;
            }
        }
    )
);

Developers can create user-defined functions to extend the functionality of the database:

function!(TestFunction::test(LogicalType::Integer, LogicalType::Integer) -> LogicalType::Integer => |v1: ValueRef, v2: ValueRef| {
    let plus_binary_evaluator = EvaluatorFactory::binary_create(LogicalType::Integer, BinaryOperator::Plus)?;
    let value = plus_binary_evaluator.binary_eval(&v1, &v2);
 
    let plus_unary_evaluator = EvaluatorFactory::unary_create(LogicalType::Integer, UnaryOperator::Minus)?;
    Ok(plus_unary_evaluator.unary_eval(&value))
});
 
let fnck_sql = DataBaseBuilder::path("./data")
    .register_function(TestFunction::new())
    .build()
    .await?;

FnckSQL features both a Rule-Based Optimizer (RBO) and a Cost-Based Optimizer (CBO). For execution, it uses the Volcano model and supports LuaJIT for code generation.

FnckSQL implements Multi-Version Concurrency Control (MVCC) with support for optimistic transactions, ensuring data consistency and isolation across multiple transactions.

FnckSQL supports various index types, including primary, unique, normal, and composite indexes. It also offers a full range of SQL query capabilities, such as:

• Select: With SeqScan and IndexScan
• Where, Distinct, Alias
• Aggregation: count(), sum(), avg(), min(), max()
• Subqueries: Select, From, Where
• Join: Inner, Left, Right, Full, Cross
• Group By, Having, Order By, Limit
• DDL: Create Table, Index, Drop Table, Add/Drop Column, Truncate
• DML: Insert, Update, Delete

FnckSQL provides Docker images for easy deployment:

docker pull kould23333/fncksql:latest

To build from source:

git clone https://github.com/KipData/FnckSQL.git
cd FnckSQL
docker build -t kould23333/fncksql:latest .

To run the Docker container:

docker run -d \
--name=fncksql \
-p 5432:5432 \
--restart=always \
-v fncksql-data:/fnck_sql/fncksql_data \
-v /etc/localtime:/etc/localtime:ro \
kould23333/fncksql:latest