sqldata

package module
v0.0.0-...-cce7947 Latest Latest
Warning

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

 Go to latest Published: Jul 9, 2025 License: Unlicense Imports: 12 Imported by: 0

README

Package sqldata was an experiment in connecting SQL queries with Go data structures.

This package is not an ORM, but meets a similar need. Its performance is competitive with using database/sql directly, at least if you use prepared statements. It provides conveniences for writing queries, passing arguments, and scanning results based on data structures.

This package is FROZEN and should probably not be used in new work. Everything here is functional, but after using this system in production for several years, I see too many warts. The template language is too specialized and too difficult to extend. The SQLer concept is not quite right.

On the other hand, I like using this package better than any other database access method I've used – in Go or otherwise. I just think it needs another iteration. I have much of the design of the next iteration done, but I haven't had time to finish implementing it yet. It will not be API-compatible with this package.

I do not intend to make any further changes to this package. When I have the replacement ready, I'll reference it here. If anyone besides me is using this, please contact me.

Installation

go get github.com/mstetson/sqldata

Usage

Usage is intended to be much like database/sql. See godoc for details.

GoDoc

Example

	type Person struct {
		ID    int64
		Name  string
		City  string
		State string
	}
	var Person p
	err := sqldata.QueryRow(db, `select {{.}} from Persons where ID={{$1}}`, &p, 5)
	if err != nil { panic(err) }

This software is released into the public domain. See LICENSE for details.

Thanks to SchoolsPLP, LLC for funding part of the work and allowing this code to be released freely.

Documentation

Overview

Package sqldata was an experiment in connecting SQL queries with Go data structures.

This packages is FROZEN and should probably not be used in new work. Everything here is functional, but after using this system in production for two years, I see too many warts. The template language is too specialized and too difficult to extend. The SQLer concept is not quite right.

On the other hand, I like using this package better than any other database access method I've used – in Go or otherwise. I just think it needs another iteration. I have much of the design of the next iteration done, but I haven't had time to finish implementing it yet. It will not be API-compatible with this package.

I do not intend to make any further changes to this package. When I have the replacement ready, I'll reference it here. If anyone besides me is using this, please contact me.

Original documentation

This package provides a simple query template language and a custom kind of prepared statement to make it easier to read and write structured data in an SQL database. SQL is a rich query language, and this package does not attempt to replace or hide it. Its intent is to provide the flexibility and efficiency of hand-written queries, while eliminating much of the tedium of using data structures in queries and keeping the queries synchronized with the data structures.

Query templates consist of literal SQL interspersed with template actions, which are delimited by "{{" and "}}". Template actions allow the query to depend on the types of the query parameters and result data structure. Non-prepared statements may also depend on parameter values using the getSQL construct described below.

Here is a trivial example that retrieves a single record by ID: 

type Person struct {
	ID    int64
	Name  string
	City  string
	State string
}
var Person p
err := sqldata.QueryRow(db, `select {{.}} from Persons where ID={{$1}}`, &p, 5)
if err != nil { panic(err) }

Actions

Here is the list of actions. "Receivers" and "parameters" are evaluations of data defined in detail below. 

{{receiver}}
	The field expressions generated by the receiver are
	inserted into the query, separated by commas. The
	result scanner is prepared to receive values for
	each field.

{{receiver tableAlias}}
	As {{receiver}} above, but the given table alias
	is prepended to column names. E.g. if {{.Foo}} would
	insert "ID, Name", then {{.Foo t}} will insert
	"t.ID, t.Name".

{{receiver "SQL expression"}}
	Instead of evaluating the receiver type for field
	expressions as normal, the given literal SQL
	expression is inserted into the query and the
	receiver is used as a single destination when
	scanning result rows.

{{exprs receiver}}
{{exprs receiver alias}}
{{exprs receiver alias "SQL expression"}}
	These work exactly as their corresponding non-expr
	versions above, except that they do not affect the
	expected result columns. This is particularly useful
	for UNION queries where the receiver fields may need
	to be referenced in each SELECT, but the number of
	result columns is established by the first.

