Database Package

Database client with connection pooling, transactions, and migrations

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)

Creates a new sqlx database client with connection pooling configured according to the provided Config.

cfg · Config · required

Database configuration containing connection URL, driver, and connection pool settings.

Client · *Client

A configured database client ready for use.

error · error

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

Executes a database operation with a timeout. The timeout duration is configured via Config.MaxQueryTimeout.

ctx · context.Context · required

Parent context for the operation.

op · func(ctx context.Context) error · required

The database operation to execute. It receives a context with timeout applied.

error · error

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

Executes a function within a database transaction. The transaction is automatically committed if the function succeeds, or rolled back if it returns an error or panics.

ctx · context.Context · required

Context for the transaction.

txnOptions · sql.TxOptions · required

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.

error · error

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", "[e-mail]")
    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

Returns the database connection URL.

string · string

The database connection URL as configured.

Host

func (c *Client) Host() string

Returns the database host information extracted from the connection URL.

string · string

The database host (hostname:port).

Close

func (c *Client) Close() error

Closes the database connection and releases all resources.

error · 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 configuration structure.

Driver · string · required

Database driver name (e.g., “postgres”, “mysql”).

URL · string · required

Database connection URL.

MaxIdleConns · int · default: "10"

Maximum number of idle connections in the pool.

MaxOpenConns · int · default: "10"

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

Runs all pending database migrations from an embedded file system. Uses the golang-migrate library under the hood.

config · Config · required

Database configuration for connecting to the database.

embeddedMigrations · fs.FS · required

Embedded file system containing migration files.

resourcePath · string · required

Path within the embedded file system where migration files are located.

error · error

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

Rolls back the last applied migration.

config · Config · required

Database configuration for connecting to the database.

embeddedMigrations · fs.FS · required

Embedded file system containing migration files.

resourcePath · string · required

Path within the embedded file system where migration files are located.

error · error

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.