NOT NULL, defaults, and generated columns

Last lesson you picked each column’s Postgres type: text for a name, numeric for money, timestamp for a date. The type is the first decision a column needs, not the last. For every column you settle three more, in order:

1. Can it be null? Is “we don’t have this value yet” a state the column may hold?
2. Does it have a default? When you insert a row without this column, something fills it in, and who fills it in matters.
3. Is it derived? Does Postgres compute the value from other columns, so you never set it yourself?

You already have the type, so this lesson covers the other three. The syntax is the same for each: chain a method onto last lesson’s builder, so text() becomes text().notNull(). Chaining is the easy part; the judgment is knowing which method to add, and what the wrong one costs you downstream.

Along the way you build two things the rest of the course reuses: a db/columns.ts file holding the columns every table needs (id, createdAt, updatedAt), written once and spread into each table, and the first half of a case-insensitive email setup that the UNIQUE and CHECK lesson finishes.

One column declaration. The type is the only required link; the rest are decisions you chain on.

Default every column to NOT NULL

One fact trips up almost everyone: a bare column in Drizzle is nullable. Write this:

name: text(),

and you have told Postgres the column may hold a string or nothing at all. “An organization has a name” means every organization has one, so a row without a name is a bug you want the database to refuse.

The fix is one method:

name: text(),            // nullable — a row with no name is allowed
name: text().notNull(),  // required — Postgres rejects a row without one

.notNull() adds a NOT NULL constraint, so an insert that omits name fails at the database before a bad row lands.

Tip

Put .notNull() on every column, unless absence is a genuinely meaningful state for that column. The builder defaults to nullable because nullable is the loosest option; tightening it is your job. Reach for nullability deliberately, never by forgetting.

Nullability costs you more in the code that reads the column than in the database. The column becomes a field on the inferred row type, and its nullability flows straight through. Hover the two versions:

export const orgsLoose = pgTable('orgs_loose', {
  name: text(),
});

export const orgsStrict = pgTable('orgs_strict', {
  name: text().notNull(),
});

That | null is enforced at every read site: anywhere you touch org.name you must handle the missing case with a ?., a guard, a fallback, or a non-null assertion. You cannot remove it at the read site, because it was generated upstream from the schema. The only place to remove it is the column declaration: add .notNull() once, and the | null never appears.

When null is the right answer

Make a column nullable when absence is itself a meaningful state, distinct from any real value.

The clearest example is soft delete :

deletedAt: timestamp({ withTimezone: true }),

Here null means the row is live, and a timestamp means it was deleted at that moment. That is the test: if you can finish “null here means ___” with something real about your domain, the column should be nullable; if the only meaning is “missing,” use .notNull().

Optional relationships are the other common case:

assignedToId: uuid(),  // null = unassigned, a real state

null means “nobody owns this yet,” again a deliberate, meaningful state.

SQL-side vs app-side defaults

The second checklist question: when you insert a row and leave a column out, what fills it in? That value is a default. The easy part is the value; the decision that matters is who computes it, Postgres or your app, because that determines whether the default still fires when something writes to the table without going through Drizzle.

Drizzle gives you three ways to declare one.

status: text().default('draft'),
createdAt: timestamp({ withTimezone: true }).defaultNow(),
id: uuid().$defaultFn(() => uuidv7()),

• .default(value) is a constant, like 'draft' or 1. Drizzle emits a SQL DEFAULT clause , so Postgres fills it in.
• .defaultNow() emits DEFAULT now(), so Postgres stamps the insert time. This is the standard createdAt.
• .$defaultFn(() => …) is a value your app computes in TypeScript just before the insert, running inside Drizzle in your Node process. Reach for it when the value needs JavaScript, like a generated UUIDv7 id or a slug derived from a title.

The $ is a deliberate signal: a $-method runs in the client, in JavaScript, not in SQL. That split decides where the default runs, and it stays silent until something other than your Drizzle code writes a row, which in a real app it eventually does: a psql session, a raw SQL seed, a coworker’s script.

SQL-side · .default / .defaultNow

createdAt: timestamp({ withTimezone: true }).defaultNow(),
// → emits  created_at timestamptz DEFAULT now()  in the table itself

Fires for everyone. The DEFAULT lives in Postgres, so it fills in on a Drizzle insert, a raw INSERT in psql, and a migration seed alike. Anything that touches the table gets the value.

App-side · .$defaultFn

id: uuid().$defaultFn(() => uuidv7()),
// → no DEFAULT in the table; Drizzle calls uuidv7() before each insert

Fires only through Drizzle. The table has no DEFAULT, so the value appears only when your Drizzle code does the insert. A raw INSERT writes the row with no id, and if id is required, Postgres rejects it.

Tip

Prefer a SQL-side default whenever Postgres can compute the value, a constant or now(): it survives every code path that touches the table. Reach for .$defaultFn(...) only when the value genuinely needs TypeScript.

A default also makes the column optional in the insert type, so when a later lesson generates $inferInsert, you can omit any column you gave a default to.

Caution

