fursy plugins/database

Database integration plugin for fursy HTTP router. Provides seamless integration with database/sql for any SQL driver (PostgreSQL, MySQL, SQLite, etc.).

Features

Database Middleware: Share database connection across handlers
Transaction Helpers: Easy transaction management with auto-commit/rollback
Context Integration: database.GetDB(c) for convenient database access
Generic SQL Support: Works with any database/sql driver
Zero External Dependencies: Only stdlib database/sql

Installation

go get github.com/coregx/fursy/plugins/database
go get github.com/lib/pq  # PostgreSQL driver (example)

Quick Start

package main

import (
    "database/sql"
    "log"
    "net/http"

    "github.com/coregx/fursy"
    "github.com/coregx/fursy/plugins/database"
    _ "github.com/lib/pq" // PostgreSQL driver
)

func main() {
    // Open database connection.
    sqlDB, err := sql.Open("postgres", "user=postgres dbname=mydb sslmode=disable")
    if err != nil {
        panic(err)
    }
    defer sqlDB.Close()

    // Wrap with fursy database plugin.
    db := database.NewDB(sqlDB)

    // Create router with database middleware.
    router := fursy.New()
    router.Use(database.Middleware(db))

    // Use database in handlers.
    router.Handle("GET", "/users/:id", func(c *fursy.Context) error {
        retrievedDB, ok := database.GetDB(c)
        if !ok {
            return c.Problem(fursy.InternalServerError("Database not configured"))
        }

        var user User
        err := retrievedDB.QueryRow(c.Request.Context(),
            "SELECT * FROM users WHERE id = $1", c.Param("id")).
            Scan(&user.ID, &user.Name)

        if err == sql.ErrNoRows {
            return c.Problem(fursy.NotFound("User not found"))
        }
        if err != nil {
            return c.Problem(fursy.InternalServerError(err.Error()))
        }

        return c.JSON(200, user)
    })

    log.Fatal(http.ListenAndServe(":8080", router))
}

API Reference

DB Type

type DB struct {
    // Wraps *sql.DB with context support
}

Methods

NewDB(db *sql.DB) *DB - Create new DB wrapper
DB() *sql.DB - Get underlying *sql.DB
Ping(ctx context.Context) error - Verify connection
Close() error - Close connection
Exec(ctx, query, args...) - Execute query without rows
Query(ctx, query, args...) - Execute query returning rows
QueryRow(ctx, query, args...) - Execute query returning single row
BeginTx(ctx, opts) (*Tx, error) - Start transaction

Middleware

func Middleware(db *DB) fursy.HandlerFunc

Stores database in request context, making it available to all handlers.

Usage:

db := database.NewDB(sqlDB)
router.Use(database.Middleware(db))

GetDB Helper

func GetDB(c *fursy.Context) (*DB, bool)

Retrieves database from context.

Returns:

*DB, true if database is configured
nil, false if middleware not configured

Usage:

db, ok := database.GetDB(c)
if !ok {
    return c.Problem(fursy.InternalServerError("Database not configured"))
}

Transactions

Tx Type

type Tx struct {
    // Wraps *sql.Tx with context support
}

Methods

Commit() error - Commit transaction
Rollback() error - Rollback transaction
Exec(ctx, query, args...) - Execute query without rows
Query(ctx, query, args...) - Execute query returning rows
QueryRow(ctx, query, args...) - Execute query returning single row

Manual Transactions

router.Handle("POST", "/transfer", func(c *fursy.Context) error {
    db, _ := database.GetDB(c)

    tx, err := db.BeginTx(c.Request.Context(), nil)
    if err != nil {
        return err
    }
    defer tx.Rollback() // Rollback if not committed

    // ... do work ...

    return tx.Commit()
})

WithTx Helper

func WithTx(ctx context.Context, db *DB, fn func(*Tx) error) error

Executes a function within a transaction. Automatically commits on success, rolls back on error.

Usage:

err := database.WithTx(c.Request.Context(), db, func(tx *database.Tx) error {
    _, err := tx.Exec(ctx, "INSERT INTO users (name) VALUES ($1)", "Alice")
    if err != nil {
        return err // Automatic rollback
    }
    _, err = tx.Exec(ctx, "INSERT INTO audit (action) VALUES ($1)", "user_created")
    return err // Automatic commit on nil error
})

Transaction Middleware

func TxMiddleware(db *DB) fursy.HandlerFunc

Wraps each request in a database transaction. Auto-commits on success, auto-rolls back on error.

Usage:

// Apply to specific routes that need transactions.
txGroup := router.Group("/api/v1")
txGroup.Use(database.Middleware(db))
txGroup.Use(database.TxMiddleware(db))

txGroup.POST("/users", func(c *fursy.Context) error {
    tx, _ := database.GetTx(c)
    // Use tx for all database operations
    // Auto-commit on success, auto-rollback on error
    return nil
})

