Inserts

Quarry supports two insert shapes:

• value inserts with values([...])
• server-side INSERT INTO ... SELECT with fromSelect(...)

Insert row values

When you call values([...]), each row is type-checked against the target table's row type from the schema you passed to createClickHouseDB.

await db
  .insertInto("users")
  .values([
    {
      id: 1,
      email: "alice@example.com",
      status: "active",
    },
    {
      id: 2,
      email: "bob@example.com",
      status: "active",
    },
  ])
  .execute();

This compiles to ClickHouse's INSERT ... FORMAT JSONEachRow shape, so you do not have to think about column ordering — the keys in your object literal map to columns by name.

Insert into explicit target columns

Use columns(...) when you want to insert into only part of the target table.

await db
  .insertInto("users")
  .columns("id", "email")
  .values([
    { id: 1, email: "alice@example.com" },
    { id: 2, email: "bob@example.com" },
  ])
  .execute();

For value inserts, columns(...) also narrows the accepted row shape at the type level.

Insert from a select query

Use fromSelect(...) when the rows should be produced entirely inside ClickHouse.

await db
  .insertInto("daily_aggregates")
  .columns("user_id", "event_date", "event_count")
  .fromSelect(
    db
      .selectFrom("event_logs as e")
      .selectExpr((eb) => [
        "e.user_id",
        eb.fn.toDate("e.created_at").as("event_date"),
        eb.fn.count().as("event_count"),
      ])
      .where("e.created_at", ">=", param("2025-01-01", "Date"))
      .groupBy("e.user_id", (eb) => eb.fn.toDate("e.created_at")),
  )
  .execute();

This compiles to INSERT INTO ... SELECT ... and runs entirely server-side. The selected rows never pass back through your application.

Inspecting before executing

Just like select queries, insert queries can be compiled with toSQL() without running:

const compiled = db
  .insertInto("users")
  .values([{ id: 1, email: "alice@example.com", status: "active" }])
  .toSQL();

compiled.query;
compiled.params;
compiled.values;

For value inserts, compiled.values contains the row payload and compiled.params is empty. For fromSelect(...), compiled.values is undefined and compiled.params contains any parameters from the select subquery.