Database

The framework/database package provides a SQL database wrapper built on sqlx. It implements the contract.Database interface with prepared statements, named parameter support, and safe transaction handling.

type Database interface {
    Close() error
    Ping(ctx context.Context) error
    Exec(ctx context.Context, query string, args ...any) (int64, error)
    ExecNamed(ctx context.Context, query string, arg any) (int64, error)
    Select(ctx context.Context, query string, dest any, args ...any) error
    SelectNamed(ctx context.Context, query string, dest any, arg any) error
    Find(ctx context.Context, query string, dest any, args ...any) error
    FindNamed(ctx context.Context, query string, dest any, arg any) error
    WithTransaction(ctx context.Context, fn func(tx Database) error) error
}

Setup

Create a database connection by specifying a driver and DSN:

import "github.com/studiolambda/cosmos/framework/database"

db, err := database.NewSQL("postgres", "postgres://user:pass@localhost/mydb?sslmode=disable")
if err != nil {
    log.Fatal(err)
}
defer db.Close()

Or wrap an existing *sqlx.DB:

db := database.NewSQLFrom(existingSqlxDB)

You’ll need to import the appropriate database driver:

import _ "github.com/lib/pq"          // PostgreSQL
import _ "github.com/go-sql-driver/mysql" // MySQL
import _ "modernc.org/sqlite"           // SQLite

Health Check

if err := db.Ping(ctx); err != nil {
    log.Fatal("database unreachable:", err)
}

Queries

Exec

Execute a statement and get the number of affected rows:

affected, err := db.Exec(ctx, "DELETE FROM users WHERE active = false")

With positional parameters:

affected, err := db.Exec(ctx, "UPDATE users SET name = $1 WHERE id = $2", "Alice", 42)

ExecNamed

Execute with named parameters from a struct or map:

affected, err := db.ExecNamed(ctx,
    "UPDATE users SET name = :name WHERE id = :id",
    map[string]any{"name": "Alice", "id": 42},
)

Or using a struct with db tags:

type UpdateUser struct {
    ID   int    `db:"id"`
    Name string `db:"name"`
}

affected, err := db.ExecNamed(ctx,
    "UPDATE users SET name = :name WHERE id = :id",
    UpdateUser{ID: 42, Name: "Alice"},
)

Select

Query multiple rows into a slice:

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

SelectNamed

Query multiple rows with named parameters:

var users []User
err := db.SelectNamed(ctx,
    "SELECT * FROM users WHERE role = :role",
    &users,
    map[string]any{"role": "admin"},
)

Find

Query a single row:

var user User
err := db.Find(ctx, "SELECT * FROM users WHERE id = $1", &user, 42)

If no row is found, the error wraps contract.ErrDatabaseNoRows:

if errors.Is(err, contract.ErrDatabaseNoRows) {
    return problem.Problem{
        Title:  "User Not Found",
        Status: http.StatusNotFound,
    }
}

FindNamed

Single row with named parameters:

var user User
err := db.FindNamed(ctx,
    "SELECT * FROM users WHERE email = :email",
    &user,
    map[string]any{"email": "alice@example.com"},
)

Transactions

Wrap multiple operations in a transaction:

err := db.WithTransaction(ctx, func(tx contract.Database) error {
    _, err := tx.Exec(ctx, "INSERT INTO orders (user_id, total) VALUES ($1, $2)", userID, total)
    if err != nil {
        return err // triggers rollback
    }

    _, err = tx.Exec(ctx, "UPDATE users SET order_count = order_count + 1 WHERE id = $1", userID)
    if err != nil {
        return err // triggers rollback
    }

    return nil // triggers commit
})

If the callback returns an error, the transaction is rolled back. If it returns nil, the transaction is committed.

Nested transactions are not supported — calling WithTransaction inside a transaction callback returns contract.ErrDatabaseNestedTransaction.

Providing to Handlers

Use the Provide middleware to make the database available through the request context:

app.Use(middleware.Provide(dbKey, db))

func listUsers(w http.ResponseWriter, r *http.Request) error {
    db := r.Context().Value(dbKey).(contract.Database)
    // ...
}