crossbind
GitHub
← All libraries

SQLite

Embedded relational database.

Install

Runs in browsers; Node.js and edge runtimes use the same package. Built for wasm32, single-threaded and multi-threaded.

shell
npm install @crossbind/port-sqlite3-wasm@beta
crossbind.config.js
import sqlite3Wasm from '@crossbind/port-sqlite3-wasm/crossbind.config.js';
 
export default {
dependencies: [sqlite3Wasm],
paths: { config: import.meta.url },
};
main.js
import { initNative, sqlite3_libversion } from '@crossbind/port-sqlite3/sqlite3.h';
 
await initNative();

crossbind itself comes with your bundler plugin; Browser has Vite, Webpack, Rspack and Rollup.

SQLite in your browser, with your C++ running beside it

These apps run SQLite 3.54.1, compiled by crossbind, next to small C++ wrappers: a BM25 ranking function that FTS4 lacks, an R*Tree timed against a B-tree and a table scan, and a read-only explorer for database files you open. The first run downloads 1.6 MB of WebAssembly once; every app on this page shares it, and nothing is uploaded.

APP 02

Ask 50,000 points what is inside a box

Drag a box across the points. SQLite's R*Tree finds them through its spatial index; the same query then runs against a B-tree index on x and against a table with no index at all, each timed here in your tab, next to the plan SQLite chose for it.

The module fills three tables with the same generated points; the page draws them from the same xorshift32 sequence, so only the answers cross over.

Load the points, then drag a box over them.
SHOW THE CODE
src/native/spatial_index.h
// src/native/spatial_index.h (excerpt)
create virtual table pts using rtree_i32(id, minx, maxx, miny, maxy);
create table indexed(id integer primary key, x integer not null, y integer not null);
create index indexed_x on indexed(x);
create table plain(id integer primary key, x integer not null, y integer not null);
 
-- a point is a box of size zero
insert into pts values (?1, ?2, ?2, ?3, ?3);
 
select count(*) from pts where minx >= ?1 and maxx <= ?3 and miny >= ?2 and maxy <= ?4;
select count(*) from indexed where x between ?1 and ?3 and y between ?2 and ?4;
select count(*) from plain where x between ?1 and ?3 and y between ?2 and ?4;
main.js
const m = await initNative();
const space = await new m.SpatialIndex();
await space.generate(50000, 2463534242); // xorshift32 points in [0, 1000)²
 
await space.count(400, 400, 599, 599, 'rtree'); // 2018
await space.plan('rtree'); // SCAN pts VIRTUAL TABLE INDEX 2:D0B1D2B3
await space.measure(400, 400, 599, 599, 'scan'); // ms per query, timed in C++
APP 03

Open any SQLite file, privately

Open a .sqlite or .db file, a GeoPackage or an MBTiles tile set to see its tables, run SQL on it and export the result as CSV. SQLite opens it read-only as an immutable database, so it never writes to your file, and a WAL-mode file opens without its -wal and -shm companions. Or generate a log of 100,000 API requests and ask it for p95 latency with SQLite's percentile().

Your file stays in this tab: it is copied into the module's in-memory filesystem and never uploaded. Changes still waiting in a separate -wal file are not part of what you see.

Open the sample or a SQLite file of your own to see its tables and query it.
SHOW THE CODE
src/native/db_explorer.h
// src/native/db_explorer.h (excerpt)
// "file:/memfs/…/name.sqlite?immutable=1": no writes, no locks, no -wal or -shm needed
explicit DbExplorer(const std::string& path)
: file(path), db(uri(path), SQLITE_OPEN_READONLY | SQLITE_OPEN_URI) {
// a runaway query stops after 10 s instead of holding the worker
sqlite3_progress_handler(db.get(), 1000, stopAtDeadline, this);
sql::exec(db.get(), "select count(*) from sqlite_schema"); // "file is not a database"
}
 
// a compacted copy for the download
sql::Statement vacuum = sql::prepare(db.get(), "vacuum into ?1");
sql::bind(vacuum.get(), 1, path);
sql::step(db.get(), vacuum.get());
main.js
const m = await initNative();
const [path] = await m.autoMountFiles([file], await m.getRandomPath('/memfs'));
const db = await new m.DbExplorer(path);
 
