Queries

Read data with type-safe procedures — createCrud() auto-generation and custom procedure.query

Frontend SDK: gencow codegen emits api.* defs from `@gencow/client`. Use `useQuery` from @gencow/react in components. Register createCrud() output through defineApi({ crud }) in gencow/index.ts.

TanStack Query apps: If your frontend uses @tanstack/react-query, use `@gencow/tanstack-query` instead of @gencow/react for the same procedures. Do not mix both query stacks for the same data.

createCrud() First — The Recommended Approach

90% of apps only need `createCrud()` for reads. That guide covers setup, schema rules, pagination, search, filtering, and the { data, total } list response shape.

Use manual procedure.query (below) only for joins, aggregation, or other logic createCrud() cannot express:

┌─ Decision Flow ────────────────────────────────────────────┐
│                                                             │
│  Need to read data from the database?                       │
│                                                             │
│  ├─ Standard CRUD (list, get, search, filters)?             │
│  │  → createCrud() — see CRUD API guide ✅                  │
│  │                                                          │
│  ├─ "I need joins/aggregation/complex SQL"                  │
│  │  → Write procedure.query 📝 (this guide)               │
│  │                                                          │
│  └─ "I need a REST endpoint" → httpRoute 🌐                 │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Writing Custom Queries

When createCrud() doesn't cover your use case, use procedure.query from gencow/runtime.ts.

.input() and .output() are optional on procedure.query. Add .input() when the query takes arguments; omit it when it takes none. .output() is strongly recommended so responses are validated and gencow codegen can emit typed useQuery results in gencow/api.ts. Use any library that implements Standard Schema v1 — Zod is used in the examples below. Gencow also ships a minimal built-in v validator from @gencow/core; it is optional and fine for small schemas, but Zod (or Valibot, ArkType, etc.) is recommended for real apps. Invalid input returns 400; invalid output returns 500.

For .output() schemas that describe database rows, derive them from your Drizzle table with `createSelectSchema` — do not hand-write z.object({ id: z.number(), ... }) fields that duplicate gencow/schema.ts. Hand-written z.object() is fine for procedure-only args (pagination, filters) or composite envelopes that are not a single table row.

With an RLS policy on the table, access control is automatic at the PostgreSQL level — see Schema.

import { z } from "zod";
import { desc } from "drizzle-orm";
import { createSelectSchema } from "drizzle-orm/zod";
import { procedure } from "./runtime";
import { posts } from "./schema";

const postSchema = createSelectSchema(posts);

export const list = procedure.query
    .name("posts.list")
    .output(z.array(postSchema))
    .handler(async ({ context: ctx }) => {
        // RLS on ctx.db filters rows automatically when configured on the table
        return ctx.db
            .select()
            .from(posts)
            .orderBy(desc(posts.createdAt));
    });

Key Parts

Part	Description
`.name("posts.list")`	Unique name — must match `{module}.{export}` pattern
`.input(schema)`	Optional Standard Schema v1 validator for request args — add when the query takes arguments; omit when it takes none
`.output(schema)`	Optional Standard Schema v1 validator for the handler return value — strongly recommended for typed `gencow codegen`
`.handler({ context, input })`	Async function that reads from `context.db`
`context.auth.requireAuth()`	Enforces authentication, returns session
`context.db`	Drizzle ORM instance for database queries

Query with Arguments

import { z } from "zod";
import { eq } from "drizzle-orm";
import { createSelectSchema } from "drizzle-orm/zod";
import { procedure } from "./runtime";
import { posts } from "./schema";

const postSchema = createSelectSchema(posts);

export const getById = procedure.query
    .name("posts.getById")
    .input(z.object({ id: z.number() }))
    .output(postSchema.nullable())
    .handler(async ({ context: ctx, input }) => {
        ctx.auth.requireAuth();
        const [post] = await ctx.db
            .select()
            .from(posts)
            .where(eq(posts.id, input.id));
        return post ?? null;
    });

Filtering, Sorting & Pagination

Standard list behavior — filters, search, sort order, and pagination — is built into `createCrud()`. See that guide for allowedFilters, sortable, page/limit, and cursor-based list.

For custom procedure.query handlers, use Drizzle `where`, `orderBy`, and `limit`/`offset` in your handler as needed.

Public Queries (No Auth Required)

For endpoints that don't require authentication:

import { z } from "zod";
import { desc, eq } from "drizzle-orm";
import { createSelectSchema } from "drizzle-orm/zod";
import { procedure } from "./runtime";
import { posts } from "./schema";

const publicPostSchema = createSelectSchema(posts).pick({
    id: true,
    title: true,
    createdAt: true,
});

export const listPublic = procedure.query
    .name("posts.listPublic")
    .allowAnonymous()
    .output(z.array(publicPostSchema))
    .handler(async ({ context: ctx }) => {
        return ctx.db
            .select({
                id: posts.id,
                title: posts.title,
                createdAt: posts.createdAt,
            })
            .from(posts)
            .where(eq(posts.published, true))
            .orderBy(desc(posts.createdAt))
            .limit(50);
    });