{{table.column}}
	Only valid in sqldataExpr struct tags. Marks a table
	column reference so that a supplied table alias may
	be used with the expression. E.g. if the tag is
	`sum({{table.Price}})` and the column is used with
	a table alias of "p", then the query will contain
	"sum(p.Price)". See the sqldataExpr example below.

{{parameter}}
	A replacable parameter is put into the query at
	this point, which will reference the given parameter's
	value when the query is run.

{{values parameter}}
	A comma-separated list of replacable parameters is
	put into the query at this point, one for each non-excluded
	field in the parameter.

{{keyValues parameter}}
	As {{values parameter}} above but only includes
	fields marked as key values in their sqldata struct
	tag.

{{nonKeyValues parameter}}
	As {{values parameter}} above but only includes
	fields not marked as key values in their sqldata
	struct tag.

{{names parameter}}
	A comma-separated list of the names of non-excluded
	fields in the parameter is put into the query at
	this point.

{{keyNames parameter}}
	As {{names parameter}} above but only includes
	fields marked as key values in their sqldata struct
	tag.

{{nonKeyNames parameter}}
	As {{names parameter}} above but only includes
	fields not marked as key values in their sqldata
	struct tag.

{{names=values parameter}}
	A comma-separated list of names and replacable
	parameters is put into the query at this point, one
	for each non-excluded field in the parameter. E.g.
	if the parameter has fields "ID" and "Name", then
	the query will include "ID=?, Name=?" or the
	equivalent for the current SQL dialect.

{{keyNames=values parameter}}
	As {{names=values parameter}} above but only includes
	fields marked as key values in their sqldata struct
	tag.

{{nonKeyNames=values parameter}}
	As {{names=values parameter}} above but only includes
	fields not marked as key values in their sqldata
	struct tag.

{{$alias := $number}}
	An alias, which must be alphanumeric, is created to
	reference the given parameter number. Later in the
	query, $alias may be used instead $number.

{{$alias := shift}}
	This is equivalent to {{$alias := $1}}, except that
	it shifts the meaning of the parameter numbers used
	later in the query. $1 will now refer to what was $2,
	$2 to what was $3, etc. Shifts do not affect which
	parameter is referenced by previously established
	aliases.

{{dialect "name" ...}} T1 {{end}}
	If the current dialect name is equal to one of the
	quoted strings, include T1 in the query.

{{dialect "name" ...}} T1 {{else}} T0 {{end}}
	If the current dialect name is equal to one of the
	quoted strings, include T1 in the query. Otherwise,
	include T0.

{{dialect "name" ...}} T1 {{else dialect "name" ...}} T0 {{end}}
	This is a more concise equivalent to writing
		{{dialect "name" ...}} T1 {{else}}{{dialect "name" ...}} T0 {{end}}{{end}}
	Any number of {{else dialect}} clauses may be included,
	followed by an optional {{else}} clause.

{{getSQL parameter}}
	Only valid in non-prepared statements. The given
	parameter must implement the SQLer interface. Its
	SQL method is called and the resulting SQL text and
	parameters are inserted into the query.

Receivers

Receivers are used to specify how result rows are scanned into the destination. A receiver turns the destination type (or the type of a struct field within the destination) into a list of fields, each with an SQL expression and single-field scan destination. These expressions and destinations are used in the query as described under Actions above.

Receivers may take one of the following forms.

  • The character '.' (period). In this case, the destination type itself is used as the receiver.
  • The name of a field of the destination type, which must be a struct, preceded by a period, such as .Field In this case, the field is used as the receiver. Field references may be chained: .Field1.Field2

Pointers within the destination are dereferenced as needed. If the receiver type is a database/sql/driver.Value or implements the database/sql.Scanner interface, it is treated as a single field. Otherwise, the receiver must be of struct type, and its fields are used as the receiver fields.

Struct fields may be excluded from automatic use as receiver fields by setting their name to "-" using the sqldata struct tag (see below). They may still be used with an explicit SQL expression in the query template.

The field expression is one of the following, in order of preference:

  • The value of the field's sqldataExpr struct tag.
  • The field's name as set in its sqldata struct tag.
  • The field's name in the struct.