GetTx Helper:

func GetTx(c *fursy.Context) (*Tx, bool)

Retrieves transaction from context (requires TxMiddleware).

Examples

CRUD Operations

See examples/09-rest-api-with-db for a complete REST API example with:

Create, Read, Update, Delete operations
Transaction management
Error handling with RFC 9457
Batch operations

Batch Insert with Transaction

router.Handle("POST", "/users/batch", func(c *fursy.Context) error {
    db, _ := database.GetDB(c)

    var users []User
    json.NewDecoder(c.Request.Body).Decode(&users)

    var count int
    err := database.WithTx(c.Request.Context(), db, func(tx *database.Tx) error {
        for _, user := range users {
            _, err := tx.Exec(c.Request.Context(),
                "INSERT INTO users (name) VALUES ($1)", user.Name)
            if err != nil {
                return err // Rollback entire batch
            }
            count++
        }
        return nil // Commit all inserts
    })

    if err != nil {
        return c.Problem(fursy.InternalServerError(err.Error()))
    }

    return c.Created(map[string]int{"inserted": count})
})

Error Handling

router.Handle("GET", "/users/:id", func(c *fursy.Context) error {
    db, _ := database.GetDB(c)

    var user User
    err := db.QueryRow(c.Request.Context(),
        "SELECT id, name FROM users WHERE id = $1", c.Param("id")).
        Scan(&user.ID, &user.Name)

    if err == sql.ErrNoRows {
        return c.Problem(fursy.NotFound("User not found"))
    }
    if err != nil {
        return c.Problem(fursy.InternalServerError(err.Error()))
    }

    return c.JSON(200, user)
})

Supported Databases

Any database/sql compatible driver:

PostgreSQL: github.com/lib/pq or github.com/jackc/pgx/v5/stdlib
MySQL: github.com/go-sql-driver/mysql
SQLite: github.com/mattn/go-sqlite3
SQL Server: github.com/denisenkom/go-mssqldb
Oracle: github.com/godror/godror

dbcontext Pattern

The dbcontext pattern refers to best practices for managing database connections in request context. This plugin provides three approaches with different trade-offs:

Approach 1: GetDBOrError (Recommended for Production)

Use when: You want clean error handling with RFC 9457 Problem Details.

router.Handle("GET", "/users/:id", func(c *fursy.Context) error {
    db, err := database.GetDBOrError(c)
    if err != nil {
        return err // Returns 500 Internal Server Error
    }

    var user User
    err = db.QueryRow(c.Request.Context(),
        "SELECT id, name FROM users WHERE id = $1", c.Param("id")).
        Scan(&user.ID, &user.Name)

    if err == sql.ErrNoRows {
        return c.Problem(fursy.NotFound("User not found"))
    }
    return c.JSON(200, user)
})

Pros:

Clean, production-ready error handling
Returns RFC 9457 compliant errors
Single line to get DB with error handling

Cons:

Requires error check on every handler

Approach 2: MustGetDB (For Prototyping)

Use when: Rapid prototyping or when DB absence indicates programming error.

router.Handle("GET", "/users", func(c *fursy.Context) error {
    db := database.MustGetDB(c) // Panics if middleware not configured

    rows, err := db.Query(c.Request.Context(), "SELECT * FROM users")
    // ... handle query errors only
})

Pros:

Minimal boilerplate
Fast to write during development

Cons:

Panics on misconfiguration (not production-friendly)
Less explicit error handling

Approach 3: GetDB (Manual Control)

Use when: You need custom error handling or conditional DB usage.

router.Handle("GET", "/users", func(c *fursy.Context) error {
    db, ok := database.GetDB(c)
    if !ok {
        // Custom error handling
        return c.Problem(fursy.Problem{
            Type:   "https://example.com/errors/db-not-configured",
            Title:  "Database Unavailable",
            Status: 503,
            Detail: "Service is temporarily unavailable",
        })
    }
    // Use db...
})

Pros:

Full control over error handling
Can return custom error responses

Cons:

More verbose
Requires manual error construction

When to Use Each Approach

Scenario	Recommended Approach
Production REST API	`GetDBOrError()` - Clean errors
Internal Admin Panel	`MustGetDB()` - Fast prototyping
Microservice Health Check	`GetDB()` - Custom 503 responses
Conditional DB Usage	`GetDB()` - Check availability

Transaction Patterns

Similar helpers exist for transactions:

GetTxOrError (Recommended):

txGroup := router.Group("/api")
txGroup.Use(database.TxMiddleware(db))

txGroup.POST("/transfer", func(c *fursy.Context) error {
    tx, err := database.GetTxOrError(c)
    if err != nil {
        return err
    }
    // Use tx - auto-commit on success, auto-rollback on error
})