Warning: Anonymous procedures should only expose non-sensitive data. Never return user private data without authentication.

Joining Tables

import { z } from "zod";
import { and, desc, eq } from "drizzle-orm";
import { createSelectSchema } from "drizzle-orm/zod";
import { procedure } from "./runtime";
import { posts, comments } from "./schema";

const postSchema = createSelectSchema(posts);
const commentSchema = createSelectSchema(comments);

const postWithCommentsSchema = postSchema.extend({
    comments: z.array(commentSchema),
});

export const getWithComments = procedure.query
    .name("posts.getWithComments")
    .input(z.object({ id: z.number() }))
    .output(postWithCommentsSchema.nullable())
    .handler(async ({ context: ctx, input }) => {
        const session = ctx.auth.requireAuth();

        const [row] = await ctx.db
            .select()
            .from(posts)
            .where(and(eq(posts.id, input.id), eq(posts.userId, session.user.id)))
            .limit(1);

        if (!row) return null;

        const postComments = await ctx.db
            .select()
            .from(comments)
            .where(eq(comments.postId, input.id))
            .orderBy(desc(comments.createdAt));

        return { ...row, comments: postComments };
    });

Using from React

createCrud() procedures

See CRUD API for useQuery(api.tasks.list, {}) and the { data, total } response shape.

Custom procedure — direct handler response

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

function PostWithComments({ id }: { id: number }) {
    const { data: post, isLoading } = useQuery(api.posts.getWithComments, { id });

    if (isLoading) return <div>Loading...</div>;
    if (!post) return <div>Not found</div>;

    return (
        <div>
            <h1>{post.title}</h1>
            <p>{post.comments.length} comments</p>
        </div>
    );
}

Conditional query — skip when no selection

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

function PostDetail({ selectedId }: { selectedId?: number }) {
    const { data: post, isLoading } = useQuery(
        api.posts.getById,
        selectedId ? { id: selectedId } : "skip"
    );

    if (!selectedId) return <div>Select a post</div>;
    if (isLoading) return <div>Loading...</div>;
    if (!post) return <div>Not found</div>;

    return <h1>{post.title}</h1>;
}

Public query — runs without auth

// Server: procedure.query.allowAnonymous() — codegen sets allowAnonymous on api.ts
const { data: articles } = useQuery(api.posts.listPublic);

Realtime: useQuery automatically subscribes to WebSocket updates. When data changes on the server, your component re-renders with fresh data — no manual refetching.

Frontend Cheatsheet

For createCrud() list / get usage, see CRUD API.

// ═══ Custom procedure.query ═══════════════════════════════

const { data: post } = useQuery(api.posts.getWithComments, { id: 42 });

const { data: item } = useQuery(
    api.posts.getById,
    selectedId ? { id: selectedId } : "skip"
);

const { data: articles } = useQuery(api.posts.listPublic);

Input & Output Validation

.input() and .output() accept any Standard Schema v1 validator. Pick the library you already use in the project — Zod, Valibot, and ArkType all work the same way on the procedure builder.

Row shapes: use `createSelectSchema(table)` from drizzle-orm/zod so validation stays in sync when columns change. API-only shapes (pagination args, composite envelopes) can stay as hand-written z.object().

import { z } from "zod";
import { createSelectSchema } from "drizzle-orm/zod";
import { procedure } from "./runtime";
import { posts } from "./schema";

const listInput = z.object({
    page: z.number().int().min(1).optional(),
    limit: z.number().int().min(1).max(100).optional(),
});

const listOutput = z.object({
    items: z.array(createSelectSchema(posts)),
    total: z.number(),
});

export const listPaged = procedure.query
    .name("posts.listPaged")
    .input(listInput)
    .output(listOutput)
    .handler(async ({ context: ctx, input }) => {
        // ...
    });

Gencow's built-in v validator (import { v } from "@gencow/core") is a lightweight alternative for simple shapes. It is optional — prefer Zod or another Standard Schema library when you need refinements, transforms, or shared schemas across modules. See Core API — procedure for the full builder chain.

Security Rules

When the table has an RLS policy, row filtering is automatic at the database level:

// RLS policy on the table — ctx.db only returns allowed rows
ctx.db.select().from(posts);

// Additional filters are AND-combined with RLS
ctx.db.select().from(posts).where(eq(posts.published, true));

ctx.db vs ctx.unsafeDb

ctx.db         // ✅ Default — schema filter auto-applied, execute() blocked
ctx.unsafeDb   // ⚠️ System-plane handle, for audited admin/system operations only

Note: ctx.unsafeDb usage is flagged in security audits. .allowAnonymous() handlers that use ctx.unsafeDb, rawSql, SQL.unsafe, or client.unsafe are blocked at deploy time unless they carry a complete gencow-allow-unsafe-db reason: ... scope: ... owner: ... test: ... review comment.

Next Steps

CRUD API — Schema rules, allowAnonymous, and access policies for createCrud()
Mutations — Custom write operations
Core API — Full createCrud() options and ctx reference
Authentication — Auth setup details
Realtime — How WebSocket sync works

CRUD API Mutations