README
¶
sqlf 
Generate parameterized SQL statements in Go, sprintf Style.
q := sqlf.Sprintf("SELECT * FROM users WHERE country = %s AND age > %d", "US", 27);
rows, err := db.Query(q.Query(sqlf.SimpleBindVar), q.Args()...) // db is a database/sql.DB
sqlf.Sprintf does not return a string. It returns *sqlf.Query which has
methods for a parameterized SQL query and arguments. You then pass that to
db.Query, db.Exec, etc. This is not like using fmt.Sprintf, which could
expose you to malformed SQL or SQL injection attacks.
sqlf.Query can be passed as an argument to sqlf.Sprintf. It will "flatten"
the query string, while preserving the correct variable binding. This allows
you to easily compose and build SQL queries. See the below examples to find
out more.
// This is an example which shows off embedding SQL, which simplifies building
// complicated SQL queries
name := "John"
age, offset := 27, 100
where := sqlf.Sprintf("name=%s AND age=%d", name, age)
limit := sqlf.Sprintf("%d OFFSET %d", 10, offset)
q := sqlf.Sprintf("SELECT name FROM users WHERE %s LIMIT %s", where, limit)
fmt.Println(q.Query(sqlf.PostgresBindVar))
fmt.Println(q.Args())
// Output: SELECT name FROM users WHERE name=$1 AND age=$2 LIMIT $3 OFFSET $4
// [John 27 10 100]
Another common task is joining conditionals with AND or OR. sqlf
simplifies this task with sqlf.Join:
// Our inputs
minQuantity := 100
nameFilters := []string{"apple", "orange", "coffee"}
var conds []*sqlf.Query
for _, filter := range nameFilters {
conds = append(conds, sqlf.Sprintf("name LIKE %s", "%"+filter+"%"))
}
subQuery := sqlf.Sprintf("SELECT product_id FROM order_item WHERE quantity > %d", minQuantity)
q := sqlf.Sprintf("SELECT name FROM product WHERE id IN (%s) AND (%s)", subQuery, sqlf.Join(conds, "OR"))
fmt.Println(q.Query(sqlf.PostgresBindVar))
fmt.Println(q.Args())
// Output: SELECT name FROM product WHERE id IN (SELECT product_id FROM order_item WHERE quantity > $1) AND (name LIKE $2 OR name LIKE $3 OR name LIKE $4)
// [100 %apple% %orange% %coffee%]
See https://godoc.org/github.com/keegancsmith/sqlf for more information.
Documentation
¶
Overview ¶
Package sqlf generates parameterized SQL statements in Go, sprintf style.
A simple example:
q := sqlf.Sprintf("SELECT * FROM users WHERE country = %s AND age > %d", "US", 27);
rows, err := db.Query(q.Query(sqlf.SimpleBindVar), q.Args()...) // db is a database/sql.DB
sqlf.Sprintf does not return a string. It returns *sqlf.Query which has methods for a parameterized SQL query and arguments. You then pass that to db.Query, db.Exec, etc. This is not like using fmt.Sprintf, which could expose you to malformed SQL or SQL injection attacks.
sqlf.Query can be passed as an argument to sqlf.Sprintf. It will "flatten" the query string, while preserving the correct variable binding. This allows you to easily compose and build SQL queries. See the below examples to find out more.
Example ¶
package main
import (
"fmt"
"github.com/keegancsmith/sqlf"
)
func main() {
// This is an example which shows off embedding SQL, which simplifies building
// complicated SQL queries
name := "John"
age, offset := 27, 100
where := sqlf.Sprintf("name=%s AND age=%d", name, age)
limit := sqlf.Sprintf("%d OFFSET %d", 10, offset)
q := sqlf.Sprintf("SELECT name FROM users WHERE %s LIMIT %s", where, limit)
fmt.Println(q.Query(sqlf.PostgresBindVar))
fmt.Println(q.Args())
}
Output: SELECT name FROM users WHERE name=$1 AND age=$2 LIMIT $3 OFFSET $4 [John 27 10 100]
Example (Dbquery) ¶
package main
import (
"database/sql"
"fmt"
"log"
"github.com/keegancsmith/sqlf"
)
var db *sql.DB
func main() {
age := 27
// The next two lines are the only difference from the dabatabase/sql/db.Query example.
// Original is rows, err := db.Query("SELECT name FROM users WHERE age=?", age)
s := sqlf.Sprintf("SELECT name FROM users WHERE age=%d", age)
rows, err := db.Query(s.Query(sqlf.SimpleBindVar), s.Args()...)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var name string
if err := rows.Scan(&name); err != nil {
log.Fatal(err)
}
fmt.Printf("%s is %d\n", name, age)
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}
}
Output:
Index ¶
Examples ¶
Constants ¶
This section is empty.
Variables ¶
var OracleBindVar = oracleBindVar{}
OracleBindVar is the BindVar format used by Oracle Database
var PostgresBindVar = postgresBindVar{}
PostgresBindVar is the BindVar format used by PostgreSQL
var SQLServerBindVar = sqlServerBindVar{}
SQLServerBindVar is the BindVar format used by SQL Server(sqlserver driver) https://github.com/denisenkom/go-mssqldb#parameters https://github.com/denisenkom/go-mssqldb#deprecated
var SimpleBindVar = simpleBindVar{}
SimpleBindVar is the BindVar format used by SQLite, MySQL, SQLServer(mssql driver)
Functions ¶
This section is empty.
Types ¶
type BindVar ¶
type BindVar interface {
// BindVar binds a variable string to use when forming SQL statements
// in many dbs it is "?", but Postgres appears to use $1
//
// i is a zero based index of the bind variable in this statement
//
BindVar(i int) string
}
BindVar is used to take a format string and convert it into query string that a gorp.SqlExecutor or sql.Query can use. It is the same as BindVar from gorp.Dialect.
type Query ¶
type Query struct {
// contains filtered or unexported fields
}
Query stores a SQL expression and arguments for passing on to database/sql/db.Query or gorp.SqlExecutor.
func Join ¶
Join concatenates the elements of queries to create a single Query. The separator string sep is placed between elements in the resulting Query.
This is commonly used to join clauses in a WHERE query. As such sep is usually "AND" or "OR".
Example ¶
package main
import (
"fmt"
"github.com/keegancsmith/sqlf"
)
func main() {
// Our inputs
minQuantity := 100
nameFilters := []string{"apple", "orange", "coffee"}
var conds []*sqlf.Query
for _, filter := range nameFilters {
conds = append(conds, sqlf.Sprintf("name LIKE %s", "%"+filter+"%"))
}
subQuery := sqlf.Sprintf("SELECT product_id FROM order_item WHERE quantity > %d", minQuantity)
q := sqlf.Sprintf("SELECT name FROM product WHERE id IN (%s) AND (%s)", subQuery, sqlf.Join(conds, "OR"))
fmt.Println(q.Query(sqlf.PostgresBindVar))
fmt.Println(q.Args())
}
Output: SELECT name FROM product WHERE id IN (SELECT product_id FROM order_item WHERE quantity > $1) AND (name LIKE $2 OR name LIKE $3 OR name LIKE $4) [100 %apple% %orange% %coffee%]
Source Files