Skip to main content
0 reactions

Better Auth Database Tables Explained: user, session, account and verification

A Terminal window showing psql describing the Better Auth user table, with the columns id, name, email, emailVerified, image, createdAt and updatedAt, its indexes, and the account and session tables that reference it.
The user table, as PostgreSQL describes it.

Better Auth keeps everything it knows about your users in four database tables: user, session, account and verification. This guide shows each table exactly as PostgreSQL describes it, and explains every column in plain words, so you know what’s stored where.

TableOne row isCreated when
usera personsomeone signs up
sessiona signed-in browser or devicesomeone signs in
accountone way a person signs in, such as a password or GitHuba sign-in method is added
verificationa short-lived value, such as an email verification linkBetter Auth sends such a link or code

Prerequisites

  • a Better Auth project whose tables are already created, such as the one from Part 2 of the Better Auth Tutorial
  • its PostgreSQL running in Docker Compose, with a service named db. The commands below use Part 2’s user and database, both named bookshop.

How to Look at a Table

psql, PostgreSQL’s command-line tool, describes a table with \d. This command runs psql inside the database container and describes the user table:

Bash
docker compose exec db psql -U bookshop -d bookshop -c '\d "user"'
  • docker compose exec db runs a command inside the db container.
  • psql -U bookshop -d bookshop connects as the user bookshop to the database bookshop.
  • -c '\d "user"' runs one psql command: describe the table named user. The double quotes matter, because user is also a word with its own meaning in SQL.

Run it from your project folder, and swap user for session, account or verification to see the other tables.

The user Table

One row for each person who signs up:

Plain text
                                 Table "public.user"
    Column     |           Type           | Collation | Nullable |      Default
---------------+--------------------------+-----------+----------+-------------------
 id            | text                     |           | not null |
 name          | text                     |           | not null |
 email         | text                     |           | not null |
 emailVerified | boolean                  |           | not null |
 image         | text                     |           |          |
 createdAt     | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
 updatedAt     | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
Indexes:
    "user_pkey" PRIMARY KEY, btree (id)
    "user_email_key" UNIQUE CONSTRAINT, btree (email)
Referenced by:
    TABLE "account" CONSTRAINT "account_userId_fkey" FOREIGN KEY ("userId") REFERENCES "user"(id) ON DELETE CASCADE
    TABLE "session" CONSTRAINT "session_userId_fkey" FOREIGN KEY ("userId") REFERENCES "user"(id) ON DELETE CASCADE
A Terminal window showing psql describing the Better Auth user table, with the columns id, name, email, emailVerified, image, createdAt and updatedAt, its indexes, and the account and session tables that reference it.
The user table, as PostgreSQL describes it.
  • id: the user’s unique ID, made by Better Auth. Every other table points to a user with this value.
  • name: the display name the person chose.
  • email: the email address used to sign in. The user_email_key index makes it unique, so two people can’t sign up with the same address.
  • emailVerified: true once the person has confirmed their email address.
  • image: an optional picture address, for example from a social sign-in.
  • createdAt and updatedAt: when the row was created and last changed.

Referenced by lists the tables that point to a user. ON DELETE CASCADE means that deleting a user also deletes their sessions and accounts.

The session Table

One row for each signed-in browser or device:

Bash
docker compose exec db psql -U bookshop -d bookshop -c '\d "session"'
Plain text
                             Table "public.session"
  Column   |           Type           | Collation | Nullable |      Default
-----------+--------------------------+-----------+----------+-------------------
 id        | text                     |           | not null |
 expiresAt | timestamp with time zone |           | not null |
 token     | text                     |           | not null |
 createdAt | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
 updatedAt | timestamp with time zone |           | not null |
 ipAddress | text                     |           |          |
 userAgent | text                     |           |          |
 userId    | text                     |           | not null |
Indexes:
    "session_pkey" PRIMARY KEY, btree (id)
    "session_token_key" UNIQUE CONSTRAINT, btree (token)
    "session_userId_idx" btree ("userId")
Foreign-key constraints:
    "session_userId_fkey" FOREIGN KEY ("userId") REFERENCES "user"(id) ON DELETE CASCADE
  • token: the random value in the session cookie. It’s unique, and it’s how Better Auth recognises a signed-in browser.
  • expiresAt: when the session ends, unless Better Auth extends it while it’s used.
  • ipAddress and userAgent: optional details of the device, useful for a “your sessions” page.
  • userId: the user this session belongs to. The session_userId_idx index finds a user’s sessions quickly.

