Database

The sqlDB package uses database/sql with PostgreSQL and instruments operations through the SDK monitoring layer.

Configure and initialize

SQL_DB_HOST=localhost
SQL_DB_PORT=5432
SQL_DB_NAME=app
SQL_DB_USER=postgres
SQL_DB_PASSWORD=postgres
SQL_DB_SSL_MODE=disable

colibri.InitializeApp()
sqlDB.Initialize()

Initialize() opens and checks the connection, configures the pool, and registers graceful shutdown.

Query one item

user, err := sqlDB.NewQuery[User](
	ctx,
	"SELECT id, name, email FROM users WHERE id = $1",
	id,
).One()
if err != nil {
	return nil, err
}
if user == nil {
	return nil, ErrUserNotFound
}

One() returns nil, nil when no row is found.

Query multiple items

users, err := sqlDB.NewQuery[User](
	ctx,
	"SELECT id, name, email FROM users WHERE active = $1",
	true,
).Many()

Values are mapped by column and field order. Select only the columns you need and keep their order compatible with the struct.

Execute a statement

err := sqlDB.NewStatement(
	ctx,
	"UPDATE users SET name = $1 WHERE id = $2",
	name,
	id,
).Execute()

Pagination

pageRequest := types.NewPageRequest(
	1,
	20,
	[]types.Sort{types.NewSort(types.ASC, "name")},
)

page, err := sqlDB.NewPageQuery[User](
	ctx,
	pageRequest,
	"SELECT id, name, email FROM users WHERE active = $1",
	true,
).Execute()

users := page.Items
total := page.TotalItems

caution

The sort field is added to the generated SQL. Map user input to an allowlist of known columns instead of accepting arbitrary values.

Migrations and pool

Set SQL_DB_MIGRATION=true and MIGRATION_SOURCE_URL=./migrations to run golang-migrate up migrations during initialization.

Variable	Default
SQL_DB_MAX_OPEN_CONNS	10
SQL_DB_MAX_IDLE_CONNS	3

For atomic operations, see Transactions.