litsql

package module
v0.6.6 Latest Latest
Warning

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

 Go to latest Published: Jun 5, 2024 License: MIT Imports: 1 Imported by: 5

README

litsql - Literal SQL query builder

Test Status GitHub go.mod Go version Go Reference Go Report Card GitHub tag (latest SemVer) Coverage Status

litsql is a Golang string concatenation library disguised as an SQL query builder.

Ok, it really is an SQL query builder, but it aims to be an easier-to-use replacement for raw SQL strings.

Each litsql statement must be directly related to an SQL output, including whitespace (backed by whitespace tests), which must be obvious to the user of the library. The output will be exactly the passed values, so the library won't prevent invalid SQL from being generated.

func ExampleSelect_literalSimple() {
    // SELECT
    q := psql.Select(
        // u.id, u.name
        sm.Columns("u.id", "u.name"),
        // , u.created_at, u.updated_at
        sm.Columns("u.created_at", "u.updated_at"),
        // FROM users AS u
        sm.From("users AS u"),
        // WHERE u.age > $1
        sm.WhereC("u.age > ?", 40),
        // WHERE u.city_id = $2
        sm.WhereC("u.city_id = ?", sq.NamedArg("city_id")),
        // AND u.deleted_at IS NOT NULL
        sm.Where("u.deleted_at IS NOT NULL"),
        // ORDER BY u.name ASC, u.age DESC
        sm.OrderBy("u.name ASC", "u.age DESC"),
    )
    qs, args, err := q.Build(
        sq.WithParseArgs(map[string]any{
            "city_id": 66,
        }),
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(qs)
    fmt.Println("===")
    fmt.Println(args)

    // Output:
    // SELECT u.id, u.name, u.created_at, u.updated_at
    // FROM users AS u
    // WHERE u.age > $1 AND u.city_id = $2 AND u.deleted_at IS NOT NULL
    // ORDER BY u.name ASC, u.age DESC
    // ===
    // [40 66]
}

The library will do:

  • ensure clause ordering
  • enforce some kind of code structure
  • be type-safe without using any too much
  • guarantee whitespace. extra whitespace is considered a bug
  • output correct argument characters for each database dialect

The library won't do:

  • prevent invalid SQL from being output
  • quoting
  • execute queries in databases
  • provide helper expressions to build things like "IsEQ()", "Not(expression)", "LT(value)". These are expected to be written as strings
  • be an ORM (never)

Installation

go get -u github.com/rrgmc/litsql

Reference

This library is heavily inspired by the excellent Bob Go SQL Access Toolkit. Its base ideas and some of its implementations where used to build this library.

The biggest difference is that Bob is not only a query builder, but an ORM, so the query builder part must be much more complex to be able to tackle multiple jobs. It encourages using Go to code SQL expressions, which this library heavily discourages.

Dialects

Sub-packages

  • structargs: get named arguments from struct fields

Examples

func ExampleSelect_literalJoin() {
    // SELECT
    q := psql.Select(
        // orders.id as order_id, orders.date
        sm.Columns("orders.id AS order_id", "orders.date"),
        // u.id AS user_id, u.name as user_name
        sm.Columns("u.id AS user_id", "u.name AS user_name"),
        // FROM orders
        sm.From("orders"),
        // INNER JOIN users AS u ON orders.user_id = u.id
        sm.InnerJoin("users AS u").On("orders.user_id = u.id"),
        // WHERE u.age > $1
        sm.WhereC("u.age ?",
            // example to use either IS NULL or a comparison
            expr.ExprIfElse(true, // some condition
                expr.C("> ?", 32),
                expr.S("IS NULL"))),
        // AND u.deleted_at IS NOT NULL
        sm.Where("u.deleted_at IS NOT NULL"),
        // ORDER BY order.date DESC, u.name ASC
        sm.OrderBy("orders.date DESC", "u.name ASC"),
    )
    qs, args, err := q.Build()
    if err != nil {
        panic(err)
    }
    fmt.Println(qs)
    fmt.Println("===")
    fmt.Println(args)

    // Output:
    // SELECT orders.id AS order_id, orders.date, u.id AS user_id, u.name AS user_name
    // FROM orders
    // INNER JOIN users AS u ON orders.user_id = u.id
    // WHERE u.age > $1 AND u.deleted_at IS NOT NULL
    // ORDER BY orders.date DESC, u.name ASC
    // ===
    // [32]
}

func ExampleSelect_literalWith() {
    q := psql.Select(
        // WITH regional_sales AS (
        sm.With("regional_sales").As(
            // SELECT
            psql.Select(
                // region, SUM(amount) AS total_sales
                sm.Columns("region", "SUM(amount) AS total_sales"),
                // FROM orders
                sm.From("orders"),
                // GROUP BY region
                sm.GroupBy("region"),
            ),
        ),
        // ), top_regions AS (
        sm.With("top_regions").As(
            // SELECT
            psql.Select(
                // region
                sm.Columns("region"),
                // FROM regional_sales
                sm.From("regional_sales"),
                // WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
                sm.WhereC("total_sales > ?",
                    psql.Select(
                        sm.Columns("SUM(total_sales)/10"),
                        sm.From("regional_sales"),
                    ),
                ),
            ),
        ),
        // )
        // SELECT
        // region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales
        sm.Columns("region", "product", "SUM(quantity) AS product_units", "SUM(amount) AS product_sales"),
        // FROM orders
        sm.From("orders"),
        // WHERE region IN (SELECT region FROM top_regions)
        sm.WhereC("region IN ?",
            psql.Select(
                sm.Columns("region"),
                sm.From("top_regions"),
            ),
        ),
        // GROUP BY region, product
        sm.GroupBy("region", "product"),
    )
    qs, _, err := q.Build()
    if err != nil {
        panic(err)
    }
    fmt.Println(qs)

    // Output:
    // WITH regional_sales AS (
    //   SELECT region, SUM(amount) AS total_sales
    //   FROM orders
    //   GROUP BY region
    // ),
    // top_regions AS (
    //   SELECT region
    //   FROM regional_sales
    //   WHERE total_sales > (
    //     SELECT SUM(total_sales)/10
    //     FROM regional_sales
    //   )
    // )
    // SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales
    // FROM orders
    // WHERE region IN (
    //   SELECT region
    //   FROM top_regions
    // )
    // GROUP BY region, product
}

Tasks

Dynamic query building
type userFilter struct {
    Name string
}
filter := userFilter{
    Name: "john",
}
query := psql.Select(
    sm.Columns("id", "name"),
    sm.From("users"),
)
if filter.Name != "" {
    query.Apply(
        sm.WhereC("name = ?", filter.Name),
    )
}
Select from subselect
query := psql.Select(
    sm.Columns("id", "name", "age"),
    sm.FromQ(psql.Select(
        sm.Columns("id", "name", "age"),
        sm.From("users"),
        sm.Where("age > 10"),
    )),
)
WHERE value IN
query := psql.Select(
    sm.Columns("id", "name", "age"),
    sm.From("users"),
    sm.WhereC("age IN (?)", expr.In([]any{15, 30, 45})),
)
WHERE value IN using named arguments
query := psql.Select(
    sm.Columns("id", "name", "age"),
    sm.From("users"),
    sm.WhereC("age IN (?)", expr.In([]any{
        sq.NamedArg("first"),
        sq.NamedArg("second"),
        sq.NamedArg("third"),
    })),
)
qs, args, err := query.Build(
    sq.WithParseArgs(map[string]any{
        "first":  15,
        "second": 30,
        "third":  45,
    }),
)
WHERE value IN subselect
query := psql.Select(
    sm.Columns("id", "name", "age"),
    sm.From("users"),
    sm.WhereC("region IN ?",
        psql.Select(
            sm.Columns("region"),
            sm.From("top_regions"),
        ),
    ),
)
Expression function to generate using custom code
query := psql.Select(
    sm.Columns("id", "name", "age"),
    sm.From("users"),
    sm.WhereC("age > ?",
        expr.F(func() (litsql.Expression, error) {
            r := rand.Intn(3)
            switch r {
            case 0:
                return expr.Arg(20), nil
            case 1:
                return expr.Arg(30), nil
            default:
                return expr.Arg(50), nil
            }
        }),
    ),
)
Add clauses in inline callback
query := psql.Select(
    sm.Columns("id", "name", "age"),
    sm.From("users"),
    sm.Apply(func(a psql.SelectModApply) {
        a.Apply(
            sm.Where("age > 10"),
        )
    }),
)
Use IS NULL or a condition depending on a flag
v := any(32)
query := psql.Select(
    sm.Columns("id", "name", "age"),
    sm.From("users"),
    sm.WhereC("u.age ?",
        expr.ExprIfElse(v != nil,
            expr.C("> ?", 32),
            expr.S("IS NULL"))),
)
OR expression
query := psql.Select(
    sm.Columns("id", "name", "age"),
    sm.From("users"),
    sm.WhereE(
        expr.Or(
            "(age > 10 AND city_id = 12)",
            "(age < 10 AND city_id = 15)",
        ),
    ),
)
UNION
query := psql.Select(
    sm.Columns("id", "name", "age"),
    sm.From("users"),
    sm.Where("age < 10"),
    sm.Union(psql.Select(
        sm.Columns("id", "name", "age"),
        sm.From("users"),
        sm.Where("age > 50"),
    )),
)
Prepared statements

When using prepared statements, the use of named arguments is required, as it would be impossible to know which argument maps to each value.

query := psql.Select(
    sm.Columns("film_id", "title", "length"),
    sm.From("film"),
    sm.WhereC("length > ?", sq.NamedArg("length")),
    sm.LimitE(expr.ArgNamed("limit")),
)

queryStr, args, err := query.Build()
if err != nil {
    return err
}

prepq, err := db.PrepareContext(ctx, queryStr)
if err != nil {
    return err
}

pargs, err := sq.ParseArgs(args, map[string]any{
    "length": 100,
    "limit":  10,
})
if err != nil {
    return err
}

rows, err := prepq.QueryContext(ctx, pargs...)
if err != nil {
    return err
}
defer rows.Close()

for rows.Next() {
    var id, length int
    var title string
    if err := rows.Scan(&id, &title, &length); err != nil {
        return err
    }
    fmt.Println(id, title, length)
}

if rows.Err() != nil {
    return rows.Err()
}

Author

Rangel Reale (rangelreale@gmail.com)

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (
	ErrClause = errors.New("clause error")
)

Functions

func Express 

func Express(w Writer, d Dialect, start int, e Expression) ([]any, error)

Express writes the expression to the output. If e is nil, nothing is done.

func ExpressIf 

func ExpressIf(w Writer, d Dialect, start int, e Expression, cond bool, prefix, suffix Expression) ([]any, error)

ExpressIf expands an express if the condition evaluates to true it can also add a prefix and suffix

func ExpressSlice 

func ExpressSlice(w Writer, d Dialect, start int, expressions []Expression, prefix, sep, suffix Expression) ([]any, error)

ExpressSlice is used to express a slice of expressions along with a prefix and suffix

Types

type ArgValues 

type ArgValues interface {
	Get(string) (any, bool)
}

ArgValues is the supplier of values for named arguments.

type Argument 

type Argument interface {
	// contains filtered or unexported methods
}

Argument is the base interface for query arguments.

type ArgumentBase 

type ArgumentBase struct{}

type DBNamedArgument 

type DBNamedArgument interface {
	Argument
	DBName() string
}

DBNamedArgument is like NamedArgument but its value will be wrapped using [sql.Named].

type Dialect 

type Dialect interface {
	WriteArg(w Writer, position int)
	WriteQuoted(w Writer, s string)
	WriteCheckQuoted(w Writer, s string) // quote only if string contains characters that need quoting.
}

Dialect implements dialect-specific methods.

type DialectWithNamed 

type DialectWithNamed interface {
	Dialect
	WriteNamedArg(w Writer, name string)
}

DialectWithNamed implements dialects that support db-specific named arguments.

type ExpressBuilder 

type ExpressBuilder interface {
	Express(e Expression)
	ExpressIf(e Expression, cond bool, prefix, suffix Expression)
	ExpressSlice(expressions []Expression, prefix, sep, suffix Expression)
	WriteQuery(e Query)
	Result() ([]any, error)
	Err() error
}

ExpressBuilder builds arguments in a sequence of Express calls.

func NewExpressBuilder 

func NewExpressBuilder(w Writer, d Dialect, start int) ExpressBuilder

type Expression 

type Expression interface {
	WriteSQL(w Writer, d Dialect, start int) (args []any, err error)
}

Expression is the base expression interface.

type ExpressionFunc 

type ExpressionFunc func(w Writer, d Dialect, start int) ([]any, error)

ExpressionFunc is the functional implementation of Expression.

func (ExpressionFunc) WriteSQL 

func (e ExpressionFunc) WriteSQL(w Writer, d Dialect, start int) ([]any, error)

type MapArgValues 

type MapArgValues map[string]any

MapArgValues is an ArgValues backed from a map[string]any.

func (MapArgValues) Get 

func (m MapArgValues) Get(s string) (any, bool)

type NamedArgument 

type NamedArgument interface {
	Argument
	Name() string
}

NamedArgument represents an argument were its value will be provided by name.

type Query 

type Query interface {
	Expression
	WriteQuery(w Writer, start int) (args []any, err error)
}

Query is the base interface for queries.

func QueryFunc added in v0.3.2 

func QueryFunc(dialect Dialect, expression Expression, f func(w Writer, start int) (args []any, err error)) Query

QueryFunc is a functional implementation of Query. If f is nil, WriteSQL will be called directly by WriteQuery.

type QueryBuilder 

type QueryBuilder interface {
	Dialect() Dialect
	AddQueryClause(q QueryClause)
}

QueryBuilder is the base interface for queries built by lists of clauses.

type QueryClause 

type QueryClause interface {
	Expression
	ClauseID() string
	ClauseOrder() int
}

QueryClause is a query clause.

type QueryClauseMerge 

type QueryClauseMerge interface {
	QueryClause
	ClauseMerge(other QueryClause) error
}

QueryClauseMerge can be implemented by QueryClause when its data can be merged.

type QueryClauseMultiple 

type QueryClauseMultiple interface {
	QueryClause
	ClauseMultiple()
}

QueryClauseMultiple can be implemented by QueryClause to signal multiple instances can be added.

type ValuedArgument 

type ValuedArgument interface {
	Argument
	Value() (any, error)
}

ValuedArgument represents an argument were its value will be provided by this instance.

type Writer 

type Writer interface {
	// Write writes a string.
	Write(s string)
	// WriteNewLine writes a newline if in newline-mode, or nothing if not.
	WriteNewLine()
	// WriteSeparator writes a newline if in newline-mode, or a space if not.
	WriteSeparator()
	// AddSeparator schedules a WriteSeparator to be written on the next Write, except on the first Write call.
	// If toplevel is true, will try to write a newline if enabled, if false will add a space.
	AddSeparator(topLevel bool)
	// StartQuery signals the writer that a new query (or subquery) will start. It resets the "first Write" flag.
	StartQuery()
	// Indent increases indentation by 1 (only in newline-mode).
	Indent()
	// Dedent decreases indentation by 1 (only in newline-mode).
	Dedent()
	// Err returns any errors that were generated in the write process.
	Err() error
}

Writer is the interface used by expressions to output strings.

Source Files

View all Source files

Directories

Path Synopsis
dialect
mysql
mysql/dm
mysql/im
mysql/sm
mysql/tag
mysql/um
psql
psql/dm
psql/im
psql/sm
psql/tag
psql/um
sqlite
sqlite/dm
sqlite/im
sqlite/sm
sqlite/tag
sqlite/um
ichain
iclause
idm
iim
imod
ism
isq
ium
testutils
dialectgen Module
sq
chain
clause
mod
reflectxargs Module
structargs Module

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.