The smallest table: pgTable and the snake_case bridge

Last lesson you named db/schema.ts the source of truth but wrote no code into it. Now you open that empty file and write the first table: one Postgres will accept, and that you can read back as a plain TypeScript object.

The one decision that separates a schema that scales from one that rots is how you bridge the two naming worlds. Your TypeScript wants amountDue; Postgres wants amount_due. You can spell every column the SQL way by hand in each table, which works for ten columns and becomes a chore at a hundred, or set a single policy that does it for you. We’ll set the policy, and keep building last lesson’s domain: organizations and the invoices they own.

Where the schema lives: the db/ folder

Everything in this chapter lands in a db/ directory at the top of your src/ tree, a sibling of app/ and lib/.

• Directorysrc/

  • Directoryapp/ your routes

    • …
  • Directorylib/ shared helpers

    • …
  • Directorydb/

    • schema.ts every table, the source of truth
    • relations.ts the relations graph (built later this chapter)
    • index.ts the db client (wired up in a later chapter)

All three files fill up across this chapter; today only schema.ts matters.

Earlier you met the rule to co-locate code with the feature that owns it, so the invoice form lives beside the invoice page. The schema is the deliberate exception: organizations, invoices, billing, and the auth tables all read from the same schema.ts, so it can’t live under any one feature and sits above all of them.

One rule pays off repeatedly: db/schema.ts is the only file the migration generator ever reads. A table not exported from this file does not exist as far as your database is concerned.

pgTable: the smallest table that runs

A table is one function call, pgTable(name, columns). The first argument, name, is the table’s name as Postgres stores it: snake_case and plural, like organizations, invoices, and invoice_line_items. The second argument, columns, is an object literal whose keys are the property names your TypeScript code uses and whose values are column builders: small functions like text() and uuid() that say what kind of column each one is.

Here is the smallest believable organizations table, with an id and a name:

import { pgTable, text, uuid } from 'drizzle-orm/pg-core';

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

Two of the chained calls, .primaryKey() and .notNull(), and the choice of uuid over another type, are real decisions that later lessons own in full. For now read them at face value: .primaryKey() marks this column as the table’s key, .notNull() means it can’t be empty, and uuid means it holds a UUID. They are named here, explained later.

The export is not a formality; it’s the handle the rest of the system grabs onto. Your queries import organizations to read from it, your relations file imports it to describe how it connects to other tables, the migration generator reads it to create the table, and the validator generator, much later, reads it to build a Zod schema. Everything downstream pulls on this one exported name, so a table you don’t export is invisible.

import { pgTable, text, uuid } from 'drizzle-orm/pg-core';

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

The column builders all come from drizzle-orm/pg-core. Add a named import for each kind of column you use, here pgTable itself plus the text and uuid builders.

import { pgTable, text, uuid } from 'drizzle-orm/pg-core';

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

The pgTable call and its first argument. 'organizations' is the name Postgres stores: snake_case and plural, the SQL convention.

import { pgTable, text, uuid } from 'drizzle-orm/pg-core';

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

The exported handle. organizations is what every query, relation, and migration in the codebase imports, and exporting it is the whole point of the file.

import { pgTable, text, uuid } from 'drizzle-orm/pg-core';

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

A column. The key id is the name your TypeScript uses, and uuid() is the builder that says what it holds. .primaryKey() is chained on, named here and explained in a later lesson.

import { pgTable, text, uuid } from 'drizzle-orm/pg-core';

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

The same pattern again, a key then a builder, as in name: text(). This pairing of a TS property name with a column builder is the one repeating unit every table is made of. Once you can spot it, every schema reads the same way.

← Previous 1 / 1 Next →

Two naming worlds: camelCase in TS, snake_case in SQL

In that last example the column key was name, one word, so it looked the same in both worlds. Real columns are rarely one word. The moment you name a column for when a row was created, for an organization’s foreign key, or for the amount an invoice owes, the two worlds disagree.

This is convention, not taste. Two ecosystems hold two firm rules:

• TypeScript and JavaScript use camelCase for properties: createdAt, organizationId, amountDue.
• SQL and Postgres use snake_case for identifiers: created_at, organization_id, amount_due. Postgres folds any unquoted identifier to lowercase, so a column you named createdAt silently becomes createdat. Snake_case sidesteps that.

So on every multi-word column, the name your code writes and the name your database stores are spelled differently.

Your TypeScript

createdAtorganizationIdamountDue

Your TypeScript

↔

↔

↔

casing: 'snake_case'

Your Postgres

created_atorganization_idamount_due

One config line translates every camelCase key to its snake_case column — both directions, reads and writes.

Without that one config line you close the gap by hand. The two versions below declare the same columns; flip between them.

Manual strings (drifts)

export const invoices = pgTable('invoices', {
  organizationId: uuid('organization_id').notNull(),
  amountDue: integer('amount_due').notNull(),
  createdAt: timestamp('created_at').notNull(),
});

This works, but the cost grows with the schema: a hand-written snake_case string on every column of every table. The day someone types 'amount_due ' with a stray space, writes 'amountdue', or forgets the string, that column drifts from the convention and nothing flags it.

One policy (casing)

export const invoices = pgTable('invoices', {
  organizationId: uuid().notNull(),
  amountDue: integer().notNull(),
  createdAt: timestamp().notNull(),
});

Set the policy once and every key is translated for you. The SQL names live in a single decision instead of being scattered across hundreds of builders, so there is no per-column string left to get wrong.

That policy line lives on the db client, in db/index.ts, the file greyed out in the tree. You’ll wire the client up in a later chapter; for now only casing matters.

// db/index.ts — you'll wire this up properly in a later chapter
export const db = drizzle({ client: pool, schema, casing: 'snake_case' });

