CRUD Endpoints

This page documents every endpoint generated by AsDbController and AsDbReadableController. All paths are relative to the controller prefix (e.g., /todos/).

See HTTP Setup for installation and wiring.

Reading Records

Read-response baseline

Every row-returning read endpoint (/query, /pages, /one, /one/:id, including $search and vector-search paths) silently widens the projection so each row carries the table's preferredId field set, regardless of $select.

`$select` shape	Behaviour
absent / `undefined`	full projection — preferred-id fields already present
`string[]` inclusion	dedupe + append missing preferred-id fields
pure inclusion map (`{ name: 1 }`)	add missing preferred-id keys with value `1`
pure exclusion map (`{ id: 0 }`)	rewritten to inclusion (all non-ignored own-table fields minus excluded) + every preferred-id field — exclusion CANNOT remove a preferred-id field
mixed inclusion/exclusion (`{ a: 1, b: 0 }`)	rejected before the readable call (HTTP 400)

Not widened: $groupBy aggregate (group keys are the only fields) and $count (returns a number).

preferredId defaults to primaryKeys. Declare @db.table.preferredId.uniqueIndex(name?) to use a specific @db.index.unique group as the preferred identifier — see Actions — Preferred row identifier. The widening happens AFTER any transformProjection() override resolves; preferred-id fields cannot be suppressed via projection. Hide identifiers at the network/authz layer instead.

GET /query

Returns an array of records. Supports filtering, sorting, pagination, projection, relation loading, and search.

bash

curl "http://localhost:3000/todos/query?completed=false&\$sort=-createdAt&\$limit=10"

Query parameters:

Parameter	Type	Default	Description
`$sort`	`string`	—	Sort expression (e.g., `-createdAt` for descending)
`$limit`	`number`	`1000`	Maximum records to return
`$skip`	`number`	`0`	Number of records to skip
`$select`	`string`	—	Comma-separated field names for projection
`$count`	`boolean`	—	Return count instead of records
`$search`	`string`	—	Fulltext search term
`$index`	`string`	—	Named search index
`$vector`	`string`	—	Vector field name for similarity search
`$threshold`	`string`	—	Similarity threshold for vector search
`$with`	`string`	—	Load relations (e.g., `$with=author,comments`)
`$groupBy`	`string`	—	Group by fields for aggregation
(other)	any	—	Filter fields (e.g., `status=active`)

Response (array):

json

[
  { "id": 1, "title": "Buy milk", "completed": false, "priority": "high" },
  { "id": 2, "title": "Write docs", "completed": false, "priority": "medium" }
]

When $count is set, returns a number instead of an array:

bash

curl "http://localhost:3000/todos/query?completed=true&\$count"

json

See URL Query Syntax for the full filter syntax and Relations & Search for $with, $search, $vector, and $groupBy.

GET /pages

Returns paginated results with metadata. Uses page-based pagination instead of offset-based.

bash

curl "http://localhost:3000/todos/pages?\$page=2&\$size=10&status=active"

Additional parameters:

Parameter	Type	Default	Description
`$page`	`number`	`1`	Page number (1-based)
`$size`	`number`	`10`	Items per page

All other query parameters from GET /query (filters, $sort, $select, $search, $with) are also supported, except $skip, $limit, and $count.

Response:

json

{
  "data": [{ "id": 11, "title": "Task 11", "status": "active" }],
  "page": 2,
  "itemsPerPage": 10,
  "pages": 5,
  "count": 47
}

Field	Description
`data`	Array of records for the current page
`page`	Current page number
`itemsPerPage`	Page size
`pages`	Total number of pages
`count`	Total number of matching records

GET /one/:id

Retrieves a single record by primary key (or any single-field unique index — the path resolver walks every legitimate identification). Returns 404 if not found.

bash

curl http://localhost:3000/todos/one/42
curl http://localhost:3000/users/one/admin   # by username unique index

Response:

json

{ "id": 42, "title": "Buy milk", "completed": false, "priority": "high" }

Supports $select and $with in the query string:

bash

curl "http://localhost:3000/todos/one/42?\$select=id,title&\$with=project"

No filters

Filter parameters (like status=active) are not allowed on this endpoint. They return a 400 error. Use GET /query with filters instead.

When the table declares @db.table.preferredId.uniqueIndex (the canonical addressing identifier), scalar lookups via /one/:id resolve against the preferredId field first; PK and other unique indexes are not retried for the same scalar (the named form below covers them when needed).

Named-form (object) addressing — use query parameters when you want to be explicit about which identification you are addressing, or when the identifier is compound:

bash

