SQL as a Function for Rust

 

Introduction

KiteSQL is a lightweight embedded relational database written in Rust, inspired by MyRocks and SQLite. It runs inside your application with no external service: execute SQL directly, or use typed ORM models, migrations, and builder-style queries.

• Most of the SQL 2016 syntax
• All metadata and data stored in KV storage (RocksDB, LMDB, or in-memory)
• Typed ORM with schema migration (orm feature), see src/orm/README.md
• WebAssembly and Python bindings

👉 More features

Example

use kite_sql::db::DataBaseBuilder;
use kite_sql::errors::DatabaseError;
use kite_sql::orm::OrmQueryResultExt;
use kite_sql::Model;

#[derive(Default, Debug, PartialEq, Model)]
#[model(table = "users")]
struct User {
    #[model(primary_key)]
    id: i32,
    #[model(varchar = 64)]
    name: String,
    #[model(default = "18", index)]
    age: Option<i32>,
}

fn main() -> Result<(), DatabaseError> {
    let mut database = DataBaseBuilder::path("./data").build_rocksdb()?;
    database.migrate::<User>()?;

    database.insert_many([
        User { id: 1, name: "Alice".to_string(), age: Some(18) },
        User { id: 2, name: "Bob".to_string(), age: Some(24) },
    ])?;

    let users = database
        .bind(|ctx| {
            ctx.from::<User>()?
                .filter(|e| e.column(User::age())?.gte(18))?
                .project_scalars((User::id(), User::name()))?
                .order_by(User::name())?
                .finish()
        })?
        .project_tuple::<(i32, String)>();
    for user in users {
        println!("{:?}", user?);
    }

    // Plain SQL works too.
    database.run("select count(*) from users")?.done()?;
    Ok(())
}

More: hello_world, transaction.

Storage Backends

Builder	Storage
build_rocksdb()	RocksDB (default feature rocksdb), stronger for write-heavy workloads
build_lmdb()	LMDB (feature lmdb), stronger for read-heavy workloads
build_in_memory()	In-memory, for tests and temporary data
build_optimistic()	RocksDB with optimistic transactions

Feature flags, checkpoints, and transaction isolation: docs/features.md, docs/transaction-isolation.md.

Shell, WebAssembly, Python

Shell

cargo run --bin kitesql-shell
cargo run --bin kitesql-shell -- -e "select 1"

Type .help for metacommands.

WebAssembly

wasm-pack build --release --target nodejs

import { WasmDatabase } from "./pkg/kite_sql.js";

const db = new WasmDatabase();
await db.ddl("create table demo(id int primary key, v int)");
console.log(db.run("select * from demo").rows());

Python

Requires the python feature.

import kite_sql

db = kite_sql.Database.in_memory()  # or Database(path, backend="lmdb")
db.execute("create table demo(id int primary key, v int)")
print(list(db.run("select * from demo")))

TPC-C

make tpcc runs the benchmark (--backend kitesql-lmdb|kitesql-rocksdb|sqlite); make tpcc-dual cross-checks every statement against SQLite.

720-second single-thread run on an i9-13900HX (32 GB, KIOXIA EXCERIA PLUS G3), every backend pinned to the same P-core and run without fsync on commit. Latencies are p90 in µs, including commit.

Backend	TpmC	New-Order	Payment	Order-Status	Delivery	Stock-Level
KiteSQL LMDB	165956	303	75	43	436	318
KiteSQL RocksDB	50739	392	188	198	11743	812
SQLite (WAL, synchronous=OFF)	191484	274	41	33	343	268

👉 Details and how to reproduce

Roadmap

• Get SQL 2016 mostly supported
• LLVM JIT: Perf: TPCC

License

KiteSQL uses the Apache 2.0 license to strike a balance between open contributions and allowing you to use the software however you want.

Contributors