Back to Details

sqlite-storage

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

SQLite Storage

SQLite 存储

Val Town provides built-in SQLite via the 
std/sqlite
module. Reach for it whenever a val needs relational or structured persistent data. For simple key/value data, prefer 
std/blob
instead.
Val Town 通过 
std/sqlite
模块提供内置 SQLite。当 val 需要关系型或结构化的持久化数据时，应使用它。对于简单的键值数据，则应优先使用 
std/blob
。

Basic usage

基本用法

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

await sqlite.execute(`CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
)`);

await sqlite.execute({
  sql: "INSERT INTO users (name, email) VALUES (?, ?)",
  args: ["Alice", "alice@example.com"],
});

const result = await sqlite.execute("SELECT * FROM users");
// result.rows = [{ id: 1, name: "Alice", email: "alice@example.com" }]
ts
import { sqlite } from "https://esm.town/v/std/sqlite/main.ts";

await sqlite.execute(`CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
)`);

await sqlite.execute({
  sql: "INSERT INTO users (name, email) VALUES (?, ?)",
  args: ["Alice", "alice@example.com"],
});

const result = await sqlite.execute("SELECT * FROM users");
// result.rows = [{ id: 1, name: "Alice", email: "alice@example.com" }]

Transactions

事务

Use 
sqlite.batch
for atomic multi-statement transactions — all succeed or all roll back:
ts
await sqlite.batch([
  { sql: "INSERT INTO users (name, email) VALUES (?, ?)", args: ["Bob", "bob@example.com"] },
  { sql: "UPDATE users SET name = ? WHERE id = ?", args: ["Robert", 2] },
]);
使用 
sqlite.batch
执行原子的多语句事务——所有语句要么全部成功，要么全部回滚：
ts
await sqlite.batch([
  { sql: "INSERT INTO users (name, email) VALUES (?, ?)", args: ["Bob", "bob@example.com"] },
  { sql: "UPDATE users SET name = ? WHERE id = ?", args: ["Robert", 2] },
]);

Per-val vs organization databases

Val-scoped 与 organization-scoped 数据库

The import path determines which database you get. Both expose the same 
@libsql/client
API (
execute
, 
batch
) and return rows as keyed objects (
Record<string, unknown>[]
):
  • std/sqlite/main.ts
    — val-scoped database, isolated to this val. The default for new vals, and what you almost always want.
  • std/sqlite/global.ts
    — organization-scoped database, shared across every val owned by the same account. (Your personal account counts as its own organization here, so this database is shared across all of your vals.)
Do not switch an existing val between these import paths — it changes which database the val reads and writes.
导入路径决定使用哪个数据库。两者都提供相同的 
@libsrc/client
API（
execute
、
batch
），并以带键对象的形式返回各行（
Record<string, unknown>[]
）：
  • std/sqlite/main.ts
    ——val-scoped 数据库，仅限于该 val 使用。这是新建 val 的默认选项，也是几乎所有情况下都应选择的方案。
  • std/sqlite/global.ts
    ——organization-scoped 数据库，由同一账户拥有的每个 val 共享。（在此处，你的个人账户也被视为一个独立组织，因此该数据库会在你的所有 val 之间共享。）
不要在现有 val 的这两个导入路径之间切换——这会改变该 val 读写的是哪个数据库。

Querying org-owned vals via tools

通过工具查询组织拥有的 val

When using the 
sqlite_execute
or 
sqlite_batch
tools to query a val owned by an organization (not your personal account), pass the org handle as the 
org
parameter so the call hits the right database. Example: 
{ sql: "SELECT * FROM users", org: "some-org" }
. This only matters for the tool calls — code inside the val itself reads from its own database automatically.
使用 
sqlite_execute
或 
sqlite_batch
工具查询组织（而非你的个人账户）拥有的 val 时，请将组织 handle 作为 
org
参数传入，使调用访问正确的数据库。例如：
{ sql: "SELECT * FROM users", org: "some-org" }
。这只影响工具调用——val 内部的代码会自动读取其自己的数据库。

Rules

规则

  • Always use parameterized queries (the 
    args
    field) for any value derived from user input. Never interpolate strings into SQL.
  • Use 
    CREATE TABLE IF NOT EXISTS
    so schema setup is idempotent across val restarts.
  • Schema migrations: add new columns with 
    ALTER TABLE ... ADD COLUMN
    . Wrap in 
    try/catch
    if the migration may run against an already-updated table.
  • 对于任何派生自用户输入的值，始终使用参数化查询（
    args
    字段）。绝不将字符串插值到 SQL 中。
  • 使用 
    CREATE TABLE IF NOT EXISTS
    ，使 schema 设置在 val 重启后保持幂等性。
  • Schema 迁移：使用 
    ALTER TABLE ... ADD COLUMN
    添加新列。如果迁移可能会对已经更新过的表执行，请将其包裹在 
    try/catch
    中。

Reference

参考文档

Full API docs: https://docs.val.town/reference/std/sqlite/usage/
完整 API 文档：https://docs.val.town/reference/std/sqlite/usage/