curl "http://localhost:3000/users/one?username=admin"          # single-field unique index
curl "http://localhost:3000/task-tags/one?taskId=1&tagId=2"    # composite primary key

The controller walks every registered identification (primary key + every unique index, single-field and compound) in declaration order and picks the first whose fields are all present in the query.

GET /meta

Returns table or view metadata for use by UI tooling or client libraries.

bash

curl http://localhost:3000/todos/meta

Response (abbreviated):

json

{
  "searchable": true,
  "vectorSearchable": false,
  "searchIndexes": [{ "name": "DEFAULT", "description": "dynamic_text index" }],
  "primaryKeys": ["id"],
  "preferredId": ["id"],
  "relations": [{ "name": "comments", "direction": "from", "isArray": true }],
  "fields": {
    "id": { "sortable": true, "filterable": true },
    "title": { "sortable": true, "filterable": true }
  },
  "type": { "...": "serialized Atscript type definition" },
  "actions": [],
  "crud": {
    "query": ["filter", "insights", "...", "actions", "groupBy"],
    "pages": ["filter", "page", "size", "...", "actions"],
    "one": ["select", "with", "actions"],
    "insert": [],
    "update": [],
    "replace": [],
    "remove": []
  }
}

Field	Description
`searchable`	Whether the table has fulltext search indexes
`vectorSearchable`	Whether the table has vector search indexes
`searchIndexes`	Array of available search index definitions
`primaryKeys`	Primary key field names (logical, not column names)
`preferredId`	Logical field names of the preferred identifier (PK or `@db.table.preferredId.uniqueIndex` group). See Preferred row identifier
`versionColumn`	Logical field name of the `@db.column.version` column, if the table declares one. Absent (undefined) otherwise. See OCC over HTTP
`relations`	Available navigation properties
`fields`	Per-field capability flags (`sortable`, `filterable`)
`type`	Full serialized Atscript type (field names, types, annotations, metadata). FK fields ship as shallow refs (`{ id, metadata }`) — enough to resolve the target's URL via `db.http.path`; deeper structure is reachable through the target's own `/meta` (see below)
`actions`	Declared domain actions — see Actions
`crud`	Built-in CRUD permissions / control whitelists — see Permissions

For the full payload shape including actions[] entries and complete crud whitelists, see HTTP Client — Metadata.

The type.metadata["db.http.path"] carried in this payload follows the normalization contract — it is always the final public URL, prefixed with / and inclusive of the Moost globalPrefix, safe to use verbatim with fetch() or new Client(url).

FK ref shape in meta

/meta always serializes FK fields as shallow refs — ref.type is the { id, metadata } shape, independent of @db.depth.limit (which is a security guard on nested writes, not a serialization policy). The target's db.http.path is carried in metadata, so clients can:

resolve the target endpoint for value-help pickers, and
fetch the target's own /meta on demand when they need the target's structural body.

Nav-prop trees (@db.rel.from / @db.rel.to / @db.rel.via) are fully expanded in meta regardless — they are not .ref nodes — so the write-payload shape clients need for nested inserts is always present. Only the FK pointer bodies are shallow, and those are recoverable via a cached per-target /meta fetch.

Creating Records

POST /

Insert one or many records. The request body determines single vs. batch mode.

Single insert:

bash

curl -X POST http://localhost:3000/todos/ \
  -H "Content-Type: application/json" \
  -d '{"title": "Buy milk", "priority": "high"}'

Response:

json

{ "insertedId": 1 }

Batch insert:

bash

curl -X POST http://localhost:3000/todos/ \
  -H "Content-Type: application/json" \
  -d '[{"title": "Buy milk"}, {"title": "Write docs"}]'

Response:

json

{ "insertedCount": 2, "insertedIds": [1, 2] }

Default values from @db.default and generated defaults (@db.default.increment, @db.default.uuid, @db.default.now) are applied automatically. Request bodies can contain nested relation data for deep insert operations.

Batch edge cases

Empty array [] — behavior is adapter-dependent (may return 200, 201, 400, or 500)
Single-item array [{...}] — treated as a batch insert, returns insertedCount / insertedIds
Large batches (100+ items) — supported; the entire batch runs in a single transaction
Partial failure — if any item fails validation or violates a constraint, the entire batch is rolled back

Updating Records

PUT /

Full replace by primary key. The body must include all required fields and the primary key field(s).

Single replace:

bash

curl -X PUT http://localhost:3000/todos/ \
  -H "Content-Type: application/json" \
  -d '{"id": 1, "title": "Buy oat milk", "completed": true, "priority": "high"}'

