sqlext

Documentation

Overview

Package sqlext contains helper functions for SQL queries that are not specific to PostgreSQL.

Index

• func ForeachRow(db Executor, query string, args []any, action func(*sql.Rows) error) (returnedErr error)
• func RollbackUnlessCommitted(tx Rollbacker)
• func SimplifyWhitespace(query string) string
• func WithPreparedStatement(db Executor, query string, action func(*sql.Stmt) error) (returnedErr error)
• type Executor
• type Rollbacker

Functions

func ForeachRow

func ForeachRow(db Executor, query string, args []any, action func(*sql.Rows) error) (returnedErr error)

ForeachRow calls db.Query() with the given query and args, then executes the given action one for every row in the result set. It then cleans up the result set, and collects any errors that occur during all of this.

Inside the action, you only have to call rows.Scan() and use the values obtained from it. For example:

err := sqlext.ForeachRow(tx,
  `SELECT value FROM metadata WHERE key = $1`, []any{"mykey"},
  func(rows *sql.Rows) error {
    var value string
    err := rows.Scan(&value)
    if err != nil {
      return err
    }
    logg.Info("value fetched: %q", value)
    return nil
  },
)

func RollbackUnlessCommitted

func RollbackUnlessCommitted(tx Rollbacker)

RollbackUnlessCommitted calls Rollback() on a transaction if it hasn't been committed or rolled back yet. Use this with the defer keyword to make sure that a transaction is automatically rolled back when a function fails.

func SimplifyWhitespace

func SimplifyWhitespace(query string) string

SimplifyWhitespace takes an SQL query string that's hardcoded in the program and simplifies all the whitespaces, esp. ensuring that there are no comments and newlines. This makes the database log nicer when queries are logged there (e.g. for running too long), while still allowing nice multi-line formatting and inline comments in the source code.

func WithPreparedStatement

func WithPreparedStatement(db Executor, query string, action func(*sql.Stmt) error) (returnedErr error)

WithPreparedStatement calls db.Prepare() and passes the resulting prepared statement into the given action. It then cleans up the prepared statements, and it collects any errors that occur during all of this.

Inside the action, you only have to call stmt.Exec() as often as you need. For example:

var someData map[string]string
err := sqlext.WithPreparedStatement(tx,
  `INSERT INTO datatable (key, value) VALUES ($1, $2)`,
  func(stmt *sql.Stmt) error {
    for k, v := range someData {
      err := stmt.Exec(k, v)
      if err != nil {
        return err
      }
    }
    return nil
  },
)

Types

type Executor

type Executor interface {
	Exec(query string, args ...any) (sql.Result, error)
	Prepare(query string) (*sql.Stmt, error)
	Query(query string, args ...any) (*sql.Rows, error)
	QueryRow(query string, args ...any) *sql.Row
}

Executor contains the common methods that both SQL connections (*sql.DB) and transactions (*sql.Tx) implement. This is useful for functions that don't care whether they execute within a transaction or not.

For compatibility with applications using gorp (the ORM library), this interface only contains methods that are also implemented by *gorp.DbMap and *gorp.Transaction. This interface is therefore a restricted version of type gorp.SqlExecutor, from which it inherits its name.

type Rollbacker

type Rollbacker interface {
	Rollback() error
}

Rollbacker contains the Rollback() method from *sql.Tx. This interface is also satisfied by other types with transaction-like behavior like *gorp.Transaction.