Skip to main content
The db package provides a wrapper around sqlx for database connection management, connection pooling, and database migrations using golang-migrate.

​
Features

  • Connection Pooling: Configurable connection pool settings
  • Query Timeouts: Built-in timeout management for queries
  • Transaction Support: Safe transaction handling with automatic rollback
  • Migrations: Embedded migration support using golang-migrate
  • Multiple Drivers: Support for PostgreSQL, MySQL, and more

​
Installation

go get github.com/raystack/salt/db

​
Quick Start

package main

import (
    "context"
    "log"
    
    "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: time.Minute * 5,
        MaxQueryTimeout: time.Second * 10,
    }
    
    client, err := db.New(cfg)
    if err != nil {
        log.Fatal("Failed to connect to database:", err)
    }
    defer client.Close()
    
    // Use the client
    var count int
    err = client.Get(&count, "SELECT COUNT(*) FROM users")
    if err != nil {
        log.Fatal(err)
    }
    
    log.Printf("Total users: %d", count)
}

​
Configuration

​
Config

type Config struct {
    Driver          string        `yaml:"driver" mapstructure:"driver"`
    URL             string        `yaml:"url" mapstructure:"url"`
    MaxIdleConns    int           `yaml:"max_idle_conns" mapstructure:"max_idle_conns" default:"10"`
    MaxOpenConns    int           `yaml:"max_open_conns" mapstructure:"max_open_conns" default:"10"`
    ConnMaxLifeTime time.Duration `yaml:"conn_max_life_time" mapstructure:"conn_max_life_time" default:"10ms"`
    MaxQueryTimeout time.Duration `yaml:"max_query_timeout" mapstructure:"max_query_timeout" default:"100ms"`
}
Example configuration file: 
database:
  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: 10s

​
Client

​
Creating a Client

func New(cfg Config) (*Client, error)
Creates a new database client with connection pooling. Example: 
client, err := db.New(db.Config{
    Driver: "postgres",
    URL:    "postgres://localhost:5432/mydb",
    MaxOpenConns: 25,
    MaxIdleConns: 10,
})
if err != nil {
    log.Fatal(err)
}
defer client.Close()

​
Client Methods

​
WithTimeout

func (c Client) WithTimeout(ctx context.Context, op func(ctx context.Context) error) error
Executes an operation with the configured query timeout. Example: 
err := client.WithTimeout(ctx, func(ctx context.Context) error {
    _, err := client.ExecContext(ctx, "UPDATE users SET active = true")
    return err
})

​
WithTxn

func (c Client) WithTxn(ctx context.Context, txnOptions sql.TxOptions, txFunc func(*sqlx.Tx) error) error
Executes a function within a transaction with automatic rollback on error. Example: 
err := client.WithTxn(ctx, sql.TxOptions{}, func(tx *sqlx.Tx) error {
    _, err := tx.Exec("INSERT INTO users (name) VALUES (?)", "Alice")
    if err != nil {
        return err
    }
    
    _, err = tx.Exec("INSERT INTO audit_log (action) VALUES (?)", "user_created")
    return err
})

if err != nil {
    // Transaction was rolled back
    log.Printf("Transaction failed: %v", err)
}

​
ConnectionURL

func (c *Client) ConnectionURL() string
Returns the database connection URL. 
url := client.ConnectionURL()

​
Host

func (c *Client) Host() string
Returns the database host. 
host := client.Host()

​
Close

func (c *Client) Close() error
Closes the database connection. 
defer client.Close()

​
Queries

The client embeds *sqlx.DB, so all sqlx methods are available:

​
Get (Single Row)

type User struct {
    ID    int    `db:"id"`
    Name  string `db:"name"`
    Email string `db:"email"`
}

var user User
err := client.Get(&user, "SELECT * FROM users WHERE id = $1", 1)

​
Select (Multiple Rows)

var users []User
err := client.Select(&users, "SELECT * FROM users WHERE active = $1", true)

​
Exec (Insert/Update/Delete)

result, err := client.Exec(
    "INSERT INTO users (name, email) VALUES ($1, $2)",
    "Alice",
    "[email protected]",
)
if err != nil {
    log.Fatal(err)
}

id, _ := result.LastInsertId()
rowsAffected, _ := result.RowsAffected()

​
Named Queries

type UserFilter struct {
    MinAge int    `db:"min_age"`
    City   string `db:"city"`
}

filter := UserFilter{MinAge: 18, City: "New York"}

var users []User
query, args, err := client.BindNamed(
    "SELECT * FROM users WHERE age >= :min_age AND city = :city",
    filter,
)
if err != nil {
    log.Fatal(err)
}

