sqlt

README

sqlt

Description

Simple SQL template.

Sample (PostgreSQL)

Template

Template is compatible with SQL.
Use /*% and %*/ as delimiter instead of {{ and }} for processing template.

SELECT *
FROM users
WHERE id IN /*% in "ids" %*/(1, 2)
AND name = /*% p "name" %*/'John Doe'
/*%- if .onlyMale %*/
AND sex = 'MALE'
/*%- end %*/
ORDER BY /*% .order %*/id

Go code

• func param or p replace to placeholder by name.
• func in deploy slice values to parentheses and placeholders.
• func time returns current time and cache it, this func always same time in same template.
• func now returns current time each calling.
• func escape, prefix, inffix, suffix replace to placeholder with escape for LIKE keyword.
• If you want customized time in template, you can set TimeFunc.
• If database driver that you use supports sql.NamedArg, you should call ExecNamed func.

// sql is generated SQL from template.
// args are arguments for generated SQL.
query, args, err := sqlt.New(sqlt.Postgres).Exec(s, map[string]interface{}{
	"ids":      []int{1, 2, 3},
	"order":    "name DESC",
	"onlyMale": false,
	"name":     "Alex",
})
rows, err := db.Query(sql, args...)

Generated SQL

call Exec

SELECT *
FROM users
WHERE id IN ($1, $2, $3)
AND name = $4
ORDER BY name DESC

call ExecNamed

Currently there are also many drivers who do not support sql.NamedArg.
In future, driver support sql.NamedArg, you only need to change Exec to ExecNamed.

SELECT *
FROM users
WHERE id IN (:ids__1, :ids__2, :ids__3)
AND name = :name
ORDER BY name DESC

Install

$ go get github.com/pinzolo/sqlt

Suppor

Go version

Go 1.9 or later

Databses

• PostgreSQL
• MySQL
• Oracle
• SQL Server

Contribution

1. Fork (https://github.com/pinzolo/sqlt/fork)
2. Create a feature branch
3. Commit your changes
4. Rebase your local changes against the master branch
5. Run test suite with the go test ./... command and confirm that it passes
6. Run gofmt -s
7. Create a new Pull Request

Author

pinzolo

Documentation

Index

• Constants
• Variables
• type Dialect
• type SQLTemplate
  • func New(dialect Dialect) SQLTemplate
  • func (st SQLTemplate) AddFunc(name string, fn interface{}) SQLTemplate
  • func (st SQLTemplate) AddFuncs(funcs map[string]interface{}) SQLTemplate
  • func (st SQLTemplate) Exec(text string, m map[string]interface{}) (string, []interface{}, error)
  • func (st SQLTemplate) ExecNamed(text string, m map[string]interface{}) (string, []sql.NamedArg, error)

Constants

const (
	// LeftDelim is start delimiter for SQL template.
	LeftDelim = "/*%"
	// RightDelim is end delimiter for SQL template.
	RightDelim = "%*/"
	// Connector is delimiter that is used when `sqlt` makes original argument name.
	Connector = "__"
)

Variables

var (
	// Postgres is PostgreSQL dialect resolver.
	Postgres = postgres{}
	// MySQL is MySQL dialect resolver.
	MySQL = mysql{}
	// Oracle is Oracle dialect resolver.
	Oracle = oracle{}
	// SQLServer is SQLServer dialect resolver.
	SQLServer = sqlserver{}
)

Types

type Dialect

type Dialect interface {
	// IsOrdinalPlaceholderSupportedreturns true if databse support ordinal placeholder.
	// ex: $1, $2 (PostgreSQL)
	IsOrdinalPlaceholderSupported() bool
	// OrdinalPlaceholderPrefix returns prefix of placeholder.
	// This is used when IsOrdinalPlaceholderSupported is true in Exec func.
	OrdinalPlaceholderPrefix() string
	// Placeholder character.
	// This is used when IsOrdinalPlaceholderSupported is false.
	Placeholder() string
	// NamedPlaceholderPrefix returns prefix of placeholder.
	// This is used in ExecNamed func.
	NamedPlaceholderPrefix() string
	// WildcardRunes are wildcard characters that are used with `LIKE`.
	WildcardRunes() []rune
}

Dialect resolves dialect of each databse.

type SQLTemplate

type SQLTemplate struct {

	// TimeFunc used `time` and `now` function in template.
	// This func should return current time.
	// If this function is not set, used `time.Now()` as default function.
	TimeFunc func() time.Time
	// contains filtered or unexported fields
}

SQLTemplate is template struct.

func New

func New(dialect Dialect) SQLTemplate

New template initialized with dialect.

func (SQLTemplate) AddFunc

func (st SQLTemplate) AddFunc(name string, fn interface{}) SQLTemplate

AddFunc add custom template func.

func (SQLTemplate) AddFuncs

func (st SQLTemplate) AddFuncs(funcs map[string]interface{}) SQLTemplate

AddFuncs add custom template functions.

func (SQLTemplate) Exec

func (st SQLTemplate) Exec(text string, m map[string]interface{}) (string, []interface{}, error)

Exec executes given template with given map parameters. This function replaces to normal placeholder.

func (SQLTemplate) ExecNamed

func (st SQLTemplate) ExecNamed(text string, m map[string]interface{}) (string, []sql.NamedArg, error)

ExecNamed executes given template with given map parameters. This function replaces to named placeholder.