Database connections

Set the database connection in your environment file. App assembly opens one Bun pool and shares it with the repositories and database drivers that need it.

DATABASE_DRIVER=postgres
DATABASE_URL=postgres://app:password@127.0.0.1:5432/app?sslmode=disable

The credentials above are local placeholders. Use verified TLS and appropriate credentials for remote connections. Configuration is loaded centrally; repositories do not read environment variables.

Choose a driver#

For independent MySQL components, use its native DSN:

DATABASE_DRIVER=mysql
DATABASE_URL=app:password@tcp(127.0.0.1:3306)/app

The connection package enables MySQL time parsing, UTC decoding, and a UTC session time zone. Other driver options, including TLS options, remain available.

Changing DATABASE_DRIVER does not translate existing SQL or migrate data. Keep one chosen engine for an application's migration history. The shipped app rejects a mixed migration dialect even for CLI help. A MySQL application requires porting or replacing the installed modules' queries and SQL; it is not an alternative quickstart profile. The required integration gate verifies PostgreSQL and Redis; optional engines need their own verification.

Use the app's connection#

App assembly opens a database when DATABASE_URL is nonempty. Database cache requires that connection. With no URL and database cache disabled, a.resources.DB and a.resources.Transactions remain nil; Redis tasks and local events do not require SQL independently. Serving the installed business modules still requires PostgreSQL.

Inside the owning module’s register.go, inject its borrowed pool into the repository:

catalogRepo, err := catalogrepository.NewProductRepository(deps.DB)
if err != nil {
    return fmt.Errorf("app: catalog repository: %w", err)
}

This fragment assumes the repository from Models and queries has been added and imported as catalogrepository. Pass the repository to the owning service, then pass that service to package registration. Do not open another pool per request or module.

The app owns the pool and closes it after its consumers stop. Repositories and the database cache driver borrow it.

Configure the pool#

These are the current application defaults:

DATABASE_NAME=
DATABASE_MAX_OPEN_CONNS=20
DATABASE_MAX_IDLE_CONNS=5
DATABASE_CONN_MAX_LIFETIME=30m
DATABASE_CONN_MAX_IDLE_TIME=5m
DATABASE_CONNECT_TIMEOUT=5s
DATABASE_PING_TIMEOUT=5s
DATABASE_SLOW_QUERY_THRESHOLD=500ms

DATABASE_NAME is a diagnostic name; the DSN selects the actual database. Opening a pool includes a ping and closes the pool if initialization fails.

Budget connections across every process and replica. Ten replicas with a limit of 20 can open 200 connections, before migration tools and other services are counted. More connections are not automatically more throughput.

Connection and ping timeouts do not limit query duration. Give each operation a bounded context:

ctx, cancel := context.WithTimeout(ctx, 3*time.Second)
defer cancel()
product, err := repository.Get(ctx, id)

This fragment belongs in a caller that owns the operation deadline. Pass the derived context onward, handle the returned error, and use the returned product.

Open a standalone pool#

Use pkg/database directly for tooling that does not use app assembly. This complete function borrows its caller's configuration and context, verifies access, and closes its own pool:

package probe

import (
	"context"
	"errors"

	"github.com/wisalgroup/labena/pkg/database"
)

func Check(ctx context.Context, cfg database.Config) (err error) {
	db, err := database.Open(ctx, cfg)
	if err != nil {
		return err
	}
	defer func() { err = errors.Join(err, db.Close()) }()
	var value int
	return db.NewRaw("SELECT 1").Scan(ctx, &value)
}

Start with database.DefaultConfig() and set its DSN before calling. Open returns *bun.DB. OpenSQL returns a native *sql.DB for SQL-only callers. The Bun pool's db.DB is its underlying SQL pool, not a second connection owner; close the owner once.

A literal database.Config with MaxIdleConns: 0 disables idle retention. DefaultConfig supplies five idle connections. Private in-memory SQLite databases use one retained connection so their schema survives between calls.

Query logs#

Bun queries resolve logging.FromContext(ctx) at execution time, preserving request and job fields. Successful queries log metadata at DEBUG, slow queries at WARN, and failures at ERROR. Cancellation is DEBUG; deadline expiry is WARN. Missing rows are not logged as database failures.

Set the desired channel’s Options.Level to slog.LevelDebug in internal/app/config/observability.go to include successful queries.

DATABASE_SLOW_QUERY_THRESHOLD=500ms

The hook records operation, duration, and database name. It omits SQL text, bound values, credentials, and server error text. Queries through the underlying db.DB bypass Bun hooks. Returned driver errors may still contain sensitive detail; translate them before sending responses.

To inspect SQL for a request, open its database span in Grafana and read db.query.text. Tracing captures sanitized query structure, with values replaced by ? and comments removed; enabling DEBUG does not add SQL to logs. See request SQL in Grafana for navigation, supported queries and capture limits.

See Transactions before adding multi-query workflows, and Migrations before creating tables.

Verify a connection and its schema#

After configuring the intended database:

go run ./cmd/cli migrate status
go run ./cmd/cli migrate up

The first command inspects migration state; the second changes schema. A successful connection ping proves access to the server, not that every required table or permission exists. Exercise a representative request after applying migrations.

For deployment sizing, reserve connections for migrations, administration, and other applications too. Readiness dependency checks are bounded; they do not replace query-level error handling or recovery. Continue with entities and queries, database tests, and observability.