MustGetTx (Prototyping):

txGroup.POST("/batch", func(c *fursy.Context) error {
    tx := database.MustGetTx(c) // Panics if TxMiddleware not configured
    // Use tx...
})

Repository Pattern Integration

Combine dbcontext with repository pattern for clean separation:

// Repository encapsulates database operations
type UserRepository struct {
    db *database.DB
}

func NewUserRepository(db *database.DB) *UserRepository {
    return &UserRepository{db: db}
}

func (r *UserRepository) FindByID(ctx context.Context, id string) (*User, error) {
    var user User
    err := r.db.QueryRow(ctx,
        "SELECT id, name FROM users WHERE id = $1", id).
        Scan(&user.ID, &user.Name)

    if err == sql.ErrNoRows {
        return nil, ErrUserNotFound
    }
    return &user, err
}

// Handler using repository pattern
router.Handle("GET", "/users/:id", func(c *fursy.Context) error {
    db, err := database.GetDBOrError(c)
    if err != nil {
        return err
    }

    repo := NewUserRepository(db)
    user, err := repo.FindByID(c.Request.Context(), c.Param("id"))
    if err == ErrUserNotFound {
        return c.Problem(fursy.NotFound("User not found"))
    }
    if err != nil {
        return c.Problem(fursy.InternalServerError(err.Error()))
    }

    return c.JSON(200, user)
})

Benefits:

Testable (mock repository interface)
Clean separation of concerns
Reusable across handlers
Type-safe domain errors

Best Practices

Connection Pooling

Configure connection pool settings for production:

sqlDB, _ := sql.Open("postgres", dsn)

// Configure pool
sqlDB.SetMaxOpenConns(25)
sqlDB.SetMaxIdleConns(5)
sqlDB.SetConnMaxLifetime(5 * time.Minute)

db := database.NewDB(sqlDB)

Use Transactions for Multi-Step Operations

Always use transactions for operations that modify multiple rows or tables:

database.WithTx(ctx, db, func(tx *database.Tx) error {
    // Step 1: Insert user
    // Step 2: Insert audit log
    // Both succeed or both fail
    return nil
})

Context Cancellation

Always pass request context to database operations:

db.QueryRow(c.Request.Context(), query, args...) // Use c.Request.Context()

This ensures:

Operations are canceled if client disconnects
Timeout policies are enforced
Graceful shutdown works correctly

Prepared Statements

For frequently executed queries, use prepared statements:

stmt, _ := db.DB().PrepareContext(ctx, "SELECT * FROM users WHERE id = $1")
defer stmt.Close()
// Use stmt.QueryRowContext(ctx, id)

Testing

Run tests:

cd plugins/database
go test -v ./...

Run with coverage:

go test -coverprofile=coverage.txt ./...
go tool cover -html=coverage.txt

License

MIT License - see LICENSE for details.

Database Drivers

Compatible with any database/sql driver:

PostgreSQL: github.com/lib/pq
MySQL: github.com/go-sql-driver/mysql
SQLite: modernc.org/sqlite (pure Go, no CGO)
SQL Server: github.com/denisenkom/go-mssqldb

See database/sql drivers for full list.

Navigation Menu

Uh oh!

Uh oh!

fursy plugins/database

Features

Installation

Quick Start

API Reference

DB Type

Methods

Middleware

GetDB Helper

Transactions

Tx Type

Methods

Manual Transactions

WithTx Helper

Transaction Middleware

Examples

CRUD Operations

Batch Insert with Transaction

Error Handling

Supported Databases

dbcontext Pattern

Approach 1: GetDBOrError (Recommended for Production)

Approach 2: MustGetDB (For Prototyping)

Approach 3: GetDB (Manual Control)

When to Use Each Approach

Transaction Patterns

Repository Pattern Integration

Best Practices

Connection Pooling

Use Transactions for Multi-Step Operations

Context Cancellation

Prepared Statements

Testing

License

Related

See Also

Database Drivers

Uh oh!

Uh oh!

FilesExpand file tree

README.md

Latest commit

History

README.md

File metadata and controls

fursy plugins/database

Features

Installation

Quick Start

API Reference

DB Type

Methods

Middleware

GetDB Helper

Transactions

Tx Type

Methods

Manual Transactions

WithTx Helper

Transaction Middleware

Examples

CRUD Operations

Batch Insert with Transaction

Error Handling

Supported Databases

dbcontext Pattern

Approach 1: GetDBOrError (Recommended for Production)

Approach 2: MustGetDB (For Prototyping)

Approach 3: GetDB (Manual Control)

When to Use Each Approach

Transaction Patterns

Repository Pattern Integration

Best Practices

Connection Pooling

Use Transactions for Multi-Step Operations

Context Cancellation

Prepared Statements

Testing

License

Related

See Also

Database Drivers