GitHub - tonbo-io/tonbolite: TonboLite: Analysis-ready SQLite using Tonbo

TonboLite

TonboLite is a WASM compatible SQLite extension that allows users to create tables which supports analytical processing directly in SQLite. Its storage engine is powered by our open-source embedded key-value database, Tonbo.

Features

Organizing Parquet files using Log-Structured Merge Tree for fast writing
Supports OPFS, S3 as remote storage or mixed them together as storage back-end.
Compatibility with WebAssembly

Usage

Use in SQLite CLI

Use .load command to load a SQLite extension

sqlite> .load target/release/libsqlite_tonbo

sqlite> CREATE VIRTUAL TABLE temp.tonbo USING tonbo(
    create_sql = 'create table tonbo(id bigint primary key, name varchar, like int)',
    path = 'db_path/tonbo'
);
sqlite> insert into tonbo (id, name, like) values (0, 'tonbo', 100);
sqlite> insert into tonbo (id, name, like) values (1, 'sqlite', 200);

sqlite> select * from tonbo;
0|tonbo|100
1|sqlite|200

sqlite> update tonbo set like = 123 where id = 0;

sqlite> select * from tonbo;
0|tonbo|123
1|sqlite|200

sqlite> delete from tonbo where id = 0;

sqlite> select * from tonbo;
1|sqlite|200

Or use SQLite extension in Python:

import sqlite3

conn = sqlite3.connect(":memory")
conn.enable_load_extension(True)
# Load the tonbolite extension
conn.load_extension("target/release/libsqlite_tonbo.dylib")
con.enable_load_extension(False)

conn.execute("CREATE VIRTUAL TABLE temp.tonbo USING tonbo("
                "create_sql = 'create table tonbo(id bigint primary key, name varchar, like int)', "
                "path = 'db_path/tonbo'"
             ")")
conn.execute("INSERT INTO tonbo (id, name, like) VALUES (0, 'lol', 1)")
conn.execute("INSERT INTO tonbo (id, name, like) VALUES (1, 'lol', 100)")
rows = conn.execute("SELECT * FROM tonbo;")
for row in rows:
    print(row)

Use in Rust

TonboLite is able to be used just like a regular SQLite program.

Please use our Rusqlite patch

[patch.crates-io.rusqlite]
git = "https://github.com/tonbo-io/rusqlite"
branch = "feat/integrity"

#[tokio::main]
async fn main() -> rusqlite::Result<()>  {
    let db = Connection::open_in_memory()?;
    // load TonboLite
    load_module(&db)?;

    // use TonboLite like normal SQLite
    db.execute_batch(
        "CREATE VIRTUAL TABLE temp.tonbo USING tonbo(
            create_sql = 'create table tonbo(id bigint primary key, name varchar, like int)'
            path = 'db_path/tonbo'
        );",
    )?;
    db.execute("INSERT INTO tonbo (id, name, like) VALUES (0, 'lol', 1)", [])?;

    // query from table
    let mut stmt = db.prepare("SELECT * FROM tonbo;")?;
    let mut rows = stmt.query([])?;
    while let Some(row) = rows.next()? {
        println!("{:#?}", row);
    }
}

Use in Wasm

TonboLite exposed an easy-to-use API

const conn = new tonbo.Connection();

// create table with `CREATE VIRTUAL TABLE` statement
await conn.create(
    `CREATE VIRTUAL TABLE temp.tonbo USING tonbo(
        create_sql = 'create table tonbo(id bigint primary key, name varchar, like int)',
        path = 'db_path/tonbo'
    );`
);

// insert/update/delete table
await conn.insert(
  `INSERT INTO tonbo (id, name, like) VALUES (0, 'lol', 0)`
);
await conn.update("UPDATE tonbo SET name = 'bar' WHERE id = 0");
await conn.delete("DELETE from tonbo WHERE id = 0");

// query from table
const rows = await conn.select("SELECT * from tonbo");

// fulsh in-memory data to S3
await conn.flush("tonbo");

Configuration

Configure tonbolite in CREATE statement:

create_sql(required): The CREATE SQL statement
path(required): Path to local storage
fs: local/s3
level: All data below the level will be stored in local storage, otherwise, it will be stored in S3.
S3 option:
- key_id: The S3 access key
- secret_key: The S3 secret access key
- bucket: The S3 bucket
- endpoint: The S3 endpoint
- region: The S3 region
- sign_payload: true/false. Whether to use payload
- checksum: true/false. Whether to use checksum
- token: security token

Here is an example to configure S3 storage:

CREATE VIRTUAL TABLE temp.tonbo USING tonbo(
    create_sql='create table tonbo(id bigint primary key, name varchar, like int)',
    path = 'db_path/test_s3',
    level = '0',
    fs = 's3',
    bucket = 'bucket',
    key_id = 'access_key',
    secret_key = 'access_secret_key',
    endpoint = 'https://xxx.s3.us-east.amazonaws.com'
);

Build

Build as Extension

cargo build --release --features loadable_extension

Once building successfully, you will get a file named libsqlite_tonbo.dylib(.dll on windows, .so on most other unixes) in target/release/

Build on Rust

Build on Wasm

To use TonboLite in wasm, it takes a few steps to build.

Add wasm32-unknown-unknown target

rustup target add wasm32-unknown-unknown

Override toolchain with nightly

rustup override set nightly

Build with wasm-pack

wasm-pack build --target web --no-default-features --features wasm

Once you build successfully, you will get a pkg folder containing compiled js and wasm files. Copy it to your project and then you can start to use it.

const tonbo = await import("./pkg/sqlite_tonbo.js");
await tonbo.default();

// start to use TonboLite ...

Limitation

TonboLite should be used in a secure context and cross-origin isolated, since it uses SharedArrayBuffer to share memory. Please refer to this article for a detailed explanation.