JSON.parse(await db.tables()); // [{ name, type, rows, columns: [...] }]
JSON.parse(await db.query('select * from requests limit 5', 200));
await db.csv('select * from requests'); // RFC 4180 text
await db.saveAs('/memfs/sqliteapps/copy.sqlite'); // then m.getFileBytes(...)

Examples

Each one is a small C++ header and the JavaScript that calls it, with what it printed when the site was built. The JavaScript only tab does the same with SQLite's own headers and no C++ file. The last is a whole C++ program, built for WASI.

Insert and query with prepared statements

The calls behind most SQLite code: sqlite3_prepare_v2, sqlite3_bind_text, sqlite3_step and sqlite3_column_*. Values go in as bound parameters, so the apostrophe in "parser's" is data, not SQL.

src/native/notes.h
#pragma once
 
#include <sqlite3.h>
 
#include <memory>
#include <stdexcept>
#include <string>
 
// Notes in an in-memory SQLite database. Values reach SQL only as bound parameters (?1), never by
// pasting them into the statement, so a quote inside a note is data, not SQL.
class Notes {
public:
Notes() {
if (sqlite3_open(":memory:", &db) != SQLITE_OK) throw std::runtime_error("cannot open the database");
run("create table notes(id integer primary key, body text not null)");
}
 
~Notes() { sqlite3_close(db); }
 
static std::string version() { return sqlite3_libversion(); }
 
// Inserts one note and returns its id.
int add(const std::string& body) {
Statement insert = prepare("insert into notes(body) values (?1)");
sqlite3_bind_text(insert.get(), 1, body.c_str(), static_cast<int>(body.size()), SQLITE_TRANSIENT);
if (sqlite3_step(insert.get()) != SQLITE_DONE) throw std::runtime_error(sqlite3_errmsg(db));
return static_cast<int>(sqlite3_last_insert_rowid(db));
}
 
int count() {
Statement query = prepare("select count(*) from notes");
return sqlite3_step(query.get()) == SQLITE_ROW ? sqlite3_column_int(query.get(), 0) : 0;
}
 
// Every note containing `word`, oldest first, one "id: body" line each.
std::string find(const std::string& word) {
Statement query = prepare("select id, body from notes where body like '%' || ?1 || '%' order by id");
sqlite3_bind_text(query.get(), 1, word.c_str(), static_cast<int>(word.size()), SQLITE_TRANSIENT);
std::string lines;
int result;
while ((result = sqlite3_step(query.get())) == SQLITE_ROW) {
const int id = sqlite3_column_int(query.get(), 0);
const char* body = reinterpret_cast<const char*>(sqlite3_column_text(query.get(), 1));
lines += (lines.empty() ? "" : "\n") + std::to_string(id) + ": " + body;
}
if (result != SQLITE_DONE) throw std::runtime_error(sqlite3_errmsg(db));
return lines;
}
 
private:
// Finalizes the statement however the method returns.
using Statement = std::unique_ptr<sqlite3_stmt, int (*)(sqlite3_stmt*)>;
 
Statement prepare(const char* sql) {
sqlite3_stmt* statement = nullptr;
if (sqlite3_prepare_v2(db, sql, -1, &statement, nullptr) != SQLITE_OK) throw std::runtime_error(sqlite3_errmsg(db));
return Statement(statement, sqlite3_finalize);
}
 
void run(const char* sql) {
char* message = nullptr;
if (sqlite3_exec(db, sql, nullptr, nullptr, &message) == SQLITE_OK) return;
const std::string reason = message ? message : "unknown error";
sqlite3_free(message);
throw std::runtime_error(reason);
}
 
sqlite3* db = nullptr;
};
main.js
import { initNative, Notes } from './native/notes.h';
 
await initNative();
const notes = await new Notes();
for (const body of ['buy milk', 'write the README', "fix the parser's bug"]) await notes.add(body);
console.log(await Notes.version(), await notes.count());
console.log(await notes.find('the'));
PRINTSfirst run downloads 1.6 MB
3.54.1 3
2: write the README
3: fix the parser's bug

Apply a batch in one transaction

BEGIN, one prepared upsert (ON CONFLICT DO UPDATE) reset and re-bound for every row, then COMMIT. A row that breaks a CHECK constraint makes the wrapper ROLLBACK, which undoes the rows before it as well.

src/native/ledger.h
#pragma once
 
