Mytharyn Scripting

Appearance

SQLite databases ​

serverside

Server scripts can open any number of named SQLite databases and run arbitrary SQL against them. Use this when your data is naturally relational or you need ad-hoc queries: shop tables, leaderboards, audit logs, loot tables, anything you want to JOIN, ORDER BY or aggregate.

If what you actually want is "a keyed set of JSON records that clients see a live, read-only copy of" (inventories, quest logs, item catalogs), reach for replicated collections instead — they are built on the same SQLite engine but handle replication, caching and persistence for you.

You need…Use
Relational tables, joins, aggregates, server-only dataopendatabase (this page)
Per-player or global records that clients mirror livecollection()
A handful of small key/value settingsflags

Opening a database ​

opendatabase(name) returns a Database handle. The file lives at data/databases/<name>.db and is created lazily on the first query/exec — opening a handle does no I/O at all, so it is fine to do at module top level:

ts
// weapons/leaderboard.ts (server)
const db = opendatabase('leaderboard')

Names are 1–64 characters of letters, digits, _ and -. An invalid name throws synchronously from opendatabase, not from a later query.

The handle is just a name plus two methods; opening the same database from several scripts gives handles onto the same file, and db.name tells you which one you have.

query and exec ​

Both methods are async and take the SQL text plus an optional parameter array:

ts
const db = opendatabase('leaderboard')

export async function onInitialized() {
    await db.exec(`CREATE TABLE IF NOT EXISTS scores (
        account TEXT PRIMARY KEY,
        best    INTEGER NOT NULL DEFAULT 0)`)
}

export async function submitScore(pl: Player, score: number) {
    await db.exec(
        `INSERT INTO scores (account, best) VALUES (?, ?)
         ON CONFLICT(account) DO UPDATE SET best = MAX(best, excluded.best)`,
        [pl.account, score])
}

export async function top10() {
    const rows = await db.query('SELECT account, best FROM scores ORDER BY best DESC LIMIT 10')
    return rows.map(r => ({ account: String(r.account), best: Number(r.best) }))
}

A failed statement (syntax error, constraint violation, bad parameter count) rejects the promise with an Error whose message is SQLite's. Wrap awaits in try/catch:

ts
try {
    await db.exec('INSERT INTO scores (account, best) VALUES (?, ?)', [name, 10])
} catch (e) {
    echo('[leaderboard] insert failed: ' + (e instanceof Error ? e.message : String(e)))
}

e is unknown

The script tsconfig enables useUnknownInCatchVariables, so e.message fails the type check — and a type error makes the whole hot reload report compile errors. Use e instanceof Error ? e.message : String(e).

A typical pattern, e.g. for a shop: create the shops / shop_items tables with CREATE TABLE IF NOT EXISTS on load and read them into an in-memory Map.

Parameters ​

Parameters bind to bare ? placeholders in order. Each one is a SqlParam: number, string, boolean or null. Booleans are stored as SQLite's 1 / 0 (and read back as numbers).

ts
await db.query('SELECT * FROM scores WHERE best > ? AND account != ?', [100, 'Astram'])

Always pass user-supplied values (chat text, trigger parameters) as parameters rather than concatenating them into the SQL string.

Under the hood the engine numbers each bare ? as ?1…?N (skipping string literals, quoted identifiers and comments), so a ? inside 'quotes' is text, not a placeholder. An explicit numbered placeholder like ?2 is left as written, which lets you reuse one parameter twice.

Row values ​

SqlRow values map from SQLite's storage classes:

SQLiteJavaScript
INTEGERnumber (values beyond 2^53 lose precision)
REALnumber
TEXTstring
NULLnull
BLOB{ $blob: '<base64>' }

Rows are plain objects keyed by column name, so duplicate column names collide — SELECT a.id, b.id FROM … gives you one id. Alias them: SELECT a.id AS a_id, b.id AS b_id.

Script queries have no row cap, so add a LIMIT to anything that can grow.

