Schema and the four core tables

Last lesson you wired the auth instance with drizzleAdapter(db, { provider: 'pg' }), telling Better Auth to store its data in your Postgres through Drizzle. One problem: the tables it writes to don’t exist yet. Call sign-up right now and Postgres answers relation "user" does not exist.

Better Auth needs four tables; your app already has a Postgres database with its own domain tables, built earlier with Drizzle. You’ll generate the four, review what the generator produced as if it were a teammate’s pull request, and ship them as your first auth migration through the same Drizzle Kit pipeline you already use.

You’ll also pick up the idea the next chapters rest on: your identity is one row, and every way you can prove that identity is a separate row.

Pass the schema to the adapter

Last lesson’s adapter call was missing one argument:

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

You told the adapter which database driver you’re on, 'pg' for Postgres, but never where your table definitions live. Without that, it falls back to guessing: it looks for tables exported under the exact names user, session, account, and verification. That works until you rename or re-export a table, at which point the adapter silently stops finding it. Hand it your schema instead, and it resolves tables by reference.

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 guesses 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. The adapter now resolves each table by reference, the same way you passed schema to drizzle(...) when you set up db.

import * as schema is a namespace import : it gathers every export of the schema module into one schema object and hands the whole bundle to the adapter, which picks out the tables it recognizes.

Generate the schema, don’t hand-write it

You know Drizzle, so you could open a file and hand-write these four tables yourself. Don’t. Better Auth’s adapter expects specific columns, with specific names and types: its code reads a column called emailVerified. Name yours email_verified_at, or make it a timestamp where the library wants a boolean, and everything compiles, then breaks at runtime with a mismatch that’s miserable to track down. You aren’t modeling your own domain here, where you’d own the column names. You’re conforming to a contract the library already owns.

So 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: the code that reads the columns is the code that wrote them.

“Generated” can sound like a black box, but the output is a normal Drizzle schema file. You read it, edit it, commit it, and migrate it like any file you’d write by hand. It’s generated once, and from then on it lives in version control like everything else.

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

You regenerate whenever the set of plugins changes. Turn on the organizations plugin and the CLI adds member, invitation, and organization tables; add the passkey plugin 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: generate the schema, review the diff, migrate. Never hand-author auth tables.

Generate the tables with the CLI

One command generates the tables:

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

--config src/lib/auth.ts points the generator at your auth instance. It reads that config, sees the Drizzle adapter with provider: 'pg' and whichever plugins you’ve loaded, and writes a Drizzle schema file with exactly the tables those choices require.

--output decides where that file lands. Without it, the CLI writes to a root schema.ts; with --output src/db/schema/auth.ts, the file joins your domain tables. Auth tables are no different from domain tables: same src/db/schema/ folder, same version control, same migration pipeline.

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

Better Auth also ships npx @better-auth/cli migrate, which would create the tables in your database directly and skip the migration step. This stack never uses it.

Drizzle Kit owns your migrations end to end. It keeps one ordered history of every change your database has undergone, and that history is trustworthy only if nothing else writes the schema behind its back. Run Better Auth’s migrate and two tools edit the same database with conflicting ideas of its current state, so the recorded history no longer matches what is checked in.

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

How the four tables relate

You’ve generated auth.ts. Before reading it line by line, see the four tables together, so each one you study next slots into a shape you already know.

session and account each point at user, many per user. verification stands alone.

Two facts in this picture matter more than any single column.

One user, many account rows. A user is who you are; an account is one way to prove it. Email and password is one account row. Adding “Sign in with Google” creates a second account row pointing at the same user. Your identity doesn’t fork; you gain another proof of it.

verification has no foreign key. The other three tables form a small family around user, while verification connects to nothing, by design: a verification row sometimes needs to exist before the user does. We’ll return to why when we reach the verification table.

The four tables, column by column

Now the detail, one table at a time: the code the CLI generated and the columns that carry weight. The order is user, session, account, then verification.

First, why column names differ between the schema and the SQL. You set casing: 'snake_case' on your Drizzle client in pgTable and snake_case, so TypeScript reads emailVerified while the Postgres column is email_verified. Same column, two spellings, mapped automatically.

Every table also has createdAt and updatedAt timestamps that default to the current time. They’re identical bookkeeping everywhere, so assume they’re there; I won’t repeat them per table.

user: one row per identity

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

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. Nothing to change.

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, with the constraint enforced at the database, not just in app code. A “check if the email exists, then insert” written in application code can’t prevent duplicates, because two requests can both pass the check before either inserts. The database constraint closes that gap: the second insert fails instead of creating a half-built second account.

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, a boolean defaulting to false. This is the gate next chapter’s sign-in reads to decide whether an account is confirmed. Better Auth models it as a boolean, not a “verified at” timestamp.

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; image is nullable, the profile picture URL an OAuth provider hands you and that plain email signups won’t have. The two timestamps are the bookkeeping pair every table carries.

← Previous 1 / 1 Next →

