sqeasy

README

sqeasy

Small wrappers to make usage of Go's sql-package with a CockroachDB database easier to maintain, by for example mapping variables to positional arguments.

Examples

Map columns to destination values

Instead of summing all destination variables in Scan(), map the columns onto your variables with sqeasy.SelectColumns and have them scanned into them.

var db *sql.DB

var (
    colA      string
    timestamp time.Time
    count     int
)

columns := sqeasy.SelectColumns{
    {`a_column`,    &colA},
    {`"timestamp"`, &timestamp},
    {`COUNT(*)`,    &count},
}

row := db.QueryRow("SELECT " + columns.ExprList() + " FROM table")
err := columns.Scan(row)

Use named parameters

Instead of the $1, $2, $3 etc positional parameters in your queries, use named ones. Bind values to the names using sqeasy.NamedParams.

var db *sql.DB

params := sqeasy.NamedParams{
    {"colA",      "notthis"},
    {"timestamp", time.Now()},
}

query := "SELECT * FROM table WHERE a_column != :colA AND timestamp < :timestamp"
row := sqeasy.QueryRow(db, query, params)

The same sqeasy.NamedParams could be used to for example generate INSERT statements.

var db *sql.DB

params := sqeasy.NamedParams{
    "a_column":  "this",
    "timestamp": time.Now(),
}

query := "INSERT INTO table (" + params.ExprList() + ") VALUES (" + params.Params() + ")"
row := sqeasy.QueryRow(db, query, params)

Documentation

Index

• func Arg(args *[]interface{}, arg interface{}) string
• func Exec(db dbExecContext, query string, params NamedParams) (sql.Result, error)
• func ExecContext(ctx context.Context, db dbExecContext, query string, params NamedParams) (sql.Result, error)
• func Query(db dbQueryContext, query string, params NamedParams) (*sql.Rows, error)
• func QueryContext(ctx context.Context, db dbQueryContext, query string, params NamedParams) (*sql.Rows, error)
• type MissingParameterError
  • func (e MissingParameterError) Error() string
• type NamedParams
  • func (np NamedParams) Args(params []string) ([]interface{}, error)
  • func (np NamedParams) Parse(query string) (string, []interface{}, error)
• type ParameterCountError
  • func (e ParameterCountError) Error() string
• type Row
  • func QueryRow(db dbQueryRowContext, query string, params NamedParams) *Row
  • func QueryRowContext(ctx context.Context, db dbQueryRowContext, query string, params NamedParams) *Row
  • func (r *Row) Err() error
  • func (r *Row) Scan(dest ...interface{}) error
• type SelectColumns
  • func (sc SelectColumns) ExprList() string
  • func (sc SelectColumns) Scan(row interface{ ... }) error
• type Stmt
  • func Prepare(db dbPrepareContext, query string) (*Stmt, error)
  • func PrepareContext(ctx context.Context, db dbPrepareContext, query string) (*Stmt, error)
  • func (s *Stmt) Close() error
  • func (s *Stmt) Exec(args NamedParams) (sql.Result, error)
  • func (s *Stmt) ExecContext(ctx context.Context, named NamedParams) (sql.Result, error)
  • func (s *Stmt) Query(args NamedParams) (*sql.Rows, error)
  • func (s *Stmt) QueryContext(ctx context.Context, named NamedParams) (*sql.Rows, error)
  • func (s *Stmt) QueryRow(args NamedParams) *Row
  • func (s *Stmt) QueryRowContext(ctx context.Context, named NamedParams) *Row

Examples

• Arg
• NamedParams
• SelectColumns

Functions

func Arg

func Arg(args *[]interface{}, arg interface{}) string

Arg adds a value to args, returning it's position

Example

var db *sql.DB

var args []interface{}
query := `SELECT * FROM table WHERE a_column = ` + Arg(&args, `fubar`) + ` AND timestamp < ` + Arg(&args, time.Now())

_ = db.QueryRow(query, args...)

func Exec

func Exec(db dbExecContext, query string, params NamedParams) (sql.Result, error)

func ExecContext

