title:	Usage
description:	How to use Val Town SQLite, with examples.

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

The sqlite object is an initialized @libsql/client instance, so it supports the same methods and return types. The query language syntax is SQLite's version of SQL.

Simple query

Create val
import { sqlite } from "https://esm.town/v/std/sqlite/main.ts";

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

:::note Omitting main.ts in your import path (i.e. "https://esm.town/v/std/sqlite") will import the account-scoped sqlite object. :::

Account-scoped database

Importing from sqlite/main.ts connects you to a database specific to that val. You can also connect with sqlite/global.ts to connect to your account-scoped database:

Create val
import { sqlite } from "https://esm.town/v/std/sqlite/global.ts";

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

See our global sqlite docs for more information on account-scoped databases.

Basic usage

Create val
import { sqlite } from "https://esm.town/v/std/sqlite/main.ts";

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: [ { key: "d65991f8-6f03-4275-bcf1-1fdb1164e153", value: "value1" } ],
//   rowsAffected: 0,
//   lastInsertRowid: null
// }

const rows: { key: string; value: string }[] = result.rows as any;

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

Create a table

Create val
import { sqlite } from "https://esm.town/v/std/sqlite/main.ts";

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

Get data

Create val
import { sqlite } from "https://esm.town/v/std/sqlite/main.ts";

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

Insert data

Create val
import { sqlite } from "https://esm.town/v/std/sqlite/main.ts";

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

Delete data

Create val
import { sqlite } from "https://esm.town/v/std/sqlite/main.ts";

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

Batch queries

Create val
import { sqlite } from "https://esm.town/v/std/sqlite/main.ts";

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 },
  },
]);

drewmcdonald

promptCompare