SQLite for WASI

v3.53.4WASI

SQLite 3.53.4 for command-line programs under wasmtime, precompiled for wasm32-wasip3, single-threaded as @crossbind/port-sqlite3-wasi.

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

npm ↗Port recipe and licence files ↗

Install

shell

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

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 -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.53.4, shop.db: 3 statements, 4 rows changed
linus|15.0
ada|13.75
sqlite 3.53.4, shop.db: 1 statement, 0 rows changed

Command-line tools

One upstream command ships as npm executables built for wasm32-wasip3. They need wasmtime on PATH and no compiler - see WASI commands.

shell

npm install --global @crossbind/port-sqlite3-bin-wasi@beta
sqlite3-wasi --help

sqlite3-wasi

What is different on WASI

There are no JavaScript bindings: src/native provides main(int, char**), and the build is a single .wasm.
Files come from the host through --dir preopens; --dir=. gives the program the current directory.
crossbind build -p wasi -b release writes the program to .crossbind/build/<name>-wasi-wasm32-st-release.wasm.
WASI 0.3's wasi:cli/exit carries success or failure only, so any non-zero return from main reaches the shell as exit code 1.
The build needs wasi-sdk 34 or newer (WASI_SDK_PATH) or the crossbind Docker image; running needs wasmtime 47 or newer. See WASI.

Other platforms

SQLite overview: the apps, every platform's setup and the packages.
SQLite for WebAssembly: browsers, Node.js and edge runtimes.
SQLite for Android: React Native apps on Android.
SQLite for iOS: React Native apps on iOS.

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

cURL Expat GDAL GEOS GeoTIFF iconv LERC libjpeg-turbo libTIFF OpenSSL PROJ SpatiaLite WebP zlib Zstandard