Insert

Use db.insert() 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
.returning()	Specify columns to return after insert

Basic Usage

Insert a Single Row

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

await db.insert(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.insert(Users).values([
  { username: "john_doe", email: "john@example.com", type: "user" },
  { username: "jane_doe", email: "jane@example.com", type: "admin" },
]);

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())	Optional - auto-generated

Example Schema

const Users = table("public", "users", {
  id: pk(), // Optional (auto-generated)
  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.insert(Users).values({
  username: "john_doe", // Required
  type: "user", // Required
  // id: auto-generated
  // email: defaults to null
  // createdAt: defaults to now()
});

// You can optionally provide other columns
await db.insert(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
  .insert(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
  .insert(Users)
  .values({ username: "john_doe", type: "user" })
  .returning({ email: false });
// Type: { id: bigint; username: string; type: "admin" | "user"; createdAt: Date }[]

Returning All Columns

Pass "*" to .returning() to get back every column, including auto-generated values like IDs and timestamps:

const [user] = await db
  .insert(Users)
  .values({ username: "john_doe", type: "user" })
  .returning("*");
// Type: { id: bigint; username: string; email: string | null; type: "admin" | "user"; createdAt: Date }[]

console.log(user.id); // Auto-generated ID
console.log(user.createdAt); // Auto-generated timestamp

// Use the ID for a related insert
await db.insert(Posts).values({
  userId: user.id,
  title: "First Post",
});

This also works with multiple rows:

const inserted = await db
  .insert(Users)
  .values([
    { username: "john", type: "user" },
    { username: "jane", type: "admin" },
  ])
  .returning("*");
// Returns all columns for every inserted row

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.insert(Posts).values({
  title: "My Post",
  // createdAt will be auto-generated
});

// You can still override with an explicit value
await db.insert(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 SQL Expressions

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

import { sql } from "durcno";

await db.insert(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.insert(Users).values({
  username: "john",
  type: "user",
});

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

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