The smallest table: pgTable and the snake_case bridge

Last lesson you named db/schema.ts the source of truth, the root of the tree every other typed shape in the app derives from. You wrote no code into it, though. It was a promise: this is where the tables live, and this is what migrations, queries, and validators all read. Now you open that empty file and write the first table.

By the end of this lesson you can start from a blank db/schema.ts, write a table Postgres will accept, and read it as a plain TypeScript object. You’ll also understand the one decision that separates a schema that scales from one that rots: how to bridge the two naming worlds, where your TypeScript wants amountDue and Postgres wants amount_due.

There’s a fork here, and it’s easy to take the wrong branch of it without noticing. You can spell every column the SQL way by hand in each table, or you can set a single policy that does it for you. The first approach works for ten columns and turns into a maintenance burden at a hundred, so we’ll take the second. Throughout, we’ll keep building the same domain from last lesson: organizations and the invoices they own.

Where the schema lives: the db/ folder

Start with the folder. Everything in this chapter lands in one place: a db/ directory that sits at the top of your src/ tree, a sibling of app/ and lib/ rather than something buried inside a feature.

• 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; the other two are on the map so you know where you’re headed, not so you learn them now.

Notice where db/ lives: up top with app/ and lib/, not tucked inside a feature folder. Earlier in the course you met the rule to co-locate code with the feature that owns it, so the invoice form lives with the invoice page rather than in some global bin. The schema is the deliberate exception, because it’s shared by every feature and so can’t live under any one of them. Organizations, invoices, billing, and the auth tables all read from the same schema.ts, so it belongs above all of them.

One rule is worth planting now, because it pays off repeatedly: db/schema.ts is the only file the migration generator ever reads. A table that isn’t exported from this file doesn’t exist as far as your database is concerned. We’ll come back to this once you’ve seen how the exports flow.

pgTable: the smallest table that runs

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

Here is the smallest organizations table that’s still a believable 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(),
});

That’s the whole floor. Two of the chained calls here, .primaryKey() and .notNull(), along with the choice of uuid over some other type, are real decisions that later lessons own in full. For now read them at face value: .primaryKey() means this column is the table’s key, .notNull() means this column can’t be empty, and uuid means this column holds a UUID. Don’t reach for the reasons yet; just learn to read the shape.

The export const organizations 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. 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.

Let’s walk the four lines one at a time. Step through the following and watch each piece light up.

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

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

The column builders come from one place: drizzle-orm/pg-core. You 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(),
});

This is 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(),
});

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

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

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

This is 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(),
});

Here is 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

You may have already felt the friction in that last example. The column key was name, a single word, so it looked the same in both worlds. Real columns aren’t single words, though. Once you name a column for when a row was created, for an organization’s foreign key, or for the amount an invoice is owed, the two worlds disagree.

This isn’t a matter of taste. There are two ecosystems with two firm conventions:

• TypeScript and JavaScript use camelCase for properties: createdAt, organizationId, amountDue. Every object you’ve written, every React prop, and every linter rule you’ve run assumes this.
• SQL and Postgres use snake_case for identifiers: created_at, organization_id, amount_due. There’s a good reason behind the convention. Postgres folds any unquoted identifier to lowercase, so a column you named createdAt silently becomes createdat in the database. Snake_case sidesteps that, and it’s what every SQL tool, every database admin, and every psql session on earth expects to see.

So you have a mismatch: on every multi-word column, the name your code wants to write and the name your database wants to store are spelled differently. The picture below shows the gap you have to close.

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.

That highlighted pill in the middle, casing: 'snake_case', is the entire fix. You set it once, and Drizzle translates every camelCase key to its snake_case column on its own. To see why that one line is worth reaching for, look at what you’d be doing without it. The two versions below declare the same columns, so 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 your schema. You now hand-maintain a 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 entirely, that one 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 now live in a single decision instead of being scattered across hundreds of builders, so there’s no per-column string left to get wrong.

The second version only works because of that one policy line, so let’s see where it goes. It lives on the db client, in db/index.ts, the file you saw greyed out in the tree. You’ll wire that client up properly in a later chapter; for now the only token that matters is casing.

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

That’s it: casing: 'snake_case', set once, on the client, never per table. Putting it there is what keeps the policy in one spot and your table files clean. Ignore client, schema, and pool for now; that wiring is a later chapter’s job, and the connection pooling underneath it is a whole topic of its own. One version note worth holding lightly: Drizzle 1.0 is moving this config to a different surface, but the client-side casing option shown here is the stable form you’ll write today.

