sqlnt

README

SQLNT

Overview

Go package for named SQL templates... tired of battling driver does not support the use of Named Parameters or wish you could reliably use named parameters instead of incomprehensible (?, ?, ?, ?, ?, ?)

Try...

package main

import (
    "database/sql"
    "github.com/go-andiamo/sqlnt"
)

var template = sqlnt.MustCreateNamedTemplate(`INSERT INTO table 
(col_a, col_b, col_c)
VALUES (:a, :b, :c)`, nil)

func insertExample(db *sql.DB, aVal string, bVal string, cVal string) error {
    _, err := db.Exec(template.Statement(), template.MustArgs(
        map[string]any{
            "a": aVal,
            "b": bVal,
            "c": cVal,
    })...)
    return err
}

try on go-playground

Or...

package main

import (
    "database/sql"
    "github.com/go-andiamo/sqlnt"
)

var template = sqlnt.MustCreateNamedTemplate(`INSERT INTO table 
(col_a, col_b, col_c)
VALUES (:a, :b, :c)`, nil)

func insertExample(db *sql.DB, aVal string, bVal string, cVal string) error {
    _, err := template.Exec(db,
        map[string]any{
            "a": aVal,
            "b": bVal,
        }, 
        sql.NamedArg{Name: "c", Value: cVal})
    return err
}

try on go-playground

Installation

To install Sqlnt, use go get:

go get github.com/go-andiamo/sqlnt

To update Sqlnt to the latest version, run:

go get -u github.com/go-andiamo/sqlnt

Enhanced Features

Omissible args

By default, named templates check that all named args have been supplied...

template := sqlnt.MustCreateNamedTemplate(`INSERT INTO table (col_a,col_b) VALUES (:a, :b)`, nil)
_, err := template.Args(map[string]any{"a": "a value"})
if err != nil {
    panic(err) // will panic because named arg "b" is missing
}

However, named args can be set as omissible...

template := sqlnt.MustCreateNamedTemplate(`INSERT INTO table (col_a,col_b) VALUES (:a, :b)`, nil)
template.OmissibleArgs("b")
args, err := template.Args(map[string]any{"a": "a value"})
if err != nil {
    panic(err) // will not panic here because named arg "b" is missing but omissible
} else {
    fmt.Printf("%#v", args) // prints: []interface {}{"a value", interface {}(nil)}
}

Named args can also be set as omissible in the original template by suffixing the name with ?...

template := sqlnt.MustCreateNamedTemplate(`INSERT INTO table (col_a,col_b) VALUES (:a, :b?)`, nil)
args, err := template.Args(map[string]any{"a": "a value"})
if err != nil {
    panic(err) // will not panic here because named arg "b" is missing but omissible
} else {
    fmt.Printf("%#v", args) // prints: []interface {}{"a value", interface {}(nil)}
}

Default values

Named templates also provides for default - where if a named arg is not supplied a default value is used...

template := sqlnt.MustCreateNamedTemplate(`INSERT INTO table (name,status) VALUES (:name, :status)`, nil)
template.DefaultValue("status", "unknown")
args, err := template.Args(map[string]any{"name": "some name"})
if err != nil {
    panic(err) // will not panic here because named arg "status" is missing but defaulted
} else {
    fmt.Printf("%#v", args) // prints: []interface {}{"some name", "unknown"}
}

Default values can also be supplied as a function...

template := sqlnt.MustCreateNamedTemplate(`INSERT INTO table (name,status,created_at) VALUES (:name, :status, :createdAt)`, nil)
template.DefaultValue("status", "unknown")
template.DefaultValue("createdAt", func(name string) any {
    return time.Now()
})
args, err := template.Args(map[string]any{"name": "some name"})
if err != nil {
    panic(err) // will not panic here because named args "status" and "createdAt" are missing but defaulted
} else {
    fmt.Printf("%#v", args) // prints: []interface {}{"some name", "unknown", time.Date{...}}
}

