Insert

Use db.insertInto() to insert rows into a table. Durcno provides full type safety, ensuring you provide all required columns and use correct types.

Methods

Builder methods (InsertBuilder)

Method	Description
.values()	Supply row(s) to insert

Query methods (InsertQuery)

Method	Description
.onConflict()	Start an ON CONFLICT clause for upsert-style handling
.returning(cols)	Specify columns to return after insert (use "*" for all columns)

Basic Usage

Insert a Single Row

import { db } from "./db/index.ts";
import { Users } from "./db/schema.ts";

await db.insertInto(Users).values({
  username: "john_doe",
  email: "john@example.com",
  type: "user",
});

Insert Multiple Rows

Pass an array to .values() to insert multiple rows:

await db.insertInto(Users).values([
  { username: "john_doe", email: "john@example.com", type: "user" },
  { username: "jane_doe", email: "jane@example.com", type: "admin" },
]);

Upserts with ON CONFLICT

Use .onConflict() to build PostgreSQL ON CONFLICT clauses for insert operations. You can target one or more columns, or omit the arguments to apply the clause to any conflict.

Skip conflicting inserts with .doNothing()

await db
  .insertInto(Users)
  .values({
    username: "john_doe",
    email: "john@example.com",
    type: "user",
  })
  .onConflict(Users.username)
  .doNothing();

Update conflicting rows with .doUpdateSet()

Use .doUpdateSet() to update existing rows when a conflict occurs. The callback receives an excluded object that references the values from the incoming insert row.

await db
  .insertInto(Users)
  .values({
    username: "john_doe",
    email: "updated@example.com",
    type: "user",
  })
  .onConflict(Users.username)
  .doUpdateSet(({ excluded }) => ({
    email: excluded.email,
  }));

You can also use scalar SQL functions, existing table columns, raw sql expressions, and prepared arguments within .doUpdateSet():

import { add, concat } from "durcno";

await db
  .insertInto(Users)
  .values({
    username: "john_doe",
    score: 10,
    bio: "initial_bio",
    type: "user",
  })
  .onConflict(Users.username)
  .doUpdateSet(({ excluded }) => ({
    score: add(Users.score, excluded.score),
    bio: concat(Users.bio, " - updated"),
  }));

note

Undefined Values — Properties assigned undefined inside .doUpdateSet() are automatically filtered out and omitted from the SQL DO UPDATE SET clause.

You can also provide an optional predicate to make the update conditional:

import { gt } from "durcno";

await db
  .insertInto(Users)
  .values({
    username: "john_doe",
    score: 100,
  })
  .onConflict(Users.username)
  .doUpdateSet(
    ({ excluded }) => ({ score: excluded.score }),
    ({ excluded }) => gt(excluded.score, Users.score),
  );

Required vs Optional Columns

Durcno automatically determines which columns are required based on your schema:

Column Type	Insert Behavior
notNull without default	Required - must be provided
notNull with default	Optional - uses default if not provided
Nullable (no notNull)	Optional - defaults to null
Primary key (pk())	Non-insertable - auto-generated identity (never)
Generated Always (generatedAlways())	Non-insertable - excluded from insert keys (never)
Generated By Default (generatedByDefault())	Optional - uses database identity unless provided

Example Schema

const Users = table("public", "users", {
  id: pk(), // Auto-generated identity (omitted from insert values)
  username: varchar({ length: 50, notNull }), // Required
  email: varchar({ length: 255 }), // Optional (nullable)
  type: UserRole.enumed({ notNull }), // Required
  createdAt: timestamp({ notNull }).default(now()), // Optional (has default)
});

Corresponding Insert

// Only username and type are required
await db.insertInto(Users).values({
  username: "john_doe", // Required
  type: "user", // Required
  // id: auto-generated identity (omitted from insert values)
  // email: defaults to null
  // createdAt: defaults to now()
});

// You can optionally provide other columns
await db.insertInto(Users).values({
  username: "jane_doe",
  type: "admin",
  email: "jane@example.com", // Optional, but provided
});

Returning Inserted Data

Use .returning() to get data back from inserted rows:

// Return specific columns
const inserted = await db
  .insertInto(Users)
  .values({ username: "john_doe", type: "user" })
  .returning({ id: true, username: true });
// Type: { id: bigint; username: string }[]

// Return all columns except some
const inserted = await db
  .insertInto(Users)
  .values({ username: "john_doe", type: "user" })
  .returning({ email: false });
// Type: { id: bigint; username: string; type: "admin" | "user"; createdAt: Date }[]

// Return all columns using the wildcard
const inserted = await db
  .insertInto(Users)
  .values({ username: "john_doe", type: "user" })
  .returning("*");
// Type: { id: bigint; username: string; email: string | null; type: "admin" | "user"; createdAt: Date }[]

Without Returning

Without .returning(), the insert returns null:

const result = await db
  .insertInto(Users)
  .values({ username: "john_doe", type: "user" });
// Type: null

Auto-generated Values with .$insertFn()

Columns with .$insertFn() automatically generate values during insert when not explicitly provided:

// Schema with `.$insertFn()`
const Posts = table("public", "posts", {
  id: pk(),
  title: varchar({ length: 200, notNull }),
  createdAt: timestamp({ notNull }).$insertFn(() => new Date()),
});

// createdAt is optional - insertFn generates it
await db.insertInto(Posts).values({
  title: "My Post",
  // createdAt will be auto-generated
});

// You can still override with an explicit value
await db.insertInto(Posts).values({
  title: "My Post",
  createdAt: new Date("2024-01-01"), // Override insertFn
});

tip

See Dynamic Value Generation for more details on .$insertFn() and .$updateFn().

Using Scalar SQL Functions

Durcno supports scalar SQL functions directly in .values(), allowing you to transform data or compute values at insert time:

import { add, coalesce, concat, lower } from "durcno";

await db.insertInto(Users).values({
  username: lower("JOHN_DOE"),
  bio: concat("Welcome, ", "new member!"),
  email: coalesce(null, "default@example.com"),
  score: add(10, 5),
  type: "user",
});

warning

No Column References — In PostgreSQL, INSERT ... VALUES expressions cannot reference table columns (since there is no existing row in scope). Durcno statically rejects column references (e.g., lower(Users.username)) in .values() at compile time.

warning

No Aggregate Functions — Aggregate functions (such as count(), sum(), or avg()) are not allowed in INSERT VALUES and will produce a TypeScript compilation error.

Using SQL Expressions

You can use sql() for raw SQL expressions in insert values:

import { sql } from "durcno";

await db.insertInto(Users).values({
  username: "john_doe",
  type: "user",
  createdAt: sql`NOW() - INTERVAL '1 day'`,
});

Type Safety

Durcno provides compile-time validation:

// ✅ Valid - all required fields provided
await db.insertInto(Users).values({
  username: "john",
  type: "user",
});

// ❌ TypeScript Error - missing required field "type"
await db.insertInto(Users).values({
  username: "john",
});

// ❌ TypeScript Error - invalid type value
await db.insertInto(Users).values({
  username: "john",
  type: "superadmin", // Not in enum
});