SQLite for Android
v3.53.4AndroidSQLite 3.53.4 for React Native apps on Android, precompiled for arm64-v8a devices and the x86_64 emulator as @crossbind/port-sqlite3-android.
npm install @crossbind/port-sqlite3-android@betaInstall
shell
npm install @crossbind/plugin-react-native@beta @crossbind/plugin-react-native-ios-helper@beta @crossbind/port-sqlite3-android@beta
npm install --save-dev @crossbind/plugin-metro@beta
crossbind.config.mjs
import sqlite3Android from '@crossbind/port-sqlite3-android/crossbind.config.js';
export default {
dependencies: [sqlite3Android],
paths: { config: import.meta.url },
};
metro.config.js
const { getDefaultConfig, mergeConfig } = require('@react-native/metro-config');
const CrossbindMetroPlugin = require('@crossbind/plugin-metro');
const defaultConfig = getDefaultConfig(__dirname);
const config = {
...CrossbindMetroPlugin(defaultConfig),
};
module.exports = mergeConfig(defaultConfig, config);
The whole flow, including Expo, is in the React Native playbook.
Usage
The examples the WebAssembly page runs, as Android compiles them: the same headers and the same calls. They are checked on the WebAssembly build.
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'));
PRINTS
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());
PRINTS
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'));
PRINTS
{"ada":13.75,"linus":15.0}
{"pen":12,"pad":3,"ink":1}
["ada","linus"]Search text with FTS4
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)}`);
}
PRINTS
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());
PRINTS
8192 B, starts with "SQLite format 3" readings(sensor TEXT, celsius REAL): 3 rows
What is different on Android
- The React Native plugin compiles your headers with the library inside Gradle's native build, so
npm run androidbuilds everything. - Named imports from
./native/<header>.hwork as on the web:await initNative()once, then call the classes. - There is no
m.FSand no/memfs: files live in the app's own storage, and your C++ takes their paths. - No Worker and no COOP or COEP:
runtime: 'mt'uses pthreads directly.
Other platforms
- SQLite overview: the apps, every platform's setup and the packages.
- SQLite for WebAssembly: browsers, Node.js and edge runtimes.
- SQLite for iOS: React Native apps on iOS.
- SQLite for WASI: command-line programs under wasmtime.
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