Schema and the four core tables

Last lesson you wired the auth instance and passed it drizzleAdapter(db, { provider: 'pg' }). That line told Better Auth to store its data in your Postgres, through Drizzle. There’s just one problem: the tables it wants to write to don’t exist. Call sign-up right now and it would reach for a user table that isn’t there, so Postgres would answer with relation "user" does not exist and nothing would get saved.

This lesson is where the library and your database actually meet. Better Auth needs four tables to do its job, and your app already has a Postgres database with its own domain tables: the schema and migrations you built earlier with Drizzle. You’re going to connect those two halves. You’ll generate the four tables Better Auth needs, read what the generator produced as if you were reviewing a teammate’s pull request, and ship them as your first auth migration, right next to your domain tables and through the same Drizzle Kit pipeline you already know.

By the end you’ll have four new tables in Postgres and, more importantly, one mental model that the next chapters rest on. It’s a single idea, and I’ll state it now so you can watch for it: your identity is one row, and every way you can prove that identity is a separate row. That is the idea that makes the whole auth data model click.

Pointing the adapter at the schema

Before generating anything, there’s a one-line change to make. Last lesson’s adapter call left out a detail.

drizzleAdapter(db, { provider: 'pg' })

You told the adapter which database driver you’re on, 'pg' for Postgres, but you never told it where your table definitions live. Without that, the adapter has to guess: it falls back to looking for tables exported under the exact names user, session, account, and verification from the Drizzle instance. That guess works, but it’s fragile. Rename or re-export a table and the adapter can no longer find it, with no warning.

The fix is to hand the adapter your schema explicitly, so it resolves tables by reference instead of by guessing names.

Before

src/lib/auth.ts

export const auth = betterAuth({
  database: drizzleAdapter(db, { provider: 'pg' }),
  // ...
});

Lesson 1’s adapter. It knows the driver ('pg') but not where your tables are defined, so it has to guess them by name.

After

src/lib/auth.ts

import * as schema from '@/db/schema';

export const auth = betterAuth({
  database: drizzleAdapter(db, { provider: 'pg', schema }),
  // ...
});

Add the namespace import and pass schema. Now the adapter resolves each table by reference, with no name-guessing and no drift. This mirrors how you built db itself: you passed schema to drizzle(...) when you set up the client.

That’s the entire change to auth.ts for this lesson: a one-line import and one extra argument. import * as schema is a namespace import . It gathers everything the schema module exports into one schema object and hands the whole bundle to the adapter, which picks out the tables it recognizes.

One detail you might run into later: Better Auth has a usePlural option for projects that want plural table names (users, sessions). This project uses singular names, which is Better Auth’s default, so there’s nothing to set. You won’t touch the option here, but it’s worth recognizing if you come across it in the docs.

Why you generate the schema instead of writing it

You have a choice to make here, and it shapes the rest of the lesson.

You could sit down and hand-write these four tables. You know Drizzle, so you could open a file and start typing pgTable('user', { ... }). It feels like the way to truly understand them. It isn’t, and the reason why is worth pinning down.

Better Auth’s adapter expects specific columns, with specific names and types. Its code reads a column called emailVerified. If you hand-write a column and call it email_verified_at, or make it a timestamp when the library wants a boolean, everything compiles fine and then breaks at runtime with a mismatch that’s miserable to track down. This is the most common auth-setup debugging session, and it’s entirely self-inflicted. You’re not modeling your own domain here, where you’d be the authority on the column names. You’re conforming to a contract the library already owns.

So you let the library write the tables it requires. Better Auth ships a CLI that reads your auth config, sees the Drizzle adapter and the plugins you’ve loaded, and emits exactly the Drizzle definitions the adapter expects. No mismatch is possible, because the same code that reads the columns is the code that generated them.

The word “generated” can sound like a black box, so let’s be precise about what it means here, because it should change how you feel about it. The output is a normal Drizzle schema file. You open it, read it, review it, edit it if you need to, commit it, and migrate it, exactly like any file you’d write by hand. It is not a hidden runtime contract that gets re-derived behind your back. It’s generated once, and from that moment it’s yours, sitting in version control like everything else.

That reframes the whole skill. The job isn’t authoring these tables, it’s reviewing them, the way you’d review a pull request from a teammate. You’ll run a command, then read what it produced and check it against your understanding. Learning to do that review well is most of what this lesson teaches.