Execution model ​

All SQL — every script, every database, plus the GRC SQL Explorer — runs on one serialized worker thread. That has a few consequences worth knowing:

  • Queries never block the game tick; you await them like any other promise, and the continuation resumes on the tick thread in your script's context.
  • Statements run one at a time, in the order they were issued. A write you issued before a read is visible to that read, even across scripts.
  • A slow query delays every other script's queries (but not the game loop). Index what you filter on.
  • Each connection runs with journal_mode=WAL, busy_timeout=5000 and foreign_keys=ON, so REFERENCES constraints are enforced.

Hot reload parks pending queries

If a script is unloaded or hot-reloaded while one of its queries is in flight, that promise is never settled — like a cancelled sleep. The SQL itself still runs on the worker; only the continuation is dropped. Make startup work idempotent (CREATE TABLE IF NOT EXISTS, upserts) so a reload can safely repeat it.

Multi-statement SQL and transactions ​

There is no transaction API on the handle: each query/exec call is atomic on its own, and there is no way to hold a transaction open across awaits. Other scripts' statements can run between two of your calls.

A single call may contain several statements separated by ;. All of them execute, and query returns the last result set that has columns — so INSERT …; SELECT last_insert_rowid() AS id hands you the SELECT.

Parameters only in the first statement

In multi-statement SQL, ? parameters are only reliable in the first statement: SQLite numbers each statement's parameters from 1, but the engine numbers placeholders across the whole string, so a ? in the second statement asks for a parameter the statement doesn't have and the call fails. If that failure happens after a BEGIN, the database's shared connection can be left inside an open transaction. Don't combine BEGIN … COMMIT with parameters — prefer a single statement that does the whole job (INSERT … ON CONFLICT DO UPDATE, UPDATE … WHERE, INSERT … SELECT), or separate awaited calls.

Because every statement is atomic, the usual way to avoid read-modify-write races is to push the arithmetic into SQL:

ts
// Good — one atomic statement
await db.exec('UPDATE wallets SET gold = gold - ? WHERE account = ? AND gold >= ?',
    [price, pl.account, price])
    .then(r => { if (r.rowsAffected === 0) pl.chat = 'Not enough gold' })

// Racy — another call can run between the SELECT and the UPDATE
const [w] = await db.query('SELECT gold FROM wallets WHERE account = ?', [pl.account])
await db.exec('UPDATE wallets SET gold = ? WHERE account = ?', [Number(w.gold) - price, pl.account])

The GRC SQL Explorer ​

Staff can browse and edit databases from GRC's SQL Explorer window: a sidebar tree of databases, tables and columns, a SQL editor with query tabs, and a results grid. Right-click a table for generated queries (select top rows, count, scripted INSERT/UPDATE/DELETE) and wizards (create table, insert row, add/rename/drop columns, empty/drop table).

Access is gated by two staff rights:

RightAllows
sqlList databases, read schemas and run SQL on databases that already exist
sqladminCreate and delete databases

The Explorer never creates a database implicitly — scripts create them on first use, or staff with sqladmin create them explicitly. SQL is not filtered by statement type, so sql effectively means full read/write access to every database. The results grid shows at most 1000 rows per query (script queries are uncapped).

New rights and saved rights records

Staff accounts that have a saved rights record don't automatically gain rights added to the catalog later. If a staff member can't see the SQL Explorer, grant sql explicitly in the rights editor.

The collections database

Collections with sql backing persist in a database named collections. You can inspect it in the Explorer (or even with opendatabase('collections')), but writing to it bypasses replication: clients won't see the change and the server's in-memory copy won't either. Edit collection records through the collection API or RC's Collections tool.

Deleting a database ​

Deleting a database from the Explorer closes its connection and removes the .db file (and its -wal / -shm side files). A script that queries it afterwards simply recreates an empty database, so keep your CREATE TABLE IF NOT EXISTS bootstrap on every load.