Database models

Use utils.createModel with a Drizzle table for create, read, update and delete operations. The helpers return Results and include validation and pagination. Keep custom queries in your application’s model functions.

When to use itCopy Anchor Link

Use a model for each table that actions read or write. Keep joins, reports and custom queries next to the model in the same domain folder, and use the table and schemas the model exposes when you write them.

Complete exampleCopy Anchor Link

The table is an ordinary Drizzle table. The model is created from it with a database client. The action validates input with Zod, calls create, and returns the saved row or the failure.

import { createAction, createModel, createServices } from "@nilejs/nile";
import { Err, Ok } from "@nilejs/nile/fp";
import { pgTable, text, timestamp, uuid } from "drizzle-orm/pg-core";
import { z } from "zod";

export const tasks = pgTable("tasks", {
  id: uuid("id").defaultRandom().primaryKey(),
  title: text("title").notNull(),
  description: text("description"),
  status: text("status", { enum: ["pending", "in-progress", "done"] })
    .notNull()
    .default("pending"),
  created_at: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
  updated_at: timestamp("updated_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

export const taskModel = createModel(tasks, {
  db: database,
  name: "task",
});

const createTaskSchema = z.object({
  title: z.string().min(1, "Title is required"),
  description: z.string().optional().default(""),
});

type CreateTaskInput = z.infer<typeof createTaskSchema>;

const createTaskHandler = async (data: CreateTaskInput) => {
  const created = await taskModel.create({
    data: {
      title: data.title,
      description: data.description,
    },
  });

  if (created.isErr) {
    return Err(created.error);
  }

  return Ok({ task: created.value });
};

const createTaskAction = createAction({
  name: "create",
  description: "Create a task",
  validation: createTaskSchema,
  handler: createTaskHandler,
});

const listTasksHandler = async () => {
  const page = await taskModel.findPaginated({ limit: 20, offset: 0 });

  if (page.isErr) {
    return Err(page.error);
  }

  return Ok({ tasks: page.value.items, hasMore: page.value.hasMore });
};

const listTasksAction = createAction({
  name: "list",
  description: "List the first page of tasks",
  handler: listTasksHandler,
});

export const services = createServices([
  {
    name: "tasks",
    description: "Task management",
    actions: [createTaskAction, listTasksAction],
  },
]);

The database value is the Drizzle client your application creates. It is declared as a global in the snippet so the file type-checks on its own. The starter's src/db/client.ts creates it for you.

MethodsCopy Anchor Link

Each method returns a Result. Check isErr before reading value.

Options that matterCopy Anchor Link

The model also exposes table and schemas.insert, schemas.update and schemas.select. Use them in custom queries so they stay in step with the table.

How it behavesCopy Anchor Link

• create and update check the data against the generated Zod schemas before they query. They write the data you supplied.
• Offset pagination uses a count of the whole table. Cursor pages fetch one extra row to decide whether another page exists.
• findAll and offset pagination sort by created_at when the table has it. Otherwise no default order applies.
• Query failures are logged with bounded diagnostics and returned as a safe failure. Driver messages that may contain data are not returned.
• createTx and updateTx run inside a transaction only when you pass dbx. Omitting dbx does not start one.

LimitsCopy Anchor Link

• The helpers cover single-table work. Multi-step writes need an explicit transaction.
• Cursor pages need a column that is unique and ordered. id works for UUIDs only if it sorts the way you want.
• A transaction-wrapped Err is thrown to roll back. Guard that call with safeTry.

Common mistakesCopy Anchor Link

• Treating a missing row as null. findById returns a failure, so check isErr.
• Calling createTx without dbx, then expecting rollback on failure.
• Paginating with a non-unique cursor column. Rows with the same value can be skipped or repeated.
• Writing raw SQL in the action when a model method covers the case.

See alsoCopy Anchor Link

• Model helpers has the complete method reference.
• Data access overview covers multi-write workflows.
• Shared utilities explains safeTry from @nilejs/nile/fp and safe database failures.