Postgres Adapter
Persist AuthInGo users, access sessions, and refresh sessions in PostgreSQL with the built-in adapter.
Postgres Adapter
The PostgreSQL adapter implements the AuthInGo Store interface using Go's database/sql package. It is designed to work with the pgx driver.
Install
go get github.com/binit2-1/authingo/adapters/postgres
go get github.com/jackc/pgx/v5/stdlibUsage
package main
import (
"context"
"database/sql"
"log"
"os"
"time"
"github.com/binit2-1/authingo"
"github.com/binit2-1/authingo/adapters/postgres"
_ "github.com/jackc/pgx/v5/stdlib"
)
func main() {
db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
if err != nil {
log.Fatal(err)
}
ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
defer cancel()
if err := postgres.ApplySchema(ctx, db); err != nil {
log.Fatal(err)
}
adapter := postgres.NewAdapter(db)
auth := authingo.New(authingo.Options{
Store: adapter,
})
_ = auth
}Database Schema
For local development and demos, postgres.ApplySchema(ctx, db) runs the
adapter's idempotent setup statements for you.
For production applications, run the schema through your normal migration tool before starting the app.
CREATE TABLE IF NOT EXISTS users (
id VARCHAR(255) PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
email_verified BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS sessions (
id VARCHAR(255) PRIMARY KEY,
user_id VARCHAR(255) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
token VARCHAR(255) UNIQUE NOT NULL,
refresh_token VARCHAR(255) UNIQUE NOT NULL,
refresh_expires_at TIMESTAMPTZ NOT NULL,
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(token);
CREATE INDEX IF NOT EXISTS idx_sessions_refresh_token ON sessions(refresh_token);
CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_sessions_refresh_expires_at ON sessions(refresh_expires_at);Schema source
The same schema lives in adapters/postgres/schema.sql in the repository and
is exported as postgres.SchemaSQL for tooling that wants to read it from Go.
Adapter behavior
| Method | Behavior |
|---|---|
CreateUser | Inserts an AuthInGo user record. |
GetUserByEmail | Finds a user for email/password sign in. |
CreateSession | Persists a new opaque session token and refresh token. |
GetSession | Loads an unexpired session and joins the associated user by access token. |
DeleteSession | Deletes a session by access token or refresh token. |
RefreshSession | Atomically rotates access and refresh token values when given a valid refresh token. |
CleanupExpiredSessions | Deletes expired sessions in bounded batches. |
Cleanup
AuthInGo starts a daily cleanup loop when you call authingo.New. The PostgreSQL adapter implements cleanup with:
WITH expired AS (
SELECT id
FROM sessions
WHERE expires_at < NOW() OR refresh_expires_at < NOW()
LIMIT 1000
)
DELETE FROM sessions
WHERE id IN (SELECT id FROM expired);You can also call the adapter method yourself from a job if you prefer to own scheduling.
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
if err := adapter.CleanupExpiredSessions(ctx); err != nil {
log.Printf("cleanup failed: %v", err)
}Operational notes
- Use connection pooling settings appropriate for your application.
- Keep
emailunique to prevent duplicate signups. - Keep the token and expiration indexes in place for fast lookups, refresh-token rotation, and bounded cleanup.
- Use
postgres.ApplySchemafor local development, demos, and simple starts. - Run migrations with your normal migration tool for production releases.