CRUD API

Auto-generate list, get, create, update, and remove procedures with createCrud()

Prerequisite: Define tables in `gencow/schema.ts` first. Tables that use createCrud() must follow the schema rules below.

createCrud(table) is the recommended way to expose standard data operations. It auto-generates typed procedure.query / procedure.mutation handlers with authentication, realtime invalidation, input validation, and PostgreSQL RLS applied through ctx.db.

Quick Start

Step 1 — Schema (gencow/schema.ts): define the table with id and an optional RLS policy — see Schema.

export const tasks = pgTable("tasks", {
  id: serial("id").primaryKey(),
  title: text(),
  userId: text(),
});

Step 2 — createCrud (gencow/tasks.ts + gencow/index.ts): wire the table into the API.

// gencow/tasks.ts
export const tasksCrud = createCrud(tasks);

// gencow/index.ts
defineApi({ crud: { tasks: tasksCrud } });

Step 3 — React (src/App.tsx): call the generated list procedure.

import { useQuery } from "@gencow/react";
import { api } from "../gencow/api";

const result = useQuery(api.tasks.list);

result?.data.map(t => <li>{t.title}</li>);

That's it — list, get, create, update, and remove with auth and realtime sync, no boilerplate.

Schema Rules for createCrud()

Important: Your table must follow these rules before you call createCrud(table).

Required: id Column

Every table using createCrud() must have an id column. This is the primary key used by get, update, and remove.

// ✅ serial id (auto-increment number)
id: serial("id").primaryKey(),

// ✅ text id (UUID string — auto-detected by createCrud)
id: text("id").primaryKey(),

// ❌ No id column — createCrud() will throw:
// [createCrud] Table "..." must have an 'id' column.

Why id? This is a universal convention (Convex, Supabase, Prisma all use it). It keeps the API predictable for AI code generation and vibe-coding.

Recommended Columns

Complete Template (Recommended)

