Skip to main content
Version: Next

Columns

Columns are the building blocks of your database schema. Durcno provides a comprehensive set of type-safe column types that map directly to PostgreSQL column types with full TypeScript inference.

Column Options and Chainable Modifiers​

Durcno allows two ways to configure columns:

  • Constructor options (passed to the column factory): affect SQL column definitions and constraints. Common constructor options include:
    • notNull: Makes the column NOT NULL (required)
    • unique: Adds a UNIQUE constraint
    • primaryKey: Marks the column as a primary key
    • generated: Generates a column with a server-side default
    • as: Specifies the value to be generated
    • dimension: Specifies array dimensions
  • Chainable modifiers (methods you call on the column instance): .default() for SQL defaults, .references() for foreign keys, .$insertFn() / .$updateFn() for runtime value generation, and .$type<T>() to override the inferred TypeScript type.
info

Columns are nullable by default unless notNull is specified.

Column Types​

String Columns​

varchar​

  • SQL type: varchar(n)
  • JS type: string
  • Column-specific options: length (default: 255)
  • Notes: length range 1–10,485,760; use for bounded variable-length strings.

text​

  • SQL type: text
  • JS type: string
  • Notes: unlimited length. Use for large text content.

char​

  • SQL type: char(n)
  • JS type: string
  • Column-specific options: length (default: 1)
  • Notes: fixed-length; values are padded with spaces. Use for codes (ISO country codes, currency codes).
tip

Use char for fixed-length codes like ISO country codes (2 chars) or currency codes (3 chars). For variable-length strings, prefer varchar.

Numeric Columns​

integer​

  • SQL type: integer (32-bit)
  • JS type: number
  • Notes: range: -2,147,483,648 to 2,147,483,647.

bigint​

  • SQL type: bigint (64-bit)
  • JS type: bigint — represent with BigInt where appropriate
  • Notes: preferred for primary keys / large numbers.

smallint​

  • SQL type: smallint (16-bit)
  • JS type: number
  • Notes: range: -32,768 to 32,767. Use when values fit in a small range.

numeric​

  • SQL type: numeric / numeric(precision) / numeric(precision, scale)
  • JS type: string — stored as string to preserve arbitrary precision
  • Column-specific options: precision (1–1000), scale (number of digits after decimal)
  • Notes: Use for exact decimal values where precision matters (e.g., financial data, scientific calculations). Values are returned as strings to avoid JavaScript floating-point precision loss.
import { table, numeric, pk, notNull } from "durcno";

export const Products = table("public", "products", {
  id: pk(),
  // Arbitrary precision (no limit)
  price: numeric({ notNull }),
  // Precision only (max 10 total digits)
  quantity: numeric({ precision: 10, notNull }),
  // Precision and scale (10 total digits, 2 after decimal point)
  amount: numeric({ precision: 10, scale: 2, notNull }),
  // With default value
  discount: numeric({ notNull }).default("0"),
});

// Values are strings to preserve precision
db.insert(Products).values({
  price: "123.456789012345678901234567890",
  quantity: "1234567890",
  amount: "12345678.90",
  // discount uses default "0"
});
tip

Use numeric when you need exact decimal precision, such as for monetary values or scientific calculations. The string representation ensures no precision is lost due to JavaScript's floating-point limitations.

Serial Columns (Auto-increment)​

Serial columns are PostgreSQL's legacy auto-incrementing integer types.

  • SQL type: serial / smallserial / bigserial (wraps integer/smallint/bigint with a sequence)
  • JS type: number or bigint (per SQL width) — treated as NOT NULL for SELECT, optional on INSERT
  • Supported Options: primaryKey, unique.

Note: Do not set .default(), .$insertFn(), or .$updateFn() on serial columns — sequences managed by PostgreSQL provide the auto-generated values.

serial​

import { primaryKey, table, serial, varchar, notNull } from "durcno";

export const Logs = table("public", "logs", {
  id: serial({ primaryKey }),
  message: varchar({ length: 500, notNull }),
});

