README
¶
sqltemplate
sqltemplate is a Go library designed to harness the power of Go Templates for generating SQL statements. It allows developers to write SQL templates using Go Template syntax and dynamically replace template variables with actual parameter values to produce secure parameterized SQL statements and corresponding parameter lists. This parameterization helps prevent SQL injection attacks while maintaining code flexibility and maintainability.
Features
- Security: Generates SQL statements with
?placeholders, which are automatically handled by database drivers to prevent SQL injection. - Flexibility: Supports complex SQL templates, including loops (
range) and conditionals (if) statements. - Usability: A simple API that is easy to integrate into existing Go projects.
- Performance: An optimized template parsing and execution engine for fast template rendering performance.
usage
To install sqltemplate, run the following command:
go get github.com/ystyle/sqltemplate
package main
import (
"bytes"
"github.com/ystyle/sqltemplate"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"reflect"
"strings"
)
type Post struct {
ID uint
Title, Content string
}
func main() {
// Define the SQL template
sqltpl := `
insert into posts (id, created_at, title, content) values
{{range $index, $item := .list -}}
({{.ID}}, '2024-12-12 16:09:56', {{ .Title | upper}}, {{.Content}}) {{if last $index $.list}}{{else}}, {{end}}
{{end}}
`
// Prepare the data
var Posts = []Post{
{1, "Aunt Mildred", "bone china tea set"},
{2, "Uncle John", "moleskin pants"},
{3, "Cousin Rodney", ""},
}
// init sqlte
tt := sqltemplate.New("master")
tt.Funcs(sqltemplate.FuncMap{
"last": func(index int, list interface{}) bool {
return index == reflect.ValueOf(list).Len()-1
},
"upper": func(v string) string {
return strings.ToUpper(v)
},
})
tt, err = tt.Parse(sqltpl)
if err != nil {
panic(err)
}
// Use sqltemplate to render the SQL template
var buf bytes.Buffer
args, err := tt.Execute(&buf, map[string]any{"list": Posts, "data": "test"})
if err != nil {
panic(err)
}
// Print rendered sql
println(buf.String())
println("args len: ", len(args))
// Create a database connection
db, err := gorm.Open(mysql.Open("root:12345678@tcp(127.0.0.1:3306)/test"))
if err != nil {
panic(err)
}
// Execute the SQL statement
if err := db.Exec(buf.String(), args...).Error; err != nil {
panic(err)
}
}
log:
insert into posts (id, created_at, title, content) values
(?, '2024-12-12 16:09:56', ?, ?),
(?, '2024-12-12 16:09:56', ?, ?),
(?, '2024-12-12 16:09:56', ?, ?)
args len: 9
2024/12/16 18:11:10 main.go:56
[5.831ms] [rows:3]
insert into posts (id, created_at, title, content) values
(1, '2024-12-12 16:09:56', 'AUNT MILDRED', 'bone china tea set'),
(2, '2024-12-12 16:09:56', 'UNCLE JOHN', 'moleskin pants'),
(3, '2024-12-12 16:09:56', 'COUSIN RODNEY', '')
Note on Code Origin
A significant portion of the code in this project is derived from the Go standard library's text/template package. This library extends and specializes that functionality for the specific use case of generating SQL statements.
Documentation
¶
Index ¶
- func HTMLEscape(w io.Writer, b []byte)
- func HTMLEscapeString(s string) string
- func HTMLEscaper(args ...any) string
- func IsTrue(val any) (truth, ok bool)
- func JSEscape(w io.Writer, b []byte)
- func JSEscapeString(s string) string
- func JSEscaper(args ...any) string
- func URLQueryEscaper(args ...any) string
- type ExecError
- type FuncMap
- type KeyValue
- type SortedMap
- type Template
- func (t *Template) AddParseTree(name string, tree *parse.Tree) (*Template, error)
- func (t *Template) Clone() (*Template, error)
- func (t *Template) DefinedTemplates() string
- func (t *Template) Delims(left, right string) *Template
- func (t *Template) Execute(wr io.Writer, data any) ([]any, error)
- func (t *Template) ExecuteTemplate(wr io.Writer, name string, data any) ([]any, error)
- func (t *Template) Funcs(funcMap FuncMap) *Template
- func (t *Template) Lookup(name string) *Template
- func (t *Template) Name() string
- func (t *Template) New(name string) *Template
- func (t *Template) Option(opt ...string) *Template
- func (t *Template) Parse(text string) (*Template, error)
- func (t *Template) ParseFS(fsys fs.FS, patterns ...string) (*Template, error)
- func (t *Template) ParseFiles(filenames ...string) (*Template, error)
- func (t *Template) ParseGlob(pattern string) (*Template, error)
- func (t *Template) Templates() []*Template
Examples ¶
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func HTMLEscape ¶
HTMLEscape writes to w the escaped HTML equivalent of the plain text data b.
func HTMLEscapeString ¶
HTMLEscapeString returns the escaped HTML equivalent of the plain text data s.
func HTMLEscaper ¶
HTMLEscaper returns the escaped HTML equivalent of the textual representation of its arguments.
func IsTrue ¶
IsTrue reports whether the value is 'true', in the sense of not the zero of its type, and whether the value has a meaningful truth value. This is the definition of truth used by if and other such actions.
func JSEscapeString ¶
JSEscapeString returns the escaped JavaScript equivalent of the plain text data s.
func JSEscaper ¶
JSEscaper returns the escaped JavaScript equivalent of the textual representation of its arguments.
func URLQueryEscaper ¶
URLQueryEscaper returns the escaped value of the textual representation of its arguments in a form suitable for embedding in a URL query.
Types ¶
type ExecError ¶
ExecError is the custom error type returned when Execute has an error evaluating its template. (If a write error occurs, the actual error is returned; it will not be of type ExecError.)
type FuncMap ¶
FuncMap is the type of the map defining the mapping from names to functions. Each function must have either a single return value, or two return values of which the second has type error. In that case, if the second (error) return value evaluates to non-nil during execution, execution terminates and Execute returns that error.
Errors returned by Execute wrap the underlying error; call errors.As to unwrap them.
When template execution invokes a function with an argument list, that list must be assignable to the function's parameter types. Functions meant to apply to arguments of arbitrary type can use parameters of type interface{} or of type reflect.Value. Similarly, functions meant to return a result of arbitrary type can return interface{} or reflect.Value.
type Template ¶
Template is the representation of a parsed template. The *parse.Tree field is exported only for use by html/template and should be treated as unexported by all other clients.
Example ¶
// Define a template.
const letter = `
Dear {{.Name}},
{{if .Attended}}
It was a pleasure to see you at the wedding.
{{- else}}
It is a shame you couldn't make it to the wedding.
{{- end}}
{{with .Gift -}}
Thank you for the lovely {{.}}.
{{end}}
Best wishes,
Josie
`
// Prepare some data to insert into the template.
type Recipient struct {
Name, Gift string
Attended bool
}
var recipients = []Recipient{
{"Aunt Mildred", "bone china tea set", true},
{"Uncle John", "moleskin pants", false},
{"Cousin Rodney", "", false},
}
// Create a new template and parse the letter into it.
t := Must(New("letter").Parse(letter))
// Execute the template for each recipient.
for _, r := range recipients {
args, err := t.Execute(os.Stdout, r)
if err != nil {
log.Println("executing template:", err)
}
println(args)
}
Output: Dear Aunt Mildred, It was a pleasure to see you at the wedding. Thank you for the lovely bone china tea set. Best wishes, Josie Dear Uncle John, It is a shame you couldn't make it to the wedding. Thank you for the lovely moleskin pants. Best wishes, Josie Dear Cousin Rodney, It is a shame you couldn't make it to the wedding. Best wishes, Josie
Example (Block) ¶
const (
master = `Names:{{block "list" .}}{{"\n"}}{{range .}}{{println "-" .}}{{end}}{{end}}`
overlay = `{{define "list"}} {{join . ", "}}{{end}} `
)
var (
funcs = FuncMap{"join": strings.Join}
guardians = []string{"Gamora", "Groot", "Nebula", "Rocket", "Star-Lord"}
)
masterTmpl, err := New("master").Funcs(funcs).Parse(master)
if err != nil {
log.Fatal(err)
}
overlayTmpl, err := Must(masterTmpl.Clone()).Parse(overlay)
if err != nil {
log.Fatal(err)
}
if err, _ := masterTmpl.Execute(os.Stdout, guardians); err != nil {
log.Fatal(err)
}
if err, _ := overlayTmpl.Execute(os.Stdout, guardians); err != nil {
log.Fatal(err)
}
Output: Names: - Gamora - Groot - Nebula - Rocket - Star-Lord Names: Gamora, Groot, Nebula, Rocket, Star-Lord
func Must ¶
Must is a helper that wraps a call to a function returning (*Template, error) and panics if the error is non-nil. It is intended for use in variable initializations such as
var t = template.Must(template.New("name").Parse("text"))
func ParseFS ¶
ParseFS is like Template.ParseFiles or Template.ParseGlob but reads from the file system fsys instead of the host operating system's file system. It accepts a list of glob patterns (see path.Match). (Note that most file names serve as glob patterns matching only themselves.)
func ParseFiles ¶
ParseFiles creates a new Template and parses the template definitions from the named files. The returned template's name will have the base name and parsed contents of the first file. There must be at least one file. If an error occurs, parsing stops and the returned *Template is nil.
When parsing multiple files with the same name in different directories, the last one mentioned will be the one that results. For instance, ParseFiles("a/foo", "b/foo") stores "b/foo" as the template named "foo", while "a/foo" is unavailable.
func ParseGlob ¶
ParseGlob creates a new Template and parses the template definitions from the files identified by the pattern. The files are matched according to the semantics of filepath.Match, and the pattern must match at least one file. The returned template will have the filepath.Base name and (parsed) contents of the first file matched by the pattern. ParseGlob is equivalent to calling ParseFiles with the list of files matched by the pattern.
When parsing multiple files with the same name in different directories, the last one mentioned will be the one that results.
func (*Template) AddParseTree ¶
AddParseTree associates the argument parse tree with the template t, giving it the specified name. If the template has not been defined, this tree becomes its definition. If it has been defined and already has that name, the existing definition is replaced; otherwise a new template is created, defined, and returned.
func (*Template) Clone ¶
Clone returns a duplicate of the template, including all associated templates. The actual representation is not copied, but the name space of associated templates is, so further calls to Template.Parse in the copy will add templates to the copy but not to the original. Clone can be used to prepare common templates and use them with variant definitions for other templates by adding the variants after the clone is made.
func (*Template) DefinedTemplates ¶
DefinedTemplates returns a string listing the defined templates, prefixed by the string "; defined templates are: ". If there are none, it returns the empty string. For generating an error message here and in html/template.
func (*Template) Delims ¶
Delims sets the action delimiters to the specified strings, to be used in subsequent calls to Template.Parse, Template.ParseFiles, or Template.ParseGlob. Nested template definitions will inherit the settings. An empty delimiter stands for the corresponding default: {{ or }}. The return value is the template, so calls can be chained.
func (*Template) Execute ¶
Execute applies a parsed template to the specified data object, and writes the output to wr. If an error occurs executing the template or writing its output, execution stops, but partial results may already have been written to the output writer. A template may be executed safely in parallel, although if parallel executions share a Writer the output may be interleaved.
If data is a reflect.Value, the template applies to the concrete value that the reflect.Value holds, as in fmt.Print.
func (*Template) ExecuteTemplate ¶
ExecuteTemplate applies the template associated with t that has the given name to the specified data object and writes the output to wr. If an error occurs executing the template or writing its output, execution stops, but partial results may already have been written to the output writer. A template may be executed safely in parallel, although if parallel executions share a Writer the output may be interleaved.
func (*Template) Funcs ¶
Funcs adds the elements of the argument map to the template's function map. It must be called before the template is parsed. It panics if a value in the map is not a function with appropriate return type or if the name cannot be used syntactically as a function in a template. It is legal to overwrite elements of the map. The return value is the template, so calls can be chained.
func (*Template) Lookup ¶
Lookup returns the template with the given name that is associated with t. It returns nil if there is no such template or the template has no definition.
func (*Template) New ¶
New allocates a new, undefined template associated with the given one and with the same delimiters. The association, which is transitive, allows one template to invoke another with a {{template}} action.
Because associated templates share underlying data, template construction cannot be done safely in parallel. Once the templates are constructed, they can be executed in parallel.
func (*Template) Option ¶
Option sets options for the template. Options are described by strings, either a simple string or "key=value". There can be at most one equals sign in an option string. If the option string is unrecognized or otherwise invalid, Option panics.
Known options:
missingkey: Control the behavior during execution if a map is indexed with a key that is not present in the map.
"missingkey=default" or "missingkey=invalid" The default behavior: Do nothing and continue execution. If printed, the result of the index operation is the string "<no value>". "missingkey=zero" The operation returns the zero value for the map type's element. "missingkey=error" Execution stops immediately with an error.
func (*Template) Parse ¶
Parse parses text as a template body for t. Named template definitions ({{define ...}} or {{block ...}} statements) in text define additional templates associated with t and are removed from the definition of t itself.
Templates can be redefined in successive calls to Parse. A template definition with a body containing only white space and comments is considered empty and will not replace an existing template's body. This allows using Parse to add new named template definitions without overwriting the main template body.
func (*Template) ParseFS ¶
ParseFS is like Template.ParseFiles or Template.ParseGlob but reads from the file system fsys instead of the host operating system's file system. It accepts a list of glob patterns (see path.Match). (Note that most file names serve as glob patterns matching only themselves.)
func (*Template) ParseFiles ¶
ParseFiles parses the named files and associates the resulting templates with t. If an error occurs, parsing stops and the returned template is nil; otherwise it is t. There must be at least one file. Since the templates created by ParseFiles are named by the base (see filepath.Base) names of the argument files, t should usually have the name of one of the (base) names of the files. If it does not, depending on t's contents before calling ParseFiles, t.Execute may fail. In that case use t.ExecuteTemplate to execute a valid template.
When parsing multiple files with the same name in different directories, the last one mentioned will be the one that results.
func (*Template) ParseGlob ¶
ParseGlob parses the template definitions in the files identified by the pattern and associates the resulting templates with t. The files are matched according to the semantics of filepath.Match, and the pattern must match at least one file. ParseGlob is equivalent to calling Template.ParseFiles with the list of files matched by the pattern.
When parsing multiple files with the same name in different directories, the last one mentioned will be the one that results.
Source Files