Insert multiple rows from Array.map

Use when

Use a mapped fragment array when a non-empty collection should become one multi-row statement and every item has the same static SQL structure. The array can contain five rows or a runtime-sized batch; sql.join() is unnecessary for the default comma separator.

Use an ordered runtime batch or a native bulk protocol instead when per-statement isolation, very large inputs, COPY, or LOAD DATA semantics are the actual requirement.

Schema assumption

This recipe uses the maintained PostgreSQL example table:

CREATE TYPE account_status AS ENUM ('active', 'suspended');

CREATE TABLE users (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  status account_status NOT NULL DEFAULT 'active'
);

Code

The mapped callback returns a trusted fragment with one fixed row shape. Interpolated fields inside that fragment remain ordinary parameter values.

import { sql } from "@typed-sql/postgres";

export interface NewUser {
  readonly id: bigint;
  readonly email: string;
  readonly status: "active" | "suspended";
}

export function insertUsers(items: readonly NewUser[]) {
  if (items.length === 0) throw new RangeError("insertUsers requires at least one item");

  return sql`
    INSERT INTO users (id, email, status)
    VALUES ${items.map((item) => sql.fragment`(${item.id}, ${item.email}, ${item.status})`)}
    RETURNING id, email, status
  `;
}

The direct .map() is part of the analyzable query expression. typed-sql does not execute arbitrary JavaScript during compilation; it proves one synchronous callback skeleton and expands a representative non-empty statement for the PostgreSQL grammar.

Compiler sees

type InsertUsersQuery = Query<
  { "id": bigint; "email": string; "status": "active" | "suspended"; },
  readonly (string | bigint)[]
>;

The row type comes from PostgreSQL RETURNING. Because runtime array length is not statically fixed, the parameter contract is an honest readonly array rather than a fabricated 15-position tuple. A literal tuple of fragments can retain its exact concatenated tuple.

Driver receives

For five inputs, the PostgreSQL renderer produces:

INSERT INTO users (id, email, status)
VALUES ($1, $2, $3),
       ($4, $5, $6),
       ($7, $8, $9),
       ($10, $11, $12),
       ($13, $14, $15)
RETURNING id, email, status

Values are flattened in row-major order:

[
  10n, "user-0@example.com", "active",
  11n, "user-1@example.com", "suspended",
  12n, "user-2@example.com", "active",
  13n, "user-3@example.com", "suspended",
  14n, "user-4@example.com", "active",
]

The renderer inserts , between fragments. No comma is present before the first row or after the last row.

Dialect notes

• PostgreSQL uses $n placeholders and can infer the RETURNING row shown above.
• MySQL uses ? placeholders. Its supported insert-return surface differs; inspect the MySQL grammar rather than copying PostgreSQL RETURNING syntax.
• SQLite uses ? placeholders and supports its grammar-owned RETURNING surface. Its dynamic type policy can infer different field types for an otherwise similar table.
• Every database can impose a lower statement or parameter limit than typed-sql's own bounded list, parameter, and rendered-byte limits.

Runnable source

The exact source is maintained in examples/postgres/src/multi-row-documentation.ts. Its service-free example test renders five rows and asserts all 15 ordered values:

pnpm --filter @typed-sql/example-postgres test

The documentation contract also compiles the snippet against the maintained PostgreSQL snapshot and checks the displayed row and parameter types.

Related reference

• Structural composition and empty-input behavior
• Choose a multi-row statement, ordered batch, or native bulk protocol