#include <sqlite3.h>
 
#include <memory>
#include <stdexcept>
#include <string>
 
// Account balances updated in batches. A batch is one transaction: BEGIN, one prepared upsert that
// is reset and re-bound for every row, then COMMIT. When a row fails, ROLLBACK undoes the rows
// before it too, so a batch is applied completely or not at all.
class Ledger {
public:
Ledger() {
if (sqlite3_open(":memory:", &db) != SQLITE_OK) throw std::runtime_error("cannot open the database");
run("create table balances(account text primary key, total integer not null check (total >= 0))");
}
 
~Ledger() { sqlite3_close(db); }
 
// `rows` holds one "account,amount" line per entry; returns how many rows were applied.
int apply(const std::string& rows) {
run("begin");
try {
const int applied = upsertEach(rows);
run("commit");
return applied;
} catch (...) {
sqlite3_exec(db, "rollback", nullptr, nullptr, nullptr);
throw;
}
}
 
// "account total" pairs in account order.
std::string balances() {
Statement query = prepare("select account, total from balances order by account");
std::string out;
while (sqlite3_step(query.get()) == SQLITE_ROW) {
out += (out.empty() ? "" : ", ") + std::string(reinterpret_cast<const char*>(sqlite3_column_text(query.get(), 0))) + " " +
std::to_string(sqlite3_column_int64(query.get(), 1));
}
return out;
}
 
private:
int upsertEach(const std::string& rows) {
Statement upsert = prepare(
"insert into balances(account, total) values (?1, ?2) "
"on conflict(account) do update set total = total + excluded.total");
int applied = 0;
for (size_t start = 0; start < rows.size();) {
size_t end = rows.find('\n', start);
if (end == std::string::npos) end = rows.size();
const std::string row = rows.substr(start, end - start);
start = end + 1;
const size_t comma = row.find(',');
if (comma == std::string::npos) throw std::invalid_argument("expected account,amount but got: " + row);
sqlite3_bind_text(upsert.get(), 1, row.data(), static_cast<int>(comma), SQLITE_TRANSIENT);
sqlite3_bind_int64(upsert.get(), 2, std::stoll(row.substr(comma + 1)));
if (sqlite3_step(upsert.get()) != SQLITE_DONE) throw std::runtime_error(sqlite3_errmsg(db));
sqlite3_reset(upsert.get());
applied += 1;
}
return applied;
}
 
using Statement = std::unique_ptr<sqlite3_stmt, int (*)(sqlite3_stmt*)>;
 
Statement prepare(const char* sql) {
sqlite3_stmt* statement = nullptr;
if (sqlite3_prepare_v2(db, sql, -1, &statement, nullptr) != SQLITE_OK) throw std::runtime_error(sqlite3_errmsg(db));
return Statement(statement, sqlite3_finalize);
}
 
void run(const char* sql) {
char* message = nullptr;
if (sqlite3_exec(db, sql, nullptr, nullptr, &message) == SQLITE_OK) return;
const std::string reason = message ? message : "unknown error";
sqlite3_free(message);
throw std::runtime_error(reason);
}
 
sqlite3* db = nullptr;
};
main.js
import { initNative, Ledger } from './native/ledger.h';
 
await initNative();
const ledger = await new Ledger();
const accounts = ['alice', 'bob', 'carol'];
const rows = Array.from({ length: 10000 }, (_, i) => `${accounts[i % 3]},${(i % 7) + 1}`);
console.log(await ledger.apply(rows.join('\n')), 'rows applied');
console.log(await ledger.balances());
try {
await ledger.apply('alice,5\nbob,-1000000');
} catch (error) {
// wasm builds add the C++ type in front of the message and keep the text in cppMessage.
console.log('rolled back:', error.cppMessage ?? error.message);
}
console.log(await ledger.balances());
PRINTSfirst run downloads 1.6 MB
10000 rows applied
alice 13333, bob 13330, carol 13331
rolled back: CHECK constraint failed: total >= 0
alice 13333, bob 13330, carol 13331

Query JSON documents with SQL

Keep JSON as it arrives and ask questions in SQL: ->> reads a field, json_each turns an array into rows, and json_group_object and json_group_array build JSON answers.

src/native/order_log.h
#pragma once
 
#include <sqlite3.h>
 
#include <memory>
#include <stdexcept>
#include <string>
 
