crossbind
GitHub

SQLite for WebAssembly

v3.53.4WebAssembly

SQLite 3.53.4 for browsers, Node.js and edge runtimes, precompiled for wasm32, single-threaded and multi-threaded as @crossbind/port-sqlite3-wasm.

npm install @crossbind/port-sqlite3-wasm@beta

Install

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

crossbind itself arrives with your bundler plugin, or with a new project from npm create crossbind@beta; Bundlers has Vite, Webpack, Rspack and Rollup.

Usage

Each example runs here, in this tab, and prints what the site build checked.

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

What is different on WebAssembly

  • In a browser the module runs in a Worker by default (useWorker), so every call returns a promise: await calls and constructors alike.
  • The module has its own filesystem: m.FS writes files, m.getFileBytes reads them back and m.autoMountFiles mounts File objects from an <input type=file>. /memfs lives in memory; /opfs persists across reloads and needs the Worker. See Filesystem.
  • In Node.js, m.FS is the real disk, so use real paths there.
  • Multi-threaded builds (runtime: 'mt') need COOP and COEP headers in production. See Threading.

Other platforms

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