atscript-db

atscript-db

Install

npx skills add moostjs/atscript-db     # this skill (DB layer)
npx skills add moostjs/atscript        # sibling — .as syntax, @meta.*, @expect.*, asc, unplugin

Packages

@atscript/db                     core: DbSpace, AtscriptDbTable, AtscriptDbView, schema sync, relations
    ├── @atscript/db-sql-tools   shared SQL builders (WHERE, SELECT, INSERT, aggregation, filter visitor)
    │       ├── @atscript/db-sqlite     better-sqlite3 + FTS5 + sqlite-vec (vec0) + collation
    │       ├── @atscript/db-postgres   pg + pgvector + HNSW + CITEXT + FTS
    │       └── @atscript/db-mysql      mysql2 + VECTOR + FULLTEXT + utf8mb4
    ├── @atscript/db-mongo       mongodb (aggregation pipelines, Atlas Search, no SQL layer)
    ├── @atscript/db-memory      in-memory adapter (no engine); provider-backed read-only + stored read-write
    ├── @atscript/moost-db       Moost HTTP controllers: AsDbController / AsDbReadableController
    └── @atscript/db-client      browser/SSR fetch client over moost-db REST

pnpm add @atscript/core @atscript/typescript @atscript/db
pnpm add @atscript/db-sqlite better-sqlite3                 # pick one adapter
pnpm add @atscript/db-postgres pg
pnpm add @atscript/db-mysql mysql2
pnpm add @atscript/db-mongo mongodb
pnpm add @atscript/db-memory                                 # in-memory (tests / runtime surfaces)
pnpm add @atscript/moost-db @moostjs/event-http moost       # REST
pnpm add @atscript/db-client                                 # browser/SSR client

Quick start

// src/todo.as
@db.table 'todos'
@db.depth.limit 0
export interface Todo {
    @meta.id @db.default.increment
    id: number
    title: string
    @db.default 'false'
    completed?: boolean
    @db.default.now
    createdAt?: number.timestamp
}

import { DbSpace } from "@atscript/db";
import { syncSchema } from "@atscript/db/sync";
import { SqliteAdapter, BetterSqlite3Driver } from "@atscript/db-sqlite";
import { Todo } from "./todo.as";

const db = new DbSpace(() => new SqliteAdapter(new BetterSqlite3Driver("./app.db")));
await syncSchema(db, [Todo]); // idempotent, lock-coordinated
const todos = db.getTable(Todo);

await todos.insertOne({ title: "ship it" }); // { insertedId: 1 }
const open = await todos.findMany({ filter: { completed: false } });
await todos.updateOne({ id: 1, completed: true }); // PK required in payload
await todos.deleteOne(1);

Invariants

