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 { eq, prepare } 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 { and, eq, prepare } 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",
});

Reusing an Argument

The same argument can be referenced in several places in one statement. It is bound once and every position it appears in refers to it:

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

// "username" is one argument, used twice in the IN list
const findByName = prepare({ username: Users.username.arg() }, (args) => {
  return db
    .prepare()
    .from(Users)
    .select("*")
    .where(() => isIn(Users.username, [args.username, args.username]));
});

const result = await findByName.run(db, { username: "john" });
// SELECT * FROM "users" WHERE "users"."username" IN ($1, $1)

The same applies to filters, values(), set(), and relational queries — a multi-row insert can share one argument across all rows:

const createUsers = prepare({ score: Users.score.arg() }, (args) => {
  return db
    .prepare()
    .insertInto(Users)
    .values([
      { username: "alice", type: "user", score: args.score },
      { username: "bob", type: "user", score: args.score },
    ])
    .returning("*");
});

const users = await createUsers.run(db, { score: 10 });

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()
    .insertInto(Users)
    .values({
      username: args.username,
      email: "prepare@example.com",
      type: "user",
    })
    .returning("*");
});

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

Combining Arguments with Scalar Functions

Prepared arguments can be wrapped in scalar SQL functions directly inside values():

import { lower, prepare } from "durcno";

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

const [user] = await createUser.run(db, { username: "JOHN_DOE" });
// user.username is stored as "john_doe"

Using Arguments in UPDATE Statements

Prepared arguments can be used in update().set(...) to update column values dynamically, and can be combined with scalar SQL functions:

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

// 1. Direct argument assignment in set()
const updateUserEmail = prepare(
  { id: Users.id.arg(), email: Users.email.arg() },
  (args) =>
    db
      .prepare()
      .update(Users)
      .set({ email: args.email })
      .where(eq(Users.id, args.id))
      .returning("*"),
);

// 2. Combining arguments with table columns and scalar functions in set()
const incrementScore = prepare(
  { id: Users.id.arg(), inc: Users.score.arg() },
  (args) =>
    db
      .prepare()
      .update(Users)
      .set({ score: add(Users.score, args.inc) })
      .where(eq(Users.id, args.id))
      .returning("*"),
);

const [updated] = await incrementScore.run(db, { id: 1n, inc: 25 });

The same pattern also applies to 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 { eq, or, prepare } 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 { and, eq, or, prepare } 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 { eq, prepare } 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 { Arg, prepare } 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 { Arg, prepare, 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

Connector Support

While Durcno compiles and caches SQL query construction at the JavaScript level across all connectors, execution as an actual PostgreSQL prepared query at the driver/protocol level depends on the underlying connector:

Connector	Import Path	Prepared Statement Support	Details
postgres	durcno/connectors/postgres	Supported	Passes { prepare: true } to postgres.js to execute as a server-side prepared statement.
bun	durcno/connectors/bun	Supported (automatic)	Automatically activated and cached in Bun's SQL client according to its documentation.
pg	durcno/connectors/pg	Not supported	Actual PostgreSQL prepared queries are not currently supported; statements run as standard parameterized queries.
pglite	durcno/connectors/pglite	Not documented	PGlite does not document prepared statement support; statements run as in-process parameterized queries.

note

Even with connectors where server-side prepared statements are not supported (such as pg), Durcno's prepare() still provides benefits by compiling query builders, SQL strings, and parameter mapping once upfront on the client, avoiding query construction overhead on repeated .run() calls.

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.