One more point that comes up repeatedly across the next few chapters: you regenerate whenever the set of plugins changes. Turn on the organizations plugin later and the CLI will add member, invitation, and organization tables. Add the passkey plugin next chapter and it adds a passkey table. Each regeneration produces a diff, new lines you read and approve rather than accept on faith. The CLI is a code generator you review, not an ORM you trust blindly.

Tip

The whole workflow in one line, worth memorizing: generate the schema, review the diff, migrate. Never hand-author auth tables.

Generating the tables with the CLI

The command is one line:

npx @better-auth/cli generate --config src/lib/auth.ts --output src/db/schema/auth.ts

Here’s what each part does. npx @better-auth/cli generate runs Better Auth’s generator. --config src/lib/auth.ts points it at your auth instance; the CLI can often auto-discover the file, but naming it removes any ambiguity. The generator reads that config, sees the Drizzle adapter with provider: 'pg' and whichever plugins you’ve loaded, and writes out a Drizzle schema file containing exactly the tables those choices require.

The one flag worth dwelling on is --output. Left to itself, the CLI drops the schema at a root schema.ts. You don’t want that. You want it next to your domain tables, where every schema file in this project lives, so --output src/db/schema/auth.ts lands it in the right directory. This isn’t a cosmetic preference. Your schema is the source of truth for your database, and the auth tables are no different from your domain tables: they belong in the same src/db/schema/ folder, under the same version control, migrated by the same pipeline. (The CLI prompts before writing the file. In a scripted setup you’d add --yes to skip the confirmation, but run it interactively the first time so you see what it’s about to do.)

Run it, and your schema directory now looks like this:

• Directorysrc/

  • Directorydb/

    • Directoryschema/

      • index.ts re-exports every table; what @/db/schema resolves to
      • customers.ts existing domain table
      • invoices.ts existing domain table
      • auth.ts generated just now; the four auth tables
    • index.ts the Drizzle client

The new file sits alongside customers.ts and invoices.ts, exactly where it belongs.

Here is the rule that trips people up, placed right at the moment you’d be tempted to break it. Better Auth also ships npx @better-auth/cli migrate. It sounds convenient: it would create the tables in your database directly, skipping the migration step entirely. This stack does not use it. Not once.

The reason is the source-of-truth principle taken seriously. Drizzle Kit owns your migrations, end to end. It produces one ordered migration history that describes every change your database has ever undergone, and that history is only trustworthy if nothing else touches your schema behind its back. Run Better Auth’s migrate and you’d have two tools writing to the same database with two separate, conflicting ideas of its current state. That’s how you get a migration history that lies and a schema that drifts out of sync with what’s checked in. The contract is simple and absolute:

Generate with Better Auth’s CLI. Migrate with Drizzle Kit. Always.

Better Auth writes the table definitions; Drizzle Kit turns those definitions into actual tables. One generator, one migrator, no overlap.

The auth data model at a glance

You’ve generated auth.ts. Before you read it line by line, look at the whole thing from above: the four tables and how they relate. Getting the overall shape into your head first means each table you study next slots into a structure you’ve already seen, instead of arriving as four disconnected lists of columns.

Four tables. session and account both point at user, with many of each per user. verification stands alone, on purpose.

Two things in that picture matter more than any individual column, and it’s worth reading them off the diagram before we get into detail.

First: one user, many account rows. Notice that account points at user with a foreign key, and that it’s a one-to-many relationship: one user can own several account rows. That arrow is the entire account model in a single line. Here’s the idea it encodes, the one I asked you to watch for: a user is who you are, and an account is one way you can prove it. Sign in with email and password, and that’s one account row. Add “Sign in with Google”, and that’s a second account row pointing at the same user. Your identity didn’t fork; you just gained another way to prove it. We’ll make this concrete in code shortly. For now, just see it in the shape: one identity, many proofs.

Second: verification has no line to anything. That’s not an oversight in the diagram; it’s the design. The other three tables are joined into a small family around user, and verification sits apart, connected to nothing. There’s a clear reason for that, but the full payoff comes at the end of the lesson. For now, here’s the seed: a verification row sometimes needs to exist before the user does. Hold that thought, and it’ll fall into place later.

The four tables, column by column

Now the detail. We’ll take the four tables one at a time, and for each one look at the actual code the CLI generated and walk the columns that carry weight. The order is deliberate: user, then session, then account (the one that earns the most space), then verification.

A quick note before the first table, so the column names don’t confuse you. You set casing: 'snake_case' on your Drizzle client back in the smallest table — pgTable and the snake_case bridge. That means the TypeScript reads in camelCase, emailVerified, while the actual Postgres column is snake_case, email_verified. It’s the same column with two spellings, mapped automatically. When you see emailVerified in the schema and email_verified in the migration SQL later, that’s why. I’ll only flag it this once.