// Insert without providing id - it's auto-generated
db.insert(Logs).values({
  message: "Log entry",
  // id is optional - auto-generated by PostgreSQL
});

smallserial​

import { primaryKey, table, smallserial, varchar, notNull } from "durcno";

export const Categories = table("public", "categories", {
  id: smallserial({ primaryKey }),
  name: varchar({ length: 100, notNull }),
});
tip

Use smallserial for tables with a known small number of rows (under 32,767). It uses less storage than serial (2 bytes vs 4 bytes).

bigserial​

import { primaryKey, table, bigserial, timestamp, notNull } from "durcno";

export const Events = table("public", "events", {
  id: bigserial({ primaryKey }),
  occurredAt: timestamp({ notNull }),
});

UUID Column​

  • SQL type: uuid
  • JS type: string
  • Notes: Commonly used for immutable identifiers. UUID values can be generated either by the database (server-side DEFAULT clauses) or in your application before insertion.
import { primaryKey, table, uuid, varchar, notNull } from "durcno";

export const Users = table("public", "users", {
  id: uuid({ primaryKey, notNull }),
  apiKey: uuid({ unique }),
  name: varchar({ length: 255, notNull }),
});

version option​

You can specify a version to enforce UUID version validation in the generated Zod schema. Supported versions: "v1" through "v8". Defaults to "v7" when not specified.

import { table, uuid, notNull, pk } from "durcno";

export const Tokens = table("public", "tokens", {
  id: pk(),
  // Zod schema will only accept v4 UUIDs
  tokenId: uuid({ notNull, version: "v4" }),
  // Zod schema will only accept v7 UUIDs (same as default)
  sortableId: uuid({ notNull, version: "v7" }),
  // Defaults to v7 validation
  modernId: uuid({ notNull }),
});
note

The version option only affects Zod schema validation (via createInsertSchema / createUpdateSchema). PostgreSQL's uuid type stores any valid UUID regardless of version.

  • Insert behavior: UUID columns with a server .default() or .$insertFn() are optional on insert; otherwise provide a value or mark notNull accordingly.
  • Best practices: Prefer server-side generation for primary keys (so the DB controls uniqueness/creation), or use .$insertFn() for application-generated UUIDs (e.g., crypto.randomUUID()).

:::info Extension note To use PostgreSQL server-side generators, enable the appropriate extension:

CREATE EXTENSION IF NOT EXISTS pgcrypto; -- for gen_random_uuid()
-- or
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- for uuid_generate_v4()

:::

Boolean Column​

boolean​

  • SQL type: boolean
  • JS type: boolean
  • Notes: Use for true/false flags.

Date & Time Columns​

timestamp​

  • SQL type: timestamp [with time zone] / timestamp [without time zone]
  • JS type: Date
  • Column-specific options: withTimezone (default true), precision (0–6)
  • Notes: withTimezone=true is recommended for correct UTC handling. Use server-side DEFAULT clauses in migrations when you want database-generated timestamps.
import { table, timestamp, notNull } from "durcno";

export const Posts = table("public", "posts", {
  createdAt: timestamp({ notNull }).default(now()),
  updatedAt: timestamp({ notNull })
    .default(now())
    .$updateFn(() => new Date()),
  publishedAt: timestamp({}),
});

time​

  • SQL type: time [with time zone] / time [without time zone]
  • JS type: string
  • Column-specific options: withTimezone (default false), precision (0–6)
  • Notes: stores time-of-day without date. Use withTimezone: true when you need offsets (e.g., 12:30:45+02:00).
import { table, time, notNull } from "durcno";

export const Schedules = table("public", "schedules", {
  startsAt: time({ notNull }),
  endsAt: time({ precision: 3 }),
  startsAtWithTz: time({ withTimezone: true }),
});

date​

  • SQL type: date
  • JS type: Date (no time)
  • Notes: Use for calendar dates without time information.

Enum Column​