// Orders stored as JSON documents and queried with SQLite's JSON functions: ->> reads a field,
// json_each turns an array into rows, and json_group_object / json_group_array build the answer
// as JSON again. A CHECK on json_valid() rejects anything that is not JSON.
class OrderLog {
public:
OrderLog() {
if (sqlite3_open(":memory:", &db) != SQLITE_OK) throw std::runtime_error("cannot open the database");
run("create table orders(id integer primary key, doc text not null check (json_valid(doc)))");
}
 
~OrderLog() { sqlite3_close(db); }
 
void add(const std::string& json) {
Statement insert = prepare("insert into orders(doc) values (?1)");
sqlite3_bind_text(insert.get(), 1, json.c_str(), static_cast<int>(json.size()), SQLITE_TRANSIENT);
if (sqlite3_step(insert.get()) != SQLITE_DONE) throw std::runtime_error(sqlite3_errmsg(db));
}
 
// What each customer spent: {"customer": total, ...}
std::string totals() {
return value(
"select json_group_object(customer, total order by customer) from ("
" select doc ->> 'customer' as customer, sum((item.value ->> 'qty') * (item.value ->> 'price')) as total"
" from orders, json_each(orders.doc, '$.items') as item group by customer)");
}
 
// Units sold per product, best seller first: {"sku": units, ...}
std::string unitsSold() {
return value(
"select json_group_object(sku, units order by units desc, sku) from ("
" select item.value ->> 'sku' as sku, sum(item.value ->> 'qty') as units"
" from orders, json_each(orders.doc, '$.items') as item group by sku)");
}
 
// The customers who ordered `sku`, as a JSON array.
std::string buyersOf(const std::string& sku) {
return value(
"select json_group_array(distinct doc ->> 'customer' order by doc ->> 'customer')"
" from orders, json_each(orders.doc, '$.items') as item where item.value ->> 'sku' = ?1",
sku);
}
 
private:
// The first column of the first row, with `parameter` bound to ?1.
std::string value(const char* sql, const std::string& parameter = "") {
Statement query = prepare(sql);
if (sqlite3_bind_parameter_count(query.get()) > 0) {
sqlite3_bind_text(query.get(), 1, parameter.c_str(), static_cast<int>(parameter.size()), SQLITE_TRANSIENT);
}
if (sqlite3_step(query.get()) != SQLITE_ROW) throw std::runtime_error(sqlite3_errmsg(db));
const unsigned char* text = sqlite3_column_text(query.get(), 0);
return text ? reinterpret_cast<const char*>(text) : "null";
}
 
using Statement = std::unique_ptr<sqlite3_stmt, int (*)(sqlite3_stmt*)>;
 
Statement prepare(const char* sql) {
sqlite3_stmt* statement = nullptr;
if (sqlite3_prepare_v2(db, sql, -1, &statement, nullptr) != SQLITE_OK) throw std::runtime_error(sqlite3_errmsg(db));
return Statement(statement, sqlite3_finalize);
}
 
void run(const char* sql) {
char* message = nullptr;
if (sqlite3_exec(db, sql, nullptr, nullptr, &message) == SQLITE_OK) return;
const std::string reason = message ? message : "unknown error";
sqlite3_free(message);
throw std::runtime_error(reason);
}
 
sqlite3* db = nullptr;
};
main.js
import { initNative, OrderLog } from './native/order_log.h';
 
await initNative();
const orders = await new OrderLog();
await orders.add('{"customer":"ada","items":[{"sku":"pen","qty":2,"price":1.5},{"sku":"ink","qty":1,"price":4}]}');
await orders.add('{"customer":"linus","items":[{"sku":"pen","qty":10,"price":1.5}]}');
await orders.add('{"customer":"ada","items":[{"sku":"pad","qty":3,"price":2.25}]}');
console.log(await orders.totals());
console.log(await orders.unitsSold());
console.log(await orders.buyersOf('pen'));
PRINTSfirst run downloads 1.6 MB
{"ada":13.75,"linus":15.0}
{"pen":12,"pad":3,"ink":1}
["ada","linus"]

A full-text index with CREATE VIRTUAL TABLE … USING fts4, queried with MATCH and cut into snippet()s. Stemming, phrases, prefixes, NOT and NEAR come with it. This build has FTS3 and FTS4 but not FTS5.