When the destination type is used directly as a single-value receiver, there is no struct field from which to derive an expression. In that case, an explicit expression must be provided as in `{{. "expression"}}`.

In a query that produces result rows, there must be a receiver field corresponding to each result column. Otherwise, scanning will result in an error.

Parameters

Parameters reference values in the arguments provided to the query. A parameter starts with the character '$' and a number. The number is the 1-based index of the argument being referenced; e.g. "$3" to reference the third argument. After the number, the parameter may include any number of field references consisting of a '.' character followed by the name of a field of the argument type, which must be a struct; e.g. "$1.Person.ID". Pointers are automatically dereferenced as needed.

No replacement parameters may be included in the query, except through template parameters.

Struct Tags

Package sqldata examines two struct tags: sqldata and sqldataExpr.

The sqldata tag is a comma-separated list of name and options much like that used by encoding/json. A name of "-" excludes the field from automatic use as a receiver field and by the names and values parameter constructs. The only option is "key" which specifies that the field is part of a table's primary key and interacts with several of the parameter actions above.

The sqldataExpr tag specifies an SQL expression to be used instead of the field's name when the field acts as a receiver. This expression may include the {{table.column}} action described above.

Here are a few examples: 

// Field is (mostly) ignored by this package.
Field int `sqldata:"-"`

// Field is stored in the database as "some_name".
Field int `sqldata:"some_name"`

// Field is stored as Field and is a key.
Field int `sqldata:",key"`

// Field is stored as some_name but retrieved using an expression.
Field int `sqldata:"some_name" sqldataExpr:"count({{table.Value}})"`
Example (Basic) 
package main

import (
	"database/sql"
	"fmt"
	"log"

	"github.com/mstetson/sqldata"

	_ "github.com/mattn/go-sqlite3"
)

var db *sql.DB

