#!/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 a slightly-modified version of 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, which JavaScript may convert back to 0 if used unchecked in a numeric operation later.
• 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 about x/sqlite:

• 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.

Worth remembering about SQLite:

• Scalar values are dynamically typed, using the following types:

  • INTEGER/int64, a 64-bit signed integer.
  • REAL/float64, a 64-bit floating-point number that isn’t NaN.
  • TEXT, a UTF-8 text string that doesn’t contain ␀ characters.
  • BLOB, a binary byte array.
  • NULL, the null value.

  There is no boolean type; as in C true and false are just constants equal to 1 and 0.