That’s the whole identity row, and notice what’s missing: there is no password column on user. If every login tutorial you’ve seen put the password next to the email, this will look wrong. 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 from Sessions vs JWTs.

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, links the session to its owner. The onDelete: 'cascade' means deleting the user takes their sessions with it. We’ll cover all the cascades together in a moment.

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 vs JWTs, the random string that travels in the cookie. On every request the server looks the session up with SELECT ... FROM session WHERE token = ?. That lookup runs constantly, so the unique index on token is both a correctness guarantee and what keeps the hot path fast.

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 invalid even if the row still exists. (How long that window is depends on session config, 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 renders them 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 make it possible.

← Previous 1 / 1 Next →

A session, then, is a small, cheap, disposable row keyed by a random token: the cookie holds the token, the row holds everything the server needs about that login. Delete the row and the login is instantly dead, no matter how many copies of the cookie exist. That’s the revocation property you reasoned about last chapter, now backed by a real table.

account: one row per way of signing in

This table carries the central idea of the lesson, so start with the model before the columns.

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

Each proof is its own account row, and all three point back at the same user: 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. Every column follows from 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 ties the proof back to the identity: a foreign key to user.id with the same cascade as session. 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; 'google', 'github', and 'apple' mean an OAuth login with that provider. Check it first when reading an account row, because it tells you how to interpret the rest. (accountId, on the line above, holds the provider’s user id for OAuth rows, or the user’s own 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. That answers the question the user table left open. And it’s nullable: a Google-only account has no password, 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 still through all of it. (Better Auth hashes with scrypt before the value lands here, so you 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. It’s all nullable and all null for credential accounts. These columns fill in only for OAuth accounts, and only matter when your app calls the provider’s API on the user’s behalf. Ignore them until you need them.

← Previous 1 / 1 Next →

With the model in mind, every column has an obvious job. userId says whose proof this is, providerId says what kind, and password holds the secret for the one kind that has one. This is 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 the only one with no line to anything in the diagram.

verification stores short-lived tokens: the ones behind “click this link to verify your email”, password-reset links, magic-link sign-ins, and some OAuth handshake bookkeeping. Each row is 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. Notice the detail that explains the lone table in the diagram: there is no userId here, no foreign key to user at all. That’s deliberate.

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 keeps these rows short-lived. The filter on it stops a stale token from being accepted; a periodic cleanup sweeps up expired and used rows so they don’t pile up.

← Previous 1 / 1 Next →

So 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. During sign-up, someone enters their email and you send a “verify your email” link before their account is confirmed. At that instant there may be no user row to point at, so the row keys off the identifier (the email) instead. You can’t add a foreign key to a row that might not exist. That’s why the table stands alone in the diagram: its life isn’t tied to a user at all.

Foreign-key cascades on delete

You’ve seen onDelete: 'cascade' on session.userId and account.userId. Here is the decision behind both.

A cascade answers one question: when a parent row is deleted, what happens to the rows that point at it? For sessions and accounts, they go too. Deleting a user removes every session they held and every login method they linked, in one statement, leaving no orphaned rows and no cleanup SQL to remember. This is the rule for owned children, rows that have no meaning without their parent: a session belongs to a user and an account belongs to a user, so both follow the user into deletion.

verification is the exception, and its shape tells you why: with no foreign key to user, there is nothing to cascade. Its rows aren’t owned by a user, and some exist before any user does; their lifecycle is governed by expiresAt, not by user deletion.

Caution

A missing cascade on a userId foreign key is a classic source of orphan rows: dead sessions and dangling account links that pile up silently and 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 tables in Postgres. Drizzle Kit turns that description into real tables, and you learned the workflow back in the Drizzle Kit loop. Two commands, no new tooling.

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

The first command compares your schema against the current database and writes a SQL migration for the difference: here, four CREATE TABLE statements, the unique indexes on user.email and session.token, and the foreign keys. The --name flag labels the migration so your history reads like a changelog. The second command runs that SQL against the database, creating the four tables.

Between the two commands, build one habit: read the generated SQL before you apply it. Open the migration file and skim it, checking that it matches what you intended: the NOT NULL you expected is there, the unique index on email made it in, no column got dropped that shouldn’t have. Here’s roughly what you’re reading:

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;

The mappings you designed are now concrete: emailVerified became email_verified, email carries a database-level unique constraint, and the cascade you chose reads as ON DELETE cascade.

Now the part that saves you a frustrating hour: run pnpm drizzle-kit migrate now, before you move on. Skip it and next chapter your first sign-up call fails with relation "user" does not exist. That looks like a broken auth library, but it’s Postgres telling you the table was never created. Running migrate now means that debugging session never happens.

In production you don’t run migrate by hand; it runs through CI, and changing a live table follows a careful sequence you’ll meet later.

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: finish the account table

The starter gives you user and session already written, since the CLI derives those, and an account table stubbed with its obvious columns. You add the two pieces this lesson is about: the nullable password column, and 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 text('password') with no .notNull(), because an OAuth-only account has no password to store.

A nullable password on account and a rejected duplicate-email insert mean you have the shape that makes the system work: one identity, many proofs, and a database that won’t let two people claim the same email.

Recap and next steps

You now have:

• A schema-aware adapter: drizzleAdapter(db, { provider: 'pg', schema }), which resolves tables by reference, not by name.
• src/db/schema/auth.ts: the four generated tables, in version control beside your domain schema.
• Four tables in Postgres: user, session, account, and verification, applied through one Drizzle Kit migration you read first.

The ideas that outlast the syntax:

• One identity, many proofs. A user is who you are; each account is one way to prove it. That is why password is nullable and lives on account.
• Generate, review, migrate. The CLI writes the tables, you review the diff like a pull request, Drizzle Kit applies it. Never hand-author.
• Cascades keep deletion honest. Sessions and accounts follow their user into deletion; verification stands apart, since its rows can outlive or predate any user.

Next chapter you build email-and-password sign-up and sign-in, the flows that write a user, an account with a hashed password, and a session.

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.