crossbind
GitHub

SQLite

v3.53.4Database

SQLite 3.53.4, embedded relational database, packaged by crossbind as @crossbind/port-sqlite3 and one package per target. Only a variant that is actually on npm beta is listed as published.

npm install @crossbind/port-sqlite3-wasm@beta
LIVE · 3 APPS · RUNS IN THIS TAB

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

These apps run SQLite 3.53.4, 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 01

Search this site as you type

Every guide, reference, library and changelog page of crossbind.dev, split into its sections, goes into a SQLite FTS4 index in this tab. It matches word stems, phrases, prefixes, NEAR and NOT, and a BM25 function written in C++ ranks the results, because FTS4 has no ranking of its own.

The text is the site's own page data, the same copy you are reading. Try what substring matching cannot do: parse also finds "parsing", quotes make a phrase, * a prefix.

Index the site, then search it as you type.
SHOW THE CODE
src/native/doc_search.h
// src/native/doc_search.h (excerpt): FTS4 has no ranking function, so C++ supplies one
static void bm25(sqlite3_context* context, int argc, sqlite3_value** argv) {
// matchinfo(docs, 'pcnalx'): phrases, columns, rows, average and own lengths, hits
...
const double idf = std::log(1 + (rows - documents + 0.5) / (documents + 0.5));
const double norm = 1 - b + b * length[column] / average[column];
score += weight * idf * frequency * (k1 + 1) / (frequency + k1 * norm);
...
sqlite3_result_double(context, score);
}
 
sqlite3_create_function(db, "bm25", -1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, nullptr, bm25, nullptr, nullptr);
 
// then it is plain SQL
select pages.title, snippet(docs, char(2), char(3), '…', 1, 14),
bm25(matchinfo(docs, 'pcnalx'), 3.0, 1.0) as score
from docs join pages on pages.id = docs.docid
where docs match ?1 order by score desc limit ?2
main.js
const m = await initNative();
const search = await new m.DocSearch();
await search.addJson(JSON.stringify(sections)); // [{ title, section, href, body }]
 
const { total, hits } = JSON.parse(await search.search('"react native"', 8));
// hits[0]: { title, section, href, snippet, score }
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(...)

Usage

The calls most SQLite code makes, each a small C++ header crossbind binds and the JavaScript that uses it. Every example runs here in WebAssembly and prints what the site build checked; the same headers and calls work on Android and iOS.

Each example also has a JavaScript only tab: the same task with no C++ file, calling SQLite's own headers from @crossbind/port-sqlite3 directly. All 5 work that way.

Imported straight from JavaScript, the headers need this configuration today; its comments say why.

crossbind.config.js
import sqlite3Wasm from '@crossbind/port-sqlite3-wasm/crossbind.config.js';
 
// sqlite3.h declares functions this build of SQLite does not have: two exist only
// without NDEBUG (SWIG reads the header without it, the release compile defines it),
// the others are Windows-only or need compile options the port leaves off. Ignoring
// them lets the header's bindings compile and link.
const NOT_IN_THIS_BUILD = [
'sqlite3_mutex_held',
'sqlite3_mutex_notheld',
'sqlite3_win32_set_directory',
'sqlite3_win32_set_directory8',
'sqlite3_win32_set_directory16',
'sqlite3_unlock_notify',
'sqlite3_stmt_scanstatus',
'sqlite3_stmt_scanstatus_v2',
'sqlite3_stmt_scanstatus_reset',
'sqlite3_snapshot_get',
'sqlite3_snapshot_open',
'sqlite3_snapshot_free',
'sqlite3_snapshot_cmp',
'sqlite3_snapshot_recover',
'sqlite3_carray_bind',
'sqlite3_carray_bind_v2',
];
 
// No C++ in this project: every binding comes from the port headers the JavaScript
// imports.
export default {
general: { name: 'sqlite3direct' },
dependencies: [
{
...sqlite3Wasm,
export: { ...sqlite3Wasm.export, ignoredDeclarations: NOT_IN_THIS_BUILD },
},
],
paths: { config: import.meta.url },
};

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.53.4 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

Add it to your project

One package per platform: install the ones you build for and list each in crossbind.config.js; crossbind compiles only the one that matches the build target. Your C++ goes in src/native, next to the headers it binds. Libraries explains the whole flow.

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 },
};

Platforms

PlatformRuns inBuildsPage
WebAssemblybrowsers, Node.js and edge runtimeswasm32, single-threaded and multi-threadedSQLite for WebAssembly
AndroidReact Native apps on Androidarm64-v8a devices and the x86_64 emulatorSQLite for Android
iOSReact Native apps on iOSarm64 devices and simulatorsSQLite for iOS
macOSnative Node.js addons and Electron on macOSarm64 and x64, macOS 11 or laterSQLite for macOS
Linuxnative Node.js addons on Linuxx64 and arm64, glibc 2.28 or laterSQLite for Linux
Windowsnative Node.js addons on Windowsx64 and arm64, Windows 10 or laterSQLite for Windows
WASIcommand-line programs under wasmtimewasm32-wasip3, single-threadedSQLite for WASI

Packages

TargetPackagenpm `beta`
Meta package@crossbind/port-sqlite32.0.0-beta.62
Web and Node.js@crossbind/port-sqlite3-wasm2.0.0-beta.62
WASI library@crossbind/port-sqlite3-wasi2.0.0-beta.62
WASI commands@crossbind/port-sqlite3-standalone-wasinot published
Android@crossbind/port-sqlite3-android2.0.0-beta.62
iOS@crossbind/port-sqlite3-ios2.0.0-beta.62
macOS@crossbind/port-sqlite3-darwin2.0.0-beta.62
Linux@crossbind/port-sqlite3-linux2.0.0-beta.62
Linux (musl)@crossbind/port-sqlite3-linuxmuslnot published
Windows@crossbind/port-sqlite3-win322.0.0-beta.62

Licence

  • npm license field of @crossbind/port-sqlite3: blessing.
  • The licence files that ship with the package, and the port recipe, are in the port directory.

Facts on this page come from the port manifests in the repository and from what npm served on beta when the site was built. See the Libraries guide for the full consumer flow.

MORE LIBRARIES
cURLExpatGDALGEOSGeoTIFFiconvLERClibjpeg-turbolibTIFFOpenSSLPROJSpatiaLiteWebPzlibZstandard
Type to search every guide page and section.
↑↓ navigate↵ openesc close