Writes and procedures

Writes are off until you turn them on, for every table at once or one resource at a time. The examples on this page run with these options, against the demo schema:

resources: {
    tags: { write: true },
    todos: { write: { create: ['name', 'status', 'owner_id', 'estimate'], update: ['name', 'status', 'owner_id'], delete: false } },
    steps: { write: { create: true, update: true, delete: true, bulk: true } },
},
procedures: {
    'todos/{todo}/complete': { function: 'demo.complete_todo' },
    'tags/{tag}/todos': { function: 'demo.todos_tagged' },
},

write: true allows create, update and delete of every visible column. A list allows only the columns named. Generated and identity columns are never writable, and neither is a key on update.

Writes on every table

For an API that writes everywhere, set write once, beside expose:

import { createREST } from '@crudy/restloom-postgres';
 
createREST({
    db, // from a database package, see Dialects
    expose: { schemas: ['app'] },
    write: true,
    resources: {
        audit_log: { write: false },
        users: { write: { update: ['name'] } },
    },
});

It applies to every exposed table; views stay read-only until their resource allows writes, since many views cannot be written. A resource that sets its own write uses it instead: write: false keeps one table read-only, a column list narrows another. write: { create: true, update: true, delete: true, bulk: true } adds updates and deletes by filter everywhere.

What is never writable stays so: hidden and sensitive columns, generated columns, and keys on update; a bulk request still needs a filter. Who may write is decided by your authorize hook, which receives every write with its action: see Security.

Getting the rows back

Without a Prefer header a write answers with no body. Prefer: return=representation answers the written rows, read back with the request's select, so embeds work too.

POST /tags?select=id,name
Prefer: return=representation

{ "id": 4, "name": "critical" }

[{ "id": 4, "name": "critical" }]

POST /tags

[{ "id": 5, "name": "legacy" }, { "id": 6, "name": "edge" }]

POST /tags

[{ "id": 7, "name": "a" }, { "name": "b" }]

{ "error": "mixed_columns" }

A column outside the resource's writable list is refused, whatever its value:

POST /todos

{ "name": "book dentist", "status": "open", "pinned": false }

{ "error": "not_writable", "message": "pinned" }

Updating and deleting

PATCH and DELETE act on one row, by its key. A row that does not exist, or is not visible, answers 404.

PATCH /todos/2?select=id,name,owner(name)
Prefer: return=representation

{ "owner_id": 1 }

{ "id": 2, "name": "file taxes", "owner": { "name": "Ada" } }

DELETE /tags/4

DELETE /tags/3

{ "error": "still_referenced" }

DELETE /todos/1

{ "error": "method_not_allowed" }

A resource with bulk: true also takes PATCH and DELETE on the collection, with at least one filter. Without a filter they are refused, so no request can empty a table by accident.

DELETE /steps?priority=lt.3&select=id,title
Prefer: return=representation

[{ "id": 2, "title": "Find the receipts" }]

DELETE /steps

{ "error": "filter_required" }

Values

Body values are checked against each column's type, as query values are: a number for a number column, true or false for a boolean, a string in ISO 8601 for a date or a time. The database's own constraints answer with stable codes:

POST /tags

{ "id": 1, "name": "duplicate key" }

{ "error": "duplicate" }

POST /steps

{ "id": 9, "title": "Call the clinic", "todo_id": 99, "priority": 1, "due_on": "2026-04-01" }

{ "error": "reference_missing" }

Procedures

PostgreSQL, MySQL and SQL Server run procedures; SQLite has no functions to call. A MySQL function returns a single value, and a SQL Server function cannot change data.

A procedure calls a database function at a path you choose. A {name} in the path passes that segment as the argument of the same name; the body's keys pass the others. Arguments are checked by name and type, and one with a default may be left out.

A procedure is a POST unless declared method: 'GET'. A GET procedure takes its arguments from the query string (/search?q=milk&limit=10), each once, and runs in a read-only transaction: use it for a function without side effects.

POST /todos/3/complete

{ "note": "bought a new one" }

3

A function returning a set answers an array of its rows. File taxes belongs to Ada since the update above.

POST /tags/home/todos

[
  {
    "id": 2,
    "name": "file taxes",
    "status": "open",
    "owner_id": 1,
    "estimate": 1,
    "pinned": true,
    "created_at": "2026-01-05T09:30:00+00:00"
  },
  {
    "id": 4,
    "name": "pay rent",
    "status": "open",
    "owner_id": 3,
    "estimate": 4,
    "pinned": true,
    "created_at": "2026-02-11T16:45:00+00:00"
  }
]

POST /todos/3/complete

{ "why": "too busy" }

{ "error": "unknown_argument", "message": "why" }