#!/usr/bin/env deno run

import SQL from "https://deno.land/x/lite/sql.ts";

const db = SQL(":memory:");
const tableName = SQL`Message`;
await db(SQL`CREATE TABLE ${tableName} (body text)`);

const message = "Hello, world!";
await db(SQL`INSERT INTO ${tableName} VALUES (${message})`);
const query = SQL`SELECT * FROM ${tableName}`;
const rows = await db(query);

console.log(rows);

[ { body: "Hello, world!" } ]

This wraps the excellent WASM SQLite build from https://deno.land/x/sqlite/mod.ts with an interface that is slower and less capable, but simpler. Some key differences:

• The JSON1 extension is included.
• It’s harder to accidentally leak memory.
• All SQL must be expressed as composable SQL`…` tagged template literals, and any bound values must be interpolated with ${…}.
• Query results are eagerly returned as JSON-like Arrays of Objects.
• JavaScript Objects and Arrays are converted to JSON SQLite texts when they’re bound, with object keys sorted and circular references replaced with nulls.
• Most of the interface is async, even though the underlying WASM SQLite interface is synchronous. (This is to allow for certain future enhancements.)

Intended differences, not yet implemented:

• JavaScript numbers are mapped to and from SQLite reals. This is lossless except that SQLite converts NaN to null.
• JavaScript bigints are mapped to and from SQLite integers. Attempting to bind a bigint which is out-of-range of SQLite’s 64-bit signed integers will raise a TypeError (instead of being lossily cast to a real).

Not differences, but worth remembering:

• The database runs single-threaded.
• It’s not possible for SQLite’s filesystem locking to work in Deno’s sandbox, so most filesystem consistency guarantees have gone out the window. This is intended more for use with databases that live in a single process’ memory while in use.
• Several build setting are tweaked, such as foreign keys being enabled by default and some old compatibility options being disabled. You can see the Makefile for details.