Tokens

Sometimes you may have a common lexicon of table names, columns and/or arg names defined as consts. These can be used in templates using token replace notation ({{token}}) in the template string and transposed by providing a sqlnt.TokenOption implementation...

package main

import (
    "database/sql"
    "fmt"
    "github.com/go-andiamo/sqlnt"
    "time"
)

func main() {
    insertQueue := sqlnt.MustCreateNamedTemplate(InsertStatement, &Lexicon{TableNameQueues}).
        DefaultValue(ParamNameCreatedAt, nowFunc).DefaultValue(ParamNameStatus, "unknown")
    insertTopic := sqlnt.MustCreateNamedTemplate(InsertStatement, &Lexicon{TableNameTopics}).
        DefaultValue(ParamNameCreatedAt, nowFunc).DefaultValue(ParamNameStatus, "unknown")

    statement, args := insertQueue.MustStatementAndArgs(sql.Named(ParamNameName, "foo"))
    fmt.Printf("statement: %s\n    args: %#v\n", statement, args)

    statement, args = insertTopic.MustStatementAndArgs(sql.Named(ParamNameName, "foo"))
    fmt.Printf("statement: %s\n    args: %#v\n", statement, args)
}

const (
    InsertStatement    = "INSERT INTO {{table}} ({{baseCols}}) VALUES ({{insertArgs}})"
    TableNameQueues    = "queues"
    TableNameTopics    = "topics"
    BaseCols           = "name,status,created_at"
    ParamNameName      = "name"
    ParamNameStatus    = "status"
    ParamNameCreatedAt = "createdAt"
)

var nowFunc = func(name string) any {
    return time.Now()
}

var commonLexiconMap = map[string]string{
    "baseCols":   BaseCols,
    "insertArgs": ":" + ParamNameName + ",:" + ParamNameStatus + ",:" + ParamNameCreatedAt,
}

type Lexicon struct {
    TableName string
}

// Replace implements sqlnt.TokenOption.Replace
func (l *Lexicon) Replace(token string) (string, bool) {
    if token == "table" {
        return l.TableName, true
    }
    r, ok := commonLexiconMap[token]
    return r, ok
}

Documentation

Overview

Package sqlnt - a Go package for sql named arg templates

Provides a simple, driver agnostic, way to use SQL statement templates with named args

Example:

var tmp = sqlnt.MustCreateNamedTemplate(`INSERT INTO table (col_a, col_b) VALUES(:a, :b)`, nil)

args := map[string]any{
  "a": "a value",
  "b": "b value",
}
_, _ = db.Exec(tmp.Statement(), tmp.MustArgs(args)...)

Or directly using template:

var tmp = sqlnt.MustCreateNamedTemplate(`INSERT INTO table (col_a, col_b) VALUES(:a, :b)`, nil)

args := map[string]any{
  "a": "a value",
  "b": "b value",
}
_, _ = tmp.Exec(db, args)

Index

• Variables
• func MustCreateTemplateSet[T any](options ...any) *T
• func NewTemplateSet[T any](options ...any) (*T, error)
• type ArgInfo
• type DefaultValueFunc
• type NamedTemplate
  • func MustCreateNamedTemplate(statement string, options ...any) NamedTemplate
  • func NewNamedTemplate(statement string, options ...any) (NamedTemplate, error)
• type Option
• type TokenOption
• type TokenOptionMap
  • func (m TokenOptionMap) Replace(token string) (string, bool)

Variables

var DefaultArgTag = "?"

DefaultArgTag is the default setting for arg tag placeholders

var DefaultUsePositionalTags = false

DefaultUsePositionalTags is the default setting for whether to use positional arg tags

Functions

func MustCreateTemplateSet

func MustCreateTemplateSet[T any](options ...any) *T

MustCreateTemplateSet is the same as NewTemplateSet except that it panics on error

func NewTemplateSet

