README
¶
Database Abstraction Layer (í)
DALí is not exactly a database abstration layer. It doesn't try to abstract the SQL in a way that the queries could run unchanged on any supported database. It rather abstracts just the placeholder manipulation and provides convenient ways for some common situations.
The main goal of this project is to provide a clean, compact API for communication with SQL databases.
Quickstart
package main
import (
"fmt"
"log"
"time"
_ "github.com/go-sql-driver/mysql"
"github.com/mibk/dali"
)
func main() {
db, err := dali.Open("mysql", "root@/example?parseTime=true")
if err != nil {
log.Fatal(err)
}
q := db.Query(`INSERT INTO [group] ?values`, dali.Map{"name": "admins"})
groupID, err := dali.LastInsertID(q.Exec())
if err != nil {
log.Fatal(err)
}
// INSERT INTO `group` (`name`) VALUES ('admins')
users := []User{
{0, "Peter", "peter@foo.com", groupID, time.Now()},
{0, "Nick", "nick@bar.org", groupID, time.Now()},
}
_, err = db.Query(`INSERT INTO [user] ?values...`, users).Exec()
if err != nil {
log.Fatal(err)
}
// ?values... expands a slice of struct into multi insert
// INSERT INTO `user` (`name`, `email`, `group_id`, `created`) VALUES
// ('Peter', 'peter@foo.com', 1, '2015-11-20 13:59:59'),
// ('Nick', 'nick@bar.org', 1, '2015-11-20 13:59:59')
var u User
q = db.Query(`SELECT * FROM ?ident WHERE group_id IN (?...) LIMIT 1`,
"user", []int{1, 2, 5})
fmt.Println(q) // dali.Query implements fmt.Stringer. It prints:
// SELECT * FROM `user` WHERE group_id IN (1, 2, 5) LIMIT 1
if err := q.One(&u); err != nil {
log.Fatal(err)
}
fmt.Println(u)
u.Email = "peter@foo.net"
u.GroupID = 2
_, err = db.Query(`UPDATE [user] ?set WHERE [id] = ?`,
dali.Map{
"email": u.Email,
"group_id": u.GroupID,
}, 1).Exec()
if err != nil {
log.Fatal(err)
}
// UPDATE `user` SET `email` = 'peter@foo.net', `group_id` = 2
// WHERE `id` = 1
}
type User struct {
ID int64 `db:",selectonly"` // omitted on INSERT, UPDATE, etc.
Name string `db:"name"`
Email string `db:"email"`
GroupID int64 `db:"group_id"`
Registered time.Time `db:"created"`
}
Instalation
$ go get github.com/mibk/dali
Caveats
DALí processes the query unaware of the actual SQL syntax. This means it is quite stupid on deciding whether the placeholder is inside a string literal.
conn.Query(`SELECT * FROM foo WHERE name = 'really?'`)
// This will return an error because it would try to replace the `?` with an argument
// that is missing.
To avoid this just use the whole string as a parameter.
conn.Query(`SELECT * FROM foo WHERE name = ?`, "really?")
Features
Identifier escaping
This feature comes from the need to fix the clumsy way of escaping identifiers in MySQL in Go's raw string literals. So instead of
sql := `SELECT `+"`where`"+`
FROM location`
you can use
sql := `SELECT [where]
FROM location
So there is one way to escape identifiers among all dialects.
Handy placeholders
Again, placeholder manipulation is the same for all dialects and besides that it also provides some additional placeholders. The complete list is:
? primitive value or a value implementing driver.Valuer
?... a slice of values which is going to be expanded (especially useful in
IN clauses)
?values expects either Map, or a struct as an argument. It derives column names
from map keys or struct fields and constructs a VALUES clause (e.g.
INSERT INTO user ?values)
?set similar to ?values but used for SET clauses (e.g. UPDATE user SET ?set)
?values... expects a slice of structs as an argument which is expanded into multi
INSERT clause
?ident used for identifiers (column or table name)
?ident... expands identifiers and separates them with a comma
?sql inserts the parameter, a string or Marshaler, as is (meant for SQL parts)
Using the placeholders it is easy and quite expressive to write common SQL queries, but it is also possible to adjust these queries to a specific need (which is often not so easy when using query builders).
Note: only ?, ?ident, ?ident..., and ?sql are allowed in prepared statements (see the
method Prepare for more information).
Profiling and other
Using the DB.SetMiddlewareFunc it is possible to do additional operations before and after execution of every query. This example logs every executed query:
var db *dali.DB // init db...
func init() {
db.SetMiddlewareFunc(profile)
}
func profile(e dali.Execer) dali.Execer {
return profiler{e}
}
type profiler struct {
ex dali.Execer
}
func (p profiler) Exec(query string, args ...interface{}) (sql.Result, error) {
log.Println(query, args)
return p.ex.Exec(query, args...)
}
func (p profiler) Query(query string, args ...interface{}) (*sql.Rows, error) {
log.Println(query, args)
return p.ex.Query(query, args...)
}
func (p profiler) QueryRow(query string, args ...interface{}) *sql.Row {
log.Println(query, args)
return p.ex.QueryRow(query, args...)
}
Faster performance
DALí interpolates all parameters before it gets to the database which has a huge performance benefit. This behaviour is taken from the gocraft/dbr library. See this for more information.
Supported dialects
Currently, only a MySQL dialect is implemented directly in this package (see dialects for more information). Nevertheless, supporting another dialect should be as easy as creating a new dialect implementing dialects.Dialect interface. The most common dialects will be implemented directly in the future.
Thanks
Ideas for building this library come mainly from these sources:
- gocraft/dbr for interpolation, loading methods and other
- nextras/dbal for the placeholders (although it is a PHP library)
- jmoiron/sqlx for general ideas
License
DALí is distributed under the MIT license found in the LICENSE file.
Documentation
¶
Overview ¶
Package dali wraps the sql.DB and provides convenient API for building database driven applications. Its main goal is to create a unified way of handling placeholders among all drivers and to simplify some common, repetive queries.
There is no support for query builders (you have to write pure SQL queries). It focuses on the common queries (like writing INSERTs or UPDATEs) and on loading of results into structs, for which it provides easy-to-write alternatives.
Placeholders ¶
The following is the complete list of possible placeholders that can be used when writing a query using Query method.
? primitive value or a value implementing driver.Valuer
?... a slice of values which is going to be expanded (especially useful in
IN clauses)
?values expects either Map, or a struct as an argument. It derives column names
from map keys or struct fields and constructs a VALUES clause (e.g.
INSERT INTO user ?values)
?set similar to ?values but used for SET clauses (e.g. UPDATE user SET ?set)
?values... expects a slice of structs as an argument which is expanded into multi
INSERT clause
?ident used for identifiers (column or table name)
?ident... expands identifiers and separates them with a comma
?sql inserts the parameter, a string or Marshaler, as is (meant for SQL parts)
Prepared statements ¶
dali has also a support for prepared statements. However, it doesn't support certain placeholders. Only ?ident, ?ident..., and ?sql placeholders are allowed in the phase of the query building (befored the statement is prepared). The ? placeholder is the only one left for parameter binding. So working with prepared statements can look like this:
cols := []strings{"name", "group_id"}
var (
name string
groupID int64
)
stmt := db.Prepare(`SELECT ?ident... FROM [user] WHERE [id] = ?`, cols)
// This prepares this statement:
// SELECT `name`, `group_id` FROM `user` WHERE `id` = ?
stmt.Bind(14).ScanRow(&name, &groupID)
// Bind the statement with 14 value and scan the row into these variables.
Index ¶
- func LastInsertID(res sql.Result, err error) (int64, error)
- type DB
- func (db *DB) Begin() (*Tx, error)
- func (db *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error)
- func (db *DB) Close() error
- func (db *DB) Ping() error
- func (db *DB) Prepare(query string, args ...interface{}) (*Stmt, error)
- func (db *DB) PrepareContext(ctx context.Context, query string, args ...interface{}) (*Stmt, error)
- func (db *DB) Query(query string, args ...interface{}) *Query
- func (db *DB) QueryWithContext(ctx context.Context, query string, args ...interface{}) *Query
- func (db *DB) SetMiddlewareFunc(f func(Execer) Execer)
- type Execer
- type Map
- type Marshaler
- type Query
- func (q *Query) All(dest interface{}) error
- func (q *Query) Exec() (sql.Result, error)
- func (q *Query) One(dest interface{}) error
- func (q *Query) Rows() (*sql.Rows, error)
- func (q *Query) ScanAllRows(dests ...interface{}) error
- func (q *Query) ScanRow(dest ...interface{}) error
- func (q *Query) String() string
- type Stmt
- type Translator
- type Tx
- func (tx *Tx) Commit() error
- func (tx *Tx) Prepare(query string, args ...interface{}) (*Stmt, error)
- func (tx *Tx) PrepareContext(ctx context.Context, query string, args ...interface{}) (*Stmt, error)
- func (tx *Tx) Query(query string, args ...interface{}) *Query
- func (tx *Tx) QueryWithContext(ctx context.Context, query string, args ...interface{}) *Query
- func (tx *Tx) Rollback() error
- func (tx *Tx) Stmt(stmt *Stmt) *Stmt
- func (tx *Tx) StmtContext(ctx context.Context, stmt *Stmt) *Stmt
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func LastInsertID ¶
LastInsertID is a helper that wraps a call to a function returning (res sql.Result, err error). It returns err if it is not nil, otherwise it returns res.LastInsertId(). It is intended for uses such as
q := db.Query(...) id, err := dali.LastInsertID(q.Exec())
Types ¶
type DB ¶
DB wraps the sql.DB and provides slightly different API for communication with the database. The primary method is Query which provides methods for executing queries or scanning results.
func Open ¶
Open opens a database by calling sql.Open. It returns a new DB and selects the appropriate dialect which is inferred from the driverName. It panics if the dialect is not supported by dali itself.
func (*DB) BeginTx ¶ added in v0.4.0
BeginTx starts a transaction.
The provided context is used until the transaction is committed or rolled back. If the context is canceled, the sql package will roll back the transaction. Tx.Commit will return an error if the context provided to BeginTx is canceled.
The provided TxOptions is optional and may be nil if defaults should be used. If a non-default isolation level is used that the driver doesn't support, an error will be returned.
func (*DB) Ping ¶
Ping verifies a connection to the database is still alive, establishing a connection if necessary.
func (*DB) Prepare ¶
Prepare creates a prepared statement for later queries or executions. The caller must call the statement's Close method when the statement is no longer needed. Unlike the Prepare methods in database/sql this method also accepts args, which are meant only for query building. Therefore, only ?ident, ?ident..., ?sql are interpolated in this phase. Apart of that, ? is the only other placeholder allowed (this one will be transformed into a dialect specific one to allow the parameter binding.
func (*DB) PrepareContext ¶ added in v0.4.0
PrepareContext creates a prepared statement for later queries or executions. The caller must call the statement's Close method when the statement is no longer needed. Unlike the Prepare methods in database/sql this method also accepts args, which are meant only for query building. Therefore, only ?ident, ?ident..., ?sql are interpolated in this phase. Apart of that, ? is the only other placeholder allowed (this one will be transformed into a dialect specific one to allow the parameter binding.
The provided context is used for the preparation of the statement, not for the execution of the statement.
func (*DB) Query ¶
Query is a fundamental method of DB. It returns a Query struct which is capable of executing the sql (given by the query and the args) or loading the result into structs or primitive values.
func (*DB) QueryWithContext ¶ added in v0.4.0
QueryWithContext is a fundamental method of DB. It returns a Query struct which is capable of executing the sql (given by the query and the args) or loading the result into structs or primitive values.
func (*DB) SetMiddlewareFunc ¶
SetMiddlewareFunc changes the DB middleware func. Default func passes the Execer unchanged. SetMiddlewareFunc allowes the user to set his own middleware to perform additional operations (e.g. profiling) when executing queries.
type Execer ¶
type Execer interface {
ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}
Execer is an interface that Query works with.
type Marshaler ¶ added in v0.3.0
type Marshaler interface {
MarshalSQL(t Translator) (string, error)
}
Marshaler is the interface implemented by types that can marshal themselves into valid SQL. Any type that implements Marshaler can be used as an argument to the ?sql placeholder.
type Query ¶
type Query struct {
// contains filtered or unexported fields
}
Query represents an arbitrary SQL statement. The SQL is preprocessed by Preprocessor before running.
func (*Query) All ¶
All executes the query that should return rows, and loads the resulting data into dest which must be a slice of structs. Only fields that match the column names (after filtering through the mapperFunc) are filled.
func (*Query) Exec ¶
Exec executes the query that shouldn't return rows. For example: INSERT or UPDATE.
func (*Query) One ¶
One executes the query that should return rows and loads the resulting data from the first row into dest which must be a struct. Only fields that match the column names (after filtering through the mapperFunc) are filled. One returns sql.ErrNoRows if there are no rows.
func (*Query) ScanAllRows ¶ added in v0.3.0
ScanAllRows executes the query that is expected to return rows. It copies the columns from the matched rows into the slices pointed at by dests.
func (*Query) ScanRow ¶
ScanRow executes the query that is expected to return at most one row. It copies the columns from the matched row into the values pointed at by dest. If more than one row matches the query, ScanRow uses the first row and discards the rest. If no row matches the query, ScanRow returns sql.ErrNoRows.
type Stmt ¶
type Stmt struct {
// contains filtered or unexported fields
}
Stmt is a prepared statement.
func (*Stmt) Bind ¶
Bind binds args to the prepared statement and returns a Query struct ready to be executed. See (*DB).Query method.
func (*Stmt) BindContext ¶ added in v0.4.0
BindContext binds args to the prepared statement and returns a Query struct ready to be executed. See (*DB).Query method.
type Translator ¶ added in v0.3.0
type Translator struct {
// contains filtered or unexported fields
}
A Translator translates SQL queries using a dialect.
type Tx ¶
Tx wraps the sql.Tx to provide the Query method instead of the sql.Tx's original methods for comunication with the database.
func (*Tx) Prepare ¶
Prepare creates a prepared statement for later queries or executions. The caller must call the statement's Close method when the statement is no longer needed. Unlike the Prepare methods in database/sql this method also accepts args, which are meant only for query building. Therefore, only ?ident, ?ident..., ?sql are interpolated in this phase. Apart of that, ? is the only other placeholder allowed (this one will be transformed into a dialect specific one to allow the parameter binding.
func (*Tx) PrepareContext ¶ added in v0.4.0
PrepareContext creates a prepared statement for later queries or executions. The caller must call the statement's Close method when the statement is no longer needed. Unlike the Prepare methods in database/sql this method also accepts args, which are meant only for query building. Therefore, only ?ident, ?ident..., ?sql are interpolated in this phase. Apart of that, ? is the only other placeholder allowed (this one will be transformed into a dialect specific one to allow the parameter binding.
The provided context is used for the preparation of the statement, not for the execution of the statement.
func (*Tx) QueryWithContext ¶ added in v0.4.0
QueryWithContext is a (*DB).Query equivalent for transactions.
func (*Tx) Stmt ¶ added in v0.2.0
Stmt returns a transaction-specific prepared statement from an existing statement. The returned statement operates within the transaction and can no longer be used once the transaction has been committed or rolled back.
func (*Tx) StmtContext ¶ added in v0.4.0
Stmt returns a transaction-specific prepared statement from an existing statement.
The provided context is used for the preparation of the statement, not for the execution of the statement.
The returned statement operates within the transaction and can no longer be used once the transaction has been committed or rolled back.
Source Files
Directories