Response:

json

{ "matchedCount": 1, "modifiedCount": 1 }

Bulk replace:

bash

curl -X PUT http://localhost:3000/todos/ \
  -H "Content-Type: application/json" \
  -d '[
    {"id": 1, "title": "Buy oat milk", "completed": true, "priority": "high"},
    {"id": 2, "title": "Write tests", "completed": false, "priority": "medium"}
  ]'

Response:

json

{ "matchedCount": 2, "modifiedCount": 2 }

Nested relation data is supported per item — each record goes through the deep replace process.

PATCH /

Partial update by primary key. Only the provided fields are changed.

Single update:

bash

curl -X PATCH http://localhost:3000/todos/ \
  -H "Content-Type: application/json" \
  -d '{"id": 1, "completed": true}'

Response:

json

{ "matchedCount": 1, "modifiedCount": 1 }

Bulk update:

bash

curl -X PATCH http://localhost:3000/todos/ \
  -H "Content-Type: application/json" \
  -d '[{"id": 1, "completed": true}, {"id": 2, "priority": "high"}]'

Response:

json

{ "matchedCount": 2, "modifiedCount": 2 }

Field operations ($inc, $dec, $mul) work as plain JSON objects in the body:

bash

curl -X PATCH http://localhost:3000/products/ \
  -H "Content-Type: application/json" \
  -d '{"id": 42, "views": {"$inc": 1}, "stock": {"$dec": 1}}'

Also supports array patch operators. See Update & Patch for the full programmatic API.

OCC over HTTP

Tables that declare @db.column.version get optimistic concurrency control over HTTP automatically. Clients round-trip the version field — read the row, send it back as-is on PATCH or PUT, and the controller auto-lifts version from the body into $cas. No special headers, no special endpoints.

Auto-lift policy

The controller intercepts every write payload on a table whose /meta exposes a versionColumn:

If version is present in the body: it is stripped from the SET payload and lifted to $cas: { version: N }. The auto-bump still applies — the stored row ends up at N + 1, never at the value the client sent.
If version is absent: the write proceeds with no $cas — last-write-wins semantics (client opted out by stripping it).

Policy is presence-based, not enforced. There is no 428 Precondition Required gate.

bash

# Read the row — version comes back.
curl http://localhost:3000/tasks/one/1
# → { "id": 1, "title": "Bake bread", "status": "open", "version": 4 }

# Send it back. version: 4 is auto-lifted to $cas: { version: 4 }.
curl -X PATCH http://localhost:3000/tasks/ \
  -H "Content-Type: application/json" \
  -d '{"id": 1, "status": "done", "version": 4}'
# → 200 OK  { "matchedCount": 1, "modifiedCount": 1 }
# Row is now { …, status: "done", version: 5 }.

409 Conflict — version mismatch

When the row's stored version no longer matches what the client submitted, the controller returns 409 Conflict:

jsonc

{
  "statusCode": 409,
  "error": "Conflict",
  "message": "version_mismatch",
  "kind": "version_mismatch",
  "currentVersion": 6,
}

Discriminate on kind, not error

The proposal called for { error: "version_mismatch", currentVersion: N }. The actual response carries error: "Conflict" — the Wooks framework that powers Moost overrides the error field with the standard HTTP reason phrase, and @atscript/moost-db cannot reclaim it. The OCC discriminator lives in kind instead, and currentVersion is preserved at the top level.

Clients should test kind === "version_mismatch" and read currentVersion to refresh their view of the row, then retry.

Clients using @atscript/db-client get a typed VersionMismatchError subclass thrown automatically — no need to inspect body.kind. See the client doc for the pattern.

Clients on 409 typically:

Re-read the row via GET /one/:id (or use currentVersion directly if they have the rest of the row cached).
Re-apply their changes against the refreshed state.
Retry the PATCH / PUT.

404 Not Found — missing row

A CAS-bearing write on a row that does not exist returns 404, not 409. The controller disambiguates by issuing a single findOne(id) after the adapter reports matchedCount === 0:

Row missing → 404 Not Found.
Row present → 409 Conflict with the body above.

The extra findOne is paid only on the conflict path, never on the happy path.

PUT (replace) — same contract

PUT behaves identically to PATCH at this layer: round-trip version → $cas, mismatch → 409 with kind: "version_mismatch" + currentVersion, missing row → 404.

Bulk PATCH / PUT — per-item CAS

Each item in an array body carries its own optional version. Behavior:

Items with version get per-row CAS.
Items without version are last-write-wins.
Mismatches are silently skipped, not failed — never "fail all on first conflict".
Response is the aggregate shape: { matchedCount, modifiedCount }. Detect partial failure via matchedCount < items.length.

