database

Database Module

This module stores device sessions for the auth-middleware project in PostgreSQL. User accounts are owned by your application; this module only references them by email.

Requirements

• PostgreSQL 14+
• An existing users table whose email column is a PRIMARY KEY or has a UNIQUE constraint, so a foreign key can reference it

Installation

go get github.com/lucap9056/auth-middleware/database/v2

Usage

import "github.com/lucap9056/auth-middleware/database/v2"

userRef, err := database.WithUserEmailReference("users(email)")
if err != nil {
	return err
}

db, err := database.NewDatabase(dsn,
	database.WithAutoCreateSchema(true),
	userRef,
)
if err != nil {
	return err
}
defer db.Close()

Using an Existing Connection Pool

If your application already has a *sql.DB connected to the same PostgreSQL, use New instead:

sqlDB, err := sql.Open("pgx", dsn)
if err != nil {
	return err
}
defer sqlDB.Close()

db, err := database.New(sqlDB, database.WithAutoCreateSchema(true))
if err != nil {
	return err
}
defer db.Close()

• The *sql.DB must use the pgx stdlib driver
• The caller owns the *sql.DB: Close() only stops the cleanup worker
• Connection pool options (WithMaxOpenConns, WithMaxIdleConns, WithConnMaxLifetime, WithConnMaxIdleTime) are ignored

Options

User Email Reference

Format: [<schema>.]<table>(<column>)[:<type>]

The reference is case-insensitive and normalized to lowercase. <type> must match the type of the referenced column.

Auto Create Schema

When enabled, NewDatabase and New create the schema, which requires DDL privileges. When disabled, create the schema yourself using schema.Generate, or with the SQL below.

Schema

CREATE TABLE IF NOT EXISTS auth_user_devices (
    device_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    device_name TEXT NOT NULL,
    user_email TEXT NOT NULL,
    secret TEXT NOT NULL,
    generation INT NOT NULL DEFAULT 1,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_auth_user_devices FOREIGN KEY (user_email)
        REFERENCES users (email)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_auth_user_devices_user_email ON auth_user_devices(user_email);
CREATE INDEX IF NOT EXISTS idx_auth_user_devices_updated_at ON auth_user_devices(updated_at);

API