NOT NULL, defaults, and generated columns

Last lesson you learned which Postgres type each column should be: text for a name, numeric for money, timestamp with a time zone for a date. Picking the type is the first decision a column needs, but it is not the last. The type tells Postgres what shape of value can live in the slot, and nothing more. There are three other things you have to settle for every column you write.

Those three things are the subject of this lesson, and they always come in the same order:

1. Can it be null? Is “we don’t have this value” a state the column is allowed to be in?
2. Does it have a default? When you insert a row and leave this column out, does something fill it in, and who fills it in?
3. Is it derived? Or does Postgres compute this column’s value from the other columns, so you never set it yourself?

Treat that as a checklist. Every time you add a column, you run through it in order: type, then null, then default, then derived. You already know the type, so this lesson teaches the other three, one at a time, against the same organizations and invoices domain you have been building. The syntax for all three works the same way: you chain a method onto the builder from last lesson, so text() becomes text().notNull(). The chaining is never the hard part. What takes judgment is knowing which method to add, and the cost you pay downstream when you add the wrong one.

By the end you will have two things you keep for the rest of the course. The first is a db/columns.ts file holding the boilerplate columns (id, createdAt, updatedAt) that every table needs, written once and spread into every table. The second is the first half of a clean technique for case-insensitive email, which the lesson on unique constraints will finish.

Here is the shape of a single column declaration, with each method tagged by the question it answers. Keep this picture in mind, because every section below fills in one of these slots.

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

NOT NULL is the default you should reach for

Of the three questions, nullability costs the most to get wrong. The cost is invisible the day you write the column, and it grows the longer the column lives.

One fact trips up almost everyone arriving from another database, or from no database at all: a bare column in Drizzle is nullable. Write this:

name: text(),

and you have told Postgres that this column may hold a string, or it may hold nothing at all. A row with no name is a perfectly legal row. That is almost never what you meant. When you said “an organization has a name,” you meant every organization has a name. A row without one is a bug, and you want the database to refuse it.

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 to the column. Now an insert that omits name and has no default to fall back on fails at the database, before a single bad row lands. The rule that follows is the most useful default in this chapter:

Tip

Put .notNull() on every column, unless absence is a genuinely meaningful state for that column. The exceptions are rare, and you will recognize them. Everything else is required.

That tip runs opposite to how the builder behaves, and that is deliberate. The builder defaults to nullable because nullable is the loosest, most permissive option, and your job as the person designing the schema is to tighten it. Lean toward .notNull(), and reach for nullability deliberately rather than by forgetting.

Why nullability is a tax you pay downstream

The reason this matters so much is not really about the database. It is about every line of code that reads the column, forever.

Remember Principle #2 from the start of this chapter: the schema is the source of truth, and the row types flow out of it. The name column you just declared becomes a field on the inferred row type, the one a later lesson generates with $inferSelect. 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 not a small thing. It is a contract the type checker now enforces at every read site. Anywhere you touch org.name, you have to prove you have handled the missing case with a ?., a guard, a fallback, or a non-null assertion that quietly lies. One column’s loose declaration spreads narrowing work across the entire codebase, and TypeScript makes you pay it every time, because it has no way to know the column is “really” always present. You told it the column was nullable, so it believes you.

What makes this a design decision and not a typing annoyance is that you cannot fix | null at the read site. It was generated upstream, from the schema, so the read site can only cope with it. The only place to actually remove it is the column declaration: add .notNull() once, at the root, and the | null never appears anywhere. You pay it once in the schema, or forever at every read.

The same decision echoes one more layer out, at validation. Much later, a tool called drizzle-zod will read your schema and generate a Zod validator from it, the check that runs on form input before it reaches the database. A NOT NULL column becomes a required field in that validator, and a nullable one becomes optional. So this single .notNull() choice quietly sets the shape of the row type, the insert type, the validator, and the form field that feeds it: one decision, four layers. That is exactly what Principle #2 promised, and why it is worth getting right at the source.

When null is the right answer

The rule has an exception, and the exception is worth as much as the rule, because it tells you what nullable actually means: a nullable column is one where absence is itself a meaningful state, distinct from any real value.

The cleanest example is soft delete :

deletedAt: timestamp({ withTimezone: true }),

No .notNull(), and that is the whole point. Here null is not “we forgot to set it.” null means the row is live, and a timestamp means the row was soft-deleted at that moment. The absence of a value carries information: it is the difference between a live record and a deleted one. That is the test. If you can finish the sentence “null here means ___” with something real about your domain, the column should be nullable. If the only thing null would mean is “oops, missing,” it should be .notNull().

