pg-user-service

pg-user-service is a reusable Go module for PostgreSQL connection pools, migrations, user records, and Argon2id password hashing. The Fiber/templ administrator application is isolated in cmd/demo.

Use as a library

Add a released version to another Go project:

Environment Variables

DATABASE_URL is required to connect to PostgreSQL container.

DATABASE_URL=postgres://user:password@localhost:5432/databse_name
PORT=8080
DEMO_ENABLE_SQL_CONSOLE=false

Replace user, password, and database_name with your PostgreSQL DB credentials.

docker compose up -d will start a PostgreSQL container with the default credentials in .env.example.

The demo application will connect to this database.

go get github.com/whalelogic/pg-user-service@v1.0.0

Configure and open a database pool:

ctx := context.Background()
pool, err := db.Open(ctx, db.Config{
    DSN: os.Getenv("DATABASE_URL"),
})
if err != nil {
    return err
}
defer pool.Close()

if err := db.MigrateUserSchema(ctx, pool); err != nil {
    return err
}

The reusable packages are:

• config: loads DATABASE_URL from the environment.
• db: connection pooling, health checks, transactions, and migrations.
• models: shared data types.
• password: Argon2id password hashing and verification.
• users: user creation, listing, password updates, and authentication.

Product direction

pg-user-service is a PostgreSQL service foundation for modern Go applications. It is deliberately SQL-first and pgx-native: applications keep control of their schema and queries, while this module makes the operationally hard parts predictable.

It is not an ORM, a query generator, or an instant API server. Those are excellent but distinct products. The goal here is a compact, composable layer that teams can trust in a production Go service:

• Explicit over magical: typed configuration, context.Context, plain SQL, and pgx types remain visible at the API boundary.
• Safe by default: parameterized operations, password hashing, actionable PostgreSQL errors, and migration drift detection protect common failure paths.
• Operationally ready: connection lifecycle, health checks, transactions, migrations, tracing hooks, and diagnostics work without coupling callers to an HTTP framework or logging stack.
• Easy to adopt and test: small packages, stable contracts, embedded migrations, and integration-test helpers fit into existing Go services without a generated-code toolchain.

Foundation roadmap

The product will grow in the order that earns production trust:

1. Trust layer: locking migrations with checksum verification, typed PostgreSQL error classification, safe transaction options, and reusable pagination primitives. Migration checksums are implemented.
2. Operational layer: optional OpenTelemetry hooks, pool and query diagnostics, readiness helpers, and test utilities for PostgreSQL-backed services.
3. Service building blocks: focused optional modules such as user accounts, auditing, and outbox/event support. These remain independent of the generic database package.
4. Excellent reference app: the demo shows polished, conventional service UX without becoming a dependency or an attempt to replace a product admin console.

Library specification

Scope and boundaries

• Packages outside cmd/ are the reusable library surface.
• db provides generic PostgreSQL primitives and must not depend on HTTP, Fiber, templ, a particular application schema, or environment loading.
• users, models, and password provide optional reusable user-account features and must remain separate from the generic db package.
• cmd/demo is an example consumer only. Its web UI, static assets, and development dependencies are not required by projects importing the library.

Supported database capabilities

The library will provide and maintain:

• Connection-pool configuration, opening, health checks, and lifecycle support.
• Context-aware transactions, including configurable transaction options.
• Ordered SQL migrations with locking and applied-migration tracking.
• PostgreSQL error classification for common constraint failures.
• Reusable pagination and query helpers where they have no application-specific assumptions.

Migration tracking includes SHA-256 checksums so modified migrations are detected before environments drift.

API and security requirements

• Exported functions, types, and behavior are a supported public contract.
• All database operations accept context.Context and return actionable errors.
• The library must never log credentials, plaintext passwords, or password hashes.
• Password helpers use Argon2id and store hashes only.
• Application configuration is supplied explicitly through typed values; the library must not require a web framework or a specific deployment runtime.

Testing and releases

• Unit tests cover validation, error handling, and public package behavior.
• Integration tests run against PostgreSQL and cover migrations, transactions, constraints, and PostgreSQL-specific types.
• CI runs root-module tests, demo-module tests, vulnerability scanning, and integration tests before release.
• Releases follow semantic versioning. Use v0.x while the public API evolves; publish v1.0.0 once the core API is stable. Breaking exported-API changes require a new major version.

Demo application

The demo is intentionally separate from the library and has its own Go module, web dependencies, templates, and Docker Compose configuration.

docker compose -f cmd/demo/docker-compose.yml up -d
cp .env.example .env
# Set DATABASE_URL in .env to match your PostgreSQL instance.
source .env
cd cmd/demo
npm install
npm run build:css
go run .

The demo provides an unauthenticated user administration page at /users with create, edit, password-update, and delete flows. Its /admin page browses application tables. Set DEMO_ENABLE_SQL_CONSOLE=true only for a local development database to enable the privileged SQL console. It is an example integration, not a dependency of consuming projects. npm run build:css generates a minified stylesheet at static/css/app.css, which the Go binary embeds and serves locally. Run npm run watch:css while developing templates.

Demo UX review

The demo is a focused reference implementation: it should look credible and show how the library supports useful, polished applications. It is not a production administrator console. Its most important usability gaps and next improvements are:

• Progressive disclosure: The list should make users and primary actions easy to scan, while a focused detail view handles complex profile, password, and deletion work. Addressed in the current demo.
• Readable records: Alternating row shades, stable integer IDs, and concise date-time values make it easier to compare records. Addressed in the current demo.
• Clear feedback: Successful mutations and validation errors should appear in the interface rather than as generic response pages.
• Finding records: Pagination, sorting, and search are needed before the table can be useful with more than a small number of users.
• Safe administration: Authentication, CSRF protection, authorization, and an audit trail are required before exposing administration outside local development. Destructive operations should retain explicit confirmation.
• Operational visibility: A production tool should surface connection health, migration status, and actionable database errors without exposing sensitive values.

TODO

• Load DATABASE_URL through a typed configuration package and provide .env.example; .env is ignored so credentials are never committed.
• Require administrator authentication before allowing access to /users and other admin routes.