sqlt

package module
v0.1.13 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Dec 8, 2024 License: MIT Imports: 16 Imported by: 3

README

sqlt: A Go Template-Based SQL Builder and ORM

go.dev reference GitHub tag (latest SemVer) codecov

import "github.com/wroge/sqlt"

sqlt uses Go’s template engine to create a flexible, powerful, and type-safe SQL builder and ORM.

Type-Safety without a Build Step

  • Define SQL statements at the global level using functions like New, Parse, ParseFiles, ParseFS, ParseGlob, Funcs and Lookup.
  • Templates are validated via jba/templatecheck during application startup.
  • Execute statements using methods such as Exec, Query or QueryRow.
  • Execute query statements using First, One or All.
  • Use Scan functions to map columns to struct fields (Scan for sql.Scanner's, ScanInt64 for int64, ScanString for string, ScanTime for time.Time, ScanStringP for *string, etc.).
  • Single-column queries do not require Scan functions.
type Insert struct {
	ID    int64
	Title string
}

var insertBooks = sqlt.Stmt[[]Insert](
	sqlt.Parse(`
		INSERT INTO books (id, title) VALUES
			{{ range $i, $v := . }} 
				{{ if $i }}, {{ end }}
				({{ $v.ID }}, {{ $v.Title }})
			{{ end }}
		RETURNING id;
	`),
)

type Query struct {
	Title string
}

type Book struct {
	ID    int64
	Title string
}

var queryBooks = sqlt.QueryStmt[Query, Book](
	sqlt.New("query_books"),
	sqlt.Parse(`
		SELECT
			{{ ScanInt64 Dest.ID "id" }}
			{{ ScanString Dest.Title ", title" }}
		FROM books
		WHERE title = {{ .Titel }};
	`),
)
// panic: location: [/.../main.go:17]: template: query_books:6:19: checking "query_books" at <.Titel>: can't use field Titel in type main.Query

var queryID = sqlt.QueryStmt[string, int64](
	sqlt.Parse(`SELECT id FROM books WHERE title = {{ . }};`),
)

result, err := insertBooks.Exec(ctx, db, []Insert{
	{ID: 1, Title: "The Hobbit"},
	{ID: 2, Title: "Harry Potter and the Philosopher's Stone"},
})

books, err := queryBooks.All(ctx, db, Query{Title: "The Hobbit"})

id, err := queryID.One(ctx, db, "The Hobbit")

Support for multiple Dialects and Placeholders

  • Templates are escaped, ensuring the package is not vulnerable to SQL injection.
  • You can use both static placeholders (?) and positional placeholders (Go format strings like %d).
  • This package supports any template functions (like lower or fail from Masterminds/sprig).
  • Multiple dialects can be used by implementing your own template functions.
var queryBooks = sqlt.QueryStmt[string, Book](
	sqlt.Dollar(), // equivalent to sqlt.Placeholder("$%d")
	sqlt.Funcs(sprig.TxtFuncMap()),
	sqlt.Funcs(template.FuncMap{
		"Dialect": func() string {
			return "postgres"
		},
	}),
	sqlt.Parse(`
		SELECT
			{{ ScanInt64 Dest.ID "id" }}
			{{ ScanString Dest.Title ", title" }}
		FROM books
		WHERE
		{{ if eq Dialect "sqlite" }}
			INSTR(LOWER(title), {{ lower . }})
		{{ else if eq Dialect "postgres" }}
			POSITION({{ lower . }} IN LOWER(title)) > 0
		{{ else }}
			{{ fail "invalid dialect" }}
		{{ end }};
	`),
)

books, err := queryBooks.All(ctx, db, "The Hobbit")
// SELECT id, title FROM books WHERE POSITION($1 IN LOWER(title)) > 0; ["the hobbit"]

Outsourcing Options into a Config

  • All options can be grouped into a configuration struct for reusability.
  • The Start and End functions enable monitoring and logging of SQL queries.
type StartTime struct{}

var config = sqlt.Config{
	Placeholder: sqlt.Dollar(),
	TemplateOptions: []sqlt.TemplateOption{
		sqlt.Funcs(sprig.TxtFuncMap()),
		sqlt.Funcs(template.FuncMap{
			"Dialect": func() string {
				return "postgres"
			},
		}),
	},
	Start: func(runner *sqlt.Runner) {
		runner.Context = context.WithValue(runner.Context, StartTime{}, time.Now())
	},
	End: func(err error, runner *sqlt.Runner) {
		fmt.Println("location=%s, sql=%s, duration=%s", runner.Location, runner.SQL, time.Since(runner.Context.Value(StartTime{}).(time.Time)))
	},
}

var queryBooks = sqlt.QueryStmt[string, Book](
	config,
	sqlt.Parse(`
		SELECT
			{{ ScanInt64 Dest.ID "id" }}
			{{ ScanString Dest.Title ", title" }}
		FROM books
		WHERE
		{{ if eq Dialect "sqlite" }}
			INSTR(LOWER(title), {{ lower . }})
		{{ else if eq Dialect "postgres" }}
			POSITION({{ lower . }} IN LOWER(title)) > 0
		{{ else }}
			{{ fail "invalid dialect" }}
		{{ end }};
	`),
)

Any more Questions?

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var ErrTooManyRows = errors.New("too many rows")

ErrTooManyRows is returned from One, when there are more than one rows.

Functions

func InTx 

func InTx(ctx context.Context, opts *sql.TxOptions, db *sql.DB, do func(db DB) error) (err error)

InTx simplifies the execution of multiple queries in a transaction.

Types

type Config added in v0.0.60 

type Config struct {
	Start           Start
	End             End
	Placeholder     Placeholder
	TemplateOptions []TemplateOption
}

Config groups the available options.

func (Config) Configure added in v0.1.5 

func (c Config) Configure(config *Config)

Configure implements the Option interface.

type DB 

type DB interface {
	QueryContext(ctx context.Context, str string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, str string, args ...any) *sql.Row
	ExecContext(ctx context.Context, str string, args ...any) (sql.Result, error)
}

DB is implemented by *sql.DB and, *sql.Tx.

type End added in v0.1.5 

type End func(err error, runner *Runner)

End is executed when a Runner is put back into a statement pool.

func (End) Configure added in v0.1.5 

func (e End) Configure(config *Config)

Configure implements the Option interface.

type Option added in v0.0.60 

type Option interface {
	Configure(config *Config)
}

Options are used to configure the statements.

type Placeholder added in v0.1.5 

type Placeholder string

Placeholder can be static or positional using a go-formatted string ('%d').

func AtP added in v0.1.5 

func AtP() Placeholder

AtP is a positional placeholder.

func Colon added in v0.1.5 

func Colon() Placeholder

Colon is a positional placeholder.

func Dollar added in v0.1.5 

func Dollar() Placeholder

Dollar is a positional placeholder.

func Question added in v0.1.5 

func Question() Placeholder

Question is a static placeholder.

func (Placeholder) Configure added in v0.1.5 

func (p Placeholder) Configure(config *Config)

Configure implements the Option interface.

type QueryRunner added in v0.1.5 

type QueryRunner[Dest any] struct {
	Runner  *Runner
	Dest    *Dest
	Values  []any
	Mappers []func() error
}

QueryRunner groups the relevant data for each 'run' of a QueryStatement.

func (*QueryRunner[Dest]) Reset added in v0.1.5 

func (qr *QueryRunner[Dest]) Reset()

Reset the QueryRunner for the next run of a statement.

type QueryStatement added in v0.0.60 

type QueryStatement[Param, Dest any] struct {
	// contains filtered or unexported fields
}

QueryStatement is a QueryRunner pool and a type-safe sql query executor.

func QueryStmt added in v0.0.60 

func QueryStmt[Param, Dest any](opts ...Option) *QueryStatement[Param, Dest]

QueryStmt creates a type-safe QueryStatement using variadic options. Define the mapping of a column to a struct field here using the Scan functions. Invalid templates panic.

func (*QueryStatement[Param, Dest]) All added in v0.0.60 

func (qs *QueryStatement[Param, Dest]) All(ctx context.Context, db DB, param Param) (result []Dest, err error)

All returns a slice of Dest for each row.

func (*QueryStatement[Param, Dest]) First added in v0.0.60 

func (qs *QueryStatement[Param, Dest]) First(ctx context.Context, db DB, param Param) (result Dest, err error)

First returns the first row mapped into Dest.

func (*QueryStatement[Param, Dest]) Get added in v0.1.10 

func (qs *QueryStatement[Param, Dest]) Get(ctx context.Context) *QueryRunner[Dest]

Get a QueryRunner from the pool and execute the start option.

func (*QueryStatement[Param, Dest]) One added in v0.0.60 

func (qs *QueryStatement[Param, Dest]) One(ctx context.Context, db DB, param Param) (result Dest, err error)

One returns exactly one Dest. If there is more than one row in the result set, ErrTooManyRows is returned.

func (*QueryStatement[Param, Dest]) Put added in v0.1.10 

func (qs *QueryStatement[Param, Dest]) Put(err error, runner *QueryRunner[Dest])

Put a QueryRunner into the pool and execute the end option.

type Raw added in v0.0.20 

type Raw string

Raw is used to write strings directly into the sql output. It should be used carefully.

type Runner 

type Runner struct {
	Context  context.Context
	Template *template.Template
	SQL      *SQL
	Args     []any
	Location string
}

Runner groups the relevant data for each 'run' of a Statement.

func (*Runner) Exec added in v0.0.55 

func (r *Runner) Exec(db DB, param any) (sql.Result, error)

Exec creates and execute the sql query using ExecContext.

func (*Runner) Query added in v0.0.55 

func (r *Runner) Query(db DB, param any) (*sql.Rows, error)

Query creates and execute the sql query using QueryContext.

func (*Runner) QueryRow added in v0.0.55 

func (r *Runner) QueryRow(db DB, param any) (*sql.Row, error)

Query creates and execute the sql query using QueryRow.

func (*Runner) Reset added in v0.0.26 

func (r *Runner) Reset()

Reset the Runner for the next run of a statement.

type SQL added in v0.0.40 

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

SQL implements io.Writer and fmt.Stringer.

func (*SQL) Reset added in v0.0.40 

func (w *SQL) Reset()

Reset the internal byte slice.

func (*SQL) String added in v0.0.40 

func (w *SQL) String() string

String implements the fmt.Stringer interface.

func (*SQL) Write added in v0.0.40 

func (w *SQL) Write(data []byte) (int, error)

Write implements the io.Writer interface.

type Scanner 

type Scanner struct {
	Value any
	Map   func() error
	SQL   string
}

A Scanner is used to map columns to struct fields. Value should be a pointer to a struct field.

func Scan added in v0.0.38 

func Scan[T any](dest *T, str string) (Scanner, error)

Scan is a Scanner for values, that can be used directly with your sql driver.

func ScanJSON added in v0.0.38 

func ScanJSON[T any](dest *T, str string) (Scanner, error)

ScanJSON is a Scanner to unmarshal byte strings into T.

type Start added in v0.1.5 

type Start func(runner *Runner)

Start is executed when a Runner is returned from a statement pool.

func (Start) Configure added in v0.1.5 

func (s Start) Configure(config *Config)

Configure implements the Option interface.

type Statement added in v0.0.60 

type Statement[Param any] struct {
	// contains filtered or unexported fields
}

Statements is a Runner pool and a type-safe sql executor.

func Stmt added in v0.0.60 

func Stmt[Param any](opts ...Option) *Statement[Param]

Stmt creates a type-safe Statement using variadic options. Invalid templates panic.

func (*Statement[Param]) Exec added in v0.0.60 

func (s *Statement[Param]) Exec(ctx context.Context, db DB, param Param) (result sql.Result, err error)

Exec takes a runner and executes it.

func (*Statement[Param]) Get added in v0.1.5 

func (s *Statement[Param]) Get(ctx context.Context) *Runner

Get a Runner from the pool and execute the start option.

func (*Statement[Param]) Put added in v0.1.5 

func (s *Statement[Param]) Put(err error, runner *Runner)

Put a Runner into the pool and execute the end option.

func (*Statement[Param]) Query added in v0.0.60 

func (s *Statement[Param]) Query(ctx context.Context, db DB, param Param) (rows *sql.Rows, err error)

Query takes a runner and queries rows.

func (*Statement[Param]) QueryRow added in v0.0.60 

func (s *Statement[Param]) QueryRow(ctx context.Context, db DB, param Param) (row *sql.Row, err error)

QueryRow takes a runner and queries a row.

type TemplateOption added in v0.1.5 

type TemplateOption func(tpl *template.Template) (*template.Template, error)

TemplateOption can be used to configure the template of a statement.

func Funcs added in v0.0.60 

func Funcs(fm template.FuncMap) TemplateOption

Funcs is equivalent to the method from text/template.

func Lookup added in v0.0.60 

func Lookup(name string) TemplateOption

Lookup is equivalent to the method from text/template.

func MissingKeyError added in v0.0.60 

func MissingKeyError() TemplateOption

MissingKeyError is equivalent to the method 'Option("missingkey=error")' from text/template.

func MissingKeyInvalid added in v0.0.60 

func MissingKeyInvalid() TemplateOption

MissingKeyInvalid is equivalent to the method 'Option("missingkey=invalid")' from text/template.

func MissingKeyZero added in v0.0.60 

func MissingKeyZero() TemplateOption

MissingKeyZero is equivalent to the method 'Option("missingkey=zero")' from text/template.

func New 

func New(name string) TemplateOption

New is equivalent to the method from text/template.

func Parse added in v0.0.60 

func Parse(text string) TemplateOption

Parse is equivalent to the method from text/template.

func ParseFS added in v0.0.60 

func ParseFS(fs fs.FS, patterns ...string) TemplateOption

ParseFS is equivalent to the method from text/template.

func ParseFiles added in v0.0.60 

func ParseFiles(filenames ...string) TemplateOption

ParseFiles is equivalent to the method from text/template.

func ParseGlob added in v0.0.60 

func ParseGlob(pattern string) TemplateOption

ParseGlob is equivalent to the method from text/template.

func (TemplateOption) Configure added in v0.1.5 

func (to TemplateOption) Configure(config *Config)

Configure implements the Option interface.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.