#	Rule
1	adapter parity — application code must not branch on adapter type. Every adapter accepts the same filter shape, patch shape, and controls; per-engine features are surfaced through annotations, not API forks.
2	@meta.id is the composite-key marker. No @meta.isKey. Multiple @meta.id on different props form a composite PK. Takes no arguments.
3	@db.depth.limit N gates nested writes (insert / replace / patch) along @db.rel.from / @db.rel.via chains. Absent or 0 → server rejects nested FROM/VIA payloads with HTTP 400 and /meta ships shallow FK refs. Set N ≥ 1 to opt in. @db.rel.to (single parent ref) is not subject to this gate.
4	MongoDB indexes use the atscript__ prefix. syncIndexes() only manages indexes with this prefix; consumer-created indexes that start with atscript__ are treated as managed and may be dropped on drift.
5	Generated *.as.d.ts / atscript.d.ts files in a consuming project are produced by asc. Never hand-edit. Regenerate via npx asc (or let unplugin-atscript do it at bundle time).
6	Schema sync takes a distributed lock. Multi-pod deployments must configure podId, lockTtlMs, waitTimeoutMs on the syncSchema() options; the control table is __atscript_control.
7	Third-party BaseDbAdapter implementations MUST NOT import any other in-tree adapter. Shared SQL helpers live in @atscript/db-sql-tools. Each adapter is independent.
8	Navigation relations are lazy. @db.rel.to / .from / .via fields are undefined on read unless requested via controls.$with. No N+1 lazy loading.
9	@db.column has a per-row cost — don't use it without a hard reason. A single use flips the whole table onto the key-translation path on every read/write/filter. Legit reasons only: (a) target name is a SQL reserved word; (b) integrating with a schema you don't own; (c) repo-wide convention forces snake_case at DB + camelCase in TS. Same spirit for @db.table 'physical' (no per-row cost, same intent). Details + non-reasons → annotations.md § @db.column — when to use it.
10	Read responses always carry preferredId fields. The moost-db controller silently widens $select to include meta.preferredId on every row-returning read endpoint (/query, /pages, /one, /one/:id, search/vector). Aggregate ($groupBy) and count ($count) responses are NOT widened. transformProjection() overrides cannot suppress preferred-id fields — hide identifiers at the network/authz layer instead.
11	Action body is the envelope { ids?, input? }. ids carries the identifier(s) — JSON object (single) or array of objects (multi) for @DbActionID* / @DbActionRow*, never scalars; field set must EXACTLY match one legitimate identification (PK or any @db.index.unique group), strict — unknown fields rejected. input carries the optional @InputForm payload, validated server-side against the declared/inferred form before the handler fires (ValidatorError → structured 400; absent input validates as {}, so required fields 400 and the handler always receives an object). Single-field PKs send { "ids": { "id": "abc" } }, never bare "abc". Array or scalar root → 400. Empty body / {} is valid for table-level actions with no form.
12	disabled requires requiredFields. @DbAction / @DbActions* predicates must declare every field they read as a literal requiredFields tuple. The tuple type-narrows the predicate's row arg (Pick<FlatOf<TRow>, R[number]>[]) AND drives server-side projection widening for @DbActionRow* injection and the $actions=true augmentation. requiredFields is server-internal — never on the /meta wire. disabled without requiredFields → action dropped at discovery.
13	Per-row action availability is server-evaluated via ?$actions=true. Opt-in URL control on /query, /pages, /one. Each returned row gets $actions: string[] (row/rows-level action names NOT disabled). Stripped on $count/$groupBy. 'table'-level actions never appear. Filters through per-request applyMetaOverlay().
14	/meta.fields is exact — it IS the gate (since 0.1.128). fields[P].sortable === ($sort=P accepted) and fields[P].filterable === (filter on P accepted) on every adapter, mode and field kind; both come from one FieldCapabilityIndex. indexed: true is a separate advisory hint (explicit @db.index*, PK, unique) — auto mode advertises EVERY adapter-sortable column. Adapter capability vetoes annotations: @db.json / array columns are never sortable on ANY adapter (canSortField vetoes designType 'json' / 'array', so Mongo/memory 400 $sort=tags too) and not filterable on SQL. Never listed → always 400: nested-object parents (contact — use a leaf), navigation paths (assignee.name — use $with=assignee(...)), JSON descendants on SQL (prefs.theme— select the parent; Mongo/memory list + accept them), unknown fields. Manual-mode policy covers filters and$sort only; $groupBy/$having/aggregate fields use physical capability. Rejections: { statusCode: 400, message, errors: [{ path, message }] }. Physical column names (contact\_\_email) are rejected everywhere, incl. transformFilteroverlays andupdateMany/deleteMany. Detail → moost-db.md § Gate mode.
15	Bind controllers by model token; register spaces before init(). @TableController(Model) resolves lazily at app.init() against the ambient registry — provideDbSpace(db) (+ named spaces via provideDbSpace(x, "name") matching @db.space) must run first. Lazy-factory form requires an explicit prefix. A subclass with its own constructor calls super(moost) and the base resolves from class metadata (ctor is (app, readable?) — readable last and optional). Instance form (@TableController(table)) still works but couples binding to module evaluation order. → moost-db.md § Binding forms
16	Sync failures are loud by default. syncSchema runs with errored entries report a summary + per-entry errors even under the NoopLogger default (console fallback); onError: "throw" | "warn" | "silent" tunes it. Feed sync from the generated manifest (dbPlugin({ manifest }) → atscriptModels) instead of a hand-maintained import array. → schema-sync.md
17	Value-import .as artifacts — never import type. Compiled .as models/forms are classes consumed at runtime (decorator args, DI by param type, .validator(), manifest arrays). A type-only import elides the value and design:paramtypes emits Object — form binding and DI-by-type break silently. Keep typescript/consistent-type-imports-style lint rules off in apps importing .as files (the create-moost preset disables it).
18	guardWrite / guardRemove are the table's write guards (since 0.1.128). onWrite / onRemove see the untrusted body OUTSIDE any transaction; an overridden guard is passed as the table call's guard option and runs ONCE inside the TABLE's own transaction after defaults + validation (rows re-validated after it); 404/409 disambiguation runs after the table call. No transactionalWrites flag — the override is the switch. SDK callers get the same hook via insertMany(rows, { guard }) / deleteOne(id, { guard }). Reject by THROWING an HttpError; never swallow DbError (PG aborted tx); never await external I/O on SQLite. Built-in write failures are thrown HttpErrors; a non-object body is a 400 shape-gate error before any hook; a non-object onWrite result is a 500. See references/moost-db.md, references/crud.md.
19	A PK-only CAS write is a real write (since 0.1.128). updateOne({ ...pk, $cas }) / PATCH { id, version } execute SET version = version + 1 WHERE pk AND version = ? — hit bumps and reports { 1, 1 }, stale/missing → { 0, 0 } (HTTP 409/404). Without $cas an empty patch writes nothing and reports the honest existence count; withOptimisticRetry mutators returning undefined / {} are an explicit no-write. Never re-submit an unchanged form with its version. See references/versioning.md. Batch fence: touchMany(keys, { require }) (since 0.1.129) — primary key (composite ok, never a unique index) + version per key, no payload; stale/missing → CasMismatchError (DbError code CAS_MISMATCH, 409), nothing bumped; updateMany(orFilter, {}) is NOT a lock since 0.1.128.
20	undefined ≡ absent, null ≡ explicit NULL — on every write path, at every plain-object depth (since 0.1.128). Undefined props are pruned before defaults and validation: a defaulted column gets its DEFAULT, a patch never SETs it; array elements are never dropped; class instances untouched. In a non-merge nested object an undefined optional leaf behaves like omission (null-filled). See references/crud.md, references/patch.md.
21	SQLite transactions are serialised per driver (since 0.1.128). withTransaction = FIFO gate + BEGIN IMMEDIATE; plain statements from other contexts wait for COMMIT. Never await external I/O inside a SQLite transaction or guard (deadlock — unbounded by default; transactionWaitTimeoutMs → DbError("TX_WAIT_TIMEOUT") → 503, transactionWaitWarnMs warns); schema operations take the same connection gate (since 0.1.128) and never land inside a request's transaction; one driver per database. See references/adapters-sqlite.md.
22	Schema sync refuses before it applies (since 0.1.128). run() = discover → pre-flight → execute; a predictable impossibility (primary-key change on a populated table or under a live FK, auto-increment column leaving the PK, dropping a table a model/view still references, FK to a table that exists nowhere, physical table under a managed view's name) returns status: 'refused' with ZERO DDL, nothing persisted, lock released — a boot checking status === 'synced' must treat it as a failed migration. Creates run parents-first, drops children-first (FK cycles as one group), PG drops never CASCADE, safe-mode skips stay tracked. See references/schema-sync.md.

Key imports

// Core
import { DbSpace, AtscriptDbTable, AtscriptDbView, BaseDbAdapter, DbError } from "@atscript/db";
import { syncSchema, planSchema, SchemaSync, readStoredSnapshot } from "@atscript/db/sync";
import { dbPlugin } from "@atscript/db/plugin"; // dbPlugin({ manifest: "atscript.models.ts" }) — path is rootDir-relative
import { $inc, $dec, $mul, $replace, $insert, $upsert, $update, $remove } from "@atscript/db/ops";
// Optimistic concurrency — see references/versioning.md
import { withOptimisticRetry, CasExhaustedError } from "@atscript/db";

// Adapters (pick one)
import {
  SqliteAdapter,
  BetterSqlite3Driver,
  createAdapter as sqliteSpace,
} from "@atscript/db-sqlite";
import { PostgresAdapter, PgDriver, createAdapter as pgSpace } from "@atscript/db-postgres";
import { MysqlAdapter, Mysql2Driver, createAdapter as mysqlSpace } from "@atscript/db-mysql";
import { MongoAdapter } from "@atscript/db-mongo";
import {
  MemoryAdapter,
  createAdapter as memorySpace,
  setMemoryProvider,
  buildMemoryPredicate,
  sortRows,
  projectRow, // shared JS-native engine — also backs moost-db value-help
} from "@atscript/db-memory";

// HTTP
import {
  AsDbController,
  AsDbReadableController,
  TableController,
  ReadableController,
  provideDbSpace, // register the DbSpace for token-bound controllers, BEFORE app.init()
  clearDbSpaces,
  assertExposed, // dev check: @db.http.path models without a bound controller
} from "@atscript/moost-db";
import { provideTestDbSpace, resetTestDbSpaces } from "@atscript/moost-db/testing";

// Browser client
import { Client } from "@atscript/db-client";

References — load only what's needed

Domain	File	When
First contact	getting-started.md	Install, atscript.config, first .as model, DbSpace wiring, syncSchema, first CRUD call
@db.* annotations	annotations.md	@db.table, @db.table.preferredId.uniqueIndex, @db.column, @db.default*, indexes, @db.rel.*, @db.json, @db.ignore, @db.space, @meta.id, gate mode, @db.amount.currency[.ref], @db.unit[.ref], db.currencyCode primitive
Mongo annotations	mongo-annotations.md	@db.mongo.*: collection, capped, search.text/static/dynamic/autocomplete (typeahead, edgeGram/nGram/rightEdgeGram), query-time fuzzy + $fuzzy control, index strategy (compound/autocomplete/text), same-field multi-index variants via $index, patch.strategy, array.uniqueItems, primitives
Tables & views	tables-and-views.md	DbSpace.getTable/getView/get, lifecycle, ensureTable, syncIndexes, view kinds (managed/materialized/external)
CRUD	crud.md	insertOne/Many, replaceOne/Many, updateOne/Many, deleteOne/Many, findOne/Many, count, bulkUpdate/Replace, DbError, undefined props ≡ absent (0.1.128), DbPatch / DbRow payload types, write guards (TWriteOptions.guard / TDeleteOptions.guard), replaceMany = $set merge, empty-patch count path
Queries	queries.md	Filter operators, $and / $or / $not, projection ($select), $sort, $skip / $limit / $page / $size, $count, $with, $groupBy aggregation, null filter typing (NullableOptional, { note: null } / $ne: null), path guard (INVALID_QUERY for JSON descendants on SQL, nav paths, physical names)
Patch semantics	patch.md	Field ops ($inc/$dec/$mul), array ops, @db.json handling, @db.patch.strategy merge vs replace, @db.depth.limit depth gate, Mongo CollectionPatcher
Relations	relations.md	@db.rel.FK/.to/.from/.via, optional FKs, referential actions, controls.$with, fractional ref depth on /meta, nested writes, value help through views / reference chains (terminal ref + inherited db.rel.FK in /meta)
Schema sync	schema-sync.md	FNV-1a hash, __atscript_control store, distributed lock (podId, lockTtlMs, waitTimeoutMs), @db.sync.method, safe mode, onError policy (loud-by-default failures), planSchema dry-run, generated model manifest (dbPlugin({ manifest }), atscriptModels, modelsBySpace), sync hooks, status: 'error' entries + retry semantics (hash not persisted on error), dropping indexed/view-referenced/fulltext columns, composite index definition drift, status: 'refused' / pre-flight refusals (0.1.128): "refused", "primary key change" / PK moved / @meta.id moved / composite key, "drop order" / "foreign key order" / parents first / children first / "dropped right before" / removed child of a 'drop'-recreated or key-rebuilt table / "other objects depend on it" / FK cycle / deferForeignKeysTo, "cannot drop … still referenced", unknown FK target, table-vs-view name collision, dependsOn / dropGroup / pkChange / skipped / pending, safe-mode tracking retention + pending work ("skipped (safe mode)", snapshot + hash withheld), view join-condition / having hashing + one-time recreate, isAtscriptDbView
SQLite specifics	adapters-sqlite.md	BetterSqlite3Driver (with vector: true opt-in), FTS5, sqlite-vec / vec0 shadow tables for @db.search.vector, @db.column.collate, native FKs, in-memory :memory:, $regex → LIKE … ESCAPE '\' (restricted subset, throws on \d/[…]/(a|b)/quantifiers), per-driver transaction gate / BEGIN IMMEDIATE / transactionWaitTimeoutMs + transactionWaitWarnMs / TX_WAIT_TIMEOUT 503 (0.1.128)
PostgreSQL specifics	adapters-postgres.md	PgDriver, pgvector + HNSW, CITEXT, @db.pg.type, @db.pg.schema, @db.pg.collate, tsvector FTS
MySQL specifics	adapters-mysql.md	Mysql2Driver, @db.mysql.engine/.charset/.collate/.type/.unsigned/.onUpdate, VECTOR, FULLTEXT, utf8mb4 default, column-definition rules (0.1.128): never DEFAULT NULL / NOT NULL DEFAULT NULL (ER 1067), invented type default + DROP DEFAULT, NULL backfill before NOT NULL, one MODIFY per column, expression defaults ≥ 8.0.13, "index prefix" / key length 768 / (255) / SUB_PART drift, KEY_COLUMN_USAGE PK source, one-statement PK swap, FOREIGN_KEY_CHECKS on a dedicated connection
MongoDB specifics	adapters-mongo.md	MongoAdapter(db, client?), aggregation-pipeline patches, Atlas Search text + vector, atscript__ index prefix + physical index-name helper (mongoIndexKey/INDEX_PREFIX for raw-driver $search interop), ObjectId primitive
Memory specifics	adapters-memory.md	in-memory adapter; provider-backed runtime surfaces, in-memory tests, JS-native filter semantics
Custom adapters	creating-adapters.md	BaseDbAdapter contract, abstract methods, overridable hooks, supports* flags, @atscript/db-sql-tools reuse
moost-db HTTP	moost-db.md	AsDbController / AsDbReadableController routes, TableController / ReadableController binding forms (model token / lazy factory / instance), provideDbSpace + @db.space multi-space resolution, super(moost) subclass fallback, assertExposed, @db.http.path resolution, value-help endpoints, guardWrite / guardRemove (the table's write guards) + withTransaction, shape gate 400 (also on onWrite output → 500), thrown write errors, reconcileCas 400s (0.1.128), sortable metadata / /meta.fields ⇔ gate parity (FieldCapabilityIndex, indexed hint, 400 envelope)
Actions	actions.md	@DbAction / @DbActionID* / @DbActionRow* / @InputForm / @DbActions* decorators, { ids?, input? } body envelope, row/rows/table actions, processor: 'backend' | 'navigate' | 'custom', sync batch disabled gate (typed via requiredFields tuple, mandatory), perRow() helper, onDisabledRows, @DbActionRow* projection widening, $actions=true server-evaluated row availability augmentation, inputForm on /meta + GET /meta/form/:name form-schema endpoint (built-in server-side input validation; form inferable from the param type), preferred-id navigate substitution, ActionDisabledError, /meta actions[] shape
URL query syntax	http-query-syntax.md	URL filter encoding (field=v, !=, >, <, {v1,v2}, ~=/re/i, ranges), $sort, $select, $with, $page, $size, $search/$index/$fuzzy (Mongo Atlas), $vector/$threshold, JSON subfield / nested path rules per adapter, $having aliases / $groupBy-only keys ($having key "x" must be an aggregate alias or a $groupBy field), nested grouped keys, gate rejection table
Browser client	db-client.md	Client<T extends AtscriptClientShape>, typed CRUD methods with $with response narrowing (ClientResponse<T, Q>), client.action<R>(name, id?, input?) envelope-shaped contract, client.getActionForm(name) lazy form-schema fetch, filter construction, auth headers, ClientError / ActionDisabledError, meta + validator caching, preferred-id-driven navigate URLs, $cas lift to version + PatchOf / RowOf write types (0.1.128) TransportError (0.1.129: fetch rejected or 2xx body not JSON — no server verdict, method/url/cause, does not extend ClientError); non-JSON error body → body.message never empty (HTTP <status> fallback)
Validation	validation.md	createDbValidatorPlugin, buildDbValidator, server+client validator, error shape over HTTP, ValidatorMode
Versioning / OCC	versioning.md	@db.column.version annotation, $cas operator, mandatory auto-bump, withOptimisticRetry helper, CasExhaustedError, updateMany exclusion, direct-write rejection (DbError("VERSION_COLUMN_WRITE")), HTTP auto-lift + 409 kind: "version_mismatch" body (typed VersionMismatchError in @atscript/db-client) + 404 disambiguation, versionColumn in /meta, bulk aggregate response, limitations (no per-item conflict status, no renamed version columns), PK-only $cas versioned touch / empty-patch semantics, version+$cas conflict 400, db-client $cas lift (0.1.128) touchMany(keys, { require: "all" | "any" }) batch versioned touch + CasMismatchError (CAS_MISMATCH) → 409 (0.1.129)
Field encryption	encryption.md	@db.encrypted, DbSpace encryption options (defaultKeyId, keys, resolveKey for KMS/Vault, onUnencrypted), envelope format, key rotation, plaintext-column migration, ENC_CONFIG_MISSING / ENC_KEY_INVALID / ENC_FIELD_FILTER / ENC_FIELD_SORT / ENC_FIELD_AGG / ENC_FIELD_PATCH_OP / ENC_NOT_ENCRYPTED / ENC_DECRYPT_FAILED, CSFLE boundary
Geo search	geo-search.md	db.geoPoint ([lng, lat] — longitude FIRST), @db.index.geo, table.geoSearch() / geoSearchWithCount() / isGeoSearchable(), $maxDistance / $minDistance (meters), $distance row field, $geoWithin circle filter, GET /geo + $center, client.geoSearch/geoPages, native on all adapters (Mongo 2dsphere / PG PostGIS GiST / MySQL POINT SRID 4326 / SQLite haversine), GEO_NOT_SUPPORTED (PG without PostGIS) / GEO_INDEX_MISSING / FILTER_TYPE_MISMATCH, atscript__geo__* managed index
App-side testing	testing.md	provideTestDbSpace / resetTestDbSpaces (@atscript/moost-db/testing), in-memory SQLite (:memory:), MongoDB in-memory server, fixture seeding, resetting schema, driving controllers from a test harness, testing CAS hit/miss

Reference docs: https://db.atscript.dev.