enumtype & .enumed()​

  • SQL type: CREATE TYPE ... AS ENUM / enum
  • JS type: string (one of allowed values)
  • Notes: Use when allowed values are stable. For frequently-changing allowed values consider check constraints.
import { table, enumtype, pk, notNull } from "durcno";

export const UserRole = enumtype("public", "user_role", [
  "admin",
  "moderator",
  "user",
]);
export const Status = enumtype("public", "status", [
  "active",
  "inactive",
  "suspended",
]);

export const Users = table("public", "users", {
  id: pk(),
  role: UserRole.enumed({ notNull }),
  status: Status.enumed({ notNull }),
});

Binary Columns​

bytea​

  • SQL type: bytea
  • JS type: Buffer
  • Notes: Use for binary blobs, encrypted tokens, or file content. For large files prefer external storage.

JSON & JSONB Columns​

json / jsonb​

  • SQL type: json or jsonb
  • JS type: unknown (by default) or T (if typed)
  • Notes: jsonb is generally recommended over json for its indexing capabilities and efficiency.

Use the .$type<T>() chainable modifier to specify the TypeScript type of the JSON data.

import { table, jsonb, pk } from "durcno";

export const Users = table("public", "users", {
  id: pk(),
  rawData: jsonb({}),
  preferences: jsonb({}).$type<{
    theme: "dark" | "light";
    notifications: boolean;
  }>(),
});

Network Address Columns​

PostgreSQL provides specialized column types for storing network addresses, making it easy to work with IP addresses, network ranges, and MAC addresses with built-in validation.

inet​

  • SQL type: inet
  • JS type: string
  • Notes: Stores IPv4 or IPv6 host addresses with optional subnet mask. Use for individual host addresses.
import { table, inet, pk, notNull, unique } from "durcno";

export const Servers = table("public", "servers", {
  id: pk(),
  // Required IP address
  ipAddress: inet({ notNull }),
  // Optional secondary IP
  secondaryIp: inet({}),
  // Unique management IP
  managementIp: inet({ unique }),
});

// Insert with IPv4
await db.insert(Servers).values({
  ipAddress: "192.168.1.100",
});

// Insert with IPv6
await db.insert(Servers).values({
  ipAddress: "2001:db8::1",
});

// Insert with CIDR notation
await db.insert(Servers).values({
  ipAddress: "192.168.1.100/24",
});

cidr​

  • SQL type: cidr
  • JS type: string
  • Notes: Stores IPv4 or IPv6 network addresses. CIDR requires network prefix notation (e.g., 192.168.0.0/24). Use for defining network ranges and subnets.
import { table, cidr, pk, notNull } from "durcno";

export const Networks = table("public", "networks", {
  id: pk(),
  // Required network range
  networkRange: cidr({ notNull }),
  // Optional allowed network
  allowedNetwork: cidr({}),
});

// Insert network ranges
await db.insert(Networks).values({
  networkRange: "10.0.0.0/8", // Class A private network
});

await db.insert(Networks).values({
  networkRange: "192.168.0.0/16", // Class C private network block
});

await db.insert(Networks).values({
  networkRange: "2001:db8::/32", // IPv6 network
});

:::tip INET vs CIDR Use inet for individual host addresses (with optional netmask for routing info). Use cidr for network addresses where the host bits must be zero (e.g., 192.168.1.0/24 not 192.168.1.5/24). :::

macaddr​

  • SQL type: macaddr
  • JS type: string
  • Notes: Stores MAC (Media Access Control) addresses. Supports common formats: colon-separated (00:11:22:33:44:55), hyphen-separated (00-11-22-33-44-55), or plain hex.
import { table, macaddr, pk, notNull, unique, varchar } from "durcno";

export const NetworkDevices = table("public", "network_devices", {
  id: pk(),
  name: varchar({ length: 100, notNull }),
  // Required MAC address
  macAddress: macaddr({ notNull, unique }),
  // Optional backup MAC
  backupMac: macaddr({}),
});

// Insert with colon-separated format
await db.insert(NetworkDevices).values({
  name: "Router 1",
  macAddress: "00:11:22:33:44:55",
});

