Prepared Query

Use prepare() to create reusable, type-safe prepared statements that can be executed multiple times with different parameter values. Prepared queries compile the SQL once and reuse the compiled query for better performance.

Basic Usage

Creating a Prepared Query

import { prepare, eq } from "durcno";
import { db } from "./db/index.ts";
import { Users } from "./db/schema.ts";

// Create a prepared query with a username parameter
const findUserByUsername = prepare(
  { username: Users.username.arg() },
  (args) => {
    return db
      .prepare()
      .from(Users)
      .select()
      .where(eq(Users.username, args.username));
  },
);

Executing a Prepared Query

Use .run() to execute the prepared query with specific values:

// Execute with type-safe parameters
const users = await findUserByUsername.run(db, { username: "john" });
// Type: { id: bigint; username: string; email: string | null; type: "admin" | "user"; createdAt: Date }[]

warning

Prepared queries does not provide runtime validation of argument type. Make sure to validate the argument values before calling .run().

How It Works

1. Define arguments: Create argument placeholders using Column.arg() or Arg.number() / Arg.bigint()
2. Build the query: Use db.prepare() with the appropriate builder (from(), insert(), update(), delete(), or query())
3. Wrap with prepare: Pass arguments and query builder to prepare()
4. Execute: Call .run(db, values) to execute with specific values

Multiple Arguments

You can define multiple arguments in a single prepared query:

import { prepare, and, eq } from "durcno";
import { db } from "./db/index.ts";
import { Users } from "./db/schema.ts";

const findUser = prepare(
  {
    username: Users.username.arg(),
    email: Users.email.arg(),
    type: Users.type.arg(),
  },
  (args) => {
    return db
      .prepare()
      .from(Users)
      .select()
      .where(
        and(
          eq(Users.username, args.username),
          eq(Users.email, args.email),
          eq(Users.type, args.type),
        ),
      );
  },
);

// Execute with all required parameters
const result = await findUser.run(db, {
  username: "john",
  email: "john@example.com",
  type: "admin",
});

Using Arguments in INSERT Values

Prepared arguments can also be used inside insert().values(...) when you want to bind runtime values for a new row:

const createUser = prepare({ username: Users.username.arg() }, (args) => {
  return db
    .prepare()
    .insert(Users)
    .values({
      username: args.username,
      email: "prepare@example.com",
      type: "user",
    })
    .returning("*");
});

const [user] = await createUser.run(db, { username: "new_user" });

The same pattern also applies to update().set(...), delete().where(...), and relational queries created with query().findMany({...}).

Selective Column Selection

You can select specific columns in your prepared query:

const findUserInfo = prepare({ id: Users.id.arg() }, (args) => {
  return db
    .prepare()
    .from(Users)
    .select({ username: Users.username, email: Users.email })
    .where(eq(Users.id, args.id));
});

const result = await findUserInfo.run(db, { id: 1n });
// Type: { username: string; email: string | null }[]

Complex Conditions

OR Conditions

import { prepare, or, eq } from "durcno";

const findByEitherUsername = prepare(
  {
    username1: Users.username.arg(),
    username2: Users.username.arg(),
  },
  (args) => {
    return db
      .prepare()
      .from(Users)
      .select({ id: Users.id, username: Users.username })
      .where(
        or(
          eq(Users.username, args.username1),
          eq(Users.username, args.username2),
        ),
      );
  },
);

const result = await findByEitherUsername.run(db, {
  username1: "john",
  username2: "jane",
});
// Type: { id: bigint; username: string }[]

Combined AND/OR Conditions

import { prepare, and, or, eq } from "durcno";

const complexQuery = prepare(
  {
    username: Users.username.arg(),
    type1: Users.type.arg(),
    type2: Users.type.arg(),
  },
  (args) => {
    return db
      .prepare()
      .from(Users)
      .select({ username: Users.username, type: Users.type })
      .where(
        and(
          eq(Users.username, args.username),
          or(eq(Users.type, args.type1), eq(Users.type, args.type2)),
        ),
      );
  },
);

const result = await complexQuery.run(db, {
  username: "john",
  type1: "admin",
  type2: "user",
});
// Type: { username: string; type: "admin" | "user" }[]

Working with Different Column Types

Numeric Arguments

import { prepare, eq } from "durcno";
import { Posts } from "./db/schema.ts";

const findPostsByUser = prepare({ userId: Posts.userId.arg() }, (args) => {
  return db.prepare().from(Posts).select().where(eq(Posts.userId, args.userId));
});

