sqlt

README

A Go Template-Based SQL Builder and Struct Mapper

go get -u github.com/go-sqlt/sqlt

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

Struct mapping is handled by the structscan package. The Scan function (== structscan.Scan[Dest]()) provides a fluent API for field-based value extraction and transformation.

Example

package main

import (
	"context"
	"database/sql"
	"fmt"
	"math/big"
	"net/url"
	"time"

	"github.com/go-sqlt/sqlt"
	_ "modernc.org/sqlite"
)

type Data struct {
	Int    int64
	String string
	Bool   bool
	Time   time.Time
	Big    *big.Int
	URL    *url.URL
	Slice  []string
	JSON   map[string]any
}

var query = sqlt.All[string, Data](sqlt.Parse(`
	SELECT
		100                                    {{ Scan.Int.To "Int" }}
		, NULL                                 {{ Scan.Nullable.String.To "String" }}
		, true                                 {{ Scan.Bool.To "Bool" }}
		, {{ . }}                              {{ (Scan.String.ParseTime DateOnly).To "Time" }}
		, '300'                                {{ Scan.Text.To "Big" }}
		, 'https://example.com/path?query=yes' {{ Scan.Binary.To "URL" }}
		, 'hello,world'                        {{ (Scan.String.Split ",").To "Slice" }}
		, '{"hello":"world"}'                  {{ Scan.JSON.To "JSON" }}
`))

func main() {
	db, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		panic(err)
	}

	data, err := query.Exec(context.Background(), db, time.Now().Format(time.DateOnly))
	if err != nil {
		panic(err)
	}

	fmt.Println(data) // [{100  true 2025-07-22 00:00:00 +0000 UTC 300 https://example.com/path?query=yes [hello world] map[hello:world]}]
}

Documentation

Overview

Package sqlt provides a type-safe SQL builder and struct mapper using Go's text/template engine.

Struct mapping is handled by the structscan(https://pkg.go.dev/github.com/go-sqlt/structscan) package. The Dest function returns a structscan.Schema[Dest], which provides a fluent API for field-based value extraction and transformation.

Example:

package main

import (

"context"
"database/sql"
"fmt"
"math/big"
"net/url"
"time"

"github.com/go-sqlt/sqlt"
_ "modernc.org/sqlite"

)

type Data struct {
	Int    int64
	String string
	Bool   bool
	Time   time.Time
	Big    *big.Int
	URL    *url.URL
	Slice  []string
	JSON   map[string]any
}

var query = sqlt.All[string, Data](sqlt.Parse(`

SELECT
	100                                    {{ Scan.Int.To "Int" }}
	, NULL                                 {{ Scan.Nullable.String.To "String" }}
	, true                                 {{ Scan.Bool.To "Bool" }}
	, {{ . }}                              {{ (Scan.String.ParseTime DateOnly).To "Time" }}
	, '300'                                {{ Scan.Text.To "Big" }}
	, 'https://example.com/path?query=yes' {{ Scan.Binary.To "URL" }}
	, 'hello,world'                        {{ (Scan.String.Split ",").To "Slice" }}
	, '{"hello":"world"}'                  {{ Scan.JSON.To "JSON" }}

`))

func main() {
	db, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		panic(err)
	}

	data, err := query.Exec(context.Background(), db, time.Now().Format(time.DateOnly))
	if err != nil {
		panic(err)
	}

	fmt.Println(data) // [{100  true 2025-07-09 00:00:00 +0000 UTC 300 https://example.com/path?query=yes [hello world] map[hello:world]}]
}

Index

• type Config
  • func AtP() Config
  • func Colon() Config
  • func Dollar() Config
  • func ExpressionExpiration(expiration time.Duration) Config
  • func ExpressionSize(size int) Config
  • func Funcs(fm template.FuncMap) Config
  • func Hasher(fn func(param any) (uint64, error)) Config
  • func Logger(fn func(ctx context.Context, info Info)) Config
  • func Lookup(name string) Config
  • func New(name string) Config
  • func Parse(txt string) Config
  • func ParseFS(sys fs.FS, patterns ...string) Config
  • func ParseFiles(filenames ...string) Config
  • func ParseGlob(pattern string) Config
  • func PositionalPlaceholder(p string) Config
  • func Question() Config
  • func StaticPlaceholder(p string) Config
  • func (c Config) With(configs ...Config) Config
• type DB
• type Expression
• type Info
  • func (i Info) CollapsedSQL() string