// Insert with hyphen-separated format
await db.insert(NetworkDevices).values({
  name: "Switch 1",
  macAddress: "aa-bb-cc-dd-ee-ff",
});

:::info Network Address Validation All network address types perform format validation. Invalid addresses will cause runtime errors when inserting. The validation happens both in TypeScript (via Zod schemas) and in PostgreSQL. :::

PostGIS Columns​

Durcno provides type-safe column types for PostGIS geography data, including point, multipoint, linestring, multilinestring, polygon, and multipolygon. Values are represented as GeoJSON coordinate arrays with full TypeScript inference.

See the PostGIS extension page for setup instructions, all available types, and usage examples.

Array Columns​

Durcno supports PostgreSQL arrays for any column type using the dimension option. This allows you to store lists, matrices, or multi-dimensional collections of data with full type safety.

Usage​

To create an array column, pass the dimension property to the column configuration. The dimension property accepts a tuple defining the shape of the array.

  • null: Variable-length dimension (e.g., [])
  • number: Fixed-length dimension (e.g., [3])
import { table, integer, varchar, pk } from "durcno";

export const SensorReadings = table("public", "sensor_readings", {
  id: pk(),

  // 1D Array: integer[]
  temperatures: integer({ dimension: [null] }),

  // 1D Array with fixed length strings: varchar(10)[]
  codes: varchar({ length: 10, dimension: [null] }),

  // 2D Array (Matrix): integer[][]
  grid: integer({ dimension: [null, null] }),

  // Fixed-length Array (Tuple): integer[3]
  // TypeScript defines this as [number, number, number]
  coordinates: integer({ dimension: [3] }),

  // Mix fixed and variable (Array of Tuples): integer[3][]
  // The dimensions are defined from innermost to outermost
  polygonRings: integer({ dimension: [3, null] }),
});

Type Inference​

Durcno automatically infers the correct TypeScript type based on the dimensions:

  • dimension: [null] → T[]
  • dimension: [null, null] → T[][]
  • dimension: [3] → [T, T, T] (Fixed-length Tuple)
const readings = await db.from(SensorReadings).select();

// readings[0].temperatures is number[] | null
// readings[0].coordinates is [number, number, number] | null

SQL Mapping​

Durcno appends the array dimensions to the base SQL type:

  • dimension: [null] maps to type[]
  • dimension: [3] maps to type[3]
  • dimension: [3, null] maps to type[3][]

Column Modifiers​

Constructor Options​

These are passed directly to the column factory function and affect SQL column definitions.

notNull​

Makes a column required (NOT NULL constraint).

import { table, varchar, notNull } from "durcno";

export const Users = table("public", "users", {
  email: varchar({ length: 255, notNull }), // Required
  bio: varchar({ length: 500 }), // Optional (nullable)
});

unique​

Adds a UNIQUE constraint to ensure values are unique across rows.

import { table, varchar, unique, notNull } from "durcno";

export const Users = table("public", "users", {
  username: varchar({ length: 50, unique, notNull }),
  email: varchar({ length: 255, unique, notNull }),
});

Chainable Modifiers​

Methods called on a column instance after construction. These can be chained together.

.default(value)​

Sets a SQL DEFAULT clause for the column. Accepts a literal value or an Sql expression. Columns with a default become optional on insert.

import { table, integer, boolean, timestamp, notNull, now } from "durcno";

export const Posts = table("public", "posts", {
  viewCount: integer().default(0),
  isPublished: boolean().default(false),
  createdAt: timestamp({ notNull }).default(now()),
});

.references(ref)​

Creates a foreign key reference to another table's column. Accepts either a lazy column getter () => Table.column or an object { column: () => Table.column, onDelete?: OnDeleteAction }. Defaults to CASCADE on delete.

import { table, pk, bigint, notNull } from "durcno";

export const Users = table("public", "users", { id: pk() });

