Database

What it is for

gas/database owns an app’s SQL connection pool. Its *database.Service implements gas.DatabaseProvider, the core interface services inject to reach the database, along with gas.Service, gas.HealthReporter and gas.ReadyReporter. It has two backends:

Mode	Backend	Use it for
ModeSQL (sql, the default)	database/sql, with a driver you register	PostgreSQL or SQLite, or another database through WithConnector
ModePgx (pgx)	a native *pgxpool.Pool	PostgreSQL, when you want pgx types, batching, or sqlc’s pgx output

DB() returns a *sql.DB in both modes; in pgx mode it is derived from the pool through pgx’s stdlib adapter. On top of the pool the module adds transaction helpers, connection retries with exponential backoff, and health checks. Schema migrations are a separate module, gas/migrate, because plenty of apps want a database without them.

Install

go get github.com/gasmod/gas/database

Gas does not choose a database/sql driver for you. The module imports github.com/jackc/pgx/v5/stdlib, so the pgx driver name is always registered. For SQLite, import a driver that registers the name sqlite, such as modernc.org/sqlite. The default driver name is postgres, which only a third-party driver such as lib/pq registers; with no such driver imported, set database.driver to pgx.

Wiring

main.go

app := gas.NewApp(
  // database.New() resolves both of these from the container.
  gas.WithServiceInstance[gas.ConfigProvider](cfg),
  gas.WithSingletonService[gas.Logger](gaslog.NewSlogLogger()),

  // Registered under the interface services inject, not *database.Service.
  gas.WithSingletonService[gas.DatabaseProvider](database.New()),
)

database.New(opts...) returns a constructor that the container calls with gas.ConfigProvider and gas.Logger, so both must be registered. The constructor never returns an error and does no I/O; the connection is opened in Init, when the app starts.

Caution

Register the constructor under gas.DatabaseProvider. The container matches by exact type, so registering it as *database.Service leaves every gas.DatabaseProvider dependency unresolved, including the one migrate.New() takes, and startup fails. See Services and DI.

The settings normally come from the gas.ConfigProvider under the database key; see Configuration. To pass them from code instead, such as a DSN fetched from a vault at runtime, use WithConfig:

// explicitConfig takes a DSN known only at runtime, such as one built from a
// secret fetched from a vault. WithConfig skips binding, so every setting the
// app needs is set here.
func explicitConfig(dsn string) gas.Option {
  // Start from DefaultConfig so Mode and the pool settings are populated.
  cfg := database.DefaultConfig()
  cfg.Database.Driver = database.DriverPgx
  cfg.Database.DSN = dsn
  cfg.Database.ConnRetries = 5

  return gas.WithSingletonService[gas.DatabaseProvider](
    database.New(database.WithConfig(cfg)),
  )
}

Usage

Queries

A service takes gas.DatabaseProvider as a constructor parameter. Query and Exec run against the pool; the gas.Rows and gas.Result they return are the *sql.Rows and sql.Result from database/sql. For anything else, such as QueryRowContext or a prepared statement, use DB().

notes/service.go

type Notes struct {
  db gas.DatabaseProvider
}

func NewNotes(db gas.DatabaseProvider) *Notes {
  return &Notes{db: db}
}

func (n *Notes) Name() string                { return "notes" }
func (n *Notes) Init(context.Context) error  { return nil }
func (n *Notes) Close(context.Context) error { return nil }

func (n *Notes) Titles(ctx context.Context) (titles []string, err error) {
  rows, err := n.db.Query(ctx, "SELECT title FROM notes ORDER BY id")
  if err != nil {
    return nil, err
  }
  defer func() { err = errors.Join(err, rows.Close()) }()

  for rows.Next() {
    var title string
    if err := rows.Scan(&title); err != nil {
      return nil, err
    }
    titles = append(titles, title)
  }
  return titles, rows.Err()
}

Transactions

WithTx runs a function in a transaction. It commits when the function returns nil and rolls back when it returns an error or panics.