.defaultNow() is correct only on a timestamptz (timestamp({ withTimezone: true })). On a plain timestamp it stores the server’s local time with no zone, a bug that stays hidden until your server moves regions or your team spans time zones. Always pair .defaultNow() with withTimezone: true.

Re-stamping updatedAt on every write

createdAt is set once and never changes. updatedAt records the row’s last change, so it must be re-stamped on every update, not just set at insert. That takes one extra modifier.

Every SaaS table you build will have an updatedAt shaped like this:

updatedAt: timestamp({ withTimezone: true }).defaultNow().notNull().$onUpdate(() => new Date()),

A timestamp stored as UTC, the same timestamptz as createdAt.

updatedAt: timestamp({ withTimezone: true }).defaultNow().notNull().$onUpdate(() => new Date()),

On insert Postgres stamps now(), so a new row’s updatedAt equals its createdAt.

updatedAt: timestamp({ withTimezone: true }).defaultNow().notNull().$onUpdate(() => new Date()),

A row always has a last-changed time, so this is never absent.

updatedAt: timestamp({ withTimezone: true }).defaultNow().notNull().$onUpdate(() => new Date()),

Before every Drizzle update, Drizzle calls this and re-stamps the column with the current time.

← Previous 1 / 1 Next →

Note the $ again: like $defaultFn, .$onUpdate(...) runs inside Drizzle, with the same catch.

Caution

.$onUpdate(...) runs at the Drizzle layer only. A raw UPDATE in psql, a bulk migration, or another service writing your table leaves updatedAt stale, because none of them call Drizzle’s function. It is only as trustworthy as your promise that every write goes through Drizzle.

The reliable fix lives in the database, where nothing can route around it: a Postgres BEFORE UPDATE trigger that stamps updated_at on every change. A later chapter adds it. Until then .$onUpdate(...) is correct as long as every write goes through Drizzle, which early on it does; reach for the trigger once another writer appears.

Define your boilerplate columns once

The same createdAt and updatedAt lines belong on organizations, invoices, line items, tags, and every other table. Hand-writing the four-modifier chain into each one invites drift: mistype one table, leave .notNull() off its updatedAt, and it quietly diverges from the rest with nothing to warn you.

So define them once. Next to your schema, make db/columns.ts that exports the boilerplate columns as plain objects:

db/columns.ts

import { timestamp } from 'drizzle-orm/pg-core';

export const timestamps = {
  createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp({ withTimezone: true })
    .defaultNow()
    .notNull()
    .$onUpdate(() => new Date()),
};

export const softDelete = {
  deletedAt: timestamp({ withTimezone: true }),
};

timestamps carries the two lifecycle columns; softDelete carries the deliberately nullable deletedAt, present only on tables that opt into soft delete. Spread either into a table’s column map with ...:

Hand-written

export const organizations = pgTable('organizations', {
  id: uuid().primaryKey(),
  name: text().notNull(),
  createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp({ withTimezone: true })
    .defaultNow()
    .notNull()
    .$onUpdate(() => new Date()),
});

Every table restates the same four lines, and one eventually forgets a modifier.

Spread from columns.ts

export const organizations = pgTable('organizations', {
  id: uuid().primaryKey(),
  name: text().notNull(),
  ...timestamps,
});

...timestamps drops createdAt and updatedAt into the column map. Change the shape once in columns.ts and every table updates together, so drift becomes impossible.

If timestamps is one shared object, do all your tables share the same column instances? No: spreading copies the builders into each table’s map, so the createdAt on organizations and on invoices are independent. You reuse the recipe, not one live column.

Note

The id above is a placeholder, a bare .primaryKey(). Which kind of id and how it gets generated is the whole subject of the next lesson, so columns.ts leaves it out and each table declares its own until then.

Generated columns: let Postgres compute the value

The last checklist question is the one you’ll reach for least often: is this column derived? A generated column is one Postgres computes from the other columns in the same row. You never write it; you write the inputs, and the database keeps the derived value in sync.

Say a row has firstName and lastName, and you want a fullName that is always the two joined:

firstName: text().notNull(),
lastName: text().notNull(),
fullName: text().generatedAlwaysAs(
  () => sql`${users.firstName} || ' ' || ${users.lastName}`,
),

You hand .generatedAlwaysAs() a SQL expression, and fullName becomes read-only: Postgres fills it in on insert and recomputes it whenever a name changes, so the value can never disagree with its parts. Write the expression with the sql tag , interpolating sibling columns by their TypeScript reference so Drizzle emits the right names. Wrapping it in a callback (() => sql\…“) defers reading those references until the table is fully defined, avoiding a circular-reference error.

STORED vs VIRTUAL

A generated column is either stored (computed on write, saved to disk, and indexable) or virtual (computed on read, stored nowhere, and not indexable). You index any value you’ll filter or sort by, so stored is usually what you want.

Drizzle’s Postgres builder always emits STORED. There’s no mode argument to flip; the { mode } option you may have seen exists only in Drizzle’s MySQL and SQLite builders. One change worth recognizing: Postgres 18 made VIRTUAL the default for a bare GENERATED ALWAYS AS in raw SQL, so a hand-written column now comes out non-indexable unless you say STORED.

A real one: case-insensitive email