func ExecContext(ctx context.Context, db dbExecContext, query string, params NamedParams) (sql.Result, error)

func Query

func Query(db dbQueryContext, query string, params NamedParams) (*sql.Rows, error)

func QueryContext

func QueryContext(ctx context.Context, db dbQueryContext, query string, params NamedParams) (*sql.Rows, error)

Types

type MissingParameterError

type MissingParameterError struct {
	Missing string
	Got     NamedParams
}

func (MissingParameterError) Error

func (e MissingParameterError) Error() string

type NamedParams

type NamedParams map[string]interface{}

NamedParams enables the use of named parameters with any database by converting the query to use positional arguments

Example

package main

import (
	"database/sql"
	"time"

	"github.com/ferdypruis/sqeasy"
)

func main() {
	var db *sql.DB

	params := sqeasy.NamedParams{
		"colA":      "notthis",
		"timestamp": time.Now(),
	}

	query := "SELECT * FROM table WHERE a_column != :colA AND timestamp < :timestamp"
	_ = sqeasy.QueryRow(db, query, params)
}

func (NamedParams) Args

func (np NamedParams) Args(params []string) ([]interface{}, error)

args returns the value for each parameter on the position as indicated in the param slice

func (NamedParams) Parse

func (np NamedParams) Parse(query string) (string, []interface{}, error)

Parse replaces the named parameters with positional parameters and returns a slice of corresponding values

type ParameterCountError

type ParameterCountError struct {
	Expected []string
	Got      NamedParams
}

func (ParameterCountError) Error

func (e ParameterCountError) Error() string

type Row

type Row struct {
	*sql.Row
	// contains filtered or unexported fields
}

func QueryRow

func QueryRow(db dbQueryRowContext, query string, params NamedParams) *Row

func QueryRowContext

func QueryRowContext(ctx context.Context, db dbQueryRowContext, query string, params NamedParams) *Row

func (*Row) Err

func (r *Row) Err() error

func (*Row) Scan

func (r *Row) Scan(dest ...interface{}) error

type SelectColumns

type SelectColumns []struct {
	Expr string
	Dest interface{}
}

SelectColumns is a mapping of expression, like a column, to a destination variable

Example

package main

import (
	"database/sql"

	"github.com/ferdypruis/sqeasy"
)

func main() {
	var db *sql.DB

	var (
		colA      string
		timestamp sql.NullString
		count     int
	)

	columns := sqeasy.SelectColumns{
		{`a_column`, &colA},
		{`"timestamp"`, &timestamp},
		{`COUNT(*)`, &count},
	}

	row := db.QueryRow("SELECT " + columns.ExprList() + " FROM table")
	_ = columns.Scan(row)
}

func (SelectColumns) ExprList

func (sc SelectColumns) ExprList() string

ExprList returns all the expressions separated by comma Use this in your SELECT statement

func (SelectColumns) Scan

func (sc SelectColumns) Scan(row interface {
	Scan(dest ...interface{}) error
}) error

Scan copies the columns from the row into the values

type Stmt

type Stmt struct {
	// contains filtered or unexported fields
}

func Prepare

func Prepare(db dbPrepareContext, query string) (*Stmt, error)

func PrepareContext

func PrepareContext(ctx context.Context, db dbPrepareContext, query string) (*Stmt, error)

func (*Stmt) Close

func (s *Stmt) Close() error

func (*Stmt) Exec

func (s *Stmt) Exec(args NamedParams) (sql.Result, error)

func (*Stmt) ExecContext

func (s *Stmt) ExecContext(ctx context.Context, named NamedParams) (sql.Result, error)

func (*Stmt) Query

func (s *Stmt) Query(args NamedParams) (*sql.Rows, error)

func (*Stmt) QueryContext

func (s *Stmt) QueryContext(ctx context.Context, named NamedParams) (*sql.Rows, error)

func (*Stmt) QueryRow

func (s *Stmt) QueryRow(args NamedParams) *Row

func (*Stmt) QueryRowContext

func (s *Stmt) QueryRowContext(ctx context.Context, named NamedParams) *Row