You will meet deletedAt again. It is one of the boilerplate columns later in this lesson, and a whole chapter is devoted to the soft-delete pattern it enables. For now it is just the textbook case of a deliberately nullable column.

Optional relationships are the other common case. An invoice might or might not be assigned to a teammate:

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

An unassigned invoice is not a broken invoice. null means “nobody owns this yet”: again a meaningful state, again deliberately nullable. (The .references(...) that makes this an actual foreign key comes in the lesson on foreign keys; here we only care that the column allows null.)

So the discipline is this: reach for .notNull() by default, and treat every nullable column as a small, defensible decision you could explain in one sentence. If you can’t explain why null is allowed, it shouldn’t be.

Three kinds of default

The second question on the checklist is this: when you insert a row and leave a column out, does something fill it in? That is what a default is. But “a default” hides a decision that catches a lot of people, and that decision is the focus of this section. The thing to settle is not what the default value is. It is who computes it, Postgres or your application code, because that determines whether the default still fires when something writes to the table without going through your app.

Drizzle gives you three ways to declare a default. Learn them as three answers to “who fills this in,” not as three unrelated methods.

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

• .default(value) is a constant. status starts at 'draft', a quantity starts at 1. Drizzle emits a SQL DEFAULT clause , and Postgres fills the value in.
• .defaultNow() is the canonical createdAt. It emits DEFAULT now(), so Postgres stamps the current time at insert. This is the standard way to record when a row was created, and you will write it on essentially every table.
• .$defaultFn(() => …) is a value your application computes in TypeScript, right before the insert is sent. The headline case is a generated primary key, like a UUIDv7 ($defaultFn(() => uuidv7())), which is the exact hook the next lesson uses to make every id. You would also reach for it to derive a value with code that SQL can’t easily express, like a URL slug built from a title. The key difference is that this function runs inside Drizzle, in your Node process, not in the database.

Notice the $ on $defaultFn. Drizzle uses that prefix as a deliberate signal: a $-method runs in the client, in JavaScript, not in SQL. It marks the exact boundary this section is about.

Where the default actually runs

Here is where that boundary falls. .default(...) and .defaultNow() become part of the table’s definition in Postgres itself, a DEFAULT clause baked into the column. .$defaultFn(...) becomes part of Drizzle’s behavior instead: a function Drizzle calls for you, but only when Drizzle is the one doing the insert.

That difference stays silent until the day something writes to your table that isn’t your Drizzle code, and in a real SaaS that day always comes. You open psql to fix a row by hand, a migration seeds initial data with raw SQL, a coworker’s script touches the table, or an admin tool runs an INSERT. Watch what each kind of default does in those situations:

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, through your app or around it, 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 in psql or a SQL seed writes the row with no id, and if id is required, Postgres rejects it. The default is invisible to anything that bypasses Drizzle.

That difference in reach is what points to the rule:

Tip

Prefer a SQL-side default whenever Postgres can compute the value itself: a constant, now(), anything expressible in SQL. It survives every code path that touches the table. Reach for .$defaultFn(...) only when the value genuinely needs TypeScript to produce it, like a generated id or a value derived in app code.

There is one more thing defaults buy you, and it is pure ergonomics: any column with a default becomes optional in the insert type. When a later lesson generates the $inferInsert type from this schema, every column you gave a default to is a field you are allowed to omit. You insert a row and just don’t mention createdAt, because something fills it in. So a default is two decisions in one: what value appears when you skip the column, and the fact that you’re now allowed to skip it at all.

Caution

.defaultNow() is correct only on a timestamptz, the timestamp({ withTimezone: true }) shape from last lesson. On a plain timestamp (the anti-pattern the last lesson warned you off) it quietly stores the server’s local time with no zone attached, and the bug stays hidden until your server moves regions or your team spans time zones. Always pair .defaultNow() with withTimezone: true.

The updatedAt pattern

createdAt records when a row was created and never changes. Its sibling, updatedAt, has to record when the row last changed, which means it needs to be re-stamped on every update, not just set once at insert. That extra requirement introduces one new modifier and one real production caveat, so it earns its own short section.

Here is the canonical line. Every SaaS table you build will have a column shaped exactly like this, so it is worth memorizing as one unit:

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

The type, from last lesson: a timestamp that stores UTC. Same timestamptz as createdAt.

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

The initial value. On insert, Postgres stamps now(), so a freshly created row has updatedAt equal to createdAt.

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

Required. A row always has a last-changed time, so absence is never meaningful here.

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

The new piece. Before every Drizzle update to this row, Drizzle calls this function and re-stamps the column with the current time. This is what keeps updatedAt honest.

← Previous 1 / 1 Next →