src/native/search_index.h
#pragma once
 
#include <sqlite3.h>
 
#include <memory>
#include <stdexcept>
#include <string>
 
// Full-text search with SQLite's FTS4 module (this build has FTS3 and FTS4, not FTS5). The porter
// tokenizer reduces English words to their stems, so "parse" also finds "parses" and "parsing".
// MATCH takes FTS query syntax: "a phrase", prefix*, NOT, OR and NEAR/n.
class SearchIndex {
public:
SearchIndex() {
if (sqlite3_open(":memory:", &db) != SQLITE_OK) throw std::runtime_error("cannot open the database");
run("create virtual table docs using fts4(title, body, tokenize=porter)");
}
 
~SearchIndex() { sqlite3_close(db); }
 
void add(const std::string& title, const std::string& body) {
Statement insert = prepare("insert into docs(title, body) values (?1, ?2)");
sqlite3_bind_text(insert.get(), 1, title.c_str(), static_cast<int>(title.size()), SQLITE_TRANSIENT);
sqlite3_bind_text(insert.get(), 2, body.c_str(), static_cast<int>(body.size()), SQLITE_TRANSIENT);
if (sqlite3_step(insert.get()) != SQLITE_DONE) throw std::runtime_error(sqlite3_errmsg(db));
}
 
// A snippet of every matching document, in the order they were added, with the hits in
// [brackets]; the snippets are joined by " | ".
std::string search(const std::string& query) {
Statement select = prepare("select snippet(docs, '[', ']', '…', -1, 8) from docs where docs match ?1 order by docid");
sqlite3_bind_text(select.get(), 1, query.c_str(), static_cast<int>(query.size()), SQLITE_TRANSIENT);
std::string out;
int result;
while ((result = sqlite3_step(select.get())) == SQLITE_ROW) {
out += (out.empty() ? "" : " | ") + std::string(reinterpret_cast<const char*>(sqlite3_column_text(select.get(), 0)));
}
if (result != SQLITE_DONE) throw std::runtime_error(sqlite3_errmsg(db));
return out;
}
 
private:
using Statement = std::unique_ptr<sqlite3_stmt, int (*)(sqlite3_stmt*)>;
 
Statement prepare(const char* sql) {
sqlite3_stmt* statement = nullptr;
if (sqlite3_prepare_v2(db, sql, -1, &statement, nullptr) != SQLITE_OK) throw std::runtime_error(sqlite3_errmsg(db));
return Statement(statement, sqlite3_finalize);
}
 
void run(const char* sql) {
char* message = nullptr;
if (sqlite3_exec(db, sql, nullptr, nullptr, &message) == SQLITE_OK) return;
const std::string reason = message ? message : "unknown error";
sqlite3_free(message);
throw std::runtime_error(reason);
}
 
sqlite3* db = nullptr;
};
main.js
import { initNative, SearchIndex } from './native/search_index.h';
 
await initNative();
const index = await new SearchIndex();
await index.add('SQLite in the browser', 'SQLite runs inside the browser tab as WebAssembly; queries never leave the page.');
await index.add('OpenSSL certificates', 'Generate a key, a certificate signing request and a self-signed certificate offline.');
await index.add('curl URL parser', 'libcurl parses and normalizes URLs exactly like the curl command line tool.');
await index.add('Parsing XML with expat', 'expat is a stream-oriented parser: it reports each start tag, end tag and text run as it reads.');
for (const query of ['parse', '"signing request"', 'brows*', 'parser NOT expat', 'tag NEAR/2 text']) {
console.log(`${query} -> ${await index.search(query)}`);
}
PRINTSfirst run downloads 1.6 MB
parse -> libcurl [parses] and normalizes URLs exactly like the… | [Parsing] XML with expat
"signing request" -> …key, a certificate [signing] [request] and a self…
brows* -> SQLite in the [browser]
parser NOT expat -> curl URL [parser]
tag NEAR/2 text -> …start tag, end [tag] and [text] run as…

Save a database to bytes and open it again

sqlite3_serialize copies a database out as the bytes of its file, and sqlite3_deserialize opens such bytes as a database: the way to download, upload or cache a whole database. The copy is then listed with sqlite_schema and pragma_table_info.

src/native/snapshot.h
#pragma once
 
#include <sqlite3.h>
 