func NewTemplateSet[T any](options ...any) (*T, error)

NewTemplateSet builds a set of templates for the given struct type T

Fields of type sqlnt.NamedTemplate are created and set from the field tag 'sql'

Example:

type MyTemplateSet struct {
  Select sqlnt.NamedTemplate `sql:"SELECT * FROM foo WHERE col_a = :a"`
  Insert sqlnt.NamedTemplate `sql:"INSERT INTO foo (col_a, col_b, col_c) VALUES(:a, :b, :c)"`
  Delete sqlnt.NamedTemplate `sql:"DELETE FROM foo WHERE col_a = :a"`
}
set, err := sqlnt.NewTemplateSet[MyTemplateSet]()

Note: If the overall field tag does not contain a 'sql' tag nor any other tags (i.e. there are no double-quotes in it) then the entire field tag value is used as the template - enabling the use of carriage returns to format the statement

Example:

type MyTemplateSet struct {
  Select sqlnt.NamedTemplate `SELECT *
  FROM foo
  WHERE col_a = :a`
}

Types

type ArgInfo

type ArgInfo struct {
	// Tag is the final arg tag used for the named arg
	Tag string
	// Positions is the final arg positions for the named arg
	Positions []int
	// Omissible denotes whether the named arg is omissible
	Omissible bool
	// DefaultValue is the DefaultValueFunc provider function
	DefaultValue DefaultValueFunc
	// NullableString denotes whether the named arg is a nullable string
	// (i.e. if the supplied value is an empty, then nil is used)
	NullableString bool
}

ArgInfo is the info about a named arg returned from NamedTemplate.GetArgsInfo

type DefaultValueFunc

type DefaultValueFunc func(name string) any

DefaultValueFunc is the function signature for funcs that can be passed to NamedTemplate.DefaultValue

type NamedTemplate

type NamedTemplate interface {
	// Statement returns the sql statement to use (with named args transposed)
	Statement() string
	// StatementAndArgs returns the sql statement to use (with named args transposed) and
	// the input named args converted to positional args
	//
	// Essentially the same as calling Statement and then Args
	StatementAndArgs(args ...any) (string, []any, error)
	// MustStatementAndArgs is the same as StatementAndArgs, except no error is returned (and panics on error)
	MustStatementAndArgs(args ...any) (string, []any)
	// OriginalStatement returns the original named template statement
	OriginalStatement() string
	// Args converts the input named args to positional args (for use in db.Exec, db.Query etc.)
	//
	// Each arg in the supplied args can be:
	//
	// * map[string]any
	//
	// * sql.NamedArg
	//
	// * or any map where all keys are set as string
	//
	// * or anything that can be marshalled and then unmarshalled to map[string]any (such as structs!)
	//
	// If any of the named args specified in the query are missing, returns an error
	//
	// NB. named args are not considered missing when they have denoted as omissible (see NamedTemplate.OmissibleArgs) or
	// have been set with a default value (see NamedTemplate.DefaultValue)
	Args(args ...any) ([]any, error)
	// MustArgs is the same as Args, except no error is returned (and panics on error)
	MustArgs(args ...any) []any
	// ArgsCount returns the number of args that are passed into the statement
	ArgsCount() int
	// OmissibleArgs specifies the names of args that can be omitted
	//
	// Calling this without any names makes all args omissible
	//
	// Note: Named args can also be set as omissible in the template - example:
	//    tmp := sqlnt.MustCreateNamedTemplate(`INSERT INTO table (col_a,col_b) VALUES (:a, :b?)`)
	// makes the named arg "b" omissible (denoted by the '?' after name)
	OmissibleArgs(names ...string) NamedTemplate
	// DefaultValue specifies a value to be used for a given arg name when the arg
	// is not supplied in the map for Args or MustArgs
	//
	// Setting a default value for an arg name also makes that arg omissible
	//
	// If the value passed is a
	//   func(name string) any
	// then that func is called to obtain the default value
	DefaultValue(name string, v any) NamedTemplate
	// NullableStringArgs specifies the names of args that are nullable string
	// i.e. where the value is an empty string, null is used instead
	NullableStringArgs(names ...string) NamedTemplate
	// GetArgNames returns a map of the arg names (where the map value is a bool indicating whether
	// the arg is omissible
	//
	// NB. Each arg is immutable - changing it has no effect on the template
	GetArgNames() map[string]bool
	// GetArgsInfo returns a map of each named arg info
	//
	// NB. Each arg info is immutable - changing it has no effect on the template
	GetArgsInfo() map[string]ArgInfo
	// Clone clones the named template to another with a different option
	Clone(option Option) NamedTemplate
	// Append appends a statement portion to current statement and returns a new NamedTemplate
	//
	// Returns an error if the supplied statement portion cannot be parsed for arg names
	Append(portion string) (NamedTemplate, error)
	// MustAppend is the same as Append, except no error is returned (and panics on error)
	MustAppend(portion string) NamedTemplate
	// Exec performs sql.DB.Exec on the supplied db with the supplied named args
	Exec(db *sql.DB, args ...any) (sql.Result, error)
	// ExecContext performs sql.DB.ExecContext on the supplied db with the supplied named args
	ExecContext(ctx context.Context, db *sql.DB, args ...any) (sql.Result, error)
	// Query performs sql.DB.Query on the supplied db with the supplied named args
	Query(db *sql.DB, args ...any) (*sql.Rows, error)
	// QueryContext performs sql.DB.QueryContext on the supplied db with the supplied named args
	QueryContext(ctx context.Context, db *sql.DB, args ...any) (*sql.Rows, error)
}