The fourth link, .$onUpdate(...), is the new modifier. It takes a function Drizzle runs before every update to the row, and writes the result into the column. So createdAt is set once and frozen, while updatedAt is re-stamped on every change. Notice the $ again: like $defaultFn, this runs inside Drizzle.

That brings the same catch as $defaultFn, and it is worth stating plainly because the failure is silent:

Caution

.$onUpdate(...) runs at the Drizzle layer only. A raw UPDATE in psql, a bulk SQL migration, or a different service writing your table will change the row and leave updatedAt stale, because none of them call Drizzle’s function. Your updatedAt 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, no matter who made it. That is a one-time migration, and a later chapter adds it during setup, not here. Which of the two you want is a tradeoff worth weighing for yourself. The .$onUpdate(...) version is the pragmatic default, and it is correct as long as all your writes go through Drizzle, which early on they do. The trigger is the upgrade you reach for the moment another writer enters the picture.

Define your boilerplate columns once

Look back at the last two sections. The exact same createdAt and updatedAt lines belong on organizations, on invoices, on line items, on tags, and on every other table you will ever add. Hand-writing them into each table costs you twice. You restate the same four-modifier chain dozens of times, and the day you mistype one table and leave .notNull() off its updatedAt, that table drifts from the rest with nothing to warn you. The more you repeat the chain, the more room there is for one copy to differ from the others.

So define them once. Make a new file next to your schema, 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, and softDelete carries the deliberately nullable deletedAt from earlier: present on the tables that opt into soft delete, absent on the ones that don’t. Now consuming them is one line. You spread the object 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. Multiply by a dozen tables, and one of them eventually forgets a modifier.

Spread from columns.ts

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

The lifecycle columns collapse to one line. ...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.

One natural worry: if timestamps is a single shared object, do all your tables end up sharing the same column instances? No. Spreading copies the column builders into each table’s map, and Drizzle treats each table’s columns as its own definition, so the createdAt on organizations and the createdAt on invoices are independent. You are reusing the recipe, not sharing one live column between tables.

Note

The id column above is a placeholder, a bare .primaryKey() for now. Which kind of id (a UUIDv7) and how it gets generated is the entire subject of the next lesson, so columns.ts leaves it out and each table declares its own id until then. Don’t read the bare .primaryKey() as the finished shape.

This small file is the payoff of the whole “NOT NULL everywhere, sensible defaults” discipline: once you’ve decided the right shape for a lifecycle column, you encode that decision in exactly one place and spread it forever. That is the reusable-columns pattern, and the rest of the course leans on it.

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 for you from the other columns in the same row. You never insert it and never update it. You write the inputs, and the database keeps the derived value in sync automatically.

Start with the simplest possible example, so the mechanic is clear before you meet the real use case. Say a row has firstName and lastName, and you want a fullName that is always just the two joined:

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

.generatedAlwaysAs(...) is the builder. You hand it a SQL expression: here, firstName, a space, and lastName concatenated with Postgres’s || operator. Now fullName is read-only. You insert a row with a firstName and a lastName, and Postgres fills fullName itself. Change either input later, and Postgres recomputes it. You can never type a fullName that disagrees with its parts, because the database owns the value, not you.

You write the expression with the sql tag , interpolating sibling columns by their TypeScript reference (${users.firstName}) so Drizzle emits the right SQL name. Wrapping the expression in a callback, () => sql\…“, is the form to standardize on: the callback defers reading the column references until the table is fully defined, so you can reference the table’s own columns without a circular-reference error.

STORED vs VIRTUAL

There is a real choice underneath generated columns, but in Drizzle’s Postgres builder it is already made for you. It is worth stating precisely which way, because Postgres recently changed the default for hand-written SQL and the two no longer agree.

A generated column can be stored or virtual:

• STORED is computed on write and persisted to disk. It costs storage, it recomputes (and rewrites the row) whenever an input changes, and, the property that matters most, it can be indexed. This is what you want for any derived value you’ll filter or sort by.
• VIRTUAL is computed on read, stored nowhere, and not indexable.

Here is the one piece of platform detail to carry out of this section: Drizzle’s Postgres builder always emits STORED. There is no mode argument to flip; .generatedAlwaysAs() in pg-core gives you a stored column, full stop. (You may have seen a { mode } option; that exists only in Drizzle’s MySQL and SQLite builders, not Postgres.) So whatever you generate through Drizzle is stored and indexable by default, which is exactly what you want.

