Documentation
¶
Overview ¶
Package sql manages PostgreSQL connections instrumented with OpenTelemetry, the service migrations and the testing databases.
Open opens a connection pool and checks the database is reachable, the returned DB embeds *sqlx.DB and must be closed with Close to release its connections and metrics:
db, err := sql.Open(ctx, "postgresql://user:password@host/db[?options]")
if err != nil {
return err
}
defer db.Close()
Migrate applies the migrations of a service embedded in the binary (the db directory by default):
//go:embed db var migrations embed.FS err := sql.Migrate(ctx, connection, "users", migrations)
Queryable is implemented by DB and *sqlx.Tx, a storage can run its queries either on the database or in a transaction (see DB.WithTx):
func insertUser(ctx context.Context, q sql.Queryable, u User) error {
_, err := q.NamedExecContext(ctx, `INSERT INTO users (id, name) VALUES (:id, :name)`, u)
return err
}
err := db.WithTx(ctx, func(tx *sqlx.Tx) error {
return insertUser(ctx, tx, u)
})
TestingDB creates a database dedicated to a test.
Index ¶
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func Migrate ¶
func Migrate(ctx context.Context, connection, service string, fsys fs.FS, opts ...MigrateOption) (err error)
Migrate applies the migrations of the service read from fsys: by default it looks at the currently active migration version of the service and migrates all the way up (applying all up migrations) with the files of the db directory (generally assets/db). Use WithVersion to migrate up or down to a given version and WithMigrationsPath to read the migrations from another directory.
The service migration version is stored in the <service>_schema_migrations table.
The migration runs on its own connection opened from the given connection string (see Open for the format) and closed once done.
When ctx is done, Migrate stops before starting the next migration file (a running migration is not interrupted, leaving the database in a clean state) and returns the ctx error: the migrations applied so far are kept, the migration can be resumed by calling Migrate again.
Types ¶
type DB ¶
DB is a pool of database connections instrumented with OpenTelemetry (traces and metrics).
It embeds *sqlx.DB, all the sqlx methods (GetContext, SelectContext, BeginTxx, ...) are available. A DB must be created with Open and closed with Close to release its metrics.
func Open ¶
Open opens a database connection pool based on the connection string and checks that the database is reachable, it returns an error if the database cannot be pinged before ctx is done.
The connection string should follow this format postgresql://user:password@host/db[?options]
The database must be closed with Close to release its connections and metrics.
func (*DB) Close ¶
Close closes the database connections and releases its metrics. Closing an already closed database is a no-op.
func (*DB) StatusCheck ¶
StatusCheck returns nil if it can successfully talk to the database. It returns a non-nil error otherwise.
type MigrateOption ¶
type MigrateOption func(*migrateOptions)
MigrateOption defines a migration option.
func WithMigrationsPath ¶
func WithMigrationsPath(path string) MigrateOption
WithMigrationsPath defines the directory of the migration files in the fs.FS, db by default.
func WithVersion ¶
func WithVersion(version uint) MigrateOption
WithVersion migrates up or down to the given version instead of applying all the up migrations.
type Option ¶
type Option func(*options)
Option defines a SQL option.
func WithMaxConnIdleTime ¶
WithMaxConnIdleTime defines the maximum amount of time a connection may be idle.
func WithMaxConnLifeTime ¶
WithMaxConnLifeTime defines the maximum amount of time a connection may be reused.
func WithMaxIdleConns ¶
WithMaxIdleConns defines the maximum number of connections in the idle connection pool.
func WithMaxOpenConns ¶
WithMaxOpenConns defines the maximum number of open connections to the database.
func WithStatementCacheMode ¶
WithStatementCacheMode defines how the queries are sent to the database, it sets the pgx default_query_exec_mode: cache_statement, cache_describe, describe_exec, exec or simple_protocol.
The pgx v4 statement cache modes are also supported: prepare (cache_statement) and describe (cache_describe). Use exec or simple_protocol when connecting through a pooler not supporting prepared statements (eg: pgbouncer).
type Queryable ¶
type Queryable interface {
sqlx.ExtContext
sqlx.PreparerContext
GetContext(ctx context.Context, dest any, query string, args ...any) error
SelectContext(ctx context.Context, dest any, query string, args ...any) error
QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
NamedExecContext(ctx context.Context, query string, arg any) (sql.Result, error)
PreparexContext(ctx context.Context, query string) (*sqlx.Stmt, error)
PrepareNamedContext(ctx context.Context, query string) (*sqlx.NamedStmt, error)
}
Queryable includes the context aware methods shared by DB, sqlx.DB and sqlx.Tx, allowing a function to run its queries either on the database or in a transaction.
It is a sqlx.ExtContext, it can be used with the sqlx helpers (eg: sqlx.NamedQueryContext).
type TestingDB ¶
type TestingDB struct {
// DB is the connection to the testing database, nil until Open succeeds.
*DB
// DSN is the connection string of the testing database.
DSN string
}
TestingDB is a database dedicated to a test, created by Open and dropped by Close. The zero value is ready to use.
It connects by default to localhost:5432 with username "postgres" and password "secret" (the database of kit/docker-compose.yaml), the environment variable TESTINGDB_URL can be used to control the connection settings.
Example usage:
func TestMe(t *testing.T) {
var tdb sql.TestingDB
require.NoError(t, tdb.Open(t.Context()))
t.Cleanup(func() { assert.NoError(t, tdb.Close()) })
// use the testing database connection
err := tdb.StatusCheck(t.Context())
// or connect to it with the generated DSN
db, err := sql.Open(t.Context(), tdb.DSN)
}
Source Files