Querying

Fragno DB queries are type-safe and based on your schema. The recommended way to query is via the Transaction Builder this.handlerTx() in route handlers, so reads/writes can be composed and batched into a single transaction.

Using the transaction (`tx`) helper

In route handlers, use this.handlerTx() to build and execute transactions:

handler: async function (_, { json }) {
  const [users] = await this.handlerTx()
    .retrieve(({ forSchema }) =>
      forSchema(mySchema).find("users", (b) => b.whereIndex("primary")),
    )
    .execute();

  return json({ users });
}

See Transactions for the full pattern including services with serviceTx.

Use function syntax in handlers

Route handlers should use async function (...) { ... } syntax so this.handlerTx() is available and typed. Arrow functions (() => {}) don't have their own this.

Finding Records

Index-Based Queries

All find(...) and findFirst(...) queries must specify an index using whereIndex(). This ensures optimal performance and predictable query patterns.

const [users] = await this.handlerTx()
  .retrieve(({ forSchema }) =>
    forSchema(mySchema).find("users", (b) =>
      b.whereIndex("idx_email", (eb) => eb("email", "=", "user@example.com")),
    ),
  )
  .execute();

Why Index-Based Queries?

Requiring indexes for all queries ensures consistent performance and prevents slow table scans.

Basic Queries

Find all users (table scan using the primary index):

const [users] = await this.handlerTx()
  .retrieve(({ forSchema }) => forSchema(mySchema).find("users", (b) => b.whereIndex("primary")))
  .execute();

Select specific columns:

const [userNames] = await this.handlerTx()
  .retrieve(({ forSchema }) =>
    forSchema(mySchema).find("users", (b) => b.whereIndex("primary").select(["id", "name"])),
  )
  .execute();

Count records:

const [userCount] = await this.handlerTx()
  .retrieve(({ forSchema }) =>
    forSchema(mySchema).find("users", (b) => b.whereIndex("primary").selectCount()),
  )
  .execute();

Use find(...) when you want an array of results:

const [users] = await this.handlerTx()
  .retrieve(({ forSchema }) =>
    forSchema(mySchema).find("users", (b) =>
      b.whereIndex("idx_email", (eb) => eb("email", "contains", "@example.com")),
    ),
  )
  .execute();
// users is User[]

Use findFirst(...) when you want a single row (or null):

const [user] = await this.handlerTx()
  .retrieve(({ forSchema }) =>
    forSchema(mySchema).findFirst("users", (b) =>
      b.whereIndex("idx_email", (eb) => eb("email", "=", "user@example.com")),
    ),
  )
  .execute();
// user is User | null

Cursor-Based Pagination

Use cursor-based pagination for efficient paging through large result sets. Fragno DB provides findWithCursor() which automatically generates cursors from query results:

const [page] = await this.handlerTx()
  .retrieve(({ forSchema }) =>
    forSchema(mySchema).findWithCursor("users", (b) =>
      b.whereIndex("idx_name").orderByIndex("idx_name", "asc").pageSize(10),
    ),
  )
  .execute();

// page.items - the results
// page.cursor?.encode() - cursor for next page
// page.hasNextPage - whether more results exist

// To fetch next page, pass the cursor:
const [nextPage] = await this.handlerTx()
  .retrieve(({ forSchema }) => forSchema(mySchema).findWithCursor("users", (b) => b.after(cursor)))
  .execute();

When to Use findWithCursor()

Use findWithCursor() when you need pagination metadata. Use regular find() when you don't need cursors. Both methods support the same query building features.

Ordering

Sort results using indexes:

const [users] = await this.handlerTx()
  .retrieve(({ forSchema }) =>
    forSchema(mySchema).find("users", (b) =>
      b.whereIndex("idx_created").orderByIndex("idx_created", "desc"),
    ),
  )
  .execute();

Joins

Load related data using joins:

const [postsWithAuthor] = await this.handlerTx()
  .retrieve(({ forSchema }) =>
    forSchema(mySchema).find("posts", (b) => b.whereIndex("primary").join((j) => j.author())),
  )
  .execute();

Note: We currently only support simple left joins.

Creating Records

const { userId } = await this.handlerTx()
  .mutate(({ forSchema }) => {
    const uow = forSchema(mySchema);

    const userId = uow.create("users", {
      name: "Alice",
      email: "alice@example.com",
      age: 25,
    });

    // Columns with defaultTo$() are auto-populated
    uow.create("posts", {
      title: "My Post",
      content: "Content here",
      userId: userId.toString(),
      // createdAt auto-generated via defaultTo$("now")
    });

    return { userId };
  })
  .execute();

Updating Records

await this.handlerTx()
  .mutate(({ forSchema }) => {
    forSchema(mySchema).update("users", userId, (b) => b.set({ name: "Alice Updated", age: 26 }));
  })
  .execute();

Deleting Records

await this.handlerTx()
  .mutate(({ forSchema }) => {
    forSchema(mySchema).delete("users", userId);
  })
  .execute();

Combining Retrieve and Mutate

You can chain .retrieve() and .mutate() to read data, then use it in mutations:

const { order } = await this.handlerTx()
  .retrieve(({ forSchema }) =>
    forSchema(mySchema).findFirst("users", (b) =>
      b.whereIndex("primary", (eb) => eb("id", "=", userId)),
    ),
  )
  .mutate(({ forSchema, retrieveResult: [user] }) => {
    if (!user) {
      throw new Error("User not found");
    }

    const orderId = forSchema(mySchema).create("orders", {
      userId: user.id.toString(),
      total: 100,
    });

    return { order: { id: orderId, userId: user.id } };
  })
  .execute();

Transactions

For atomic operations and batching, see Transactions.

Alternative: using `db` from dependencies (no multi-operation transactions)

You can access the db object directly inside the withDependencies() callback. Operations execute immediately as individual queries. This can be convenient for simple tasks, but you lose the key benefits of the transaction builder pattern:

No multi-operation atomicity across several reads/writes (no single transaction boundary).
No optimistic concurrency control via .check() (and no automatic retries).
No durable hooks integration, because hooks are recorded during the mutation phase.

Keep db usage for cases where you don't need transactional composition.

Next Steps

See Dependencies and Services for using the DB in context
Check the example-fragments/fragno-db-library

Querying

On this page