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, in-memory, or SlateDB on S3
• Zero-copy reads: on LMDB, strings go from the mmap'd file into your structs as Cow::Borrowed, without a copy (how)
• Async API on object storage: the same engine as async, on SlateDB over Amazon S3 or an S3-compatible store such as MinIO or R2, see Async API on S3
• Typed ORM with schema migration (orm feature), see src/orm/README.md
• WebAssembly and Python bindings

👉 More features · Blog

📝 Zero-Copy SQL Computation on LMDB (中文): how strings travel from the mmap'd file to your struct without a copy, benchmarked against SQLite.

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, program, borrowed_parse (zero-copy reads against SQLite).

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
build_with_storage(SlateDbStorage::open_s3(..))	SlateDB on S3 (feature slatedb-s3, async API), see Async API on S3

Feature flags, checkpoints, and transaction isolation: docs/features.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 runs on an i9-13900HX (32 GB, KIOXIA EXCERIA PLUS G3), without fsync on commit. Latencies are p90 in µs, including commit. SlateDB runs on a local S3 server (s3s-fs) with its block cache in memory and on local disk.

1 thread, 1 warehouse, every backend pinned to the same P-core:

Backend	TpmC	New-Order	Payment	Order-Status	Delivery	Stock-Level
KiteSQL LMDB	254023	211	38	25	293	175
KiteSQL RocksDB	47488	372	85	415	15471	389
KiteSQL SlateDB (S3)	12016	677	240	399	74047	1638
SQLite (WAL, synchronous=OFF)	214001	251	37	32	338	227

4 threads, 4 warehouses (one home warehouse per terminal), pinned to four P-cores:

Backend	TpmC	New-Order	Payment	Order-Status	Delivery	Stock-Level
KiteSQL LMDB	240584	930	780	29	890	207
KiteSQL RocksDB	212936	505	109	285	8991	400
KiteSQL SlateDB (S3)	19010	1749	1816	2481	66495	2523
SQLite (WAL, synchronous=OFF)	205896	254	41	45	336	380

👉 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