The account Table

One row for each way a person signs in. Someone with a password and a GitHub sign-in has two rows here, but only one in user:

Bash
docker compose exec db psql -U bookshop -d bookshop -c '\d "account"'
Plain text
                                   Table "public.account"
        Column         |           Type           | Collation | Nullable |      Default
-----------------------+--------------------------+-----------+----------+-------------------
 id                    | text                     |           | not null |
 accountId             | text                     |           | not null |
 providerId            | text                     |           | not null |
 userId                | text                     |           | not null |
 accessToken           | text                     |           |          |
 refreshToken          | text                     |           |          |
 idToken               | text                     |           |          |
 accessTokenExpiresAt  | timestamp with time zone |           |          |
 refreshTokenExpiresAt | timestamp with time zone |           |          |
 scope                 | text                     |           |          |
 password              | text                     |           |          |
 createdAt             | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
 updatedAt             | timestamp with time zone |           | not null |
Indexes:
    "account_pkey" PRIMARY KEY, btree (id)
    "account_userId_idx" btree ("userId")
Foreign-key constraints:
    "account_userId_fkey" FOREIGN KEY ("userId") REFERENCES "user"(id) ON DELETE CASCADE
  • providerId: the sign-in method. Email and password accounts use credential; social sign-ins use the provider’s name.
  • accountId: the person’s ID at that provider. For a credential account it’s the user’s own id.
  • password: only for email and password accounts. Better Auth stores a scrypt hash of the password here, never the password itself.
  • accessToken, refreshToken, idToken, scope and the two …ExpiresAt columns: what a social provider returns at sign-in, empty for password accounts.
  • userId: the user this sign-in method belongs to.

The verification Table

Short-lived values that prove something, such as an email verification link or a password-reset code:

Bash
docker compose exec db psql -U bookshop -d bookshop -c '\d "verification"'
Plain text
                           Table "public.verification"
   Column   |           Type           | Collation | Nullable |      Default
------------+--------------------------+-----------+----------+-------------------
 id         | text                     |           | not null |
 identifier | text                     |           | not null |
 value      | text                     |           | not null |
 expiresAt  | timestamp with time zone |           | not null |
 createdAt  | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
 updatedAt  | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
Indexes:
    "verification_pkey" PRIMARY KEY, btree (id)
    "verification_identifier_idx" btree (identifier)
  • identifier: what the value is for. The verification_identifier_idx index looks it up quickly.
  • value: the value to check.
  • expiresAt: when it stops working, so old links and codes can’t be reused.

This table has no userId: Better Auth finds each value by its identifier.

Common Mistakes

MistakeWhat happensDo this instead
Renaming tables or columns by handBetter Auth can’t find themUse the modelName and fields options, then migrate
Changing rows by hand, for example to sign someone outBetter Auth’s own checks and hooks don’t runUse Better Auth’s functions, such as sign-out
Adding your own columns in SQLBetter Auth doesn’t know themDeclare them with additionalFields in lib/auth.ts

Troubleshooting

ProblemCauseFix
psql says Did not find any relation named …The tables aren’t created yet, or the name is misspelledRun npx auth@latest migrate, and check the name
psql shows a screen that ends with (END)The output is wider or longer than your TerminalPress q, or make the window bigger

FAQ

Can I give the tables my own names?

Yes. The modelName and fields options in lib/auth.ts rename tables and columns; your code keeps using Better Auth’s field names.

Do plugins add tables?

Some do, such as two-factor authentication and organizations. Running npx auth@latest migrate after adding a plugin creates them.

Is it safe to read these tables?

Reading is fine. Treat session.token and the account tokens as secrets, and never copy them anywhere public.

Conclusion

user holds people, session holds signed-in devices, account holds the ways each person signs in, and verification holds short-lived links and codes. Every row points back to a user through userId, except verification values.

Additional Resources

Better Auth, Next.js and other names mentioned are trademarks of their respective owners. JD Bots isn’t affiliated with or endorsed by Better Auth, Inc. or Vercel.

0 comments