Use case — Persistent storage (SQL)

What does the OP store, and why does it matter where?

The OP holds rows the OAuth/OIDC specs require to be persistent across restarts:

• Refresh-token chains (RFC 6749 §6, RFC 9700 §4.14) — losing them signs every user out.
• Registered clients (OIDC Dynamic Client Registration 1.0 / RFC 7591 when enabled, or static seeds otherwise) — losing them breaks every RP.
• Sessions (OIDC RP-Initiated Logout 1.0) — preserve browser login state and RP-initiated logout state across restarts.
• Consent grants (OIDC Core 1.0 §3.1.2.4) — losing them re-prompts every user on every restart.
• Audit / introspection / revocation shadow rows — the access-token registry described in Tokens.

The default inmem store loses everything on restart, which is fine for tests and demos but unsafe for production. The library ships op/storeadapter/sql, a database/sql adapter that targets SQLite, MySQL 8.0+, and PostgreSQL 14+. CI tests run against MySQL 8.4 and PostgreSQL 16. MariaDB 10.5+ is expected to work through the MySQL dialect but is not covered by CI tests.

Sources:

• examples/06-sql-store — SQLite quick start (CGO-free).
• examples/07-mysql-store — MySQL with a production-shaped pool, paired with an in-process RP and shipped as a docker-compose stack.
• examples/53-production-sql — the production wiring in one place: schema validation at start-up, key files, reload on SIGHUP, scheduled GC, static-client sync, and an existing users table.

Already have a users table?

The adapter's tables hold protocol records only. Keep your users where they are and hand the OP a projection of them; Bring your own user store shows the wiring, and the code below then passes that projection where it passes storage.UserPasswords().

Why a sub-module

The SQL adapter is published as a separate Go module, so its driver dependencies (the SQL driver, migration libraries) don't pollute your go.sum until you opt in:

go get github.com/libraz/go-oidc-provider/op/storeadapter/sql@latest

The Redis and DynamoDB adapters are separate modules for the same reason.

Architecture

One adapter, one table per substore

The tables carry an oidc_ prefix so they can sit in a schema you already own without colliding with anything. Nothing above them knows the schema exists.

Each substore maps to one table, 25 in all. What you do with them is short: apply every file from Store.Migrations() with your migration tool (see Before the first request on an existing database), and leave the tables your configuration does not use empty. The only change to your own tables is an opaque subject column on your users table, when it lacks one. With your own user store, oidc_users exists but stays empty and needs no sync.

What op.New and ValidateSchema check

• op.New checks, at construction, only the tables your configuration uses, and names every missing one in a single error; run against an empty database, that error lists what the configuration needs.
• The default configuration needs 13 tables. Nine of them are checked in every configuration and may stay empty, because revocation and token checks reach them whatever you configure.
• oidc_users is required only when the adapter's own Users() (or UserPasswords()) is passed to PrimaryPassword.Store, StepEmailOTP.Users or op.WithUserStore. oidc_authn_lockouts is not required when op.WithAuthnLockoutStore names another store.
• Store.ValidateSchema, the deploy gate, checks every table, and the release migrations ALTER the optional tables too.
• One error lists every missing table and column, a line each, and a missing column's line names the migration file that adds it.
• schema/<dialect>/v1.sql is the same complete current schema as a single file, for reference and review.

schema/TABLES.md is the per-table reference. It opens with what to do and the tables a minimal deployment needs, then gives each table's WithNaming key, what it holds, the condition under which op.New checks it, when rows appear, and its columns. It is generated from the adapter source, and the copy inside the module matches the release you run. Choosing the subject has the SQL that adds the subject column.

Revocation writes share the grant's transaction

oidc_grant_revocations and oidc_revoked_jtis back the default RevocationStrategyGrantTombstone, and oidc_opaque_access_tokens fills only when an opaque access-token format is configured. Both groups commit in the same transaction as the grant or refresh write that triggers them, so a half-committed cascade cannot leave a revoked grant next to a still-redeemable token.

A hand-written Store has different rules for which of these substores may be nil; see Bring your own store backend.

Code

import (
  "context"
  databasesql "database/sql"
  _ "modernc.org/sqlite" // or your MySQL / Postgres driver

  "github.com/libraz/go-oidc-provider/op"
  oidcsql "github.com/libraz/go-oidc-provider/op/storeadapter/sql"
)

db, err := databasesql.Open("sqlite", "file:op.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)")
if err != nil { /* ... */ }

storage, err := oidcsql.New(db, oidcsql.SQLite()) // or oidcsql.MySQL() / oidcsql.Postgres()
if err != nil { /* ... */ }