export const Posts = table("public", "posts", {
  id: pk(),
  // Simple reference (defaults to CASCADE)
  authorId: bigint({ notNull }).references(() => Users.id),
  // With explicit onDelete action
  reviewerId: bigint({}).references({
    column: () => Users.id,
    onDelete: "SET NULL",
  }),
});

:::info Lazy References Use arrow functions () => Table.column to avoid circular dependency issues when tables reference each other. :::

.$insertFn(fn)​

Sets a JavaScript function called during INSERT when no explicit value is provided. The column becomes optional on insert. Runs in JS at runtime (not a SQL default).

createdAt: timestamp({ notNull }).$insertFn(() => new Date()),

.$updateFn(fn)​

Sets a JavaScript function called on every UPDATE operation, even if the column isn't in .set(). Runs in JS at runtime.

updatedAt: timestamp({ notNull }).$updateFn(() => new Date()),
tip

Use .$insertFn() for values set once at creation (e.g. createdAt). Use .$updateFn() for values refreshed on every modification (e.g. updatedAt). Unlike .default(), these hooks run in JavaScript and support logic that can't be expressed as SQL defaults.

.$type<T>()​

Overrides the TypeScript type inferred for the column's value. This is a compile-time only operation — it does not affect runtime behavior or SQL. Useful for json/jsonb columns where you want to narrow the type from unknown.

import { table, jsonb, pk } from "durcno";

export const Users = table("public", "users", {
  id: pk(),
  preferences: jsonb({}).$type<{
    theme: "dark" | "light";
    notifications: boolean;
  }>(),
});

Primary Keys​

pk()​

Creates an auto-incrementing bigint primary key column.

import { table, pk, varchar } from "durcno";

export const Users = table("public", "users", {
  id: pk(), // Auto-incrementing primary key
  username: varchar({ length: 50 }),
});

Custom Primary Keys​

You can also create custom primary keys using column modifiers:

import { table, varchar, integer, primaryKey, notNull } from "durcno";

export const Users = table("public", "users", {
  id: integer({ primaryKey, notNull }),
  // or
  email: varchar({ length: 255, primaryKey, notNull }),
});

Type Inference​

Durcno provides full TypeScript type inference for all column types:

import {
  table,
  pk,
  varchar,
  integer,
  boolean,
  timestamp,
  notNull,
  now,
} from "durcno";

export const Users = table("public", "users", {
  id: pk(), // bigint
  username: varchar({ length: 50, notNull }), // string
  email: varchar({ length: 255 }), // string | null
  age: integer({}), // number | null
  isActive: boolean({ notNull }), // boolean
  createdAt: timestamp({ notNull }).default(now()), // Date
});

// TypeScript infers the correct types:
const users = await db.from(Users).select();
// users  -> {
//   id: bigint;
//   username: string;
//   email: string | null;
//   age: number | null;
//   isActive: boolean;
//   createdAt: Date;
// }[]

Best Practices​

Naming Conventions​

  • Use camelCase for column names in TypeScript
  • Use descriptive names that indicate the column's purpose
export const Users = table("public", "users", {
  id: pk(),
  firstName: varchar({ length: 100 }), // Becomes first_name in SQL
  lastName: varchar({ length: 100 }), // Becomes last_name in SQL
  emailAddress: varchar({ length: 255 }), // Becomes email_address in SQL
});

Always Use notNull for Required Fields​

Be explicit about which fields are required:

export const Users = table("public", "users", {
  id: pk(),
  username: varchar({ length: 50, notNull }), // Required
  email: varchar({ length: 255, notNull }), // Required
  bio: varchar({ length: 500 }), // Optional
});

Set Sensible Defaults​

Provide default values where appropriate:

export const Posts = table("public", "posts", {
  id: pk(),
  viewCount: integer().default(0),
  isPublished: boolean().default(false),
  createdAt: timestamp({ notNull }).default(now()),
});

Next Steps​

  • Learn about Indexes to optimize query performance
  • Explore Relations to define table relationships
  • Read the Migrations guide to manage schema changes
  • Create your own Custom Columns by extending the Column class