// Move commits both updates or neither. WithTx commits when the function
// returns nil and rolls back when it returns an error or panics.
func (n *Notes) Move(ctx context.Context, noteID, toFolder int64) error {
  return n.db.WithTx(ctx, nil, func(tx *sql.Tx) error {
    res, err := tx.ExecContext(ctx,
      "UPDATE notes SET folder_id = ? WHERE id = ?", toFolder, noteID)
    if err != nil {
      return err
    }
    if count, err := res.RowsAffected(); err != nil {
      return err
    } else if count == 0 {
      return fmt.Errorf("note %d not found", noteID)
    }

    _, err = tx.ExecContext(ctx,
      "UPDATE folders SET note_count = note_count + 1 WHERE id = ?", toFolder)
    return err
  })
}

BeginTx starts a transaction you manage yourself: you call Commit or Rollback on the returned *sql.Tx. See Transaction rules for rollback and commit failures.

pgx mode

Set database.mode to pgx and the service builds a *pgxpool.Pool from the DSN alone; database.driver is ignored. With the environment provider, two variables select it:

DATABASE__MODE=pgx
DATABASE__DSN=postgres://app@localhost:5432/app?sslmode=disable

The wiring is the same database.New() as in Wiring.

The pool is reachable two ways. Pool() on *database.Service returns it, or nil in sql mode. A service that holds only a gas.DatabaseProvider calls database.PoolFrom(provider), which returns the pool and true, or nil and false when the provider is not pgx-backed, so the caller can fall back to DB(). WithPgxTx is the pgx counterpart of WithTx:

// Archive uses the native pgx pool when the provider has one, and falls back
// to database/sql otherwise.
func (n *Notes) Archive(ctx context.Context, noteID int64) error {
  if _, ok := database.PoolFrom(n.db); ok {
    // PoolFrom reported a pool, so the provider is a pgx-mode
    // *database.Service, which also has the native transaction helper.
    svc, ok := n.db.(*database.Service)
    if !ok {
      return errors.New("pgx pool without *database.Service")
    }
    return svc.WithPgxTx(ctx, nil, func(tx pgx.Tx) error {
      _, err := tx.Exec(ctx, "UPDATE notes SET archived = true WHERE id = $1", noteID)
      return err
    })
  }

  _, err := n.db.Exec(ctx, "UPDATE notes SET archived = true WHERE id = $1", noteID)
  return err
}

sqlc

sqlc generates a DBTX interface in its own output package. In database/sql output it is satisfied by *sql.DB and *sql.Tx; in pgx output, by *pgxpool.Pool and pgx.Tx. Pass DB(), the pool from PoolFrom, or a transaction straight into the generated New. This module declares no DBTX of its own.

Custom connections

WithConnector hands connection setup to a driver.Connector, for custom TLS, IAM authentication tokens, or a driver that is not on the list.

// withConnector builds the connection through pgx with a hook that runs
// before every new connection, for example to fetch a short-lived IAM token.
func withConnector(dsn string, token func(context.Context) (string, error)) (gas.Option, error) {
  connCfg, err := pgx.ParseConfig(dsn)
  if err != nil {
    return nil, err
  }

  connector := stdlib.GetConnector(*connCfg, stdlib.OptionBeforeConnect(
    func(ctx context.Context, c *pgx.ConnConfig) error {
      pw, err := token(ctx)
      if err != nil {
        return err
      }
      c.Password = pw
      return nil
    },
  ))

  return gas.WithSingletonService[gas.DatabaseProvider](
    database.New(database.WithConnector(connector)),
  ), nil
}

Set database.driver to the dialect the connector speaks, because gas/migrate picks its SQL dialect from that name. See Connectors.

Testing

There is no mock package for gas.DatabaseProvider. Run the real service against SQLite instead: it needs no server, and the code under test goes through the same pool, transactions and errors as in production.

notes/service_test.go

// newTestDB starts the real database service on a SQLite file in a temporary
// directory. A file, not ":memory:", because every pooled connection to an
// in-memory SQLite database opens a separate, empty database.
func newTestDB(t *testing.T) gas.DatabaseProvider {
  t.Helper()

  cfg := config.New()
  cfg.Set("database.driver", database.DriverSQLite)
  cfg.Set("database.dsn", filepath.Join(t.TempDir(), "test.db"))

  w := gas.NewWorker(
    gas.WithServiceInstance[gas.ConfigProvider](cfg),
    gas.WithSingletonService[gas.Logger](gaslog.NewSlogLogger()),
    gas.WithSingletonService[gas.DatabaseProvider](database.New()),
  )
  if err := w.Start(t.Context()); err != nil {
    t.Fatal(err)
  }
  t.Cleanup(func() {
    if err := w.Shutdown(context.Background()); err != nil {
      t.Error(err)
    }
  })

  db, err := w.ServiceContainer().Resolve[gas.DatabaseProvider]()
  if err != nil {
    t.Fatal(err)
  }
  return db
}

