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.

createCrud() First — The Recommended Approach

90% of apps only need createCrud(). It auto-generates list and get procedures with authentication, realtime sync, and pagination built in. Use manual procedure.query only for complex logic.

┌─ Decision Flow ────────────────────────────────────────────┐
│                                                             │
│  Need to read data from the database?                       │
│                                                             │
│  ├─ Standard CRUD (list, get)?                              │
│  │  → Use createCrud(table) ✅                              │
│  │                                                          │
│  ├─ "I need search" → createCrud({ searchFields: [...] }) ✅│
│  │                                                          │
│  ├─ "I need filtering" → createCrud({ allowedFilters }) ✅  │
│  │                                                          │
│  ├─ "I need joins/aggregation/complex SQL"                  │
│  │  → Write procedure.query 📝                              │
│  │                                                          │
│  └─ "I need a REST endpoint" → httpRoute 🌐                 │
│                                                             │
└─────────────────────────────────────────────────────────────┘

What createCrud() Gives You (No Code Needed)

Feature	How
Paginated list	`useQuery(api.tasks.list, { page: 2, limit: 20 })`
Get by id	`useQuery(api.tasks.get, { id: 42 })`
Search	`useQuery(api.tasks.list, { search: "keyword" })`
Sorting	`useQuery(api.tasks.list, { orderBy: "title", orderDir: "asc" })`
Filtering	`useQuery(api.tasks.list, { filters: { status: "active" } })`
Total count	`result?.total` (built-in for pagination UI)
Realtime	Automatic — write procedures auto-update subscribed queries

createCrud() list Returns { data, total }

// Frontend — createCrud list response shape
const result = useQuery(api.tasks.list);

// result shape:
// {
//   data: Task[],     ← the actual items
//   total: number,    ← total count across all pages (for pagination)
// }

// ✅ Correct
result?.data.map(task => <li>{task.title}</li>)
result?.total  // e.g. 42

// ❌ Common mistake — result is NOT an array
result?.map(task => ...)  // TypeError! Use result.data.map()

Writing Custom Queries

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

With ownerRls(), access control is automatic at the PostgreSQL level — no manual filtering needed:

import { procedure } from "./runtime";
import { desc } from "drizzle-orm";
import { posts } from "./schema"; // defined with pgTable and ownerRls