err = client.Select(&users, query, args...)

​
Transactions

​
Simple Transaction

err := client.WithTxn(ctx, sql.TxOptions{}, func(tx *sqlx.Tx) error {
    // All operations in this function are part of the transaction
    _, err := tx.Exec("INSERT INTO accounts (name, balance) VALUES (?, ?)", "Alice", 100)
    if err != nil {
        return err // Automatically rolls back
    }
    
    _, err = tx.Exec("UPDATE accounts SET balance = balance - ? WHERE name = ?", 50, "Bob")
    return err // Commits if nil, rolls back if error
})

​
Transaction with Isolation Level

err := client.WithTxn(ctx, sql.TxOptions{
    Isolation: sql.LevelSerializable,
    ReadOnly:  false,
}, func(tx *sqlx.Tx) error {
    // Transaction operations
    return nil
})

​
Migrations

​
RunMigrations

func RunMigrations(config Config, embeddedMigrations fs.FS, resourcePath string) error
Runs database migrations from embedded files. Example: 
package main

import (
    "embed"
    "log"
    
    "github.com/raystack/salt/db"
)

//go:embed migrations/*.sql
var migrations embed.FS

func main() {
    cfg := db.Config{
        Driver: "postgres",
        URL:    "postgres://localhost:5432/mydb",
    }
    
    err := db.RunMigrations(cfg, migrations, "migrations")
    if err != nil {
        log.Fatal("Migration failed:", err)
    }
    
    log.Println("Migrations completed successfully")
}
Migration files structure: 
migrations/
├── 000001_create_users_table.up.sql
├── 000001_create_users_table.down.sql
├── 000002_add_email_index.up.sql
└── 000002_add_email_index.down.sql
Example migration file: 
-- 000001_create_users_table.up.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

​
RunRollback

func RunRollback(config Config, embeddedMigrations fs.FS, resourcePath string) error
Rolls back the last migration. 
err := db.RunRollback(cfg, migrations, "migrations")
if err != nil {
    log.Fatal("Rollback failed:", err)
}

​
Complete Example

package main

import (
    "context"
    "database/sql"
    "embed"
    "log"
    "time"
    
    "github.com/raystack/salt/db"
)

//go:embed migrations/*.sql
var migrations embed.FS

type User struct {
    ID        int       `db:"id"`
    Name      string    `db:"name"`
    Email     string    `db:"email"`
    CreatedAt time.Time `db:"created_at"`
}

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: 10 * time.Second,
    }
    
    // Run migrations
    log.Println("Running migrations...")
    if err := db.RunMigrations(cfg, migrations, "migrations"); err != nil {
        log.Fatal("Migration failed:", err)
    }
    
    // Connect to database
    client, err := db.New(cfg)
    if err != nil {
        log.Fatal("Connection failed:", err)
    }
    defer client.Close()
    
    ctx := context.Background()
    
    // Create a user in a transaction
    err = client.WithTxn(ctx, sql.TxOptions{}, func(tx *sqlx.Tx) error {
        result, err := tx.Exec(
            "INSERT INTO users (name, email) VALUES ($1, $2)",
            "Alice",
            "[email protected]",
        )
        if err != nil {
            return err
        }
        
        id, _ := result.LastInsertId()
        log.Printf("Created user with ID: %d", id)
        
        return nil
    })
    if err != nil {
        log.Fatal("Failed to create user:", err)
    }
    
    // Query users
    var users []User
    err = client.Select(&users, "SELECT * FROM users ORDER BY created_at DESC LIMIT 10")
    if err != nil {
        log.Fatal("Query failed:", err)
    }
    
    log.Printf("Found %d users", len(users))
    for _, user := range users {
        log.Printf("- %s (%s)", user.Name, user.Email)
    }
}

​
Best Practices

Configure connection pool based on your workload:
MaxOpenConns: 25,  // Max concurrent connections
MaxIdleConns: 10,  // Keep idle connections ready
ConnMaxLifeTime: 5 * time.Minute,  // Recycle connections
Wrap related operations in transactions:
client.WithTxn(ctx, sql.TxOptions{}, func(tx *sqlx.Tx) error {
    // Multiple related operations
    return nil
})
Pass context through all database operations:
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
client.GetContext(ctx, &user, query)
Always check for connection errors and retry if appropriate:
if err != nil {
    if isConnectionError(err) {
        // Retry logic
    }
}

Build docs developers (and LLMs) love

Get started for freeTalk to us