// ✅ Recommended pattern — works perfectly with createCrud()
export const tasks = pgTable("tasks", {
    id: serial("id").primaryKey(),
    title: text("title").notNull(),
    description: text("description"),
    status: text("status").default("pending").notNull(),
    userId: text("user_id")
        .notNull()
        .references(() => user.id, { onDelete: "cascade" }),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

Tables That Don't Need createCrud()

For join tables, settings, or logs where createCrud() doesn't fit, use custom procedures instead — see Queries and Mutations.

Creating CRUD Endpoints

Instead of manually writing get, list, create, update, remove, register one createCrud export per table:

// gencow/posts.ts
import { createCrud, procedure } from "./runtime";
import { v } from "@gencow/core";
import { eq, ilike, desc } from "drizzle-orm";
import { posts } from "./schema";

export const postsCrud = createCrud(posts, { prefix: "posts" });

// gencow/index.ts
import { defineApi } from "@gencow/core";
import { postsCrud } from "./posts";

export default defineApi({
    crud: { posts: postsCrud },
});

Filtering

Use allowedFilters to whitelist which columns clients may filter on in list. Columns outside the list are rejected.

export const tasksCrud = createCrud(tasks, {
    prefix: "tasks",
    allowedFilters: ["status", "category"],
    searchFields: ["title", "description"],
});

import { useQuery } from "@gencow/react";
import { api } from "../gencow/api";

// Simple equality
const { data: active } = useQuery(api.tasks.list, {
    filters: { status: "active" },
});

// Multiple filters (implicit AND)
const { data: filtered } = useQuery(api.tasks.list, {
    filters: { status: "active", category: "tech" },
});

// Full-text search (requires searchFields on the server)
const { data: results } = useQuery(api.tasks.list, { search: "meeting" });

Set allowedFilters explicitly for any table where clients can pass filters. For comparison operators (gte, in, ilike) and OR / AND groups, see Core API — Advanced Filters.

Sorting

By default, list sorts by createdAt desc (or id desc when createdAt is missing). Clients pass orderBy and orderDir:

const newest = useQuery(api.tasks.list, { orderBy: "createdAt", orderDir: "desc" });
const byTitle = useQuery(api.tasks.list, { orderBy: "title", orderDir: "asc" });

Restrict sortable columns with sortable — any orderBy outside the list is rejected:

export const tasksCrud = createCrud(tasks, {
    sortable: ["createdAt", "title", "status"],
});

Pagination

list returns { data, total, nextCursor? }. Without a cursor option, pagination is offset-based via page and limit (defaults: page 1, limit 20, max 100):

export const tasksCrud = createCrud(tasks, {
    defaultLimit: 20,
    maxLimit: 100,
});

import { useState } from "react";
import { useQuery } from "@gencow/react";
import { api } from "../gencow/api";

const [page, setPage] = useState(1);
const { data: result } = useQuery(api.tasks.list, { page, limit: 20 });

// result?.data — current page
// result?.total — total matching rows

For large or live-updating lists, use keyset pagination with cursor. The sort order is fixed by the configured columns — include the primary key as the final segment for stable pages:

export const tasksCrud = createCrud(tasks, {
    cursor: ["createdAt", "id"],
});

import { useQuery } from "@gencow/react";
import { api } from "../gencow/api";

const { data: first } = useQuery(api.tasks.list, { limit: 20 });
const { data: next } = useQuery(api.tasks.list, {
    limit: 20,
    cursor: first?.nextCursor,
    orderDir: "desc",
});

When cursor is configured, page and orderBy are not supported — pass cursor and orderDir only. nextCursor is present when another page exists.

Limiting Generated Endpoints

Expose only the operations you need with methods. Omitted endpoints are not registered on the server and are excluded from gencow/api.ts after codegen:

// Read-only API — no create, update, or remove
export const logsCrud = createCrud(logs, {
    prefix: "logs",
    methods: ["list", "get"],
});

// Append-only feed
export const eventsCrud = createCrud(events, {
    prefix: "events",
    methods: ["list", "get", "create"],
});

Valid values: "list", "get", "create", "update", "remove". Default: all five.

Operation-Level Access Policies

createCrud() accepts an access field for gates that run before any database access. Prefer checks against session data (for example ctx.auth.requireAuth().role) so the gate stays fast.

Return false to reject with Forbidden before the handler touches the database.

export const tasksCrud = createCrud(tasks, {
    access: {
        read: () => true,
        create: (ctx) => ctx.auth.requireAuth().role === "admin",
        update: (ctx) => ctx.auth.requireAuth().role === "admin",
        remove: (ctx) => ctx.auth.requireAuth().role === "admin",
    },
});

Public / Anonymous Access

For dashboards, CMS tools, or other apps that don't require sign-in:

export const articlesCrud = createCrud(articles, { allowAnonymous: true, prefix: "articles" });

After gencow codegen, gencow/api.ts carries that flag on each generated procedure (for example defineProcedureQuery(..., { allowAnonymous: true })). The client picks it up automatically — no extra useQuery options needed:

import { useQuery } from "@gencow/react";
import { api } from "../gencow/api";

const result = useQuery(api.articles.list);

Use allowAnonymous: true only for non-sensitive data.

createCrud() + Custom Procedures Together

export const search = procedure.query
    .name("posts.search")
    .input(v.object({ keyword: v.string() }))
    .handler(async ({ context: ctx, input }) => {
        ctx.auth.requireAuth();
        return ctx.db.select().from(posts)
            .where(ilike(posts.title, `%${input.keyword}%`))
            .orderBy(desc(posts.createdAt));
    });

Next Steps

• Queries — Custom read queries when createCrud() isn't enough
• Mutations — Custom write operations
• Core API — Full createCrud() options reference

Common Patterns for AI Assistants

Table + CRUD Checklist

1. Always include id: serial("id").primaryKey() or id: text("id").primaryKey()
2. Include createdAt: timestamp("created_at").defaultNow().notNull() for sorting
3. Include userId: text("user_id") for user-owned data
4. Register via defineApi({ crud: { tasks: tasksCrud } }) in gencow/index.ts

Public / anonymous access

• Backend: createCrud(table, { allowAnonymous: true, prefix: "articles" })
• Frontend: useQuery(api.xxx.list) — codegen sets allowAnonymous on the generated api.ts entry

Authenticated CRUD (default)

• Backend: createCrud(table, { prefix: "tasks" }) (auth enabled by default)
• Frontend: useQuery(api.xxx.list) (token sent automatically)
• createCrud().list returns { data: T[], total: number, nextCursor?: string } — use .data to access the array
• Set allowedFilters, sortable, and methods to control list behavior and exposed endpoints