func init() {
	var err error
	db, err = sql.Open("sqlite3", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	e := &errExec{q: db}
	e.Exec(`
		create table Persons (
			ID integer not null primary key,
			Name text not null,
			City text not null,
			State text not null
		)
	`)
	e.Exec(`insert into Persons (ID, Name, City, State) values
		(1, "Bilbo", "The Hill", "The Shire"),
		(2, "Bombur", "Under the Mountain", "Lonely Mountain"),
		(3, "Beorn", "Carrock", "Wilderland"),
		(4, "Bard", "Dale", "Desolation of Smaug")`)
	if e.err != nil {
		log.Fatal(e.err)
	}
}

type errExec struct {
	q   sqldata.Queryer
	err error
}

func (e *errExec) Exec(query string, args ...interface{}) {
	if e.err != nil {
		return
	}
	_, e.err = sqldata.Exec(e.q, query, args...)
}

func main() {
	// A Person represents a row from the Persons table in the database.
	type Person struct {
		ID    int64
		Name  string
		City  string
		State string
	}

	// The {{}} notation allows the query writer to insert field expressions
	// and placeholders based on input and output parameter types.
	var (
		personByID, _ = sqldata.Prepare(db,
			`select {{.}} from Persons where ID={{$1}}`,
			Person{}, int64(0))
		peopleNamedLike, _ = sqldata.Prepare(db,
			`select {{.}} from Persons where Name like {{$1}}`,
			Person{}, "")
		insertPerson, _ = sqldata.Prepare(db,
			`insert into Persons ({{names $1}}) values ({{values $1}})`,
			nil, Person{})
		updatePerson, _ = sqldata.Prepare(db,
			`update Persons set {{names=values $2}} where ID={{$1}}`,
			nil, int64(0), Person{})
	)

	var err error
	var p Person
	var ps []Person

	// Insert a Person
	_, err = insertPerson.Exec(Person{5, "William The Conqueror", "London", "England"})
	if err != nil {
		log.Fatal(err)
	}

	// Load the Person with ID 5.
	err = personByID.QueryRow(5).Scan(&p)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println(p.Name, "has ID", p.ID)

	// Load a slice of Persons whose name starts with "william".
	// QueryAll scans all result rows into a slice.
	err = peopleNamedLike.QueryAll("william%").Scan(&ps)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("People named William:", len(ps))

	// Change William's primary key.
	oldID := p.ID
	p.ID = 1066
	_, err = updatePerson.Exec(oldID, p)
	if err != nil {
		log.Fatal(err)
	}
}

Output:

William The Conqueror has ID 5
People named William: 1
Example (SqldataExpr) 
package main

import (
	"database/sql"
	"time"

	"github.com/mstetson/sqldata"

	_ "github.com/mattn/go-sqlite3"
)

var db *sql.DB

func main() {
	// First, some structures for our tables.

	// A Person represents a row from the Persons table in the database.
	type Person struct {
		ID    int64
		Name  string
		City  string
		State string
	}

	// A Post represents a row from the Posts table in our database.
	type Post struct {
		ID       int64
		PersonID int64 // This Post was made by the Person with this ID.
		PostDate time.Time
		Content  string
	}

	// PostSummary represents aggregate information about a group of Posts.
	// Note the use of sqldataExpr to specify the aggregate functions we want.
	type PostSummary struct {
		TotalPosts   int       `sqldataExpr:"count({{table.ID}})"`
		LastPostDate time.Time `sqldataExpr:"max({{table.PostDate}})"`
	}

	// This will get the summary info for all Posts in the database.
	globalSummaryLoader, _ := sqldata.Prepare(db,
		`select {{.}} from Posts`, PostSummary{})
	defer globalSummaryLoader.Close()

	// Now we want the same summary data grouped by person.
	type PersonWithSummary struct {
		Person
		PostSummary
	}
	personSummaryLoader, _ := sqldata.Prepare(db,
		`select {{.Person p}}, {{.PostSummary s}}
		from Persons p join Posts s on (s.PersonID=p.ID)
		group by {{.Person p}}
		order by {{.PostSummary.LastPostDate s}} desc`,
		PersonWithSummary{})
	defer personSummaryLoader.Close()

	// And the same summary data with a different grouping.
	type PostsByWeekday struct {
		Weekday int
		PostSummary
	}
	postsByWeekdayLoader, _ := sqldata.Prepare(db,
		`select {{.Weekday "weekday(PostDate)"}}, {{.PostSummary}}
		from Posts group by weekday(PostDate)`,
		PostsByWeekday{})
	defer postsByWeekdayLoader.Close()
}

Index

Examples

Constants

This section is empty.

Variables

View Source 
var DefaultDialect = &Dialect{
	Name:            "default",
	QuoteString:     StandardQuoteString,
	QuoteIdentifier: StandardQuoteIdentifier,
	Parameter:       ParameterQuestion,
}

DefaultDialect is the default Dialect used by Prepare, Exec, Query, QueryRow, and QueryAll.

Functions

func ErrorLocation 

func ErrorLocation(e error) (file string, line int)

ErrorLocation returns the source location of an error produced by this package. If the location is unknown or the error was not produced by this package, it returns "unknown", 0.

func Exec 

func Exec(db Queryer, query string, args ...interface{}) (sql.Result, error)

Exec executes a statement with the given arguments and returns an sql.Result summarizing the effect of the statement.

Exec is a wrapper around DefaultDialect.Exec.

func ParameterDollarN 

func ParameterDollarN(n int) string

ParameterDollarN returns "$n" where n is the decimal value of n. It is intended for use in Dialects.

func ParameterQuestion 

func ParameterQuestion(_ int) string

ParameterQuestion returns "?". It is intended for use in Dialects.

func QueryAll 

func QueryAll(db Queryer, query string, dest interface{}, args ...interface{}) error

QueryAll is like Query, but retrieves all result rows and appends them to dest, which must be a slice.

Note that there is no need for a separate Scan method, since the destination must be specified for query generation.

QueryAll is a wrapper around DefaultDialect.QueryAll.

Example 
package main

import (
	"database/sql"
	"fmt"
	"log"

	"github.com/mstetson/sqldata"

	_ "github.com/mattn/go-sqlite3"
)

var db *sql.DB

func main() {
	// A Person represents a row from the Persons table in the database.
	type Person struct {
		ID    int64
		Name  string
		City  string
		State string
	}
	var ps []Person
	err := sqldata.QueryAll(db, `select {{.}} from Persons where ID < 5 order by ID`, &ps)
	if err != nil {
		log.Fatal(err)
	}
	for i := range ps {
		fmt.Println(ps[i].Name)
	}
}

Output:

Bilbo
Bombur
Beorn
Bard

func QueryRow 

func QueryRow(db Queryer, query string, dest interface{}, args ...interface{}) error

QueryRow is like Query, but it scans the first result row into dest. If the query returns no rows, QueryRow returns sql.ErrNoRows. Any result rows beyond the first are ignored.

Note that there is no need for a separate Scan method, since the destination must be specified for query generation.

QueryRow is a wrapper around DefaultDialect.QueryRow.

func RelocateError 

func RelocateError(e error) error

RelocateError changes the reported source location of an error produced by this package to the site of the caller of the caller of RelocateError. Many of the functions in this package return error values that try to reference the source location of a query template. When wrapping such functions, those references can become incorrect. RelocateError can help.

Error values not produced by this package are returned unchanged.

func RelocateErrorTo 

func RelocateErrorTo(e error, file string, line int) error

RelocateErrorTo changes the reported source location of an error produced by this package to the given file and line number. Many of the functions in this package return error values that try to reference the source location of a query template. When wrapping such functions, those references can become incorrect. RelocateErrorTo can help.

Error values not produced by this package are returned unchanged.

func StandardQuoteIdentifier 

func StandardQuoteIdentifier(s string) string

StandardQuoteIdentifier returns s enclosed in double quote characters with any embedded double quotes replaced by a pair of double quotes. It is assumed that the SQL implementation can handle arbitraray UTF-8 in quoted identifiers.

func StandardQuoteString 

func StandardQuoteString(s string) string

StandardQuoteString returns s enclosed in single quote characters with any embedded single quotes replaced by a pair of single quotes. It is assumed that the SQL implementation can handle arbitraray UTF-8 in string literals.

Types

type AllRows 

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

An AllRows is returned by Stmt.QueryAll. See Stmt.QueryAll and AllRows.Scan.

func (*AllRows) Scan 

func (r *AllRows) Scan(dest interface{}) error

Scan appends all the result rows in AllRows to the slice pointed to by dest. Dest's element type must be the same as (or a pointer to) the destination type passed to Prepare.

type AtInit 

type AtInit []func(Queryer, *Dialect) error

An AtInit holds things that should be done at program initialization, but not without an open DB and knowledge of the SQL dialect. It's mostly useful for preparing SQL statements.

Example 
package main

import (
	"database/sql"
	"log"

	"github.com/mstetson/sqldata"

	_ "github.com/mattn/go-sqlite3"
)

var db *sql.DB

func init() {
	var err error
	db, err = sql.Open("sqlite3", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	e := &errExec{q: db}
	e.Exec(`
		create table Persons (
			ID integer not null primary key,
			Name text not null,
			City text not null,
			State text not null
		)
	`)
	e.Exec(`insert into Persons (ID, Name, City, State) values
		(1, "Bilbo", "The Hill", "The Shire"),
		(2, "Bombur", "Under the Mountain", "Lonely Mountain"),
		(3, "Beorn", "Carrock", "Wilderland"),
		(4, "Bard", "Dale", "Desolation of Smaug")`)
	if e.err != nil {
		log.Fatal(e.err)
	}
}

type errExec struct {
	q   sqldata.Queryer
	err error
}

func (e *errExec) Exec(query string, args ...interface{}) {
	if e.err != nil {
		return
	}
	_, e.err = sqldata.Exec(e.q, query, args...)
}

func main() {
	// Usually, this will be package scoped.
	var atInit = new(sqldata.AtInit)

	// Someplace else, declare a type.
	type Part struct {
		ID   int64
		Name string
	}

	// And some prepared statements using the type.
	var (
		someRandomQuery = atInit.Prepare(
			`select {{.}} from Parts where ID={{$1}}`,
			Part{},
			int64(0),
		)
		someOtherQuery = atInit.Prepare(
			`select {{.}} from Parts where Name like {{$1}}`,
			Part{},
			"",
		)
	)

	// Later, open the database and initialize everything.
	db, err := sql.Open("driver", "dsn")
	if err != nil {
		log.Fatal(err)
	}
	err = atInit.Init(db)
	if err != nil {
		log.Fatal(err)
	}

	// Then use the prepared statements.
	_ = someRandomQuery.QueryRow(5)
	_ = someOtherQuery.QueryAll("widget")
}

func (*AtInit) Do 

func (atInit *AtInit) Do(fn func(Queryer, *Dialect) error)

Do appends a function to the list of functions to perform at init.

func (*AtInit) Init 

func (atInit *AtInit) Init(db Queryer) error

Init calls atInit.InitDialect(db, DefaultDialect).

func (*AtInit) InitDialect 

func (atInit *AtInit) InitDialect(db Queryer, d *Dialect) error

InitDialect runs each of the functions that have been set to run at init. If any function returns an error, the error is returned and no further functions are called.

func (*AtInit) Prepare 

func (atInit *AtInit) Prepare(query string, resultType interface{}, argTypes ...interface{}) *Stmt

Prepare schedules the SQL query to be prepared at database initialization. The returned *Stmt will be usable after a successful call to atInit.Init().

type ContextQueryer 

type ContextQueryer interface {
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
}

Queryer abstracts sql.DB, sql.Conn, and sql.Tx. It differs from Queryer by using the ...Context methods.

type Dialect 

type Dialect struct {
	// Name gives a name to this dialect for use by the
	// {{dialect "name"}} construct. Name is free form and
	// has no particular meaning to this package.
	Name string

	// QuoteString returns the properly-quoted string
	// literal representation of s for use in an SQL query.
	QuoteString func(s string) string

	// QuoteIdentifier returns the properly-quoted identifier form
	// of s for use in an SQL query. Note that the returned
	// string need not actually be quoted if QuoteIdentifier
	// determines that quoting is unnecessary.
	QuoteIdentifier func(s string) string

	// Parameter returns the string used to mark the nth
	// replacable parameter in a query. Parameters are
	// numbered from 1. Parameters are always used in order
	// in the query and each is only referenced once, so
	// dialects that can only reference parameters that
	// way are supported.
	Parameter func(n int) string
}

A Dialect hides some of the differences between implementations of SQL.

func (*Dialect) Exec 

func (d *Dialect) Exec(db Queryer, query string, args ...interface{}) (sql.Result, error)

Exec executes a statement with the given arguments and returns an sql.Result summarizing the effect of the statement.

func (*Dialect) Prepare 

func (d *Dialect) Prepare(db Queryer, query string, resultType interface{}, argTypes ...interface{}) (*Stmt, error)

Prepare creates a Stmt. It parses the query according to the template language defined above and generates SQL based on the given result type and argument types. resultType should have the same type as a single result row, and each argType the type of an argument to be passed to the Stmt's Query or Exec methods. The generated SQL statement is prepared for use with db.

func (*Dialect) Query 

func (d *Dialect) Query(db Queryer, query string, resultType interface{}, args ...interface{}) (*Rows, error)

Query parses query according to the template language defined above and executes it on db for the given result type and arguments. Since the argument values are available, the getSQL action may be used in the query.

func (*Dialect) QueryAll 

func (d *Dialect) QueryAll(db Queryer, query string, dest interface{}, args ...interface{}) error

QueryAll is like Query, but retrieves all result rows and appends them to dest, which must be a slice.

Note that there is no need for a separate Scan method, since the destination must be specified for query generation.

func (*Dialect) QueryRow 

func (d *Dialect) QueryRow(db Queryer, query string, dest interface{}, args ...interface{}) error

QueryRow is like Query, but it scans the first result row into dest. If the query returns no rows, QueryRow returns sql.ErrNoRows. Any result rows beyond the first are ignored.

Note that there is no need for a separate Scan method, since the destination must be specified for query generation.

type Int64Values 

type Int64Values []int64

Int64Values is an SQLer that produces a parenthesized list of the values in Slice. This is useful for queries like 

r, err := sqldata.Query(db,
	"select {{.}} from Table where ID in {{getSQL $1}}",
	Person{},
	sqldata.Int64Values{1, 2, 3},
)

Have a care about NOT IN and empty lists. Try `IFNULL(foo NOT IN {{getSQL $1}}, TRUE)`.

func (Int64Values) SQL 

func (s Int64Values) SQL(_ *Dialect, _ *ParamMarker) (string, []interface{}, error)

type ListValues 

type ListValues struct {
	Slice interface{}
}

ListValues is an SQLer that produces a parenthesized list of the values in Slice. This is useful for queries like 

r, err := sqldata.Query(db,
	"select {{.}} from Table where ID in {{getSQL $1}}",
	Person{},
	sqldata.ListValues{[]int{1, 2, 3}},
)

Note: If the list is empty, the sql will be "(NULL)". This often will work as expected for "WHERE foo IN (NULL)", but BE CAREFUL! Both "foo IN (NULL)" and "foo NOT IN (NULL)" evaluate to NULL in SQL – neither true nor false.

The problem is that SQL has no syntax for an empty list, and tricks with subqueries like '(select 0 from dual where 0=1)' are database-specific and sometimes problematic. See MySQL bug #44339. Caveat SQLor.

func (ListValues) SQL 

func (l ListValues) SQL(d *Dialect, p *ParamMarker) (string, []interface{}, error)

SQL implements the SQLer interface.

type ParamMarker 

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

A ParamMarker uses a Dialect to get the replaceable parameter markers for a single query. It is used both internally and by SQLers.

func NewParamMarker 

func NewParamMarker(d *Dialect) *ParamMarker

NewParamMarker returns a zeroed ParamMarker that uses Dialect d.

func (*ParamMarker) Next 

func (p *ParamMarker) Next() string

Next returns the next parameter marker string for p.

type Queryer 

type Queryer interface {
	Exec(query string, args ...interface{}) (sql.Result, error)
	Prepare(query string) (*sql.Stmt, error)
	Query(query string, args ...interface{}) (*sql.Rows, error)
}

A Queryer can prepare and execute database/sql queries. It allows methods not to care whether they are called with *sql.DB or *sql.Tx.

func WithContext 

func WithContext(ctx context.Context, q ContextQueryer) Queryer

WithContext returns a Queryer that uses q and ctx behind the scenes. This allows adapting existing sqldata code to use context without as much editing.

Where you may have used sqldata.Query(db, query) you now can use sqldata.Query(sqldata.WithContext(ctx, db), query).

type Row 

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

A Row is returned by Stmt.QueryRow. See Stmt.QueryRow and Row.Scan.

func (*Row) Scan 

func (r *Row) Scan(dest interface{}) error

Scan copies the columns in the Row into the value pointed at by dest. Dest must be a pointer to the same type as the destination type passed to Prepare.

type Rows 

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

Rows is a wrapper around sql.Rows which will scan result rows into data structures according to a query template's receiver specifications.

func Query 

func Query(db Queryer, query string, resultType interface{}, args ...interface{}) (*Rows, error)

Query parses query according to the template language defined above and executes it on db for the given result type and arguments. Since the argument values are available, the getSQL action may be used in the query.

Query is a wrapper around DefaultDialect.Query.

func (*Rows) Close 

func (r *Rows) Close() error

Close closes the Rows, preventing further enumeration. If Next returns false, the Rows are closed automatically and it will suffice to check the result of Err. Close is idempotent and does not affect the result of Err.

func (*Rows) Columns 

func (r *Rows) Columns() ([]string, error)

Columns returns the column names. Columns returns an error if the rows are closed.

func (*Rows) Err 

func (r *Rows) Err() error

Err returns the error, if any, that was encountered during iteration. Err may be called after an explicit or implicit Close.

func (*Rows) Next 

func (r *Rows) Next() bool

Next prepares the next result row for reading with the Scan method. It returns true on success, false if there is no next result row. Every call to Scan, even the first one, must be preceded by a call to Next.

func (*Rows) Scan 

func (r *Rows) Scan(dest interface{}) error

Scan copies the columns in the current row into the value pointed at by dest. Dest must be a pointer to the same type as the destination type passed to Prepare.

type SQLer 

type SQLer interface {
	// SQL returns a portion of an SQL query along with
	// argument values for any positional parameters in this
	// query portion. The Dialect and ParamMarker should be
	// used to generate the SQL fragment as appropriate.
	SQL(d *Dialect, p *ParamMarker) (string, []interface{}, error)
}

An SQLer has the SQL method. See the {{getSQL}} template action above for more details.

type Stmt 

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

Stmt is a prepared statement. Stmt is safe for concurrent use by multiple goroutines.

func Prepare 

func Prepare(db Queryer, query string, resultType interface{}, argTypes ...interface{}) (*Stmt, error)

Prepare creates a Stmt. It parses the query according to the template language defined above and generates SQL based on the given result type and argument types. resultType should have the same type as a single result row, and each argType the type of an argument to be passed to the Stmt's Query or Exec methods. The generated SQL statement is prepared for use with db.

Prepare is a wrapper around DefaultDialect.Prepare.

func (*Stmt) Close 

func (s *Stmt) Close() error

Close closes the statement.

func (*Stmt) Exec 

func (s *Stmt) Exec(args ...interface{}) (sql.Result, error)

Exec executes a prepared statement with the given arguments and returns a sql.Result summarizing the effect of the statement.

func (*Stmt) Query 

func (s *Stmt) Query(args ...interface{}) (*Rows, error)

Query executes a prepared query statement with the given arguments and returns the query results as a *Rows.

The argument types must match those passed to Prepare.

func (*Stmt) QueryAll 

func (s *Stmt) QueryAll(args ...interface{}) *AllRows

QueryAll executes a prepared query statement with the given arguments. If an error occurs during the execution of the statement, that error will be returned by a call to Scan on the returned *AllRows, which is always non-nil.

The argument types must match those passed to Prepare.

func (*Stmt) QueryRow 

func (s *Stmt) QueryRow(args ...interface{}) *Row

QueryRow executes a prepared query statement with the given arguments. If an error occurs during the execution of the statement, that error will be returned by a call to Scan on the returned *Row, which is always non-nil. If the query selects no rows, the *Row's Scan will return sql.ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

The argument types must match those passed to Prepare.

func (*Stmt) Tx 

func (s *Stmt) Tx(tx *sql.Tx) *Stmt

Tx returns a transaction-specific prepared statement from an existing statement.

type TupleKeyValues 

type TupleKeyValues struct {
	Slice interface{}
}

TupleKeyValues is just like TupleValues, except it only uses values that would be selected by the keyValues template function.

func (TupleKeyValues) SQL 

func (l TupleKeyValues) SQL(d *Dialect, p *ParamMarker) (string, []interface{}, error)

SQL implements the SQLer interface.

type TupleNonKeyValues 

type TupleNonKeyValues struct {
	Slice interface{}
}

TupleNonKeyValues is just like TupleValues, except it only uses values that would be selected by the nonKeyValues template function.

func (TupleNonKeyValues) SQL 

func (l TupleNonKeyValues) SQL(d *Dialect, p *ParamMarker) (string, []interface{}, error)

SQL implements the SQLer interface.

type TupleValues 

type TupleValues struct {
	Slice interface{}
}

TupleValues is an SQLer that produces a comma-separated list of parenthesized tuple values. This is useful for multi-record inserts like 

r, err := sqldata.Exec(db,
	"insert into Table ({{names $1}}) values {{getSQL $2}}",
	Person{},
	sqldata.TupleValues{[]Person{p1, p2, p3}},
)

func (TupleValues) SQL 

func (l TupleValues) SQL(d *Dialect, p *ParamMarker) (string, []interface{}, error)

SQL implements the SQLer 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.