The highest tagged major version is
README
¶
GQT - Go(lang) SQL Templates
Package gqt is a template engine for SQL queries.
It helps to separate SQL code from Go code and permits to compose the queries with a simple syntax.
The template engine is the standard package "text/template".
Why this package? Read more about ORM is the Vietnam of computer science.
Install/update using go get (no dependencies required by gqt):
go get -u github.com/suifengpiao14/gqt
Benefits
- SQL is the best language to write SQL.
- Separation between Go and SQL source code (your DB administrator will thank you).
- Simpler template syntax for composing queries than writing Go code.
- Simplified maintenance of the SQL code.
Compatibility
Go >= 1.6
Usage
Create a template directory tree of .sql files. Here an example template with the definition of three blocks:
-- File /path/to/sql/repository/dir/example.sql
{{define "allUsers"}}
SELECT *
FROM users
WHERE 1=1
{{end}}
{{define "getUser"}}
SELECT *
FROM users
WHERE id=?
{{end}}
{{define "allPosts"}}
SELECT *
FROM posts
WHERE date>=?
{{if ne .Order ""}}ORDER BY date {{.Order}}{{end}}
{{end}}
Then, with Go, add the directory to the default repository and execute the queries:
// Setup
gqt.Add("/path/to/sql/repository/dir", "*.sql")
// Simple query without parameters
db.Query(gqt.Get("allUsers"))
// Query with parameters
db.QueryRow(gqt.Get("getuser"), 1)
// Query with context and parameters
db.Query(gqt.Exec("allPosts", map[string]interface{
"Order": "DESC",
}), date)
The templates are parsed immediately and recursively.
Namespaces
The templates can be organized in namespaces and stored in multiple root directories.
templates1/
|-- roles/
| |-- queries.sql
|-- users/
| |-- queries.sql
| |-- commands.sql
templates2/
|-- posts/
| |-- queries.sql
| |-- commands.sql
|-- users/
| |-- queries.sql
|-- queries.sql
The blocks inside the sql files are merged, the blocks with the same namespace and name will be overridden following the alphabetical order.
The sub-directories are used as namespaces and accessed like:
gqt.Add("../templates1", "*.sql")
gqt.Add("../templates2", "*.sql")
// Will search inside templates1/users/*.sql and templates2/users/*.sql
gqt.Get("users/allUsers")
Multiple databases
When dealing with multiple databases at the same time, like PostgreSQL and MySQL, just create two repositories:
// Use a common directory
dir := "/path/to/sql/repository/dir"
// Create the PostgreSQL repository
pgsql := gqt.NewRepository()
pgsql.Add(dir, "*.pg.sql")
// Create a separated MySQL repository
mysql := gqt.NewRepository()
mysql.Add(dir, "*.my.sql")
// Then execute
pgsql.Get("queryName")
mysql.Get("queryName")
License
Copyright © 2016 Davide Muzzarelli. All right reserved.
Use of this source code is governed by a BSD-style license that can be found in the LICENSE file.
Documentation
¶
Overview ¶
Package gqt is a template engine for SQL queries.
It helps to separate SQL code from Go code and permits to compose the queries with a simple syntax.
The template engine is the standard package "text/template".
Usage ¶
Create a template directory tree of .sql files. Here an example template with the definition of three blocks:
// File /path/to/sql/repository/dir/example.sql
{{define "allUsers"}}
SELECT *
FROM users
WHERE 1=1
{{end}}
{{define "getUser"}}
SELECT *
FROM users
WHERE id=?
{{end}}
{{define "allPosts"}}
SELECT *
FROM posts
WHERE date>=?
{{if ne .Order ""}}ORDER BY date {{.Order}}{{end}}
{{end}}
Then, with Go, add the directory to the default repository and execute the queries:
// Setup
gqt.Add("/path/to/sql/repository/dir", "*.sql")
// Simple query without parameters
db.Query(gqt.Get("allUsers"))
// Query with parameters
db.QueryRow(gqt.Get("getuser"), 1)
// Query with context and parameters
db.Query(gqt.Exec("allPosts", map[string]interface{
"Order": "DESC",
}), date)
The templates are parsed immediately and recursively.
Namespaces ¶
The templates can be organized in namespaces and stored in multiple root directories.
templates1/ |-- roles/ | |-- queries.sql |-- users/ | |-- queries.sql | |-- commands.sql templates2/ |-- posts/ | |-- queries.sql | |-- commands.sql |-- users/ | |-- queries.sql |-- queries.sql
The blocks inside the sql files are merged, the blocks with the same namespace and name will be overridden following the alphabetical order.
The sub-directories are used as namespaces and accessed like:
gqt.Add("../templates1", "*.sql")
gqt.Add("../templates2", "*.sql")
// Will search inside templates1/users/*.sql and templates2/users/*.sql
gqt.Get("users/allUsers")
Multiple databases ¶
When dealing with multiple databases at the same time, like PostgreSQL and MySQL, just create two repositories:
// Use a common directory
dir := "/path/to/sql/repository/dir"
// Create the PostgreSQL repository
pgsql := gqt.NewRepository()
pgsql.Add(dir, "*.pg.sql")
// Create a separated MySQL repository
mysql := gqt.NewRepository()
mysql.Add(dir, "*.my.sql")
// Then execute
pgsql.Get("queryName")
mysql.Get("queryName")
Index ¶
- Variables
- func Add(root string, funcMap template.FuncMap) error
- func AddFromContent(filename string, content string, funcMap template.FuncMap) error
- func CurrentTime() string
- func Exec(name string, data interface{}) (sql string, e error)
- func Flight(sql string, fn func() (interface{}, error)) (err error)
- func Get(name string) (sql string, err error)
- func GetMD5LOWER(s string) string
- func GetSafeSQL(name string, data interface{}, sql *string) (e error)
- func GetStatment(name string, data interface{}) (sql string, vars []interface{}, err error)
- func InIntSet(data []int) (str string)
- func InStrSet(data []string) (str string)
- func Parse(name string, data interface{}) (string, error)
- func PermanentTime() string
- func ReadEmbedFS(repositoryFS embed.FS, filename string, fileMap *map[string][]byte)
- func ZeroTime() string
- type Repository
- func (r *Repository) Add(root string, funcMap template.FuncMap) (err error)
- func (r *Repository) AddFromContent(filename string, content string, funcMap template.FuncMap) (err error)
- func (r *Repository) Exec(name string, data interface{}) (s string, err error)
- func (r *Repository) Get(name string) (s string, err error)
- func (r *Repository) GetByNamespace(namespace string) (s map[string]string, err error)
- func (r *Repository) GetNamespace(filename string) (namespace string)
- func (r *Repository) GetSafeSQL(name string, data interface{}, sql *string) (err error)
- func (r *Repository) GetSql(name string, args interface{}, output *string) (err error)
- func (r *Repository) GetStatment(name string, data interface{}) (sql string, vars []interface{}, err error)
- func (r *Repository) Parse(name string, data interface{}) (string, error)
Constants ¶
This section is empty.
Variables ¶
var TemplatefuncMap = template.FuncMap{ "zeroTime": ZeroTime, "currentTime": CurrentTime, "permanentTime": PermanentTime, "contains": strings.Contains, "inIntSet": InIntSet, "inStrSet": InStrSet, }
Functions ¶
func AddFromContent ¶
Add method for the default repository.
func CurrentTime ¶
func CurrentTime() string
func GetMD5LOWER ¶
func GetSafeSQL ¶
Exec method for the default repository.
func GetStatment ¶
Get method for the default repository.
func PermanentTime ¶
func PermanentTime() string
func ReadEmbedFS ¶
ReadEmbedFS read embed file
Types ¶
type Repository ¶
type Repository struct {
// contains filtered or unexported fields
}
Repository stores SQL templates.
func (*Repository) Add ¶
func (r *Repository) Add(root string, funcMap template.FuncMap) (err error)
Add adds a root directory to the repository, recursively. Match only the given file extension. Blocks on the same namespace will be overridden. Does not follow symbolic links.
func (*Repository) AddFromContent ¶
func (*Repository) Exec ¶
func (r *Repository) Exec(name string, data interface{}) (s string, err error)
Exec is a shortcut for r.Parse(), but panics if an error occur.
func (*Repository) Get ¶
func (r *Repository) Get(name string) (s string, err error)
Get is a shortcut for r.Exec(), passing nil as data.
func (*Repository) GetByNamespace ¶
func (r *Repository) GetByNamespace(namespace string) (s map[string]string, err error)
GetByNamespace get all template under namespace
func (*Repository) GetNamespace ¶
func (r *Repository) GetNamespace(filename string) (namespace string)
func (*Repository) GetSafeSQL ¶
func (r *Repository) GetSafeSQL(name string, data interface{}, sql *string) (err error)
无sql注入的安全方式
func (*Repository) GetSql ¶
func (r *Repository) GetSql(name string, args interface{}, output *string) (err error)
GetSql is a shortcut for r.Parse(), but panics if an error occur.
func (*Repository) GetStatment ¶
func (r *Repository) GetStatment(name string, data interface{}) (sql string, vars []interface{}, err error)
支持返回Prepared Statement ,该模式优势1. 提升性能，避免重复解析 SQL 带来的开销，2. 避免 SQL 注入 缺点： 1. 存在两次与数据库的通信，在密集进行 SQL 查询的情况下，可能会出现 I/O 瓶颈
Source Files
Directories