• type Pgx
• type PgxStatement
  • func AllPgx[Param any, Dest any](configs ...Config) PgxStatement[Param, []Dest]
  • func CustomPgx[Param any, Dest any, Result any](exec func(ctx context.Context, db Pgx, expr Expression[Dest]) (Result, error), ...) PgxStatement[Param, Result]
  • func ExecPgx[Param any](configs ...Config) PgxStatement[Param, pgconn.CommandTag]
  • func FirstPgx[Param any, Dest any](configs ...Config) PgxStatement[Param, Dest]
  • func OnePgx[Param any, Dest any](configs ...Config) PgxStatement[Param, Dest]
  • func QueryPgx[Param any](configs ...Config) PgxStatement[Param, pgx.Rows]
  • func QueryRowPgx[Param any](configs ...Config) PgxStatement[Param, pgx.Row]
• type Raw
• type Statement
  • func All[Param any, Dest any](configs ...Config) Statement[Param, []Dest]
  • func Custom[Param any, Dest any, Result any](exec func(ctx context.Context, db DB, expr Expression[Dest]) (Result, error), ...) Statement[Param, Result]
  • func Exec[Param any](configs ...Config) Statement[Param, sql.Result]
  • func First[Param any, Dest any](configs ...Config) Statement[Param, Dest]
  • func One[Param any, Dest any](configs ...Config) Statement[Param, Dest]
  • func Query[Param any](configs ...Config) Statement[Param, *sql.Rows]
  • func QueryRow[Param any](configs ...Config) Statement[Param, *sql.Row]

Types

type Config

type Config struct {
	Placeholder          func(pos int, builder *strings.Builder) error
	Logger               func(ctx context.Context, info Info)
	ExpressionSize       int
	ExpressionExpiration time.Duration
	Hasher               func(value any) (uint64, error)
	Parsers              []func(tpl *template.Template) (*template.Template, error)
}

Config defines options for SQL template parsing and execution. Fields are merged; later values override earlier ones. Parsers are appended.

func AtP

func AtP() Config

AtP returns a Config that uses positional placeholders with "@p" (e.g., "@p1", "@p2").

func Colon

func Colon() Config

Colon returns a Config that uses positional placeholders with ":" (e.g., ":1", ":2").

func Dollar

func Dollar() Config

Dollar returns a Config that uses positional placeholders with "$" (e.g., "$1", "$2").

func ExpressionExpiration

func ExpressionExpiration(expiration time.Duration) Config

ExpressionExpiration sets how long cached expressions are valid. Avoid with non-deterministic templates.

func ExpressionSize

func ExpressionSize(size int) Config

ExpressionSize sets the number of reusable expressions to cache. Avoid if your templates are non-deterministic.

func Funcs

func Funcs(fm template.FuncMap) Config

Funcs adds a FuncMap to the template.

func Hasher

func Hasher(fn func(param any) (uint64, error)) Config

Hasher sets a custom function for hashing parameters (used for caching of expressions). Uses datahash by default. Exclude fields with: `datahash:"-"`.

func Logger

func Logger(fn func(ctx context.Context, info Info)) Config

Logger adds a callback for logging execution metadata per statement.

func Lookup

func Lookup(name string) Config

Lookup adds a parser equivalent to template.Lookup(name).

func New

func New(name string) Config

New adds a parser that creates a new named template within the Config.

func Parse

func Parse(txt string) Config

Parse adds a parser that parses the provided string using template.Parse.

func ParseFS

func ParseFS(sys fs.FS, patterns ...string) Config

ParseFS adds a parser that loads templates from an fs.FS source using patterns.

func ParseFiles

func ParseFiles(filenames ...string) Config

ParseFiles adds a parser that loads and parses templates from file paths.

func ParseGlob

func ParseGlob(pattern string) Config

ParseGlob adds a parser that loads templates matching a glob pattern.

func PositionalPlaceholder

func PositionalPlaceholder(p string) Config

PositionalPlaceholder formats placeholders using a prefix and 1-based index (e.g., "$1", ":1", "@p1").

func Question

func Question() Config

Question returns a Config that uses "?" as the SQL placeholder (e.g., for SQLite or MySQL).

func StaticPlaceholder

func StaticPlaceholder(p string) Config

StaticPlaceholder uses the same placeholder string for all parameters (e.g., "?").

func (Config) With

func (c Config) With(configs ...Config) Config