Emails are case-insensitive in practice, so Ada@Example.com and ada@example.com are the same person. Postgres compares text exactly, so to the database they are two different strings. To stop two users from signing up with the “same” email, you need a normalized form to compare against, and a generated column gives you one:

export const users = pgTable('users', {
  email: text().notNull(),
  emailLowercased: text().generatedAlwaysAs(
    () => sql`lower(${users.email})`,
  ),
});

The address exactly as the user typed it, casing and all. .notNull() because every user has an email.

export const users = pgTable('users', {
  email: text().notNull(),
  emailLowercased: text().generatedAlwaysAs(
    () => sql`lower(${users.email})`,
  ),
});

A column Postgres derives by running lower() on the email, so ada@… and Ada@… produce the same value. It stays in sync automatically and you never write it.

export const users = pgTable('users', {
  email: text().notNull(),
  emailLowercased: text().generatedAlwaysAs(
    () => sql`lower(${users.email})`,
  ),
});

A real column, not just an index expression, gives the UNIQUE and CHECK lesson a unique index on emailLowercased to enforce “no two users with the same email, ignoring case” with zero application code.

← Previous 1 / 1 Next →

You keep email as typed for display and sending mail, and emailLowercased as the normalized version Postgres maintains. On its own it does nothing yet; the payoff comes when the UNIQUE and CHECK lesson puts a unique index on it, and sign-ups with Ada@example.com and ada@example.com collide on the normalized column.

Generated columns are read-only

Because Postgres owns the value, you can never supply it: a raw INSERT that writes it is rejected, and when a later lesson generates the insert type, the column is omitted entirely, so TypeScript won’t even offer it.

STORED carries two costs. Every update to an input rewrites the value, so changing email recomputes and rewrites emailLowercased too; usually negligible, occasionally worth noticing on a hot column. And the expression sees only its own row: no other tables, no subqueries, no aggregates, so it’s the wrong tool for a value computed across rows.

Practice: finish the columns

First, sort each column by where its value comes from.

Each item describes a column from the invoicing domain. Sort it by what fills the column in. Drag each item into the bucket it belongs to, then press Check.

SQL-side default `.default(...)` / `.defaultNow()` — Postgres fills it

App-side default `.$defaultFn(...)` — Drizzle fills it in TS

Generated `.generatedAlwaysAs(...)` — Postgres derives it

No default you always supply it (or it's nullable)

An invoice’s status, which always starts at 'draft'

A createdAt that records the insert time

A line item’s quantity, defaulting to 1

An id set to a freshly generated UUIDv7 in TS at insert

A URL slug computed from the invoice title in app code on insert

emailLowercased, always lower(email)

An invoice’s amount, which the user must enter every time

A user’s deletedAt (null until the row is soft-deleted)

Check Reset

Now write the modifiers: the starter schema below has correct types but no modifiers, so run the checklist on each column and add them.

Run the checklist on every column. In users, email is required, and email_lowercased is a generated column whose value is always lower(email). In invoices, amount is required, status defaults to 'draft', created_at is stamped by Postgres on insert, and deleted_at stays nullable. Leave each id as the bare .primaryKey() placeholder.

Check schema Feedback Reset

Note

The checker can’t see inside a generated column’s expression, so email_lowercased is checked only for being a text column that exists. Compare your .generatedAlwaysAs(...) against the reference below.

Reference solution

export const users = pgTable('users', {
  id: uuid('id').primaryKey(),
  email: text('email').notNull(),
  emailLowercased: text('email_lowercased').generatedAlwaysAs(
    () => sql`lower(${users.email})`,
  ),
});

export const invoices = pgTable('invoices', {
  id: uuid('id').primaryKey(),
  amount: numeric('amount', { precision: 12, scale: 2 }).notNull(),
  status: text('status').notNull().default('draft'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  deletedAt: timestamp('deleted_at', { withTimezone: true }),
});

deletedAt is the only column without .notNull(), because null is its meaningful “live row” state. The rest are .notNull(); status and created_at add SQL-side defaults; email_lowercased is derived, so Postgres owns its value.

Wrapping up

Every column answers four questions in order: what type, can it be null, does it have a default, is it derived. Next you’ll settle the id you’ve been leaving as a placeholder: what makes a good primary key, and why this course reaches for UUIDv7.

Keep exploring

Drizzle — Generated Columns

orm.drizzle.team

The generatedAlwaysAs API across Postgres, MySQL, and SQLite — note the mode argument is MySQL/SQLite only.

Drizzle — Postgres column types

orm.drizzle.team

Every pg-core builder and the modifiers (.notNull, .default, .$defaultFn, .$onUpdate) you chain onto them.

PostgreSQL 18 — Generated Columns

postgresql.org

The STORED vs VIRTUAL distinction from the source, including the Postgres 18 default change.

11 Lessons to learn when using NULLs in PostgreSQL

ftisiot.net

Worked examples of the traps a nullable column springs — at query, join, and aggregate time — the downstream cost this lesson warns about.

PostgreSQL — citext

postgresql.org

The built-in case-insensitive text type — the alternative to the lower() generated column for case-insensitive email.