const posts = await findPostsByUser.run(db, { userId: 1n });

Enum Arguments

const findByUserType = prepare({ userType: Users.type.arg() }, (args) => {
  return db
    .prepare()
    .from(Users)
    .select({ id: Users.id, type: Users.type })
    .where(eq(Users.type, args.userType));
});

// TypeScript ensures only valid enum values can be passed
const admins = await findByUserType.run(db, { userType: "admin" });
// Type: { id: bigint; type: "admin" | "user" }[]

Date/Timestamp Arguments

const findUsersByDate = prepare(
  { createdAt: Users.createdAt.arg() },
  (args) => {
    return db
      .prepare()
      .from(Users)
      .select({ id: Users.id, createdAt: Users.createdAt })
      .where(eq(Users.createdAt, args.createdAt));
  },
);

const result = await findUsersByDate.run(db, { createdAt: new Date() });
// Type: { id: bigint; createdAt: Date }[]

Type Safety

Prepared queries provide full type safety:

• Parameter types: Arguments are typed based on the column type
• Return types: Results are inferred from the selected columns
• Enum validation: Only valid enum values can be passed as arguments

const findUser = prepare({ id: Users.id.arg() }, (args) => {
  return db
    .prepare()
    .from(Users)
    .select({ username: Users.username })
    .where(eq(Users.id, args.id));
});

// ✅ Correct: id is a bigint
await findUser.run(db, { id: 1n });

// ❌ TypeScript error: id should be bigint, not string
await findUser.run(db, { id: "1" });

Parameterized LIMIT and OFFSET

You can parameterize limit and offset values using Arg.number() and Arg.bigint(). This is useful when building paginated prepared queries where the page size or offset varies at runtime.

import { prepare, Arg } from "durcno";
import { db } from "./db/index.ts";
import { Users } from "./db/schema.ts";

const paginatedUsers = prepare(
  {
    lim: Arg.number(),
    off: Arg.number(),
  },
  (args) => {
    return db.prepare().from(Users).select().limit(args.lim).offset(args.off);
  },
);

// Execute with different page sizes and offsets
const page1 = await paginatedUsers.run(db, { lim: 10, off: 0 });
const page2 = await paginatedUsers.run(db, { lim: 10, off: 10 });

Use Arg.bigint() when your limit or offset values may exceed the safe integer range:

const paginatedUsers = prepare(
  {
    lim: Arg.bigint(),
    off: Arg.bigint(),
  },
  (args) => {
    return db.prepare().from(Users).select().limit(args.lim).offset(args.off);
  },
);

const page = await paginatedUsers.run(db, { lim: 10n, off: 20n });

Prepared Arguments in sql Tagged Templates

You can interpolate prepared arguments (Arg) directly into sql tagged template expressions when defining a prepared query builder statement:

import { prepare, Arg, sql } from "durcno";

const findUsersByAge = prepare({ age: Arg.number() }, (args) => {
  return db
    .prepare()
    .from(Users)
    .select()
    .where(sql`age = ${args.age}`);
});

const users = await findUsersByAge.run(db, { age: 30 });

caution

Evaluating .toSQL() directly on a Sql object containing an Arg parameter outside of a query context will throw an error (Cannot evaluate Sql containing prepared argument (Arg) without a query context). Prepared arguments are bound dynamically when the query is constructed by db.prepare().

Benefits of Prepared Queries

1. Performance: SQL is parsed and planned once, then reused
2. Type Safety: Full TypeScript inference for parameters and results
3. Reusability: Define once, execute many times with different values across SELECT, INSERT, UPDATE, DELETE, and relational queries

API Reference

prepare(args, statement)

Creates a prepared statement.

Parameter	Type	Description
args	Record<string, Arg>	Object mapping argument names to column arguments
statement	(args) => QueryBuilder	Function that builds the query using the provided arguments

Column.arg()

Creates an argument placeholder for a column, used in prepared queries.

const arg = Users.username.arg();

Arg.number()

Creates an argument placeholder that accepts a JS number. Use with .limit() and .offset() in prepared queries.

const arg = Arg.number();

Arg.bigint()

Creates an argument placeholder that accepts a JS bigint. Use with .limit() and .offset() in prepared queries when values may exceed the safe integer range.

const arg = Arg.bigint();

PrepareStatement.run(db, values)

Executes the prepared statement with the provided values.

Parameter	Type	Description
db	DB | Transaction	Database instance or transaction instance
values	Record<string, T>	Object with values matching the argument types

Returns a Promise that resolves to the query results.