func TestMoveRollsBackOnMissingNote(t *testing.T) {
  db := newTestDB(t)
  ctx := t.Context()

  for _, stmt := range []string{
    "CREATE TABLE folders (id INTEGER PRIMARY KEY, note_count INTEGER NOT NULL)",
    "CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT, folder_id INTEGER)",
    "INSERT INTO folders (id, note_count) VALUES (1, 0)",
  } {
    if _, err := db.Exec(ctx, stmt); err != nil {
      t.Fatal(err)
    }
  }

  notes := NewNotes(db)
  if err := notes.Move(ctx, 42, 1); err == nil {
    t.Fatal("Move of a missing note succeeded")
  }

  var count int
  if err := db.DB().QueryRowContext(ctx,
    "SELECT note_count FROM folders WHERE id = 1").Scan(&count); err != nil {
    t.Fatal(err)
  }
  if count != 0 {
    t.Errorf("note_count = %d after a rolled-back move, want 0", count)
  }
}

Use a file DSN, not :memory:: with a pool, each connection to an in-memory SQLite database opens a separate, empty database. For migrations in tests, see Migrations; for testing services in general, see Testing.

Configuration

Unless WithConfig is given, Init binds the settings from the gas.ConfigProvider under the database key, over DefaultConfig(), so a key you do not set keeps its default. Keys are case-insensitive; see Config for how keys map to environment variables and files.

Key	Type	Default	Description
database.mode	string	sql	sql or pgx
database.driver	string	postgres	database/sql driver name: postgres, pgx or sqlite. Sql mode only; not checked when a connector is set
database.dsn	string		Connection string. Required unless a connector is set
database.maxopenconns	int32	25	Maximum open connections, 1 to 1000
database.maxidleconns	int	5	Maximum idle connections, 1 to 1000 and at most maxopenconns. Sql mode only
database.connmaxlifetime	duration	30m	Maximum time a connection is reused, 1s to 2h
database.connmaxidletime	duration	5m	Maximum time a connection sits idle, 1s to 1h and at most connmaxlifetime
database.connretries	int	0	Connection retries after the first failed attempt; 0 fails at once
database.connretryinterval	duration	2s	Wait before the first retry, doubled after each

The rules on mode, maxopenconns, connmaxlifetime and connmaxidletime are validate tags, checked when the config is bound, so a configuration given with WithConfig skips them. The other rules, on the driver name, the DSN, the connector’s mode, and (in sql mode) maxidleconns, are checked in Init for any configuration, bound or given. A rule that does not hold fails Init, and the app does not start.

Errors

The package defines no sentinel errors. Every error from an operation starts with the service name, gas/database, and wraps the driver’s error with %w, so errors.Is and errors.As reach it:

Error	When
gas/database: config binding: ...	Binding or validating the bound config failed
gas/database: invalid configuration: ...	A rule checked in Init failed; the message names the field, such as Database.DSN
gas/database: unknown mode "..."	Init with a Mode other than sql, pgx or empty, possible only through WithConfig
gas/database: parse pgx config: ...	In pgx mode, the DSN could not be parsed
gas/database: open: ..., gas/database: pgxpool: ..., gas/database: ping: ...	A connection attempt failed
gas/database: query: ..., gas/database: exec: ...	Query or Exec failed
gas/database: failed to begin transaction: ...	BeginTx or WithTx could not start a transaction
gas/database: failed to begin pgx transaction: ...	BeginPgxTx or WithPgxTx could not start a transaction
gas/database: service is closed	Query, Exec, BeginTx, WithTx, BeginPgxTx or WithPgxTx after Close
gas/database: not initialized	Query, Exec, BeginTx or WithTx before Init
gas/database: attempt to begin pgx transaction in non-pgx mode ...	BeginPgxTx or WithPgxTx in sql mode