bash

curl -X PATCH http://localhost:3000/tasks/ \
  -H "Content-Type: application/json" \
  -d '[
    {"id": 1, "title": "a", "version": 5},
    {"id": 2, "title": "b", "version": 9},
    {"id": 3, "title": "c"}
  ]'
# → 200 OK  { "matchedCount": 2, "modifiedCount": 2 }
# Item 2 was silently skipped (its stored version was not 9).

Per-item conflict status is deferred

v1 returns aggregate { matchedCount, modifiedCount } only. Per-item 207 Multi-Status responses — which would tell the client exactly which items conflicted — are not yet implemented. Clients that need to know which items lost the race must re-read affected rows after a partial result.

Non-versioned tables — no change

Tables without @db.column.version are unaffected. /meta omits the versionColumn field; the auto-lift short-circuits when versionColumn is undefined; PATCH / PUT behave exactly as before.

A version field on a non-versioned table's payload is treated like any other field — passed through to the validator, which accepts it if the field exists on the type and rejects it as unknown otherwise. The OCC path is never engaged.

See the Versioning reference for the SDK-level API, error codes, and the withOptimisticRetry helper.

Deleting Records

DELETE /:id

Removes a single record by primary key. Returns 404 if the record is not found.

bash

curl -X DELETE http://localhost:3000/todos/42

Response:

json

{ "deletedCount": 1 }

Composite keys — use query parameters:

bash

curl -X DELETE "http://localhost:3000/task-tags/?taskId=1&tagId=2"

Response:

json

{ "deletedCount": 1 }

Error Handling

The controller automatically transforms errors into appropriate HTTP responses:

Error	HTTP Status	Response Body
`ValidatorError`	400	`{ message, statusCode, errors: [{ path, message }] }`
`DbError` (CONFLICT)	409	`{ message, statusCode, errors }`
`DbError` (other)	400	`{ message, statusCode, errors }`
Version mismatch (OCC)	409	`{ statusCode, error, message: "version_mismatch", kind, currentVersion }` — see OCC over HTTP
Not found	404	Standard 404

Validation error example:

json

{
  "message": "Validation failed",
  "statusCode": 400,
  "errors": [
    { "path": "title", "message": "Required field" },
    { "path": "project.title", "message": "Expected string, got number" },
    { "path": "tasks.0.status", "message": "Required field" }
  ]
}

Validation runs automatically on POST, PUT, and PATCH using the constraints defined in your .as schema.

Query Validation

Invalid query parameters return 400 errors with descriptive messages:

Invalid query	Error reason
`$with=nonexistent`	Navigation property does not exist
`$with=projectId`	FK field, not a navigation property
`$with=tasks($with=nonexistent)`	Nested relation does not exist
`$select=fakefield`	Field does not exist on the type
`$sort=nonexistent`	Cannot sort by unknown field
`GET /one/1?status=todo`	Filters not allowed on getOne endpoint

These validations apply to all endpoints that accept query controls — /query, /pages, and /one/:id.

Next Steps

URL Query Syntax — Full filter, sort, and pagination syntax
Relations & Search in URLs — $with, $search, $vector, $groupBy in query strings
Customization — Override hooks for access control and data transformation
CRUD Operations — Programmatic AtscriptDbTable API (non-HTTP)
HTTP Client — TypeScript client for consuming these endpoints

CRUD Endpoints ​

Reading Records ​

Read-response baseline ​

GET /query ​

GET /pages ​

GET /one/:id ​

GET /meta ​

FK ref shape in meta ​

Creating Records ​

POST / ​

Updating Records ​

PUT / ​

PATCH / ​

OCC over HTTP ​

Auto-lift policy ​

409 Conflict — version mismatch ​

404 Not Found — missing row ​

PUT (replace) — same contract ​

Bulk PATCH / PUT — per-item CAS ​

Non-versioned tables — no change ​

Deleting Records ​

DELETE /:id ​

Error Handling ​

Query Validation ​

Next Steps ​

CRUD Endpoints

Reading Records

Read-response baseline

GET /query

GET /pages

GET /one/:id

GET /meta

FK ref shape in meta

Creating Records

POST /

Updating Records

PUT /

PATCH /

OCC over HTTP

Auto-lift policy

409 Conflict — version mismatch

404 Not Found — missing row

PUT (replace) — same contract

Bulk PATCH / PUT — per-item CAS

Non-versioned tables — no change

Deleting Records

DELETE /:id

Error Handling

Query Validation

Next Steps