#include <memory>
#include <stdexcept>
#include <string>
 
// A whole database as bytes and back. sqlite3_serialize copies out the bytes SQLite would store in
// a database file, and sqlite3_deserialize opens such bytes as a database: in a browser, that is how
// a database is downloaded, uploaded, kept in IndexedDB or sent over the network. Bytes cross the
// binding as a byte string, one UTF-16 code unit (0-255) per byte.
class Snapshot {
public:
Snapshot() {
if (sqlite3_open(":memory:", &db) != SQLITE_OK) throw std::runtime_error("cannot open the database");
}
 
~Snapshot() { sqlite3_close(db); }
 
void exec(const std::string& sql) {
char* message = nullptr;
if (sqlite3_exec(db, sql.c_str(), nullptr, nullptr, &message) == SQLITE_OK) return;
const std::string reason = message ? message : "unknown error";
sqlite3_free(message);
throw std::runtime_error(reason);
}
 
// The database file's bytes.
std::u16string save() {
sqlite3_int64 size = 0;
unsigned char* data = sqlite3_serialize(db, "main", &size, 0);
if (!data) throw std::runtime_error("sqlite3_serialize could not copy the database");
std::u16string bytes(static_cast<size_t>(size), u'\0');
for (size_t i = 0; i < bytes.size(); ++i) bytes[i] = data[i];
sqlite3_free(data);
return bytes;
}
 
// Replaces this database with the one in `bytes`.
void load(const std::u16string& bytes) {
auto* data = static_cast<unsigned char*>(sqlite3_malloc64(bytes.size()));
if (!data) throw std::runtime_error("out of memory");
for (size_t i = 0; i < bytes.size(); ++i) {
if (bytes[i] > 0xFF) {
sqlite3_free(data);
throw std::invalid_argument("not a byte string");
}
data[i] = static_cast<unsigned char>(bytes[i]);
}
// SQLite owns `data` from here on, and frees it even when this call fails.
const int flags = SQLITE_DESERIALIZE_FREEONCLOSE | SQLITE_DESERIALIZE_RESIZEABLE;
if (sqlite3_deserialize(db, "main", data, bytes.size(), bytes.size(), flags) != SQLITE_OK) throw std::runtime_error(sqlite3_errmsg(db));
}
 
// Each table as "name(column TYPE, ...): N rows", one per line.
std::string describe() {
Statement tables = prepare("select name from sqlite_schema where type = 'table' and name not like 'sqlite_%' order by name");
std::string out;
while (sqlite3_step(tables.get()) == SQLITE_ROW) {
const std::string table = reinterpret_cast<const char*>(sqlite3_column_text(tables.get(), 0));
out += (out.empty() ? "" : "\n") + table + "(" + columnsOf(table) + "): " + std::to_string(rowsIn(table)) + " rows";
}
return out;
}
 
private:
std::string columnsOf(const std::string& table) {
Statement columns = prepare("select name, type from pragma_table_info(?1)");
sqlite3_bind_text(columns.get(), 1, table.c_str(), static_cast<int>(table.size()), SQLITE_TRANSIENT);
std::string out;
while (sqlite3_step(columns.get()) == SQLITE_ROW) {
out += (out.empty() ? "" : ", ") + std::string(reinterpret_cast<const char*>(sqlite3_column_text(columns.get(), 0))) + " " +
reinterpret_cast<const char*>(sqlite3_column_text(columns.get(), 1));
}
return out;
}
 
long long rowsIn(const std::string& table) {
// A table name cannot be a bound parameter, so it is quoted as an identifier instead.
std::string quoted = "\"";
for (char c : table) quoted += c == '"' ? std::string("\"\"") : std::string(1, c);
Statement count = prepare(("select count(*) from " + quoted + "\"").c_str());
return sqlite3_step(count.get()) == SQLITE_ROW ? sqlite3_column_int64(count.get(), 0) : 0;
}
 
using Statement = std::unique_ptr<sqlite3_stmt, int (*)(sqlite3_stmt*)>;
 
Statement prepare(const char* sql) {
sqlite3_stmt* statement = nullptr;
if (sqlite3_prepare_v2(db, sql, -1, &statement, nullptr) != SQLITE_OK) throw std::runtime_error(sqlite3_errmsg(db));
return Statement(statement, sqlite3_finalize);
}
 
sqlite3* db = nullptr;
};
main.js
import { initNative, Snapshot } from './native/snapshot.h';
 