Known issue

CheckHealth and CheckReady are the exception: they report a closed service as database: closed, and CheckHealth reports an uninitialized one as database: not initialized, without the gas/ prefix (KI-043). See Known issues.

Behavior details

The sections above cover everyday use. This section is the full contract, for when an exact edge case matters.

Options

New takes two options:

• WithConfig(cfg) is for configuration that cannot come from there, such as a DSN built or fetched at runtime (a secret from a vault) or a test. It replaces the whole configuration and skips binding from the gas.ConfigProvider. Start from database.DefaultConfig(), because a bare &database.Config{} leaves Mode empty and every pool limit at zero.
• WithConnector(c) supplies a driver.Connector, opened with sql.OpenDB instead of sql.Open(driver, dsn). See Connectors.

Transaction rules

• When the function returns an error, WithTx rolls back and returns that error. A rollback that itself fails is joined onto it with errors.Join.
• When the function panics, WithTx rolls back and panics again with the original value. A failed rollback is logged at error level instead of returned.
• A rollback that finds the transaction already finished (sql.ErrTxDone, because the function committed or rolled back itself) is not an error.
• A commit failure is returned wrapped: gas/database: failed to commit transaction: <cause>.
• opts is passed to BeginTx as given; nil uses the driver’s defaults.

BeginTx starts a transaction you manage yourself: you call Commit or Rollback on the returned *sql.Tx.

pgx transactions and pool limits

maxopenconns, connmaxlifetime and connmaxidletime are applied to the pgx pool. maxidleconns is not, because pgxpool manages idle connections itself.

BeginPgxTx and WithPgxTx are the pgx counterparts of BeginTx and WithTx, on pgx.Tx. They are methods of *database.Service, not of the interface, and both return an error outside pgx mode. Nil options use pgx’s defaults. WithPgxTx follows the same commit, rollback, panic and already-finished rules as WithTx (here the finished error is pgx.ErrTxClosed), and in addition:

• it rolls back on a context detached from cancellation, so the rollback still runs when the caller’s context is already done;
• it commits with the caller’s context unchanged, so an already-canceled context fails the commit instead of persisting work the caller abandoned.

Connectors

With a connector, database.driver and database.dsn are not required and the driver name is not checked. A connector is only accepted in sql mode; in pgx mode Init fails rather than ignore it.

Driver() reports database.driver in sql mode, whether or not a connector is set, and pgx in pgx mode. gas/migrate picks its SQL dialect from that name, so set database.driver to the dialect the connector speaks (mysql, for example) when you use one.

Connection retries

Init opens the connection and pings it, with a 5-second timeout per attempt. When the attempt fails and connretries is above zero, it waits connretryinterval, retries, and doubles the wait after each failure, for at most connretries retries; an interval of zero or less uses 2 seconds. Each retry is logged at warn level. Init blocks while it waits.

The ping and the waits run under the context Init receives, so when that context ends during a wait, Init stops and returns an error wrapping both the context’s error and the last connection error. When every attempt fails, Init returns gas/database: connect failed after <n> retries: <last error>. With the default of zero retries, the first failure is returned as is.

Lifecycle

Close marks the service closed and closes the *sql.DB and, in pgx mode, the pool. After Close, Query, Exec, BeginTx, WithTx, BeginPgxTx and WithPgxTx return an error without touching the connection. The service starts no goroutines of its own; database/sql and pgxpool manage their connections in the background until Close.

Known issue

In pgx mode, when closing the *sql.DB fails, Close returns that error before it closes the pool, so the pool stays open (KI-038). See Known issues.

Health and readiness

CheckHealth (liveness) fails only when the service is closed or not initialized, states a restart would fix. It does not ping: both database/sql and pgxpool reconnect on their own, so a lost connection is not a reason to restart the process. CheckReady pings the database with the caller’s context, so while the database is unreachable the instance reports not ready and traffic drains off it instead of the process being killed. Both results appear, under the name gas/database, in the maps the app’s CheckHealth and CheckReady return; see Deployment.

API reference

pkg.go.dev/github.com/gasmod/gas/database