sqltemplate

package
v11.1.4-modfix Latest Latest
Warning

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

 Go to latest Published: Aug 20, 2024 License: AGPL-3.0 Imports: 7 Imported by: 0

Documentation

Overview

Example 
package main

import (
	"fmt"
	"regexp"
	"strings"
	"text/template"
)

// This file contains runnable examples. They serve the purpose of providing
// idiomatic usage of the package as well as showing how it actually works,
// since the examples are actually run together with regular Go tests. Note that
// the "Output" comment section at the end of each function starting with
// "Example" is used by the standard Go test tool to check that the standard
// output of the function matches the commented text until the end of the
// function. If you change the function, you may need to adapt that comment
// section as it's possible that the output changes, causing it to fail tests.
// To learn more about Go's runnable tests, which are a core builtin feature of
// Go's standard testing library, see:
//	https://pkg.go.dev/testing#hdr-Examples
//
// If you're unfamiliar with Go text templating language, please, consider
// reading that library's documentation first.

// In this example we will use both Args and Dialect to dynamically and securely
// build SQL queries, while also keeping track of the arguments that need to be
// passed to the database methods to replace the placeholder "?" with the
// correct values.

// We will start by assuming we receive a request to retrieve a user's
// information and that we need to provide a certain response.

type GetUserRequest struct {
	ID int
}

type GetUserResponse struct {
	ID   int
	Type string
	Name string
}

// Our template will take care for us of taking the request to build the query,
// and then sort the arguments for execution as well as preparing the values
// that need to be read for the response. We wil create a struct to pass the
// request and an empty response, as well as a *SQLTemplate that will provide
// the methods to achieve our purpose::

type GetUserQuery struct {
	*SQLTemplate
	Request  *GetUserRequest
	Response *GetUserResponse
}

// And finally we will define our template, that is free to use all the power of
// the Go templating language, plus the methods we added with *SQLTemplate:
var getUserTmpl = template.Must(template.New("example").Parse(`
	SELECT
			{{ .Ident "id"   | .Into .Response.ID }},
			{{ .Ident "type" | .Into .Response.Type }},
			{{ .Ident "name" | .Into .Response.Name }}

		FROM {{ .Ident "users" }}
		WHERE
			{{ .Ident "id" }} = {{ .Arg .Request.ID }};
`))

// There are three interesting methods used in the above template:
//	1. Ident: safely escape a SQL identifier. Even though here the only
//	   identifier that may be problematic is "type" (because it is a reserved
//	   word in many dialects), it is a good practice to escape all identifiers
//	   just to make sure we're accounting for all variability in dialects, and
//	   also for consistency.
//	2. Into: this causes the selected field to be saved to the corresponding
//	   field of GetUserQuery.
//	3. Arg: this allows us to state that at this point will be a "?" that has to
//	   be populated with the value of the given field of GetUserQuery.

func main() {
	// Let's pretend this example function is the handler of the GetUser method
	// of our service to see how it all works together.

	queryData := &GetUserQuery{
		// The dialect (in this case we chose MySQL) should be set in your
		// service at startup when you connect to your database
		SQLTemplate: New(MySQL),

		// This is a synthetic request for our test
		Request: &GetUserRequest{
			ID: 1,
		},

		// Create an empty response to be populated
		Response: new(GetUserResponse),
	}

	// The next step is to execute the query template for our queryData, and
	// generate the arguments for the db.QueryRow and row.Scan methods later
	query, err := Execute(getUserTmpl, queryData)
	if err != nil {
		panic(err) // terminate the runnable example on error
	}

	// Assuming that we have a *sql.DB object named "db", we could now make our
	// query with:
	//	row := db.QueryRowContext(ctx, query, queryData.GetArgs()...)
	//	// and check row.Err() here

	// As we're not actually running a database in this example, let's verify
	// that we find our arguments populated as expected instead:
	if len(queryData.GetArgs()) != 1 {
		panic(fmt.Sprintf("unexpected number of args: %#v", queryData.Args))
	}
	id, ok := queryData.GetArgs()[0].(int)
	if !ok || id != queryData.Request.ID {
		panic(fmt.Sprintf("unexpected args: %#v", queryData.Args))
	}

	// In your code you would now have "row" populated with the row data,
	// assuming that the operation succeeded, so you would now scan the row data
	// abd populate the values of our response:
	//	err := row.Scan(queryData.GetScanDest()...)
	//	// and check err here

	// Again, as we're not actually running a database in this example, we will
	// instead run the code to assert that queryData.ScanDest was populated with
	// the expected data, which should be pointers to each of the fields of
	// Response so that the Scan method can write to them:
	if len(queryData.GetScanDest()) != 3 {
		panic(fmt.Sprintf("unexpected number of scan dest: %#v", queryData.ScanDest))
	}
	idPtr, ok := queryData.GetScanDest()[0].(*int)
	if !ok || idPtr != &queryData.Response.ID {
		panic(fmt.Sprintf("unexpected response 'id' pointer: %#v", queryData.ScanDest))
	}
	typePtr, ok := queryData.GetScanDest()[1].(*string)
	if !ok || typePtr != &queryData.Response.Type {
		panic(fmt.Sprintf("unexpected response 'type' pointer: %#v", queryData.ScanDest))
	}
	namePtr, ok := queryData.GetScanDest()[2].(*string)
	if !ok || namePtr != &queryData.Response.Name {
		panic(fmt.Sprintf("unexpected response 'name' pointer: %#v", queryData.ScanDest))
	}

	// Remember the variable "query"? Well, we didn't check it. We will now make
	// use of Go's runnable examples and print its contents to standard output
	// so Go's tooling verify this example's output each time we run tests.
	// By the way, to make the result more stable, we will remove some
	// unnecessary white space from the query.
	whiteSpaceRE := regexp.MustCompile(`\s+`)
	query = strings.TrimSpace(whiteSpaceRE.ReplaceAllString(query, " "))
	fmt.Println(query)

}

