ACTIAS

database

SQL databases with migrations.

Declare

local db = database "catalog" { migrations = "migrations/catalog" }

Top level only. Scoped to the project, so two scripts in one project declaring database "catalog" share it.

The body is optional:

FormSchema comes from
database "catalog" { migrations = "dir" }the .sql files in dir
database "notes"nothing. Manual mode: the platform never touches the schema

In manual mode the file starts empty and stays yours: nothing is created, migrated or checked. Add the key later and the files apply at the next touch.

Query

local id = uuid.v4()
db:exec("INSERT INTO listings (id, title) VALUES (?, ?)", { id, "vase" })
local row = db:query_one("SELECT * FROM listings WHERE id = ?", { id })
local rows = db:read("SELECT id, title FROM listings LIMIT 50")
MethodReturns
exec(sql, params?)rows affected
query(sql, params?)rows
query_one(sql, params?)first row, or nil
read(sql, params?)rows, from the nearest copy, up to 30 seconds behind the last write
read_one(sql, params?)first row, or nil, from the nearest copy
batch(statements)several statements in one transaction; each entry is { sql, params }; returns the rows affected per statement

Parameters are bound, never interpolated.

read and read_one see every write any caller was told succeeded before the read began, from any node. A node that replicates the database answers from its own copy once the owner has confirmed the copy is current, which costs one small round trip and no data; any other node asks the owner for the read itself; a request that already wrote to the database reads it at the owner. query and query_one always run at the owner and see writes not yet answered too.

Migrations

Create the files with the CLI:

actias sql catalog create listings

That writes migrations/catalog/0001_listings.sql. Fill in the SQL:

CREATE TABLE listings (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    created_at INTEGER NOT NULL
);

Declare the directory on the database, and the platform applies the files in filename order at the first call that touches it, inside that call's transaction.

local db = database "catalog" { migrations = "migrations/catalog" }

The declaration is what makes files apply. A directory nothing declares is never read, whatever it is named.

Adding 0003_*.sql and publishing upgrades the schema on the next touch. Nothing runs at publish time.

Gotchas

  • A failed migration applies nothing and surfaces as that call's error. Fix the file and publish again; the next touch retries.
  • The first caller after a deploy pays for the migration. Keep migrations cheap, or touch the database yourself after publishing.
  • actias check fails if the declared directory holds no .sql files, or if a directory of .sql files is declared by nothing.

Related

  • Where data goes compares databases with kv and objects.
  • object takes the same migrations key for a class's private storage.