This is the rule that ties the two worlds together for the rest of the chapter: your queries speak the TypeScript name, and Postgres receives the SQL name. When you later write db.select().from(organizations) and reference organizations.createdAt in your TypeScript, the SQL that actually hits the database says created_at. You write one world, and Drizzle speaks the other.

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

If the policy handles every column, what was that string argument from the first tab, uuid('organization_id'), even for? It’s there on purpose, as a deliberate per-column override. Sometimes one column has to map to a name the casing policy would never produce: a legacy table you inherited and can’t rename, or a column whose SQL name was fixed before your convention existed. For that column, you spell the name by hand. It’s an escape hatch for the genuine exception, not a second way of doing the everyday thing.

The risk isn’t the escape hatch itself; it’s mixing it with the policy across a schema. If most of your columns lean on casing but a handful carry hand-written strings, you now have two sources of truth for column names living side by side. A typo in one string, or a table you half-migrated from manual names to the policy, and your tables drift apart silently. The code compiles, nothing complains, and one table’s columns end 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. A schema that mixes the two freely will drift, and it won’t warn you when it does.

Seeing the bridge work: the logger flag

A fair question at this point: how do you actually know the bridge fired? You wrote createdAt, so 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, glance at the log, and there’s created_at in the emitted SQL: the translation made visible. It’s the fastest way to confirm the bridge is doing its job.

Treat it as a development tool that’s off by default, since you don’t want every production query spilling into your logs. The same flag earns its keep again later. It’s how you’ll catch a query that secretly fires once per row, the classic N+1 problem, and how you’ll grab the exact SQL to run an EXPLAIN on it. We’ll get there in a later chapter; for now it’s simply the window that lets you watch the casing bridge work.

Where the file’s exports flow

Now we can close the loop from last lesson. You exported organizations, and that export is the handle everything pulls on. Here is the whole picture drawn out, with the one 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.

This is the derivation tree from last lesson, except now there’s a real file at its root instead of an abstract idea. The schema sits on the left, and the relations file, the client, the migration generator, and the validator generator all reach back into it.

That brings back the rule we planted earlier: the migration generator treats db/schema.ts as its input, so a table you don’t export simply isn’t there for it. Forgetting the export is a confusing way to lose an afternoon. Your table type-checks fine, but at runtime your query can’t find the row, because the migration never created the table: the one tool that builds tables never saw a table to build. Export it, and all four arrows have something to point at.

Schema namespaces: named, then dropped

One word of warning, so a term doesn’t surprise you elsewhere. Postgres has a feature confusingly also called a schema : a namespace inside a database that groups tables, so you can have marketing.events 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 reach for pgTable and never pgSchema. The name is worth knowing only so it’s familiar if you bump into it in someone else’s code; there’s nothing to learn here, just an option to know exists.

Practice: write the smallest table

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

One wrinkle to call out before you start: this in-browser editor has no db client, so the casing: 'snake_case' policy that would translate your keys for you isn’t running here. This is exactly the situation the per-column escape hatch you met above is for. So in this exercise you spell the SQL name out by hand, passing it as the builder’s first argument, like timestamp('created_at'). You still write the camelCase key (createdAt), and you also hand it the snake_case column name (created_at). The organizations table in the starter already does this, so follow the same shape.

This is more than typing practice, because the grader doesn’t check for createdAt. It checks that the database column is named created_at. A green check is therefore direct proof you got the snake_case spelling right, the very 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: which name goes where

Here is a fast, no-code checkpoint on the central split. Each chip below is a column spelling. Drag it to the world it belongs in: the camelCase key you type in db/schema.ts, or the snake_case column Postgres actually stores.

Each chip is one spelling of a column. Sort it to the world that uses that spelling. Drag each item into the bucket it belongs to, then press Check.

What you write in TS the camelCase key in db/schema.ts

What Postgres stores the snake_case column name

createdAt

organizationId

amountDue

created_at

organization_id

amount_due

Check Reset

The next one tests the rule that’s easy to forget right up 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.

Closing

You opened the empty db/schema.ts and wrote the smallest real table into it: an exported const from pgTable(name, columns), where the keys are the names your TypeScript uses and the values are column builders. Then you set one policy, casing: 'snake_case' on the client, that bridges your camelCase keys to Postgres’s snake_case columns. With it in place you never hand-maintain a SQL name string, and your tables never drift apart over it. You also saw why the export is the load-bearing word. It’s the handle the relations file, the client, the migration generator, and the validator generator all pull on, which is why an unexported table doesn’t exist.

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 what comes next: which Postgres type each column should actually be, and the small, durable set of them 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.