// Run this once from a deploy job, or apply storage.Migrations() with a migration tool (below).
if err := storage.Migrate(context.Background()); err != nil {
  /* ... */
}

provider, err := op.New(
  op.WithIssuer("https://op.example.com"),
  op.WithStore(storage),
  op.WithKeyset(myKeyset),
  op.WithCookieKeys(myCookieKey),
  op.WithLoginFlow(op.LoginFlow{
    Primary: op.PrimaryPassword{Store: storage.UserPasswords()}, // users in oidc_users; or your own projection
  }),
)

Migrate above applies the files from Store.Migrations() and records them in a version table (see Applying migrations with Migrate). With a migration tool of your own, apply the same files through it instead; either way op.New checks the schema when the process starts. Run Store.ValidateSchema (since v1.5) as well in a deploy gate or CI to validate all tables, including those your configuration does not use.

To review the complete current DDL, read op/storeadapter/sql/schema/<dialect>/v1.sql (sqlite, mysql, or postgres), or call storage.Schema() when WithTablePrefix or WithNaming renames tables. The schema check reads the catalog and never creates, alters, or drops anything, so the database role the OP connects with needs no CREATE privilege. It fails when a table is missing (oidcsql: schema for <table> is missing) or when a table lacks a column the reference DDL declares (... is missing required columns: <names>, followed by ; apply migration <file> when a later release added the column). Columns you added yourself are allowed, and column types are not checked. A missing table also adds one closing line on how to create the tables, and it names where the check looked: the schemas of the connection's search_path on PostgreSQL, the default database on MySQL. When table names are customised, that line points at Store.Schema(), because the stock v1.sql creates the default names. So a skipped migration stops the start-up instead of surfacing as a query error under load. One error lists every gap, a line each, so a single run shows the whole migration still to apply.

A MySQL DSN must name a default database; without one every table looks missing.

Applying migrations with Migrate

Store.Migrate(ctx) applies the files from Store.Migrations() in order and records its progress in a version table, <prefix>schema_migrations (oidc_schema_migrations by default; WithNaming renames it through the schema_migrations key). The table has the layout golang-migrate uses, so a team that moves to golang-migrate later points x-migrations-table=oidc_schema_migrations at it and continues without migrate force. A run on a database that is already current changes nothing, and it ends with ValidateSchema.

Call it once from a deploy job, not from every replica's startup, and pass a context long enough for the DDL: on MySQL one migration alters every table. Concurrent calls are serialised by an advisory lock keyed the same way as golang-migrate's, so Migrate and golang-migrate exclude each other. Through pgbouncer, use session pooling or a direct connection, because transaction pooling does not hold an advisory lock.

• Existing tables, no version table. When the adapter's tables are there and match the current release, Migrate records that release and changes nothing else. When they do not, it changes nothing and its error gives the INSERT that records the release the schema is at, or names the migrations to apply first.
• A table of your own under the adapter's name. When a table Migrate would create already exists without the adapter's columns, for example an application table that shares the prefix, Migrate refuses before it executes any statement. Rename the adapter's tables with WithTablePrefix or WithNaming, then run it again.
• A dirty version row. On MySQL, DDL cannot be rolled back, so a statement that fails mid-file leaves the version row dirty and Migrate stops. Check which statements of the named file took effect, finish or undo them by hand, then run the UPDATE from the error (or migrate force N) and call Migrate again. PostgreSQL and SQLite apply each file in one transaction, so a failure leaves the database as it was.
• MySQL does not need multiStatements=true for Migrate; it runs the statements one at a time.
• PostgreSQL keeps the version table and the lock in the first schema of the connection's search_path. Keep the OP's tables in that schema.
• WithTablePrefix("") makes Migrate refuse, because the version table would be golang-migrate's default schema_migrations, which an application's own history usually occupies. Apply Migrations() with your tool in that case.

goose, Flyway and Atlas apply Migrations() with their own history table, as before; Migrate does not read or write those. Schema() returns the complete current DDL as a string for review. The schema files are embedded from op/storeadapter/sql/schema/.

Before the first request on an existing database

The SQL lives in op/storeadapter/sql/schema/migrations/<dialect>/, one file per release in golang-migrate's form: 000001_v1.0.0.up.sql creates the schema v1.0.0 shipped and each later file up to 000006_v1.5.0.up.sql applies one release's changes. schema/MIGRATIONS.md explains what each release changes. Store.Migrations() (since v1.5) returns the files for the configured dialect as an fs.FS, with the WithTablePrefix / WithNaming table names applied:

src, err := iofs.New(storage.Migrations(), ".")
if err != nil { /* ... */ }
// x-migrations-table keeps the OP's version history apart from the application's.
databaseURL := "postgres://user:pass@db/app?x-migrations-table=oidc_schema_migrations"
m, err := migrate.NewWithSourceInstance("iofs", src, databaseURL)
if err != nil { /* ... */ }
err = m.Up()

The OP's files and your application's migrations are both numbered from 1, so a tool that records them in one table sees the second set as already applied or out of order. Give the OP its own version table (and its own directory, for tools that read files from disk):

• golang-migrate: the x-migrations-table URL parameter above, or Config.MigrationsTable when you build the driver with WithInstance. migrate force N then targets that table only and leaves the application's history alone.
• goose: keep the OP's files in a directory of their own and run goose -table oidc_goose_db_version ....
• Flyway: a separate table and separate locations for the OP's files.

schema/MIGRATIONS.md has the details, including Atlas.

• An existing database is first recorded at the last release it applied, then takes only the later files. With golang-migrate, a database created by v1.3.0 runs migrate force 4 against the OP's version table, after which migrate up applies v1.4.0 onwards.
• A database created by Migrate already has the version table, so golang-migrate pointed at oidc_schema_migrations continues from the recorded version without migrate force.
• A database created from v1.sql or Schema() carries the newest schema but no version record. Calling Migrate records it, or record it at the newest migration yourself (migrate force N, where N is the highest file number, on the OP's own version table). Otherwise a tool replays the files from the first one.
• MySQL: each file holds several statements. golang-migrate enables multi-statement execution by itself for a mysql:// URL; a *sql.DB passed to mysql.WithInstance needs multiStatements=true in its DSN. Migrate itself does not need it.
• Atlas reads the files with format=golang-migrate once atlas migrate hash has written atlas.sum.
• goose and Flyway read files from disk, so write them with op-schema export (below): goose files open with -- +goose Up, and Flyway files are named V<N>__vX_Y_Z.sql.
• Any other tool: apply the files in numeric order. There are no down files.

op-schema export writes the same files without a database connection, in the layout of golang-migrate, goose or Flyway, with the table prefix and naming you pass applied:

go run github.com/libraz/go-oidc-provider/op/storeadapter/sql/cmd/op-schema export \
  --dialect postgres --format goose --out db/oidc \
  [--prefix auth_] [--naming clients=auth_clients]...

--dialect is mysql, postgres or sqlite. --format is golang-migrate (the default), goose or flyway. --prefix takes the value of WithTablePrefix and defaults to oidc_, and --naming key=name takes one WithNaming entry and may be repeated. A name the adapter would reject fails the command.

On PostgreSQL in a schema other than public, the adapter resolves tables through the connection's search_path, and the schema check follows the same path as the queries. Put the OP's schema in search_path on the DSN or connection (for example search_path=oidc). Names passed to WithTablePrefix and WithNaming are plain identifiers, never schema-qualified. Index names embed the table name, so a long prefix or name that pushes an index name past the identifier length limit fails at construction.

The schema check names the file to apply when it finds a column missing, and fails on a table whose reference index is missing, matching by leading column under any index name. Migration 000006_v1.5.0 adds the previous_secret_hash, previous_secret_expires_at and five tls_client_auth_* columns to the clients table, creates the ssf_streams and ssf_events tables, and adds idx_oidc_sessions_subject on the sessions table's subject column, so deleting a subject's sessions does not scan the table; op.New names the index when it is missing. On MySQL and MariaDB indexes are the part an existing database cannot pick up on its own: they are declared inside CREATE TABLE, so an existing table gains them only from the migration files, which Migrate or your tool applies. The same files carry the MODIFY ... COLLATE utf8mb4_bin statements that identifier columns need on MySQL and MariaDB. MySQL's default collation ignores case and accents, so without them PutUserWithPassword("Alice", ...) overwrites the row alice already owns and GetClient("MYAPP") resolves myapp. The check fails when an identifier column's collation is not utf8mb4_bin or utf8mb4_0900_bin, naming the column and the file. A MODIFY fails when two existing values collide under the binary collation, and the error names the rows to reconcile first. PostgreSQL and SQLite compare bytes already.

Do not rely on running v1.sql to upgrade an existing database; Migrate or the migration files do that.

Two rollout rules follow from the columns:

• MFA tables. Do not run old and current writers against oidc_totp_secrets and oidc_email_otps together. An old writer omits row_version from its updates and bypasses the compare-and-swap protocol. Drain old writers, apply and validate the columns, then start only current writers.
• Authorization-state and revocation columns. Old and current writers may run side by side once the columns exist: an old binary leaves authorization_recorded at zero and the current binary resolves such a row from its grant. One difference remains: an old node does not read access_revoked_at, so until every node runs the current binary, a JWT access token revoked through a single refresh-token revocation still verifies on the old nodes.

The storage-maintenance page gives the full rollout order.

Renaming the tables

The adapter's bundled tables are named oidc_clients, oidc_refresh_tokens, and so on. If you are grafting the OP onto a database that already owns a clients table — or your house style forbids the oidc_ prefix — oidcsql.WithTablePrefix and oidcsql.WithNaming rewrite the physical table names of the OP-internal record kinds. The adapter validates every physical name against the SQL standard identifier grammar, rewrites the embedded DDL, and builds every query against the renamed tables, so Schema() / Migrate() and the runtime queries stay in lockstep. Store.Migrations() and op-schema export apply the same names to the migration files.

To replace the oidc_ prefix on every table at once, use oidcsql.WithTablePrefix:

storage, err := oidcsql.New(db, oidcsql.Postgres(), oidcsql.WithTablePrefix("auth_"))
// oidc_clients becomes auth_clients, oidc_refresh_tokens becomes auth_refresh_tokens, ...

To rename individual tables, pass WithNaming, which overrides the prefixed default for the kinds it lists. Two logical tables resolving to the same physical name are refused at construction, and the comparison ignores case, so Auth_Clients and auth_clients collide:

storage, err := oidcsql.New(db, oidcsql.Postgres(), oidcsql.WithNaming(map[string]string{
  "clients":        "auth_clients",
  "refresh_tokens": "auth_refresh_tokens",
  "authorization_codes": "auth_codes",
  // ...rename as many as you like; unlisted kinds keep their oidc_ default.
}))

The map keys are logical record kinds, not physical names. oidcsql.New accepts these 25 keys:

Area	Logical keys
Clients and grants	clients, grants, authorization_codes, refresh_tokens
Access tokens	access_tokens, opaque_access_tokens
Revocation	grant_revocations, revoked_jtis
Browser-facing state	sessions, interactions
PAR and replay markers	par_records, consumed_jtis
Users	users
Dynamic registration	initial_access_tokens, registration_access_tokens
Provider metadata	op_metadata
Device flow and CIBA	device_codes, ciba_requests
Authenticator factors	totp_secrets, passkeys, recovery_codes, email_otps, authn_lockouts
Shared Signals	ssf_streams, ssf_events

WithNaming also accepts the key schema_migrations, which names the version table Store.Migrate records in. An unknown key makes oidcsql.New fail fast, so a typo is caught at construction time rather than at the first query.

Every resolved physical table name must be distinct. If two logical stores map to the same table, or an override collides with an unlisted default table name, oidcsql.New fails at construction time. The schema rewrite replaces the default oidc_ table names in the embedded DDL as substrings, longest name first, in a single pass. That is how the index names, which embed the table name (idx_oidc_access_tokens_expires_at), follow a renamed table.

Source: examples/25-byo-table-names renames all 25 tables under an auth_ prefix and logs them back from sqlite_master to prove the rewrite took effect.

Table names only, not column names

WithTablePrefix and WithNaming rewrite table names. The column layout is fixed — the adapter owns it. If you need custom column names too (an existing schema you cannot reshape, encrypted columns, a shared table), implement the store interfaces yourself instead of using the bundled adapter. See Bring your own store backend.

MySQL pool sizing

examples/07-mysql-store demonstrates a production-shaped DSN:

db, err := databasesql.Open("mysql",
  "oidc:secret@tcp(mysql:3306)/op?parseTime=true&charset=utf8mb4&loc=UTC")
db.SetMaxOpenConns(50)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(30 * time.Minute)
db.SetConnMaxIdleTime(5 * time.Minute)

The adapter does not tune the pool, so the limits are yours to choose.

Keep charset=utf8mb4 so 4-byte UTF-8 (emoji, CJK extensions in display names) round-trips through claim values without truncation.

The DSN's connection collation does not replace the explicit utf8mb4_bin column definitions. On an existing MySQL or MariaDB database, apply the MODIFY statements from the migration section separately.

Username + password credentials

The SQL adapter implements store.UserPasswordStore (the same surface the inmem reference adapter exposes) so the built-in op.PrimaryPassword Step works against SQL with no glue code:

flow := op.LoginFlow{
  Primary: op.PrimaryPassword{Store: storage.UserPasswords()},
}

provider, err := op.New(
  /* ... */
  op.WithLoginFlow(flow),
)

The schema adds two columns on oidc_users: a unique username lookup index (used by FindByUsername) and a PHC-encoded password_hash column (read by ReadPasswordHash). Hash encoding stays in the embedder's hands — the convenience writer *sql.Store.PutUserWithPassword(ctx, user, username, hash) accepts a hash produced by op.HashPassword (argon2id with the library defaults) and round-trips through the same upsert as PutUser:

hash, _ := op.HashPassword("demo")
_ = storage.PutUserWithPassword(ctx, &store.User{
  Subject: "demo-user",
  Claims:  map[string]any{"name": "Demo User"},
}, "demo", hash)

Passing an empty username and nil hash clears the credential — useful when a user migrates to passkey-only. ReadPasswordHash returns store.ErrNotFound both when the subject is unknown and when the row exists but carries no password, so the login orchestrator surfaces an enumeration-safe response either way.

oidc_users is the adapter's own users table, and it stays empty when your users live somewhere else. The store.Store given to op.WithStore is never read for users: the adapter's table is used only when you pass its Users() to op.WithUserStore (for a flow with no PrimaryPassword, such as passkey-only) or its UserPasswords() to op.PrimaryPassword. Point op.PrimaryPassword{Store: ...} at your own store.UserPasswordStore and the OP reads claims from that store as well; the bundled table is then never written. See Bring your own user store.

Session durability

The SQL adapter's session store reports that sessions survive a restart, so op.New derives the session-durability posture (the label on the back-channel logout bcl.no_sessions_for_subject audit event) as durable without op.WithSessionDurabilityPosture. A SQLite :memory: database is not durable; use it for development only. Declare the posture explicitly to override the derivation.

Contract test harness

The same contract test suite (op/store/contract) that exercises inmem runs against the SQL adapter under go test -tags=testcontainers, spinning up real MySQL / Postgres engines via testcontainers-go. So when the library says "the SQL adapter implements Store," it means against a real engine, not a mock. The pinned images (mysql:8.4, postgres:16-alpine) match the engine matrix the docker-compose stacks under examples/07-mysql-store and examples/09-redis-volatile use, so adapter-level and example-level integration share a single matrix.

Schedule retention GC

The SQL adapter does not delete every expired row in the request path. Schedule (*oidcsql.Store).GC(ctx, cutoff) from the application's cron, leader-elected worker, or other maintenance scheduler:

cutoff := time.Now().UTC().Add(-15 * time.Minute) // retain a short forensic grace window
stats, err := storage.GC(ctx, cutoff)
if err != nil {
  return err
}
log.Printf("oidc GC removed %d rows", stats.Total())

One GC call sweeps nine tables and returns an oidcsql.GCStats value with a count for each: expired authorization codes, PAR records, interactions, sessions, refresh-token rotation history, access-token registry rows, opaque access tokens, grant revocations (tombstones and the revoked-jti denylist), and consumed jti markers (ConsumedJTIs). One replica is enough; sweeps running concurrently on several are safe because the DELETEs are idempotent and only repeat each other's work. Never put it behind an HTTP route. Each sweep deletes a table's expired rows in one statement, so run the first sweep over a large backlog off-peak. The refresh sweep retains history until no refresh token under the grant is live (a per-grant anti-join, so it is the expensive one on a large table), and clears sealed retry-response blobs once the predecessor's own expiry has passed even when the row itself is retained; clearing a blob does not increase a row count. A cutoff in the past keeps a grace window, while time.Now() reclaims everything already expired. The adapter starts no background GC goroutine and owns no timer, so schedule and monitor the call yourself. To loop on a goroutine of your own, storage.RunGC(ctx, time.Hour, report) (since v1.5) sweeps once immediately, then every interval until ctx ends, and hands each sweep's GCStats and error to report. Publish GCStats, duration, errors, and remaining table sizes so a stalled sweep is visible. The access-token, opaque-token, and grant-revocation substores keep their own GC methods for a caller who wants one table on its own schedule; device-code and CIBA rows are evicted on their insert paths. The other tables are not swept: email OTP codes are overwritten per subject, Shared Signals events are deleted by that feature's worker, and clients, users, factor records, lockout counters and registration tokens end through their store's own delete.

When to add Redis on top

Hot data (interactions, and sessions if you accept re-login after a cache flush) churns fast and bloats the durable DB if you put it there. The next page, Hot/cold + Redis, shows how to route the volatile substores to Redis while keeping the durable substores on SQL.