CRUDSupported

Create, Read, Update, and Delete operations form the backbone of any data layer. zmdb's repository provides full CRUD semantics with automatic validation against your schema, ensuring that only well-typed data reaches the database.

Create#

Insert a new row. The payload is validated against CreateDTO<S> — auto-increment columns are rejected, and columns with defaults or nullable columns are optional.

const user = await users.create({
  email: 'alice@example.com',
  role: 'user', // optional, 'user' is the default
});
// user: Entity<UserSchema> — includes generated id, createdAt

SQL emitted:

INSERT INTO "users" ("email", "role") VALUES ($1, $2) RETURNING *
-- parameters: ['alice@example.com', 'user']

Read#

Fetch rows by ID, by arbitrary where clause, or all rows.

// By primary key — the fastest path
const user = await users.findById(1);
// user: Entity<UserSchema> | undefined

// By arbitrary columns
const admin = await users.findOne({ role: 'admin' });
// admin: Entity<UserSchema> | undefined

// All rows — use with caution on large tables
const allUsers = await users.findAll();
// allUsers: readonly Entity<UserSchema>[]

Update#

Partial update. The payload is an UpdatePatch<S> — all fields are optional; ordinary values must match UpdateDTO<S>, and branded expression operands must match the same column type.

import { inc } from '@zmdb/core/sql';

const updated = await users.update(1, { role: 'admin' });
// updated: Entity<UserSchema> | undefined (undefined if id not found)

const post = await posts.increment(1, 'views');
const affected = await posts.updateMany({ authorId: 7 }, { views: inc(1) });

SQL emitted:

UPDATE "users" SET "role" = $1 WHERE "id" = $2 RETURNING *
-- parameters: ['admin', 1]

The Postgres family, SQLite and SQL Server expression-bearing keyed updates return the computed row; updateMany returns the number of rows returned. On the MySQL family, ordinary row-returning create, update, and upsert calls refuse before driver execution. Expression-bearing keyed updates/upserts and every updateMany omit unsupported RETURNING, so those calls resolve to undefined without issuing a follow-up SELECT.

const user = await users.findById(1);
user.role = 'admin'; // ❌ This does NOTHING

await users.update(1, { role: 'admin' }); // ✅ Explicit update required

Delete#

Remove a row by ID. Returns true if a row was deleted, false if the ID didn't exist. On a table declared with SoftDelete<'deletedAt'>, this is a guarded UPDATE that records a Node Date; use hardDelete(id) for a deliberate physical delete and restore(id) to clear the managed timestamp. The Entity Filters guide covers visibility escapes, write filters, relation targets, and unique-index behavior.

const deleted = await users.delete(1);
// deleted: boolean

SQL emitted:

DELETE FROM "users" WHERE "id" = $1 RETURNING "id"
-- parameters: [1]

For a soft-deletable table the emitted statement is instead:

UPDATE "users" SET "deletedAt" = $1
WHERE "id" = $2 AND "deletedAt" IS NULL
RETURNING "id"

Validation Semantics#

create, update, updateMany, and the update object passed to upsert run validation before compiling SQL:

For an expression-valued update, only that key is removed from the ordinary row-value check; its operand is validated against the column's app type, and every ordinary sibling remains strict.

// This throws — id is auto-increment
await users.create({ id: 999, email: 'test@example.com' });

// This throws — missing required field
await users.create({}); // email is required

Cross-links#

• Repository — full repository API
• Entity Filters — tenant predicates and soft delete
• Read DTOs — typed query helpers
• Inert Rows — why rows don't auto-persist
• Validation — AOT validation details