await initNative();
const db = await new Snapshot();
await db.exec("create table readings(sensor text, celsius real); insert into readings values ('attic', 21.5), ('cellar', 12.25), ('garden', 17.0)");
const image = await db.save();
console.log(`${image.length} B, starts with "${image.slice(0, 15)}"`);
 
const copy = await new Snapshot();
await copy.load(image);
console.log(await copy.describe());
PRINTSfirst run downloads 1.6 MB
8192 B, starts with "SQLite format 3"
readings(sensor TEXT, celsius REAL): 3 rows

A SQL command-line tool

WASI has no JavaScript bindings: the program is a main() that links SQLite, built into one .wasm and run with wasmtime. This one runs SQL against a database file in the current directory and prints rows the way the sqlite3 shell does, so the file it writes opens in any SQLite tool.

crossbind.config.js
import sqlite3Wasi from '@crossbind/port-sqlite3-wasi/crossbind.config.js';
 
export default {
general: { name: 'sqlite-tool' },
dependencies: [sqlite3Wasi],
paths: { config: import.meta.url },
};
src/native/main.cpp
// A command-line SQL runner for WASI:
// sqlite-tool <database> "<sql>" runs the SQL against the database file
// sqlite-tool <database> reads the SQL from standard input
// Result rows print as the sqlite3 shell prints them by default, columns joined by |, and a
// summary line follows.
#include <sqlite3.h>
 
#include <cstdio>
#include <string>
#include <vector>
 
namespace {
 
std::string readAll(FILE* file) {
std::string text;
std::vector<char> buffer(64 * 1024);
size_t read = 0;
while ((read = std::fread(buffer.data(), 1, buffer.size(), file)) > 0) text.append(buffer.data(), read);
return text;
}
 
int fail(sqlite3* db, const char* what) {
std::fprintf(stderr, "%s: %s\n", what, sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
 
} // namespace
 
int main(int argc, char** argv) {
if (argc < 2 || argc > 3) {
std::fprintf(stderr, "usage: sqlite-tool <database> [sql] (without sql, reads it from standard input)\n");
return 2;
}
const std::string sql = argc == 3 ? argv[2] : readAll(stdin);
sqlite3* db = nullptr;
if (sqlite3_open(argv[1], &db) != SQLITE_OK) return fail(db, argv[1]);
int statements = 0;
const char* rest = sql.c_str();
const char* end = rest + sql.size();
while (rest < end) {
sqlite3_stmt* statement = nullptr;
if (sqlite3_prepare_v2(db, rest, static_cast<int>(end - rest), &statement, &rest) != SQLITE_OK) return fail(db, "sql");
if (!statement) continue;
statements += 1;
int result;
while ((result = sqlite3_step(statement)) == SQLITE_ROW) {
for (int column = 0; column < sqlite3_column_count(statement); column += 1) {
const unsigned char* text = sqlite3_column_text(statement, column);
std::printf("%s%s", column ? "|" : "", text ? reinterpret_cast<const char*>(text) : "");
}
std::printf("\n");
}
if (result != SQLITE_DONE) {
std::fprintf(stderr, "sql: %s\n", sqlite3_errmsg(db));
sqlite3_finalize(statement);
sqlite3_close(db);
return 1;
}
sqlite3_finalize(statement);
}
std::printf("sqlite %s, %s: %d statement%s, %d rows changed\n", sqlite3_libversion(), argv[1], statements, statements == 1 ? "" : "s",
sqlite3_total_changes(db));
sqlite3_close(db);
return 0;
}
shell
npx crossbind build -p wasi -e wasi -b release
wasmtime run --dir=. .crossbind/build/sqlite-tool-wasi-wasm32-st-release.wasm shop.db < shop.sql
wasmtime run --dir=. .crossbind/build/sqlite-tool-wasi-wasm32-st-release.wasm shop.db "select customer, sum(qty * price) from orders group by customer order by 2 desc"
output
sqlite 3.54.1, shop.db: 3 statements, 4 rows changed
linus|15.0
ada|13.75
sqlite 3.54.1, shop.db: 1 statement, 0 rows changed
Type to search every guide page and section.
↑↓ navigate↵ openesc close