NamedTemplate represents a named template

Use NewNamedTemplate or MustCreateNamedTemplate to create a new one

func MustCreateNamedTemplate

func MustCreateNamedTemplate(statement string, options ...any) NamedTemplate

MustCreateNamedTemplate creates a new NamedTemplate

is the same as NewNamedTemplate, except panics in case of error

func NewNamedTemplate

func NewNamedTemplate(statement string, options ...any) (NamedTemplate, error)

NewNamedTemplate creates a new NamedTemplate

Returns an error if the supplied template cannot be parsed for arg names

Multiple options can be specified - each must be either a sqlnt.Option or sqlnt.TokenOption

type Option

type Option interface {
	// UsePositionalTags specifies whether positional arg tags (e.g. $1, $2 etc.) can be used in the
	// final sql statement
	UsePositionalTags() bool
	// ArgTag specifies the string used as the arg placeholder in the final sql statement
	//
	// e.g. return "?" for MySql or "$" for Postgres
	ArgTag() string
}

Option is the interface that can be passed to NewNamedTemplate or MustCreateNamedTemplate and determines whether positional tags (i.e. numbered tags) can be used and the arg placeholder to be used

var (
	MySqlOption    Option = _MySqlOption    // option to produce final args like ?, ?, ? (e.g. for https://github.com/go-sql-driver/mysql)
	PostgresOption Option = _PostgresOption // option to produce final args like $1, $2, $3 (e.g. for https://github.com/lib/pq or https://github.com/jackc/pgx)
	DefaultsOption Option = _DefaultsOption // option to produce final args determined by DefaultUsePositionalTags and DefaultArgTag
)

type TokenOption

type TokenOption interface {
	// Replace receives the token and returns the replacement and a bool indicating whether to use the replacement
	Replace(token string) (string, bool)
}

TokenOption is an interface that can be provided to NewNamedTemplate or MustCreateNamedTemplate to replace tokens in the statement (tokens are denoted by `{{token}}`)

If tokens are found but none of the provided TokenOption implementations provides a replacement then NewNamedTemplate will error

type TokenOptionMap

type TokenOptionMap map[string]string

func (TokenOptionMap) Replace

func (m TokenOptionMap) Replace(token string) (string, bool)