SQLite

SQLite is “a small, fast, self-contained, high-reliability, full-featured, SQL database engine” and “the most used database engine in the world.” Observable provides a ESM-compatible distribution of sql.js, a WASM-based distribution of SQLite. It is available by default as SQLite in Markdown, but you can import it like so:

import SQLite from "npm:@observablehq/sqlite";

If you prefer to use sql.js directly, you can import and initialize it like so:

import initSqlJs from "npm:sql.js";

const SQLite = await initSqlJs({locateFile: (name) => import.meta.resolve("npm:sql.js/dist/") + name});

We also provide SQLiteDatabaseClient, a DatabaseClient implementation.

import {SQLiteDatabaseClient} from "npm:@observablehq/sqlite";

The easiest way to construct a SQLite database client is to declare a FileAttachment and then call file.sqlite to load a SQLite file. This returns a promise. (Here we rely on implicit await.)

const db = FileAttachment("chinook.db").sqlite();

Alternatively you can use SQLiteDatabaseClient and pass in a string (URL), Blob, ArrayBuffer, Uint8Array, FileAttachment, or promise to the same:

const db = SQLiteDatabaseClient.open(FileAttachment("chinook.db"));

(Note that unlike DuckDBClient, a SQLiteDatabaseClient takes a single argument representing all of the tables in the database; that’s because a SQLite file stores multiple tables, whereas DuckDB typically uses separate Apache Parquet, CSV, or JSON files for each table.)

Using FileAttachment means that referenced files are automatically copied to dist during build, and you can even generate SQLite files using data loaders. But if you want to “hot” load a live file from an external server, pass a string to SQLiteDatabaseClient.open:

const db = SQLiteDatabaseClient.open("https://static.observableusercontent.com/files/b3711cfd9bdf50cbe4e74751164d28e907ce366cd4bf56a39a980a48fdc5f998c42a019716a8033e2b54defdd97e4a55ebe4f6464b4f0678ea0311532605a115");

Once you’ve loaded your db you can write SQL queries.

const customers = db.sql`SELECT * FROM customers`;

display(await customers);

A call to db.sql returns a promise to an array of objects; each object represents a row returned from the query. For better readability, you can display query results using Inputs.table.

Inputs.table(customers)

For interactive or dynamic queries, you can interpolate reactive variables into SQL queries. For example, you can declare a text input to prompt the query to enter a search term, and then interpolate the input into the query parameter.

const name = view(Inputs.text({label: "Name", placeholder: "Search track names"}));

const tracks = db.sql`SELECT * FROM tracks WHERE Name LIKE ${`%${name}%`}`;

As an alternative to db.sql, you can call db.query.

db.query(`SELECT * FROM tracks WHERE Name LIKE $1`, [`%${name}%`])

There’s also db.queryRow for just getting a single row.

db.queryRow(`SELECT sqlite_version()`)