Output:

SELECT "id", "type", "name" FROM "users" WHERE "id" = ?;

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	ErrEmptyIdent              = errors.New("empty identifier")
	ErrInvalidRowLockingClause = errors.New("invalid row-locking clause")
)

Dialect-agnostic errors.

View Source 
var (
	ErrValidationNotImplemented = errors.New("validation not implemented")
	ErrSQLTemplateNoSerialize   = errors.New("SQLTemplate should not be serialized")
)

Package-level errors.

View Source 
var (
	ErrInvalidArgList = errors.New("invalid arglist")
)

Args errors.

View Source 
var (
	ErrPostgreSQLUnsupportedIdent = errors.New("identifiers in PostgreSQL cannot contain the character with code zero")
)

PostgreSQL-specific errors.

View Source 
var MySQL = mysql{
	// contains filtered or unexported fields
}

MySQL is the default implementation of Dialect for the MySQL DMBS, currently supporting MySQL-8.x. It relies on having ANSI_QUOTES SQL Mode enabled. For more information about ANSI_QUOTES and SQL Modes see: 

https://dev.mysql.com/doc/refman/8.4/en/sql-mode.html#sqlmode_ansi_quotes
View Source 
var PostgreSQL = postgresql{
	// contains filtered or unexported fields
}

PostgreSQL is an implementation of Dialect for the PostgreSQL DMBS.

View Source 
var SQLite = sqlite{
	// contains filtered or unexported fields
}

SQLite is an implementation of Dialect for the SQLite DMBS.

Functions

func Execute 

func Execute(t *template.Template, data any) (string, error)

Execute is a trivial utility to execute and return the results of any text/template as a string and an error.

func FormatSQL 

func FormatSQL(q string) string

FormatSQL is an opinionated formatter for SQL template output. It can be used to reduce the final code length, for debugging, and testing. It is not a propoer and full-fledged SQL parser, so it makes the following assumptions, which are also good practices for writing your SQL templates:

  1. There are no SQL comments. Consider adding your comments as template comments instead (i.e. "{{/* this is a template comment */}}").
  2. There are no multiline strings, and strings do not contain consecutive spaces. Code looking like this is already a smell. Avoid string literals, pass them as arguments so they can be appropriately escaped by the corresponding driver. And identifiers with white space should be avoided in all cases as well.

Types

type Args 

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

Args keeps the data that needs to be passed to the engine for execution in the right order. Add it to your data types passed to SQLTemplate, either by embedding or with a named struct field if its Arg method would clash with another struct field.

func NewArgs 

func NewArgs(d Dialect) *Args

func (*Args) Arg 

func (a *Args) Arg(x any) string

Arg can be called from within templates to pass arguments to the SQL driver to use in the execution of the query.

func (*Args) ArgList 

func (a *Args) ArgList(slice reflect.Value) (string, error)

ArgList returns a comma separated list of `?` placeholders for each element in the provided slice argument, calling Arg for each of them. Example struct: 

type sqlMyRequest struct {
	*sqltemplate.SQLTemplate
	IDs []int64
}

Example usage in a SQL template: 

DELETE FROM {{ .Ident "mytab" }}
	WHERE id IN ( {{ argList . .IDs }} )
;

func (*Args) GetArgs 

func (a *Args) GetArgs() []any

func (*Args) Reset 

func (a *Args) Reset()

type ArgsIface 

type ArgsIface interface {
	Arg(x any) string
	ArgList(slice reflect.Value) (string, error)
	GetArgs() []any
	Reset()
}

type Dialect 

