# Atscript DB > Use when working with @atscript/db, @atscript/db-{sqlite,postgres,mysql,mongo,memory}, @atscript/db-sql-tools, @atscript/moost-db, @atscript/db-client, or .as models with @db.* annotations. Covers DbSpace, adapter wiring, table/view CRUD, query filters, patch/field/array ops, relations, views, schema sync, engine-specific capabilities, BaseDbAdapter subclassing, moost-db REST routes, declarative actions, @InputForm structured input, URL query syntax, browser Client, client.action(), client.getActionForm(), DB validation, optimistic concurrency control via @db.column.version + $cas + withOptimisticRetry, field-level encryption at rest (@db.encrypted, key rotation, ENC_* errors), geo search (db.geoPoint, @db.index.geo, geoSearch, $geoWithin, GET /geo), token controller binding (provideDbSpace, @db.space), model manifest (atscriptModels), planSchema, assertExposed, provideTestDbSpace. Scope is DB only. Out of scope (use the moostjs/atscript skill): .as syntax, @meta.*, @expect.*, asc, unplugin, VSCode. - Skill: `moostjs/atscript-db` (Agent Skill, multi-file: 25 files) - Install (CLI): `npx skillmds@latest add moostjs/atscript-db` - Raw SKILL.md: https://api.skillmd.com/api/skills/moostjs/atscript-db/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - Author: moostjs (https://skillmd.com/u/moostjs) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/moostjs/atscript-db --- # atscript-db ## Install ```bash 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 ``` ```bash 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 ```atscript // 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 } ``` ```ts 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](references/annotations.md#dbcolumn--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, 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[*].filterable` / `.sortable` are adapter-gated.** `BaseDbAdapter.canFilterField(fd)` / `canSortField(fd)` defaults to `fd.storage !== 'json'`, so `@db.json` fields and array fields (both `storage: 'json'`) report `false`/`false` on SQL adapters even when annotated `@db.column.filterable`/`@db.column.sortable`. MongoAdapter overrides `canFilterField` to `true` (native dot-paths and array filters) but inherits the conservative `canSortField` default. Adapter veto wins over annotations — annotating a JSON column doesn't make the engine able to filter/sort it. In auto mode (no `@db.table.sortable 'manual'`) `sortable` additionally requires the field be **index-backed** (`TDbFieldMeta.isIndexed` = explicit `@db.index*` **OR** primary key **OR** unique — so Mongo `_id` and SQL PK/unique advertise `sortable: true` without an explicit index), while `filterable` defaults `true` for any adapter-capable field. Auto mode enforces no sort gate, so a `$sort` on a non-advertised field still succeeds — `sortable: false` is advisory, not enforced. Detail → [moost-db.md § Gate mode](references/moost-db.md). | | 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](references/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](references/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). | ## Key imports ```ts // 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](references/getting-started.md) | Install, `atscript.config`, first `.as` model, `DbSpace` wiring, `syncSchema`, first CRUD call | | `@db.*` annotations | [annotations.md](references/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](references/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](references/tables-and-views.md) | `DbSpace.getTable/getView/get`, lifecycle, `ensureTable`, `syncIndexes`, view kinds (managed/materialized/external) | | CRUD | [crud.md](references/crud.md) | `insertOne/Many`, `replaceOne/Many`, `updateOne/Many`, `deleteOne/Many`, `findOne/Many`, `count`, `bulkUpdate/Replace`, `DbError` | | Queries | [queries.md](references/queries.md) | Filter operators, `$and` / `$or` / `$not`, projection (`$select`), `$sort`, `$skip` / `$limit` / `$page` / `$size`, `$count`, `$with`, `$groupBy` aggregation | | Patch semantics | [patch.md](references/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](references/relations.md) | `@db.rel.FK/.to/.from/.via`, optional FKs, referential actions, `controls.$with`, fractional ref depth on `/meta`, nested writes | | Schema sync | [schema-sync.md](references/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 | | SQLite specifics | [adapters-sqlite.md](references/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) | | PostgreSQL specifics | [adapters-postgres.md](references/adapters-postgres.md) | `PgDriver`, pgvector + HNSW, CITEXT, `@db.pg.type`, `@db.pg.schema`, `@db.pg.collate`, tsvector FTS | | MySQL specifics | [adapters-mysql.md](references/adapters-mysql.md) | `Mysql2Driver`, `@db.mysql.engine/.charset/.collate/.type/.unsigned/.onUpdate`, VECTOR, FULLTEXT, utf8mb4 default | | MongoDB specifics | [adapters-mongo.md](references/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](references/adapters-memory.md) | in-memory adapter; provider-backed runtime surfaces, in-memory tests, JS-native filter semantics | | Custom adapters | [creating-adapters.md](references/creating-adapters.md) | `BaseDbAdapter` contract, abstract methods, overridable hooks, `supports*` flags, `@atscript/db-sql-tools` reuse | | `moost-db` HTTP | [moost-db.md](references/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 | | Actions | [actions.md](references/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](references/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` …(truncated)