Named SQL Parameters

When to use

Use named parameters when writing SQL queries that accept input values. The :parameter syntax (e.g., :id, :name, :email) replaces positional ? placeholders, making queries more readable, self-documenting, and order-independent while reducing errors from mismatched parameter positions.

The pattern

Write SQL with colon-prefixed parameter names. Pass values as a plain object whose keys match the parameter names. The database driver or query runner substitutes the values.

In the SQL file

Each parameter appears as a colon-prefixed name in the SQL statement. Reading the query tells you exactly what inputs it expects:

-- Create new account
insert into
  `Account`
set
  `name` = :name,
  `type` = :type,
  `institution_id` = :institution_id,
  `website_link` = :website_link,
  `include` = :include;

Each parameter name (:name, :type, etc.) appears in the SQL where the value will be substituted. Reading the query itself tells you exactly what inputs it expects.

In JavaScript

Pass a plain object whose keys match the parameter names. The driver substitutes values by name, so order does not matter:

await query(AccountQuery.create, {
  name: "Checking",
  type: "ASSET",
  institution_id: "chase-001",
  website_link: "https://chase.com",
  include: true,
});

The object keys correspond one-to-one with the :param placeholders. Order does not matter — the driver matches by name, not position.

Comparison with positional parameters

Positional (?) parameters require careful counting and ordering:

-- Positional: easy to get wrong
insert into `Account`
set `name` = ?, `type` = ?, `institution_id` = ?;

// Must match order exactly — silent bugs if wrong
await query(sql, ["Checking", "ASSET", "chase-001"]);

Named parameters eliminate this class of bug entirely:

-- Named: self-documenting
insert into `Account`
set `name` = :name, `type` = :type,
    `institution_id` = :institution_id;

// Order doesn't matter, names must match
await query(sql, {
  type: "ASSET",
  institution_id: "chase-001",
  name: "Checking",
});

Querying with a single parameter

The simplest case passes a single parameter object with one key:

-- Return account by ID
select * from `Account` where `id` = :id

const [account] = await query(AccountQuery.get, { id: 42 });

Querying with multiple parameters

For updates and inserts with many fields, the parameter object has multiple keys matching the SQL placeholders:

-- Update account by id
update `Account`
set
  `name` = :name,
  `type` = :type,
  `institution_id` = :institution_id,
  `website_link` = :website_link
where
  `id` = :id

await query(AccountQuery.update, {
  id: 7,
  name: "Savings",
  type: "ASSET",
  institution_id: "ally-001",
  website_link: "https://ally.com",
});

Trade-offs

• Self-documenting queries. Parameter names visible in SQL make the query's data requirements obvious without reading the calling code.
• Order-independent. Adding, removing, or reordering parameters in the SQL does not require updating the parameter array order in JavaScript.
• Driver support varies. Not all database drivers support :param syntax natively. MySQL2 supports it; others may require a preprocessing step that replaces named parameters with positional ones before execution.
• No enforcement of required parameters. If you pass an object missing a required key, the behavior depends on the driver — some insert NULL, some throw. Parameter validation should happen in the application layer before the query executes.
• Slightly more verbose. Named parameters require typing both the parameter name in SQL and the matching key in the JavaScript object. For trivial single-parameter queries, this is minor overhead.