With merges the current Config with additional Configs. Later fields override earlier ones. Parsers are appended in order.

type DB

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

DB defines the subset of database operations required by sqlt. It is implemented by *sql.DB and *sql.Tx.

type Expression

type Expression[Dest any] struct {
	SQL    string
	Args   []any
	Schema *structscan.Schema[Dest]
}

Expression holds the rendered SQL, arguments, and row mapper.

type Info

type Info struct {
	Duration time.Duration
	Template string
	Location string
	SQL      string
	Args     []any
	Err      error
	Cached   bool
}

Info contains metadata collected during statement execution for optional logging.

func (Info) CollapsedSQL

func (i Info) CollapsedSQL() string

CollapsedSQL removes double whitespace for logging.

type Pgx

type Pgx interface {
	Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
	QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
	Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
}

type PgxStatement

type PgxStatement[Param, Result any] interface {
	Exec(ctx context.Context, db Pgx, param Param) (Result, error)
}

PgxStatement can be used with pgx connection or pool.

func AllPgx

func AllPgx[Param any, Dest any](configs ...Config) PgxStatement[Param, []Dest]

AllPgx returns a PgxStatement that maps all rows in the result set to a slice of Dest. Dollar placeholder is used by default.

func CustomPgx

func CustomPgx[Param any, Dest any, Result any](exec func(ctx context.Context, db Pgx, expr Expression[Dest]) (Result, error), configs ...Config) PgxStatement[Param, Result]

CustomPgx creates a PgxStatement using the provided function to execute the rendered SQL expression. Dollar placeholder is used by default. This allows advanced behavior such as custom row scanning or side effects.

func ExecPgx

func ExecPgx[Param any](configs ...Config) PgxStatement[Param, pgconn.CommandTag]

ExecPgx returns a PgxStatement that executes a SQL statement without returning rows. Dollar placeholder is used by default.

func FirstPgx

func FirstPgx[Param any, Dest any](configs ...Config) PgxStatement[Param, Dest]

FirstPgx returns a PgxStatement that maps the first row from a query to Dest. Dollar placeholder is used by default.

func OnePgx

func OnePgx[Param any, Dest any](configs ...Config) PgxStatement[Param, Dest]

OnePgx returns a PgxStatement that expects exactly one row in the result set. Dollar placeholder is used by default. It returns an structscan.ErrTooManyRows if more than one row is returned.

func QueryPgx

func QueryPgx[Param any](configs ...Config) PgxStatement[Param, pgx.Rows]

QueryPgx returns a PgxStatement that runs the query and returns *sql.Rows. Dollar placeholder is used by default.

func QueryRowPgx

func QueryRowPgx[Param any](configs ...Config) PgxStatement[Param, pgx.Row]

QueryRowPgx returns a PgxStatement that runs the query and returns a single *sql.Row. Dollar placeholder is used by default.

type Raw

type Raw string

Raw is a string type that inserts raw SQL into a template without interpolation or escaping.

type Statement

type Statement[Param, Result any] interface {
	Exec(ctx context.Context, db DB, param Param) (Result, error)
}

Statement is a compiled SQL template that runs with parameters and a DB.

func All

func All[Param any, Dest any](configs ...Config) Statement[Param, []Dest]

All returns a Statement that maps all rows in the result set to a slice of Dest.

func Custom

func Custom[Param any, Dest any, Result any](exec func(ctx context.Context, db DB, expr Expression[Dest]) (Result, error), configs ...Config) Statement[Param, Result]

Custom creates a Statement using the provided function to execute the rendered SQL expression. This allows advanced behavior such as custom row scanning or side effects.

func Exec

func Exec[Param any](configs ...Config) Statement[Param, sql.Result]

Exec returns a Statement that executes a SQL statement without returning rows.

func First

func First[Param any, Dest any](configs ...Config) Statement[Param, Dest]

First returns a Statement that maps the first row from a query to Dest.

func One

func One[Param any, Dest any](configs ...Config) Statement[Param, Dest]

One returns a Statement that expects exactly one row in the result set. It returns an structscan.ErrTooManyRows if more than one row is returned.

func Query

func Query[Param any](configs ...Config) Statement[Param, *sql.Rows]

Query returns a Statement that runs the query and returns *sql.Rows.

func QueryRow

func QueryRow[Param any](configs ...Config) Statement[Param, *sql.Row]

QueryRow returns a Statement that runs the query and returns a single *sql.Row.