Set casing once, on the client, never per table, and the policy stays in one spot while your table files stay clean.

This is the rule that ties the two worlds together: your queries speak the TypeScript name, and Postgres receives the SQL name. Reference organizations.createdAt in your TypeScript, and the SQL that hits the database says created_at. You write one world; Drizzle speaks the other.

The per-column escape hatch, and the trap of mixing it

So what is the string argument from the first tab, uuid('organization_id'), for? It is a deliberate per-column override. Sometimes a column must map to a name the casing policy would never produce: a legacy table you inherited and cannot rename, or a column whose SQL name was fixed before your convention existed. For that column you spell the name by hand. It is an escape hatch for the genuine exception, not a second way of doing the everyday thing.

The danger is mixing it with the policy across one schema. When most columns lean on casing but a handful carry hand-written strings, you have two sources of truth for column names side by side. A typo in one string, or a table you only half-migrated off manual names, and your tables drift apart silently: the code compiles, nothing complains, and one table ends up spelled differently from the rest.

Caution

Pick one policy per schema. Let casing: 'snake_case' name every column, and reserve the explicit string for the rare column that genuinely needs it, with a comment saying why.

Seeing the bridge work: the logger flag

How do you confirm Postgres really got created_at and not some camelCase surprise? You watch the SQL go by. Drizzle’s client takes one more option for exactly this. Add logger: true next to casing:

export const db = drizzle({ client: pool, schema, casing: 'snake_case', logger: true });

With logger: true, Drizzle prints every SQL statement it sends to the database straight to your terminal. Run a query against organizations.createdAt, check the log, and there’s created_at in the emitted SQL: the translation made visible. Keep it as a development tool, off by default, so production queries don’t spill into your logs.

Where the file’s exports flow

You exported organizations, and that export is the handle everything pulls on. Here is the whole picture, the exported file on the left and every tool that reads it on the right.

db/schema.ts the exported tables

db/relations.ts

imports the tables to describe how they connect (later this chapter)

db/index.ts

re-exports them as the schema namespace for the db client (later chapter)

Drizzle Kit

reads the file to generate migrations

drizzle-zod

reads the same exports to generate validators

One exported file, and every tool downstream reads it. That is exactly why a table you forget to export doesn't exist for any of them.

The schema sits on the left, and the relations file, the client, the migration generator, and the validator generator all reach back into it.

The migration generator treats db/schema.ts as its only input, so a table you don’t export simply isn’t there for it. That makes a forgotten export a confusing way to lose an afternoon: your table type-checks fine, but at runtime the query can’t find the row, because the migration never created the table. Export it, and all four arrows have something to point at.

Schema namespaces: named, then dropped

So one term doesn’t surprise you elsewhere: Postgres also has a feature called a schema , a namespace that groups tables inside a database, so marketing.events can sit separate from public.events. Drizzle exposes it as pgSchema('marketing') for projects that need that split.

This course keeps every table in Postgres’s default public schema, so you’ll only ever reach for pgTable. Knowing the name is enough to recognize pgSchema in someone else’s code.

Practice: write the smallest table

Time to write one yourself. The organizations table below is already done and mirrors what you read above. Your job is the invoices table: an id, an amountDue money column, and a createdAt timestamp, each key in camelCase the way TypeScript wants it.

One wrinkle before you start: this in-browser editor has no db client, so the casing: 'snake_case' policy that would translate your keys isn’t running here. This is exactly what the per-column escape hatch you met above is for. So you spell the SQL name by hand as the builder’s first argument, like timestamp('created_at'): the camelCase key (createdAt) plus the snake_case column name (created_at). The organizations table in the starter shows the shape.

This is more than typing practice. The grader doesn’t check for createdAt; it checks that the database column is named created_at. A green check is direct proof you got the snake_case spelling right, the translation the casing policy does for you in a real project.

Complete the invoices table with three columns. Write each key in camelCase, and pass each builder its snake_case SQL name as the first argument: an id uuid set as the primary key, an amountDue integer money column that can't be empty, and a createdAt timestamp that can't be empty. The grader checks the emitted SQL uses snake_case column names.

Check schema Feedback Reset

Quick check

This one tests the rule that’s easy to forget until it costs you an afternoon.

You add a tags table to db/schema.ts — const tags = pgTable('tags', { ... }) — but leave off the export. tsc stays green, you run your migrations, then a query against tags blows up at runtime. Which explanation fits all three of those facts?

The migration ran but renamed the table; tsc passes because the type is fine, and the query fails because it looks for the old name.

Migrations only see what the file exports, so tags was never created. The type still resolves locally, so tsc is happy — but at runtime the table genuinely isn’t there.

The build should have failed first — a pgTable that isn’t exported is a compile error, so something else must be wrong.

Migrations created an empty tags table by scanning every pgTable call in the file; the query fails only because the table has no columns yet.

Wrap-up

Watch video

Neon’s 13-minute tour walks the same db/schema.ts, pgTable, and camelCase-to-snake_case bridge you just built, then carries on into migrations and querying.

You wrote uuid(), text(), integer(), and timestamp() without ever asking why those types. That’s next: which Postgres type each column should actually be, and the small, durable set a 2026 SaaS app reaches for.

External resources

Drizzle ORM — Schema declaration

orm.drizzle.team

The canonical reference for pgTable, the minimal table, and column builders.

Drizzle ORM — Database connection & casing

orm.drizzle.team

Where the casing policy and the logger flag are configured on the db client.

PostgreSQL — Lexical structure: identifiers

postgresql.org

The primary source for the trap: unquoted identifiers are always folded to lowercase.

Postgres case sensitivity, explained

bytebase.com

A plain-language walkthrough of identifier folding and why snake_case sidesteps it.