Sessions per user
List unexpired sessions with the user and device details.
select s.id, u.email, s."expiresAt", s."ipAddress", s."userAgent"
from session s
join "user" u on u.id = s."userId"
where s."expiresAt" > now()
order by s."expiresAt" desc;Better Auth persists authentication in four core tables. The user table holds identity and profile fields; session rows point at users and carry the token, expiry, and device info; account rows link one authentication method (one provider) to a user with tokens and an optional password for credential logins; verification rows hold one-time values for email verification and password resets. The design detail that matters: account identity is the (providerId, accountId) pair - the local id is only the row identifier you pass to account-management APIs, never the provider identity.
Written by Varun Krishnan · Founder, dbdiagramr
Published · Updated
Source: Better Auth core schema docs v1.7.12 (better-auth.com/docs/concepts/database) · License: MIT
Scroll horizontally to see the full diagram. Click it to open at full size.
Paste this HTML into a README, docs page, or blog post. The image links back to the full schema page.
<a href="https://www.dbdiagramr.space/schema/better-auth">
<img src="https://www.dbdiagramr.space/schemas/better-auth-er-diagram.svg" alt="Better Auth Database Schema Diagram (PostgreSQL)" width="933" height="790" />
</a>
<p><a href="https://www.dbdiagramr.space/schema/better-auth">Better Auth Database Schema Diagram (PostgreSQL)</a> by dbdiagramr</p>| Column | Type | Nullable | Key |
|---|---|---|---|
| user | |||
| id | text | No | PK |
| name | text | No | - |
| text | No | - | |
| emailVerified | boolean | No | - |
| image | text | Yes | - |
| createdAt | timestamptz | No | - |
| updatedAt | timestamptz | No | - |
| session | |||
| id | text | No | PK |
| userId | text | No | FK → user.id |
| token | text | No | - |
| expiresAt | timestamptz | No | - |
| ipAddress | text | Yes | - |
| userAgent | text | Yes | - |
| createdAt | timestamptz | No | - |
| updatedAt | timestamptz | No | - |
| account | |||
| id | text | No | PK |
| userId | text | No | FK → user.id |
| accountId | text | No | - |
| providerId | text | No | - |
| accessToken | text | Yes | - |
| refreshToken | text | Yes | - |
| accessTokenExpiresAt | timestamptz | Yes | - |
| refreshTokenExpiresAt | timestamptz | Yes | - |
| scope | text | Yes | - |
| idToken | text | Yes | - |
| password | text | Yes | - |
| createdAt | timestamptz | No | - |
| updatedAt | timestamptz | No | - |
| verification | |||
| id | text | No | PK |
| identifier | text | No | - |
| value | text | No | - |
| expiresAt | timestamptz | No | - |
| createdAt | timestamptz | No | - |
| updatedAt | timestamptz | No | - |
2 relationships connect the tables in this schema. Each entry shows the child columns, the parent columns they reference, and the cardinality.
PostgreSQL requires double quotes around the reserved table name user and around camelCase columns such as userId and expiresAt.
session.userId → user.id
One user can have many sessions (session.userId -> user.id, required).
account.userId → user.id
One user can have many accounts (account.userId -> user.id, required).
| Relationship | Type | Meaning |
|---|---|---|
| session.userId → user.id | One-to-many | One user can have many sessions (session.userId -> user.id, required). |
| account.userId → user.id | One-to-many | One user can have many accounts (account.userId -> user.id, required). |
These PostgreSQL queries run against the schema shown on this page.
List unexpired sessions with the user and device details.
select s.id, u.email, s."expiresAt", s."ipAddress", s."userAgent"
from session s
join "user" u on u.id = s."userId"
where s."expiresAt" > now()
order by s."expiresAt" desc;Show which sign-in provider each account uses.
select u.email, a."providerId", a."accountId"
from account a
join "user" u on u.id = a."userId"
order by u.email, a."providerId";Find accounts that store a password hash for email/password login.
select u.email, a."providerId"
from account a
join "user" u on u.id = a."userId"
where a.password is not null;Read the one-time values used by email verification and password resets.
select identifier, value, "expiresAt"
from verification
where "expiresAt" > now()
order by "expiresAt";PostgreSQL folds unquoted identifiers to lowercase. user is a reserved keyword, and Better Auth's columns such as userId, createdAt, and expiresAt are camelCase. Always write "user", "userId", and "createdAt" in SQL.
select u.id, u.email, s."userId", s."expiresAt"
from "user" u
join session s on s."userId" = u.id;
If an ORM quotes identifiers for you, it handles this automatically. The Copy as SQL button on this page also quotes the reserved word and camelCase columns.
Paste your PostgreSQL connection string and get an interactive ER diagram of your own schema in under 10 seconds. No signup required.
Try it free →