export const list = procedure.query
    .name("posts.list")
    .handler(async ({ context: ctx }) => {
        // ✅ RLS auto-filters by userId — returns only current user's posts
        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 validation (`v` or Zod)
`.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 { v } from "@gencow/core";
import { eq } from "drizzle-orm";
import { procedure } from "./runtime";

export const getById = procedure.query
    .name("posts.getById")
    .input(v.object({ id: v.number() }))
    .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

import { eq, and, or, like, between, desc, asc, gt, lt, isNull, isNotNull } from "drizzle-orm";
import { v } from "@gencow/core";
import { procedure } from "./runtime";

export const search = procedure.query
    .name("posts.search")
    .input(v.object({
        keyword: v.string(),
        published: v.optional(v.boolean()),
    }))
    .handler(async ({ context: ctx, input }) => {
        const session = ctx.auth.requireAuth();
        const conditions = [eq(posts.userId, session.user.id)];

        if (input.keyword) {
            conditions.push(like(posts.title, `%${input.keyword}%`));
        }
        if (input.published !== undefined) {
            conditions.push(eq(posts.published, input.published));
        }

        return ctx.db
            .select()
            .from(posts)
            .where(and(...conditions))
            .orderBy(desc(posts.createdAt));
    });

Available Operators

Operator	Example	Description
`eq`	`eq(posts.id, 1)`	Equals
`and`	`and(eq(...), eq(...))`	Both conditions
`or`	`or(eq(...), eq(...))`	Either condition
`like`	`like(posts.title, "%search%")`	Pattern match
`gt` / `lt`	`gt(posts.views, 100)`	Greater / less than
`between`	`between(posts.views, 10, 100)`	Range
`isNull`	`isNull(posts.content)`	Is null
`isNotNull`	`isNotNull(posts.content)`	Is not null
`desc` / `asc`	`desc(posts.createdAt)`	Sorting

Pagination

Tip: If you're using createCrud(), pagination is built-in — just pass page and limit args. The examples below are for manual procedure.query.

Offset-Based

export const listPaged = procedure.query
    .name("posts.listPaged")
    .input(v.object({
        page: v.optional(v.number()),
        limit: v.optional(v.number()),
    }))
    .handler(async ({ context: ctx, input }) => {
        const session = ctx.auth.requireAuth();
        const page = input.page ?? 1;
        const limit = input.limit ?? 20;
        const offset = (page - 1) * limit;

        const items = await ctx.db
            .select()
            .from(posts)
            .where(eq(posts.userId, session.user.id))
            .orderBy(desc(posts.createdAt))
            .limit(limit)
            .offset(offset);

        return { items, page, limit };
    });

Cursor-Based

export const listCursor = procedure.query
    .name("posts.listCursor")
    .input(v.object({
        cursor: v.optional(v.number()),
        limit: v.optional(v.number()),
    }))
    .handler(async ({ context: ctx, input }) => {
        const session = ctx.auth.requireAuth();
        const limit = input.limit ?? 20;

        const conditions = [eq(posts.userId, session.user.id)];
        if (input.cursor) {
            conditions.push(lt(posts.id, input.cursor));
        }

        const items = await ctx.db
            .select()
            .from(posts)
            .where(and(...conditions))
            .orderBy(desc(posts.id))
            .limit(limit + 1);

        const hasMore = items.length > limit;
        const data = hasMore ? items.slice(0, -1) : items;
        const nextCursor = hasMore ? data[data.length - 1].id : null;

        return { items: data, nextCursor };
    });

Public Queries (No Auth Required)

For endpoints that don't require authentication:

export const listPublic = procedure.query
    .name("posts.listPublic")
    .allowAnonymous()
    .handler(async ({ context: ctx }) => {
        return ctx.db
            .select()
            .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 { posts, comments } from "./schema";

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

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

        if (!post[0]) return null;

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

        return { ...post[0], comments: postComments };
    });

Using from React

createCrud() list — { data, total } response

import { useQuery, useMutation } from "@gencow/react";
import { api } from "../gencow/api";  // auto-generated

function TaskList() {
    // createCrud list returns { data: Task[], total: number }
    const result = useQuery(api.tasks.list);
    const { mutate: createTask, isPending: isCreating } = useMutation(api.tasks.create);

    if (result === undefined) return <div>Loading...</div>;

    return (
        <div>
            <p>{result.total} tasks total</p>
            <ul>
                {result.data.map((task) => (
                    <li key={task.id}>{task.title}</li>
                ))}
            </ul>
            <button onClick={() => createTask({ title: "New task" })}>
                {isCreating ? "Adding..." : "Add Task"}
            </button>
        </div>
    );
}

Custom procedure — direct array response

function SearchResults({ keyword }) {
    // procedure.query returns whatever the handler returns
    const posts = useQuery(api.posts.search, { keyword });

    if (posts === undefined) return <div>Loading...</div>;

    return (
        <ul>
            {posts.map((p) => (
                <li key={p.id}>{p.title}</li>
            ))}
        </ul>
    );
}

Conditional query — skip when no selection

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

    if (!selectedId) return <div>Select a post</div>;
    if (post === undefined) return <div>Loading...</div>;

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

Public query — runs without auth

// Server: procedure must call .allowAnonymous()
// Client: pass { public: true } as 3rd argument
const articles = useQuery(api.articles.list, {}, { public: true });

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

// ═══ createCrud() list ══════════════════════════════════

const result = useQuery(api.tasks.list);
result?.data.map(t => ...)    // data array
result?.total                  // total count

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

const result = useQuery(api.tasks.list, { search: "회의" });
const result = useQuery(api.tasks.list, { orderBy: "title", orderDir: "asc" });
const result = useQuery(api.tasks.list, { filters: { status: "active" } });

// ═══ createCrud() get ═════════════════════════════════════

const task = useQuery(api.tasks.get, { id: 42 });

// ═══ Write procedures ═════════════════════════════════════

const { mutate: create, isPending } = useMutation(api.tasks.create);
await create({ title: "New task" });

const { mutate: update } = useMutation(api.tasks.update);
await update({ id: 42, title: "Updated title" });

const { mutate: remove } = useMutation(api.tasks.remove);
await remove({ id: 42 });

Input Validation

Use the v validator with .input():

.input(v.object({
    id: v.number(),
    title: v.string(),
    count: v.optional(v.number()),
    tags: v.array(v.string()),
    settings: v.optional(v.object({
        theme: v.string(),
        count: v.number(),
    })),
}))

Validator	Description
`v.string()`	String
`v.number()`	Number
`v.boolean()`	Boolean
`v.optional(v.X())`	Optional field
`v.array(v.X())`	Array of type
`v.object({...})`	Nested object

Security Rules

When using ownerRls(), security is automatic at the database level:

// ✅ With ownerRls() — RLS filter auto-applied by Postgres, safe by default
ctx.db.select().from(posts);

// ✅ Additional filters are AND-combined with the schema filter
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   // ⚠️ Escape hatch — no filter, for 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

Mutations — Custom write operations
Core API — Full createCrud() options and ctx reference
Authentication — Auth setup details
Realtime — How WebSocket sync works

Schema Mutations