And one blanket statement so I don’t repeat it four times: every one of these tables has createdAt and updatedAt timestamp columns that default to the current time. They’re bookkeeping, they’re identical everywhere, and I won’t call them out per table. Assume they’re there.

user: one row per identity

This is the anchor: one row per person, holding the basics of who they are.

export const user = pgTable('user', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
  emailVerified: boolean('email_verified').notNull().default(false),
  name: text('name').notNull(),
  image: text('image'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

The id is a text primary key. The CLI emits text('id') and leaves id generation to the project’s existing default (UUIDv7 via $defaultFn, the same convention your domain tables use). There’s nothing to change; it slots into the convention you already have.

export const user = pgTable('user', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
  emailVerified: boolean('email_verified').notNull().default(false),
  name: text('name').notNull(),
  image: text('image'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

email, unique and not null. The key word is unique, and the constraint is enforced at the database, not just in your app code. A duplicate email doesn’t slip through and create a half-built second account; it hits the constraint and the insert fails. A “check if the email exists, then insert” written in application code can’t fully prevent this, because two requests can both pass the check before either one inserts. The database constraint closes that gap.

export const user = pgTable('user', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
  emailVerified: boolean('email_verified').notNull().default(false),
  name: text('name').notNull(),
  image: text('image'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

emailVerified is a boolean, defaulting to false. This is the gate next chapter’s sign-in reads to decide whether an account is confirmed. Note that it’s a boolean in Better Auth’s model, true or false, not a “verified at” timestamp like some systems use.

export const user = pgTable('user', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
  emailVerified: boolean('email_verified').notNull().default(false),
  name: text('name').notNull(),
  image: text('image'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

name is required, and image is nullable: it’s the profile picture URL an OAuth provider hands you, and you won’t have one for plain email signups. The two timestamps are the bookkeeping pair every table carries.

← Previous 1 / 1 Next →

That’s the whole identity row. Notice what’s not here, because it’s the surprise the next two tables are built around: there is no password column on user. None. If every login tutorial you’ve seen put the password right next to the email, this will look wrong. It isn’t, and by the end of the account table the reason will be clear.

session: one row per active login

Every time someone signs in, a row appears here. Sign out, and it’s gone. This table is the database side of the cookie model you built in sessions versus JWTs, and the cookie that carries them, and one column in particular is the exact thing that lesson was describing.

export const session = pgTable('session', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  token: text('token').notNull().unique(),
  expiresAt: timestamp('expires_at').notNull(),
  ipAddress: text('ip_address'),
  userAgent: text('user_agent'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

userId, the foreign key to user.id with onDelete: 'cascade'. This links the session to its owner. The onDelete: 'cascade' part means deleting the user takes their sessions with it. We’ll cover all the cascades together in a moment, so just note it for now.

export const session = pgTable('session', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  token: text('token').notNull().unique(),
  expiresAt: timestamp('expires_at').notNull(),
  ipAddress: text('ip_address'),
  userAgent: text('user_agent'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

token, unique and not null. This is the opaque session id from sessions versus JWTs, and the cookie that carries them, the random string that travels in the cookie. The cookie carries this token, and on every single request the server looks the session up with SELECT ... FROM session WHERE token = ?. That lookup runs constantly, so the unique index on token isn’t only a correctness guarantee; it’s what makes that hot-path lookup fast. Index quality here carries real weight.

export const session = pgTable('session', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  token: text('token').notNull().unique(),
  expiresAt: timestamp('expires_at').notNull(),
  ipAddress: text('ip_address'),
  userAgent: text('user_agent'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

expiresAt, when the session dies. After this timestamp the token is no longer valid, even if the row still exists. (How long that window is depends on session config, which is the next lesson.)

export const session = pgTable('session', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  token: text('token').notNull().unique(),
  expiresAt: timestamp('expires_at').notNull(),
  ipAddress: text('ip_address'),
  userAgent: text('user_agent'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

ipAddress and userAgent, both nullable, are metadata captured when the session was created. A later “active sessions” screen will render these as “Chrome on macOS, last seen from 81.x.x.x” so a user can spot and revoke a login they don’t recognize. You’re not building that UI here, but these columns are what make it possible.

← Previous 1 / 1 Next →

So a session is a small, cheap, disposable row keyed by a random token. The cookie holds the token; the row holds everything the server needs to know about that login. Delete the row and the login is instantly dead, no matter how many copies of the cookie are floating around. That’s exactly the revocation property you reasoned about last chapter, now backed by a real table.

account: one row per way of signing in

This is the table that carries the central idea of the whole lesson, so we’ll slow down and start with the model before touching a single column.

Picture a real user. She signs up with her email and a password. A month later she clicks “Sign in with Google” because it’s faster. Later still she links her GitHub so she can sign in from work. That’s one person, one identity, one user row, the whole time. But she now has three different ways to prove she’s that person: a password, a Google login, and a GitHub login.

Each of those proofs is its own account row, and all three point back at the same user. That’s the one-to-many from the diagram, made concrete: one user, many accounts. The user row never changes as she gains or drops login methods; only the set of account rows around it does. Now watch how every column falls out of that model.

export const account = pgTable('account', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  accountId: text('account_id').notNull(),
  providerId: text('provider_id').notNull(),
  password: text('password'),
  accessToken: text('access_token'),
  refreshToken: text('refresh_token'),
  // ...other OAuth token columns (idToken, scope, expiresAt)
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

userId, the foreign key to user.id with cascade, is the line that ties the proof back to the identity. It’s the same cascade as session, covered fully in the next section. Every account row points at exactly one user, and one user can be pointed at by many.

export const account = pgTable('account', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  accountId: text('account_id').notNull(),
  providerId: text('provider_id').notNull(),
  password: text('password'),
  accessToken: text('access_token'),
  refreshToken: text('refresh_token'),
  // ...other OAuth token columns (idToken, scope, expiresAt)
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

providerId is the discriminator: the column that says which kind of proof this row is. 'credential' means email-and-password, while 'google', 'github', and 'apple' mean an OAuth login with that provider. When you read an account row, this is the first column you check, because it tells you how to interpret the rest. (accountId, on the line above, holds the provider’s own user id for OAuth rows, or the user’s id for credential rows.)

export const account = pgTable('account', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  accountId: text('account_id').notNull(),
  providerId: text('provider_id').notNull(),
  password: text('password'),
  accessToken: text('access_token'),
  refreshToken: text('refresh_token'),
  // ...other OAuth token columns (idToken, scope, expiresAt)
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

password. The password hash lives here, on account, not on user. This is the answer to the question the user table left open. And it’s nullable, which is the tell: a Google-only account has no password at all, so its password is null, and only the 'credential' row carries a hash. The payoff shows up in everyday operations: “change your password” updates one account row, “link Google” inserts one, “unlink Google” deletes one, and the user row sits perfectly still through all of it. (Better Auth hashes with scrypt before the value ever lands here, so you’ll never store a plaintext password.)

export const account = pgTable('account', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  accountId: text('account_id').notNull(),
  providerId: text('provider_id').notNull(),
  password: text('password'),
  accessToken: text('access_token'),
  refreshToken: text('refresh_token'),
  // ...other OAuth token columns (idToken, scope, expiresAt)
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

The OAuth token block, accessToken, refreshToken, and a few more trimmed from the snippet (idToken, scope, their expiry timestamps), is OAuth bookkeeping. All of it is nullable, and all of it is null for credential accounts. These columns only fill in for OAuth accounts, and only matter when your app needs to call the provider’s API on the user’s behalf later. For now they’re mostly null; ignore them until you need them.

← Previous 1 / 1 Next →

Read that table again with the model in mind and every column has an obvious job. userId says whose proof this is. providerId says what kind of proof it is. password holds the secret for the one kind of proof that has one, and is null for all the others. This decomposition isn’t arbitrary library trivia. It’s the only shape that lets a person pick up and drop login methods without their identity ever moving.

Note

The whole thing in one sentence: user answers who you are, and account answers how you prove it.

verification: short-lived tokens

The last table is the simplest, and it’s the one with no line to anything in the diagram. Now we can explain why.

verification stores short-lived tokens: the ones behind “click this link to verify your email”, “here’s your password-reset link”, magic-link sign-ins, and some OAuth handshake bookkeeping. These rows are short-lived by nature: created, used once, and gone.

export const verification = pgTable('verification', {
  id: text('id').primaryKey(),
  identifier: text('identifier').notNull(),
  value: text('value').notNull(),
  expiresAt: timestamp('expires_at').notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

identifier is what is being verified, usually the email address, or a synthetic key for an OAuth handshake. Here’s the detail to notice, and it’s the payoff of the lone table in the diagram: there is no userId here, no foreign key to user at all. That’s deliberate, and the next paragraph explains why.

export const verification = pgTable('verification', {
  id: text('id').primaryKey(),
  identifier: text('identifier').notNull(),
  value: text('value').notNull(),
  expiresAt: timestamp('expires_at').notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

value is the token itself (or its hash), the secret string embedded in the link you email out. When the user clicks the link, the server looks the row up by this value.

export const verification = pgTable('verification', {
  id: text('id').primaryKey(),
  identifier: text('identifier').notNull(),
  value: text('value').notNull(),
  expiresAt: timestamp('expires_at').notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

expiresAt is what keeps these rows short-lived. A used row gets deleted, and an expired one is ignored and swept up later. The expiresAt filter keeps a stale token from being accepted, and a periodic cleanup keeps old rows from piling up.

← Previous 1 / 1 Next →

Now the question the diagram has been holding open: why does verification have no foreign key to user, when session and account both do?

Because a verification row sometimes needs to exist before the user does. Walk through a sign-up: someone enters their email, and you send a “verify your email” link before their account is fully confirmed. At that instant there may be no user row yet to point at, so the row keys off the identifier (the email) instead of a userId. You can’t add a foreign key to a row that might not exist. Keying by email rather than by user isn’t a shortcut; it’s the only correct choice, because these tokens can precede the very identity they help create. That’s why the table stands alone in the diagram: it’s the one table whose life isn’t tied to a user at all.

Deletion semantics: the foreign-key cascades

You’ve now seen onDelete: 'cascade' twice, on session.userId and on account.userId. Now that you’ve seen all four tables, let’s pull both into one place and make the decision behind them explicit.

A cascade answers a single question: when a parent row is deleted, what happens to the rows that point at it? For sessions and accounts, the answer is “they go too.” Delete a user, and that one statement takes every session they had and every login method they’d linked along with it. No orphaned rows are left behind, and there’s no follow-up cleanup SQL to remember to run. This is the project’s standard for owned children, rows that have no meaning without their parent, applied here to the auth tables. A session belongs to a user, and an account belongs to a user; neither makes any sense once that user is gone, so both follow the user into deletion automatically.

verification is the exception, and by now you can see why without being told: it has no foreign key to user, so there’s nothing to cascade. Its rows aren’t owned by a user, and some of them existed before any user did. Their lifecycle is governed entirely by expiresAt, not by user deletion. It’s the lone table once more, doing exactly what its shape promised.

It helps to picture what the cascade buys you by imagining it gone. If session.userId didn’t cascade, deleting a user would leave their session rows stranded, pointing at a user that no longer exists, cleanable only with hand-written SQL you’d have to remember to run every time. The cascade is what turns “delete this user” from a fiddly multi-step cleanup into a single, safe statement.

Caution

A missing cascade on a userId foreign key is a classic source of orphan rows: dead sessions and dangling account links that accumulate silently and only surface much later as confusing data. When a child row is owned by its parent, the cascade is what keeps deletion honest.

Shipping the first migration

You have the schema file, but it’s only a description: there are still no actual tables in Postgres. Turning a schema description into real tables is exactly what Drizzle Kit does, and you already learned the workflow back in the Drizzle Kit daily loop. There’s no new tooling, just two commands.

pnpm drizzle-kit generate --name add_auth_tables
pnpm drizzle-kit migrate

The first command reads your schema, compares it against the current database state, and writes a SQL migration file describing the difference: here, four CREATE TABLE statements, the unique indexes on user.email and session.token, and the foreign keys. The --name add_auth_tables flag gives that migration a meaningful name instead of a random one, so your migration history reads like a changelog. The second command applies the migration, running the SQL against your database. After it finishes, the four tables exist.

Between those two commands lives a habit worth keeping: read the generated SQL before you apply it. Drizzle Kit is reliable, but reliable isn’t the same as unsupervised. Open the migration file and skim it. You’re checking that what it’s about to do matches what you intended: that a NOT NULL you expected is there, that the unique index on email made it in, that no column got dropped that shouldn’t have. Here’s roughly what you’re skimming:

drizzle/0001_add_auth_tables.sql

CREATE TABLE "user" (
  "id" text PRIMARY KEY NOT NULL,
  "email" text NOT NULL,
  "email_verified" boolean DEFAULT false NOT NULL,
  "name" text NOT NULL,
  -- ...
  CONSTRAINT "user_email_unique" UNIQUE("email")
);

ALTER TABLE "session" ADD CONSTRAINT "session_user_id_user_id_fk"
  FOREIGN KEY ("user_id") REFERENCES "user"("id") ON DELETE cascade;

There’s the camelCase-to-snake_case mapping made real: emailVerified in your TypeScript became email_verified in the SQL. There’s the database-level unique constraint on email. There’s the cascade you walked through, spelled out as ON DELETE cascade. Nothing is surprising, which is the point of looking. You never ship a migration you haven’t read.

Now the part that will save you a frustrating hour later. Run pnpm drizzle-kit migrate now, before you move on. Here’s the failure mode if you skip it: next chapter you’ll build sign-up, call it for the first time, and it’ll fail with relation "user" does not exist. That error looks like the auth library is broken, but it isn’t. It’s Postgres telling you the table was never created. The fix is the migrate step you skipped here. Run it now and that misleading debugging session never happens.

One forward-looking note, then we’ll practice. In production you don’t run migrate by hand: it runs through CI as part of deployment, and changing a table that’s already live in front of users follows a careful expand-migrate-contract sequence. Both of those come much later in the course. For now, on your own machine, the two commands above are the whole story.

Watch video

OrcDev wires the Drizzle adapter, runs the Better Auth CLI to generate the user/account/verification tables, and migrates them with Drizzle Kit (~37 min). Watch the database-setup segment to see this exact generate-then-migrate flow on screen.

Practice: build the account decomposition

Reading the account model is one thing; building the part that carries it is what makes it stick. So you’re going to write the column that is the central idea, the nullable password on account, and wire the cascade that ties a proof back to its identity.

The user and session tables are already written for you in the starter, since deriving those from scratch is the CLI’s job, not yours. The account table is stubbed with the obvious columns in place. Your job is the two pieces that carry the lesson: add the nullable password column, and wire the userId foreign key to user.id with a cascade.

Finish the `account` table so one user can prove their identity multiple ways. Add the nullable `password` column (a password belongs to a login method, not to a person), and wire the `userId` foreign key to `user.id` with `onDelete: 'cascade'`. The requirements check the FK shape; the probes below prove the model holds — one identity carrying several proofs, and deleting that identity sweeping its proofs with it.

Check schema Feedback Reset

Reveal the answer

src/db/schema/auth.ts

export const account = pgTable('account', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  providerId: text('provider_id').notNull(),
  accountId: text('account_id').notNull(),
  password: text('password'),
});

The userId foreign key ties each proof back to its identity, and onDelete: 'cascade' means deleting the user takes their account rows with it. password is plain text('password') with no .notNull(), nullable on purpose, because an OAuth-only account has no password to store.

If you made the password column nullable and put it on account, and the duplicate-email insert was rejected, you’ve built the exact shape that makes this whole system work: one identity, many proofs, and a database that won’t let two people claim the same email.

Recap and what comes next

You connected Better Auth to your Postgres and, along the way, learned the data model the rest of your auth work depends on. Concretely, you now have:

• The schema-aware adapter: drizzleAdapter(db, { provider: 'pg', schema }), resolving tables by reference instead of by name.
• src/db/schema/auth.ts: the four generated tables, living right beside your domain schema, in version control and under the same migration pipeline.
• Four tables in Postgres: user, session, account, and verification, shipped through one Drizzle Kit migration you read before applying.

And the ideas that outlast the syntax:

• One identity, many proofs. A user is who you are, and each account row is one way to prove it. That’s why password is nullable and lives on account, never on user.
• Generate, review, migrate; never hand-author. The CLI writes the tables the library requires, you review the diff like a pull request, and Drizzle Kit, and only Drizzle Kit, migrates.
• Cascades keep deletion honest. Owned children, sessions and accounts, follow their user into deletion. verification stands apart because its rows can outlive, or even predate, any user.

Next chapter, these tables stop being empty. You’ll build email-and-password sign-up and sign-in: the flows that finally write a user row, an account row with a hashed password, and a session row whose token rides home in the cookie. Everything you modeled here is about to start filling up.

External resources

Better Auth — Drizzle adapter

better-auth.com

The schema option, the provider setting, and how the adapter maps to your Drizzle tables, from the source.

Better Auth — CLI

better-auth.com

The generate command, its flags, and when to regenerate as you add plugins.

Better Auth — Database & core schema

better-auth.com

The full field-by-field reference for all four core tables, the canonical contract your generated schema conforms to.

Drizzle ORM — Migrations

orm.drizzle.team

The generate-then-migrate workflow from the Drizzle Kit side, with the schema-as-source-of-truth model spelled out.