The reason to know VIRTUAL exists at all is that Postgres 18 changed the default. A bare GENERATED ALWAYS AS written by hand in raw SQL now defaults to VIRTUAL, where older Postgres defaulted to STORED. So if you ever hand-write DDL and expect the old behavior, you’ll get a non-indexable column and an unwelcome surprise. Drizzle’s builder still emits STORED explicitly, so your schema is unaffected, but recognize the change if you see it.

A real one: case-insensitive email

Here is the use case that earns its keep. Emails are case-insensitive in practice, so Ada@Example.com and ada@example.com belong to the same person. Postgres, though, compares text exactly, so to the database those are two different strings. To guarantee that no two users sign up with the “same” email regardless of case, you need a normalized form to compare against.

A generated column is the clean way to get one:

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

The real column. 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 second column Postgres derives by running lower() on the email. ada@… and Ada@… both produce the same lowercased value. It stays in sync automatically and you never write it directly.

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

Why store a lowercased copy at all? So a unique index on emailLowercased can enforce “no two users with the same email, ignoring case,” with zero application code. That index is coming in the lesson on unique constraints; this column is the setup for it.

← Previous 1 / 1 Next →

So you store two columns: email exactly as typed (for display and sending mail), and emailLowercased, which Postgres keeps as the normalized version. On its own, emailLowercased does nothing useful yet; it’s just a lowercased copy. The payoff comes when the lesson on unique constraints puts a unique index on it. From that point, two sign-ups with Ada@example.com and ada@example.com collide on the normalized column and the second is rejected, with no validation code on your part to make it happen. This lesson builds the setup; the unique-constraints lesson completes it.

(This is cleaner than indexing a lower(email) expression directly, because emailLowercased is a real column your application can also read and compare, not just an index Postgres uses behind the scenes.)

Generated columns are read-only, and the type system knows it

Because Postgres owns a generated column’s value, you can never supply it yourself. This isn’t a convention you have to remember; it’s enforced top to bottom. A raw INSERT that tries to write email_lowercased is rejected by Postgres. And when a later lesson generates the insert type from this schema, the generated column is omitted entirely: not optional, absent, so TypeScript won’t even offer it as a field. You couldn’t pass it if you tried.

Two costs to weigh, since STORED isn’t free:

• Every update to an input rewrites the value. Change email, and Postgres recomputes emailLowercased and rewrites that row. On a hot, frequently-updated column that write cost is real: usually negligible, occasionally worth noticing.
• The expression can only see the same row. A generated column may reference other columns in its own row and nothing else: no other tables, no subqueries, no aggregates. If you need a value computed across rows, a generated column is the wrong tool.

A couple of other places this pattern shows up, named so you recognize them later rather than built now: a derived line-item total (quantity * unitPrice stored on the row), and a tsvector column for full-text search, which is a stored generated column too and which a later chapter builds. The mechanic is the same every time: Postgres computes it, and you read it.

Practice: finish the columns

There are two checks. The first is fast and tests the decisions alone: the three-question checklist as pure judgment, before any syntax. Sort each column description into where it belongs.

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

The second check is the real skill: writing the modifiers. Below is a starter schema for the invoicing domain with bare columns. Every type is correct, but every modifier is missing. Add the right ones, running the checklist on each column: required or nullable, default or not, derived or not.

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 reads the column flags and runs the two status probes, but it 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 to confirm the expression.

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 one column with no .notNull(), because null is its meaningful “live row” state. Everything else gets .notNull(); status and created_at add SQL-side defaults; email_lowercased is derived, so Postgres owns its value and you never write it.

If both of those felt mechanical, the checklist has done its job: you ran type, null, default, and derived on every column without thinking about it. Running it without thinking is the goal. From here on, every column you write goes through the same four questions.

Wrapping up

Every column you declare answers four questions, always in this order: what type, can it be null, does it have a default, is it derived. The type was last lesson; this lesson was the other three, and the judgment calls that hang off each:

• .notNull() is your default, because a nullable column becomes T | null in the inferred row type and taxes every read site forever. Make a column nullable only when absence is a real, explainable state, like deletedAt or an unassigned owner.
• Defaults split by who computes them. .default(...) and .defaultNow() live in Postgres and fire for every writer; .$defaultFn(...) and .$onUpdate(...) live in Drizzle and fire only when Drizzle does the write. Prefer the SQL side whenever Postgres can compute the value.
• db/columns.ts encodes the createdAt, updatedAt, and deletedAt shapes once and spreads them into every table, so the discipline above is one line per table rather than a chain you retype and eventually fumble.
• Generated columns let Postgres derive a value from the same row’s other columns: read-only, and STORED (so indexable) in Drizzle. The emailLowercased column you wrote is waiting on a unique index, which the lesson on constraints adds.

Next you’ll settle the id you’ve been leaving as a bare 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.