type Dialect interface {
	// Name identifies the Dialect. Note that a Dialect may be common to more
	// than one DBMS (e.g. "postgres" is common to PostgreSQL and to
	// CockroachDB), while we can maintain different Dialects for the same DBMS
	// but different versions (e.g. "mysql5" and "mysql8").
	Name() string

	// Ident returns the given string quoted in a way that is suitable to be
	// used as an identifier. Database names, schema names, table names, column
	// names are all examples of identifiers.
	Ident(string) (string, error)

	// ArgPlaceholder returns a safe argument suitable to be used in a SQL
	// prepared statement for the argNum-eth argument passed in execution
	// (starting at 1). The SQL92 Standard specifies the question mark ('?')
	// should be used in all cases, but some implementations differ.
	ArgPlaceholder(argNum int) string

	// SelectFor parses and returns the given row-locking clause for a SELECT
	// statement. If the clause is invalid it returns an error. Implementations
	// of this method should use ParseRowLockingClause.
	// Example:
	//
	//	SELECT *
	//		FROM mytab
	//		WHERE id = ?
	//		{{ .SelectFor "Update NoWait" }}; -- will be uppercased
	SelectFor(...string) (string, error)
}

Dialect should be added to the data types passed to SQL templates to provide methods that deal with SQL implementation-specific traits. It can be embedded for ease of use, or with a named struct field if any of its methods would clash with other struct field names.

type RowLockingClause 

type RowLockingClause string

RowLockingClause represents a row-locking clause in a SELECT statement. 

const (
	SelectForShare            RowLockingClause = "SHARE"
	SelectForShareNoWait      RowLockingClause = "SHARE NOWAIT"
	SelectForShareSkipLocked  RowLockingClause = "SHARE SKIP LOCKED"
	SelectForUpdate           RowLockingClause = "UPDATE"
	SelectForUpdateNoWait     RowLockingClause = "UPDATE NOWAIT"
	SelectForUpdateSkipLocked RowLockingClause = "UPDATE SKIP LOCKED"
)

Row-locking clause options.

func ParseRowLockingClause 

func ParseRowLockingClause(s ...string) (RowLockingClause, error)

ParseRowLockingClause parses a RowLockingClause from the given strings. This should be used by implementations of Dialect to parse the input of the SelectFor method.

func (RowLockingClause) Valid 

func (o RowLockingClause) Valid() bool

Valid returns whether the given option is valid.

type SQLTemplate 

type SQLTemplate struct {
	Dialect
	Args
	ScanDest
}

SQLTemplate provides comprehensive support for SQL templating, handling dialect traits, execution arguments and scanning arguments.

func New 

func New(d Dialect) *SQLTemplate

New returns a nee *SQLTemplate that will use the given dialect.

func (*SQLTemplate) MarshalJSON 

func (t *SQLTemplate) MarshalJSON() ([]byte, error)

func (*SQLTemplate) Reset 

func (t *SQLTemplate) Reset()

func (*SQLTemplate) SetDialect 

func (t *SQLTemplate) SetDialect(d Dialect)

func (*SQLTemplate) UnmarshalJSON 

func (t *SQLTemplate) UnmarshalJSON([]byte) error

func (*SQLTemplate) Validate 

func (t *SQLTemplate) Validate() error

type SQLTemplateIface 

type SQLTemplateIface interface {
	Dialect
	ArgsIface
	ScanDestIface
	// Reset calls the Reset method of ArgsIface and ScanDestIface.
	Reset()
	// SetDialect allows reusing the template components. It should first call
	// Reset.
	SetDialect(Dialect)
	// Validate should be implemented to validate a request before executing the
	// template.
	Validate() error
}

SQLTemplateIface can be used as argument in general purpose utilities expecting a struct embedding *SQLTemplate.

type ScanDest 

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

func (*ScanDest) GetColNames 

func (i *ScanDest) GetColNames() []string

func (*ScanDest) GetScanDest 

func (i *ScanDest) GetScanDest() []any

func (*ScanDest) Into 

func (i *ScanDest) Into(v reflect.Value, colName string) (string, error)

func (*ScanDest) Reset 

func (i *ScanDest) Reset()

type ScanDestIface 

type ScanDestIface interface {
	Into(v reflect.Value, colName string) (string, error)
	GetScanDest() []any
	GetColNames() []string
	Reset()
}

type WithResults 

type WithResults[T any] interface {
	SQLTemplateIface

	// Results returns the results of the query. If the query is expected to
	// return a set of rows, then it should be a deep copy of the internal
	// results, so that it can be called multiple times to get the different
	// values.
	Results() (T, error)
}

WithResults has an additional method suited for structs embedding *SQLTemplate and returning a set of rows.

Source Files

View all Source files

Directories

Path Synopsis

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.