Usage

The Val Town SQLite val has two methods: execute ↗ and batch ↗. Below are examples of how to use them in Val Town.

Simple query

Simple queryRun in Val Town ↗

import { sqlite } from "https://esm.town/v/std/sqlite";

const data = await sqlite.execute("SELECT datetime();");
console.log(data.rows[0]);

Basic usage

Basic usageRun in Val Town ↗

import { sqlite } from "https://esm.town/v/std/sqlite";

await sqlite.execute(`create table if not exists kv(
  key text unique,
  value text
)`);

const key = crypto.randomUUID();
await sqlite.execute({ sql: `insert into kv(key, value) values(?, ?)`, args: [key, "value1"] });
const result = await sqlite.execute({ sql: `select * from kv where key = ?`, args: [key] });

console.log(result);
// {
//   columns: [ "key", "value" ],
//   columnTypes: [ "TEXT", "TEXT" ],
//   rows: [ [ "d65991f8-6f03-4275-bcf1-1fdb1164e153", "value1" ] ],
//   rowsAffected: 0,
//   lastInsertRowid: null
// }

const rows: { key: string; value: string }[] = result.rows.map(row =>
  Object.fromEntries(row.map((value, index) => [result.columns[index], value])) as any
);

console.log(rows); // [ { key: "d65991f8-6f03-4275-bcf1-1fdb1164e153", value: "value1" } ]

Create a table

Table creationRun in Val Town ↗

import { sqlite } from "https://esm.town/v/std/sqlite";

await sqlite.execute(`create table if not exists kv(
  key text unique,
  value text
)`);

Get data

Data queryRun in Val Town ↗

import { sqlite } from "https://esm.town/v/std/sqlite";

console.log(await sqlite.execute(`select key, value from kv`));

Insert data

Data insertionRun in Val Town ↗

import { sqlite } from "https://esm.town/v/std/sqlite";

await sqlite.execute({
  sql: `insert into kv(key, value) values (:key, :value)`,
  args: { key: "specialkey", value: "specialvalue" },
});

Delete data

Data deletionRun in Val Town ↗

import { sqlite } from "https://esm.town/v/std/sqlite";

await sqlite.execute({
  sql: `delete from kv where key = :key`,
  args: { key: "specialkey" },
});

Batch queries

Batching queriesRun in Val Town ↗

import { sqlite } from "https://esm.town/v/std/sqlite";

const charge = 10;

export const batchSqlite = await sqlite.batch([
  `create table if not exists accounts(person_id text unique, balance integer)`,
  {
    sql: `update accounts set balance = balance - :charge where person_id = 'Bob'`,
    args: { charge },
  },
  {
    sql: `update accounts set balance = balance + :charge where person_id = 'Alice'`,
    args: { charge },
  },
]);