add package gsql

4 files changed, 291 insertions, 0 deletions

diff --git a/CHANGELOG.md b/CHANGELOG.md
index f0e72a2..a253288 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -3,6 +3,12 @@ SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
 SPDX-License-Identifier: Apache-2.0
 -->
 
+# v1.12.0 (TBD)
+
+Changes:
+
+- Add package gsql.
+
 # v1.11.1 (2026-06-27)
 
 Changes:
diff --git a/README.md b/README.md
index ee306fa..5e2b2a4 100644
--- a/README.md
+++ b/README.md
@@ -16,6 +16,10 @@ My personal extension of the standard library.
 - [option](./option/): an Option type with strong isolation
 - [options](./options/): additional functions for type Option
 
+### Addons for database/sql
+
+- [gsql](./gsql/): abstraction layer for database libraries, supporting both database/sql drivers and non-standard drivers like [pgx](https://github.com/jackc/pgx)
+
 ### Addons for net/http
 
 - [assetembed](./assetembed/): HTTP handler for efficiently serving embedded assets using the cache-busting pattern
diff --git a/gsql/gsql.go b/gsql/gsql.go
new file mode 100644
index 0000000..939194b
--- /dev/null
+++ b/gsql/gsql.go
@@ -0,0 +1,77 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+// Package gsql abstracts over database libraries, supporting both database/sql drivers and non-standard drivers like [pgx].
+// The main abstractions are [Handle] and [ConnectionHandle].
+//
+// This package only provides [Handle] implementations for use with database/sql.
+// A [Handle] implementation for use with [pgx] is provided in [gg-pgx].
+//
+// [pgx]: https://github.com/jackc/pgx
+// [gg-pgx]: https://git.xyrillian.de/go-gg-pgx/
+package gsql
+
+import (
+	"context"
+	"database/sql"
+)
+
+// ConnectionHandle extends [Handle] with methods that make sense for handles referring to entire connections or connection pools, but not e.g. to transactions.
+// The standard-library types [*sql.DB] and [*sql.Conn] can satisfy this interface through the wrappers [NewDB] and [NewConn].
+//
+// Like for [Handle], the method names are deliberately clunky to avoid name clashes with well-known methods.
+type ConnectionHandle interface {
+	Handle
+
+	// GSQLClose closes the connection represented by this handle.
+	GSQLClose() error
+
+	// GSQLTransact executes an action within a database transaction.
+	// The callback will be provided with a [Handle] referring to the transaction.
+	// The transaction will be committed if the callback returns successfully, or rolled back otherwise.
+	GSQLTransact(ctx context.Context, action func(tx Handle) error) error
+}
+
+// Handle can be implemented by objects that allow executing SQL queries.
+// The standard-library types [*sql.DB], [*sql.Conn] and [*sql.Tx] can satisfy this interface through the wrappers [NewDB], [NewConn] and [NewTx].
+// Custom implementations of this interface can be used to connect non-std database drivers to functions accepting this interface.
+//
+// The method names are deliberately clunky to avoid name clashes with well-known methods like [sql.DB.Prepare] or [sql.DB.Query].
+type Handle interface {
+	// GSQLPrepare prepares to execute a certain SQL query one or multiple times.
+	//
+	// The "repeated" flag is a hint to the implementation whether the same statement is going to be run many times.
+	// If false, the implementation shall choose to forego the additional effort of a full statement preparation if possible,
+	// and execute one-off queries instead.
+	GSQLPrepare(ctx context.Context, query string, repeated bool) (Statement, error)
+
+	// GSQLQuery works like db.QueryContext(ctx, query, args...).
+	GSQLQuery(ctx context.Context, query string, args []any) (Rows, error)
+}
+
+// Statement represents a prepared statement returned from the GSQLPrepare() method of [Handle].
+// The Exec and QueryRow methods shall work similarly to the respective functions on [*sql.Tx], as indicated in the comments.
+//
+// You will not need to interact with this type except when implementing your own [Handle].
+type Statement interface {
+	Close() error
+
+	// Exec works like stmt.ExecContext(ctx, args...).
+	// The returned Result object must remain usable after Close() is called on this Statement instance.
+	Exec(ctx context.Context, args []any) (sql.Result, error)
+
+	// QueryRow works like stmt.QueryRow(ctx, args...).Scan(slots...).
+	QueryRow(ctx context.Context, args []any, slots []any) error
+}
+
+// Rows represents a set of rows returned from the GSQLQuery() method of [Handle].
+// All methods shall behave like on the [*sql.Rows] type from std.
+//
+// You will not need to interact with this type except when implementing your own [Handle].
+type Rows interface {
+	Columns() ([]string, error)
+	Close() error
+	Err() error
+	Next() bool
+	Scan(slots ...any) error
+}
diff --git a/gsql/std.go b/gsql/std.go
new file mode 100644
index 0000000..1d61371
--- /dev/null
+++ b/gsql/std.go
@@ -0,0 +1,204 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+package gsql
+
+import (
+	"context"
+	"database/sql"
+	"fmt"
+
+	"go.xyrillian.de/gg/errext"
+)
+
+// NOTE: The internal structure of these types looks weird at first glance, with
+// the pointer to the underlying instance duplicated, but of course that's deliberate.
+//
+// If our types implemented [Handle] directly, every function call taking them as an argument
+// of type [Handle] would allocate a new fat pointer when converting from e.g. [*DB]
+// at the callsite to [Handle] in the argument value.
+//
+// To circumvent this, our types only _have_ [Handle] instances within them within them
+// as an embedded field, thus implementing [Handle] indirectly instead of directly.
+
+// DB wraps [*sql.DB] into a [Handle].
+//
+// Because this type has [*sql.DB] as an embedded field,
+// all methods from that type work on this type as well.
+type DB struct {
+	*sql.DB
+	ConnectionHandle
+}
+
+// NewDB wraps an instance of [*sql.DB] into the [DB] type that implements [Handle].
+func NewDB(db *sql.DB) *DB {
+	return &DB{db, sqlConnectionHandle[*sql.DB]{sqlHandle[*sql.DB]{db}}}
+}
+
+// Begin is like [sql.DB.Begin], but wraps the resulting transaction into a [Handle].
+func (db *DB) Begin() (*Tx, error) {
+	tx, err := db.DB.Begin()
+	return maybe(NewTx, tx), err
+}
+
+// BeginTx is like [sql.DB.BeginTx], but wraps the resulting transaction into a [Handle].
+func (db *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error) {
+	tx, err := db.DB.BeginTx(ctx, opts)
+	return maybe(NewTx, tx), err
+}
+
+// Conn is like [sql.DB.Conn], but wraps the resulting connection into a [Handle].
+func (db *DB) Conn(ctx context.Context) (*Conn, error) {
+	conn, err := db.DB.Conn(ctx)
+	return maybe(NewConn, conn), err
+}
+
+// Conn wraps [*sql.Conn] into a [Handle].
+//
+// Because this type has [*sql.Conn] as an embedded field,
+// all methods from that type work on this type as well.
+type Conn struct {
+	*sql.Conn
+	ConnectionHandle
+}
+
+// NewConn wraps an instance of [*sql.Conn] into the [Conn] type that implements [Handle].
+func NewConn(db *sql.Conn) *Conn {
+	return &Conn{db, sqlConnectionHandle[*sql.Conn]{sqlHandle[*sql.Conn]{db}}}
+}
+
+// BeginTx is like [sql.DB.BeginTx], but wraps the resulting transaction into a [Handle].
+func (conn *Conn) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error) {
+	tx, err := conn.Conn.BeginTx(ctx, opts)
+	return maybe(NewTx, tx), err
+}
+
+// Tx wraps [*sql.Tx] into a [Handle].
+//
+// Because this type has [*sql.Tx] as an embedded field,
+// all methods from that type work on this type as well.
+type Tx struct {
+	*sql.Tx
+	Handle
+}
+
+// NewTx wraps an instance of [*sql.Tx] into the [Tx] type that implements [Handle].
+func NewTx(db *sql.Tx) *Tx {
+	return &Tx{db, sqlHandle[*sql.Tx]{db}}
+}
+
+func maybe[T, U any](wrap func(*T) *U, value *T) *U {
+	if value == nil {
+		return nil
+	}
+	return wrap(value)
+}
+
+// prove that we implement the interfaces that we claim
+var (
+	_ Handle = &DB{}
+	_ Handle = &Conn{}
+	_ Handle = &Tx{}
+
+	_ ConnectionHandle = &DB{}
+	_ ConnectionHandle = &Conn{}
+)
+
+////////////////////////////////////////////////////////////////////////////////
+// Handle implementation
+
+// sqlExecutor is an interface covered by both [*sql.DB], [*sql.Conn] and [*sql.Tx].
+type sqlExecutor interface {
+	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
+	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
+	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
+	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
+}
+
+// sqlHandle provides the [Handle] implementation for any type that implements [sqlExecutor].
+type sqlHandle[T sqlExecutor] struct {
+	Base T
+}
+
+// GSQLPrepare implements the [Handle] interface.
+func (h sqlHandle[T]) GSQLPrepare(ctx context.Context, query string, repeated bool) (Statement, error) {
+	if !repeated {
+		return wrappedStatement{h.Base, query, nil}, nil
+	}
+	stmt, err := h.Base.PrepareContext(ctx, query)
+	if err != nil {
+		return nil, fmt.Errorf("during Prepare(): %w", err)
+	}
+	return wrappedStatement{h.Base, query, stmt}, nil
+}
+
+// GSQLQuery implements the [Handle] interface.
+func (h sqlHandle[T]) GSQLQuery(ctx context.Context, query string, args []any) (Rows, error) {
+	return h.Base.QueryContext(ctx, query, args...) //nolint:rowserrcheck // the caller does the check
+}
+
+type wrappedStatement struct {
+	db    sqlExecutor
+	query string
+	stmt  *sql.Stmt // nil if repeated = false
+}
+
+// Close implements the [Statement] interface.
+func (s wrappedStatement) Close() error {
+	if s.stmt == nil {
+		return nil
+	}
+	return s.stmt.Close()
+}
+
+// Exec implements the [Statement] interface.
+func (s wrappedStatement) Exec(ctx context.Context, args []any) (sql.Result, error) {
+	if s.stmt == nil {
+		return s.db.ExecContext(ctx, s.query, args...)
+	} else {
+		return s.stmt.ExecContext(ctx, args...)
+	}
+}
+
+// QueryRow implements the [Statement] interface.
+func (s wrappedStatement) QueryRow(ctx context.Context, args, slots []any) error {
+	if s.stmt == nil {
+		return s.db.QueryRowContext(ctx, s.query, args...).Scan(slots...)
+	} else {
+		return s.stmt.QueryRowContext(ctx, args...).Scan(slots...)
+	}
+}
+
+////////////////////////////////////////////////////////////////////////////////
+// ConnectionHandle implementation
+
+// sqlConnection is an interface covered by both [*sql.DB] and [*sql.Conn].Tx].
+type sqlConnection interface {
+	sqlExecutor
+	Close() error
+	BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)
+}
+
+// sqlConnectionHandle provides the [ConnectionHandle] implementation for any type that implements [sqlConnection].
+type sqlConnectionHandle[T sqlConnection] struct {
+	sqlHandle[T]
+}
+
+// GSQLClose implements the [ConnectionHandle] interface.
+func (h sqlConnectionHandle[T]) GSQLClose() error {
+	return h.Base.Close()
+}
+
+// GSQLTransact implements the [ConnectionHandle] interface.
+func (h sqlConnectionHandle[T]) GSQLTransact(ctx context.Context, action func(tx Handle) error) error {
+	tx, err := h.Base.BeginTx(ctx, nil)
+	if err != nil {
+		return err
+	}
+	err = action(NewTx(tx))
+	if err == nil {
+		return errext.WithCleanup(nil, "tx.Commit", tx.Commit())
+	} else {
+		return errext.WithCleanup(err, "tx.Rollback", tx.Rollback())
+	}
+}