libdbexec

libdbexec

Driver-agnostic interfaces for SQL access in Go: DBManager, Exec, and QueryRower. One codebase writes queries against these interfaces and runs unmodified against either PostgreSQL (lib/pq) or SQLite (modernc.org/sqlite).

Install

go get github.com/contenox/libdbexec

What it provides

• DBManager — the entry point for a connection group. Obtain an Exec bound to auto-commit (WithoutTransaction) or to a transaction (WithTransaction), and Close the underlying pool on shutdown.

• Exec — ExecContext, QueryContext, QueryRowContext, and DriverName() ("postgres" or "sqlite"). Implemented identically for both drivers via an internal txAwareDB that delegates to either *sql.DB or *sql.Tx.

• QueryRower — wraps *sql.Row so Scan returns the package's ErrNotFound instead of the raw sql.ErrNoRows.

• The WithTransaction / ReleaseTx pattern — WithTransaction returns an Exec, a CommitTx, and a ReleaseTx. Defer ReleaseTx immediately; it rolls back if the transaction wasn't committed and is a safe no-op otherwise (including after a successful commit).

• A translated error-sentinel set — every driver maps its own errors (SQLite's string-based errors, PostgreSQL's pq.Error SQLSTATE codes) onto the same sentinels, checkable with errors.Is regardless of which backend is in use:

  ErrNotFound, ErrTxFailed, ErrMaxRowsReached,
  ErrUniqueViolation, ErrForeignKeyViolation, ErrNotNullViolation, ErrCheckViolation, ErrConstraintViolation,
  ErrDeadlockDetected, ErrSerializationFailure, ErrLockNotAvailable, ErrQueryCanceled,
  ErrDataTruncation, ErrNumericOutOfRange, ErrInvalidInputSyntax,
  ErrUndefinedColumn, ErrUndefinedTable
  

• SetupLocalInstance (in localpostgres.go) — spins up an ephemeral PostgreSQL container via testcontainers-go for integration tests, and returns a ready-to-use connection string plus a cleanup func.

Usage

package main

import (
	"context"
	"errors"
	"fmt"
	"log"

	"github.com/contenox/libdbexec"
)

func main() {
	ctx := context.Background()

	// Swap NewSQLiteDBManager for NewPostgresDBManager(ctx, dsn, schema) to
	// target PostgreSQL instead — the rest of the code is unchanged.
	mgr, err := libdbexec.NewSQLiteDBManager(ctx, "./local.db", `
		CREATE TABLE IF NOT EXISTS settings (
			key   TEXT PRIMARY KEY,
			value TEXT NOT NULL
		);
	`)
	if err != nil {
		log.Fatal(err)
	}
	defer mgr.Close()

	if err := updateSetting(ctx, mgr, "theme", "dark"); err != nil {
		log.Fatal(err)
	}
}

func updateSetting(ctx context.Context, mgr libdbexec.DBManager, key, value string) error {
	exec, commit, release, err := mgr.WithTransaction(ctx)
	if err != nil {
		return fmt.Errorf("begin transaction: %w", err)
	}
	defer release() // rolls back unless commit(ctx) already succeeded

	_, err = exec.ExecContext(ctx,
		"INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value",
		key, value)
	if err != nil {
		if errors.Is(err, libdbexec.ErrConstraintViolation) {
			return fmt.Errorf("invalid setting %q: %w", key, err)
		}
		return fmt.Errorf("update setting: %w", err)
	}

	var stored string
	if err := exec.QueryRowContext(ctx, "SELECT value FROM settings WHERE key = ?", key).Scan(&stored); err != nil {
		if errors.Is(err, libdbexec.ErrNotFound) {
			return fmt.Errorf("setting %q vanished mid-transaction: %w", key, err)
		}
		return fmt.Errorf("read back setting: %w", err)
	}

	return commit(ctx)
}

Drivers

Both constructors open the connection group, ping it, and (if schema is non-empty) apply it before returning — intended for local/dev bootstrapping, not as a replacement for a real migration tool in production.