The db package provides a database client built on top of sqlx with support for connection pooling, transactions, and schema migrations.
Client
New
func New(cfg Config) (*Client, error)
Database configuration containing connection URL, driver, and connection pool settings.
A configured database client ready for use.
Returns an error if the connection URL is invalid or the database connection cannot be established.
Example
package main
import (
"log"
"time"
"github.com/raystack/salt/db"
)
func main() {
cfg := db.Config{
Driver: "postgres",
URL: "postgres://user:pass@localhost:5432/mydb?sslmode=disable",
MaxIdleConns: 10,
MaxOpenConns: 25,
ConnMaxLifeTime: 5 * time.Minute,
MaxQueryTimeout: 30 * time.Second,
}
client, err := db.New(cfg)
if err != nil {
log.Fatalf("Failed to connect to database: %v", err)
}
defer client.Close()
// Use the client...
}
Client Methods
WithTimeout
func (c Client) WithTimeout(ctx context.Context, op func(ctx context.Context) error) error
Config.MaxQueryTimeout.
Parent context for the operation.
op
func(ctx context.Context) error
required
The database operation to execute. It receives a context with timeout applied.
Returns any error from the operation or a timeout error if the operation exceeds the configured timeout.
Example
err := client.WithTimeout(ctx, func(ctx context.Context) error {
_, err := client.ExecContext(ctx, "UPDATE users SET active = true WHERE id = ?", userID)
return err
})
WithTxn
func (c Client) WithTxn(ctx context.Context, txnOptions sql.TxOptions, txFunc func(*sqlx.Tx) error) error
Context for the transaction.
Transaction options including isolation level and read-only mode.
txFunc
func(*sqlx.Tx) error
required
The function to execute within the transaction. It receives the transaction object.
Returns any error from the transaction function, or an error if commit/rollback fails.
Example
import "database/sql"
err := client.WithTxn(ctx, sql.TxOptions{}, func(tx *sqlx.Tx) error {
// Insert user
result, err := tx.ExecContext(ctx,
"INSERT INTO users (name, email) VALUES (?, ?)",
"John Doe", "[email protected]")
if err != nil {
return err
}
userID, _ := result.LastInsertId()
// Insert user preferences
_, err = tx.ExecContext(ctx,
"INSERT INTO preferences (user_id, theme) VALUES (?, ?)",
userID, "dark")
return err
})
ConnectionURL
func (c *Client) ConnectionURL() string
The database connection URL as configured.
Host
func (c *Client) Host() string
The database host (hostname:port).
Close
func (c *Client) Close() error
Returns an error if closing the connection fails.
Configuration
Config
type Config struct {
Driver string
URL string
MaxIdleConns int
MaxOpenConns int
ConnMaxLifeTime time.Duration
MaxQueryTimeout time.Duration
}
Database driver name (e.g., “postgres”, “mysql”).
Maximum number of idle connections in the pool.
Maximum number of open connections to the database.
ConnMaxLifeTime
time.Duration
default:"10ms"
Maximum lifetime of a connection before it’s closed and recreated.
MaxQueryTimeout
time.Duration
default:"100ms"
Maximum duration for query execution when using WithTimeout.
Example Configuration
driver: postgres
url: postgres://user:password@localhost:5432/mydb?sslmode=disable
max_idle_conns: 10
max_open_conns: 25
conn_max_life_time: 5m
max_query_timeout: 30s
Migrations
RunMigrations
func RunMigrations(config Config, embeddedMigrations fs.FS, resourcePath string) error
golang-migrate library under the hood.
Database configuration for connecting to the database.
Embedded file system containing migration files.
Path within the embedded file system where migration files are located.
Returns an error if migrations fail. Returns nil if no migrations are needed or all migrations succeed.
Example
package main
import (
"embed"
"log"
"github.com/raystack/salt/db"
)
//go:embed migrations/*.sql
var migrationFiles embed.FS
func main() {
cfg := db.Config{
Driver: "postgres",
URL: "postgres://user:pass@localhost:5432/mydb?sslmode=disable",
}
err := db.RunMigrations(cfg, migrationFiles, "migrations")
if err != nil {
log.Fatalf("Migration failed: %v", err)
}
log.Println("Migrations completed successfully")
}
RunRollback
func RunRollback(config Config, embeddedMigrations fs.FS, resourcePath string) error
Database configuration for connecting to the database.
Embedded file system containing migration files.
Path within the embedded file system where migration files are located.
Returns an error if rollback fails. Returns nil if no migrations to rollback or rollback succeeds.
Example
err := db.RunRollback(cfg, migrationFiles, "migrations")
if err != nil {
log.Fatalf("Rollback failed: %v", err)
}
Best Practices
Connection Pooling
Configure connection pool size based on your workload:
cfg := db.Config{
MaxIdleConns: 10, // Keep some connections warm
MaxOpenConns: 25, // Limit total connections
ConnMaxLifeTime: 5 * time.Minute, // Recycle connections
}
Transaction Management
Always use WithTxn for multi-statement operations:
err := client.WithTxn(ctx, sql.TxOptions{}, func(tx *sqlx.Tx) error {
// All operations here are atomic
// Automatic rollback on error
return nil
})
Query Timeouts
Use WithTimeout to prevent long-running queries:
err := client.WithTimeout(ctx, func(ctx context.Context) error {
// This query will timeout after MaxQueryTimeout
return client.ExecContext(ctx, query)
})
Migration Files
Organize migrations with sequential numbering:
migrations/
001_create_users_table.up.sql
001_create_users_table.down.sql
002_add_email_index.up.sql
002_add_email_index.down.sql
Supported Databases
The package supports any database driver compatible with database/sql, including:
- PostgreSQL (
github.com/lib/pq)
- MySQL (
github.com/go-sql-driver/mysql)
- SQLite (
github.com/mattn/go-sqlite3)
Migrations are supported for PostgreSQL and MySQL through the golang-migrate library.