README
¶
yesql
A tool to write raw SQL in Go more effectively. WIP.
Consider it a thin client over, or a drop-in replacement for, the standard library package database/sql.
Features
- Leans on standard lib, keeping the same API
- Named arguments support
- Templates for query building (cached, thread safe)
- Query logs (TODO)
The Elevator Pitch™
Imagine if
database/sqlandtext/templateshad a baby.
Quick start
Open a connection to a database:
package foo
import (
"github.com/izolate/yesql"
_ "github.com/lib/pq"
)
func main() {
db, err := yesql.Open("postgres", "host=localhost user=foo sslmode=disable")
if err != nil {
panic(err)
}
}
Exec
Use Exec or ExecContext to execute a query without returning data. Named parameters (@Foo) allow you to bind arguments to map (or struct) fields without the risk of SQLi:
type Book struct {
ID string
Title string
Author string
}
func InsertBook(ctx context.Context, b Book) error {
q := `INSERT INTO users (id, title, author) VALUES (@ID, @Title, @Author)`
_, err := db.ExecContext(ctx, q, b)
return err
}
Query
Use Query or QueryContext to execute a query to return data. Templates offer you the chance to perform complex logic without string concatenation or query building:
type BookSearch struct {
Author string
Title string
Genre string
}
const sqlSearchBooks = `
SELECT * FROM books
WHERE author = @Author
{{if .Title}}AND title ILIKE @Title{{end}}
{{if .Genre}}AND genre = @Genre{{end}}
`
func SearchBooks(ctx context.Context, s BookSearch) ([]Book, error) {
rows, err := db.QueryContext(ctx, sqlSearchBooks, s)
if err != nil {
return nil, err
}
books := []Book{}
for rows.Next() {
var b Book
if err := rows.Scan(&b.ID, &b.Title, &b.Author, &b.Genre); err != nil {
return nil, err
}
books = append(books, b)
}
return books, nil
}
In fact, positional scanning is inflexible. Let's scan into a struct instead using db struct tags and rows.ScanStruct():
type Book struct {
ID string `db:"id"`
Title string `db:"title"`
Author string `db:"author"`
}
func SearchBooks(ctx context.Context, s BookSearch) ([]Book, error) {
rows, err := db.QueryContext(ctx, sqlSearchBooks, s)
if err != nil {
return nil, err
}
books := []Book{}
for rows.Next() {
var b Book
if err := rows.ScanStruct(&b); err != nil {
return nil, err
}
books = append(books, b)
}
return books, nil
}
Feature checklist
- Templated SQL statements
- Named arguments (bindvars)
- Statement logging
- Query tracing
- Struct scanning
- Unicode support
- Postgres support
TODO
- Exec/ExecContext
- Query/QueryContext
- QueryRow/QueryRowContext
- ScanSlice/ScanMap
Documentation
¶
Index ¶
- func ExecContext(db Execer, ctx context.Context, query string, data interface{}, ...) (sql.Result, error)
- type DB
- func (db *DB) Begin() (*Tx, error)
- func (db *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error)
- func (db *DB) Exec(query string, data interface{}) (sql.Result, error)
- func (db *DB) ExecContext(ctx context.Context, query string, data interface{}) (sql.Result, error)
- func (db *DB) Query(query string, data interface{}) (*Rows, error)
- func (db *DB) QueryContext(ctx context.Context, query string, data interface{}) (*Rows, error)
- func (db *DB) QueryRow(query string, data interface{}) *Row
- func (db *DB) QueryRowContext(ctx context.Context, query string, data interface{}) *Row
- type Execer
- type ExecerQueryer
- type Queryer
- type Row
- type Rows
- type Tx
- func (tx *Tx) Exec(query string, data interface{}) (sql.Result, error)
- func (tx *Tx) ExecContext(ctx context.Context, query string, data interface{}) (sql.Result, error)
- func (tx *Tx) Query(ctx context.Context, query string, data interface{}) (*Rows, error)
- func (tx *Tx) QueryContext(ctx context.Context, query string, data interface{}) (*Rows, error)
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func ExecContext ¶
func ExecContext( db Execer, ctx context.Context, query string, data interface{}, tpl template.Executer, bvar bindvar.Parser, ) (sql.Result, error)
ExecContext executes a query without returning any rows, e.g. an INSERT. The data object is a map/struct for any placeholder parameters in the query.
Types ¶
type DB ¶
func MustOpen ¶
MustOpen opens a database specified by its database driver name and a driver-specific data source name, and panics on any errors.
func Open ¶
Open opens a database specified by its database driver name and a driver-specific data source name, usually consisting of at least a database name and connection information.
Most users will open a database via a driver-specific connection helper function that returns a *DB. No database drivers are included in the Go standard library. See https://golang.org/s/sqldrivers for a list of third-party drivers.
Open may just validate its arguments without creating a connection to the database. To verify that the data source name is valid, call Ping.
The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the Open function should be called just once. It is rarely necessary to close a DB.
func (*DB) Begin ¶
Begin starts a transaction. The default isolation level is dependent on the driver.
func (*DB) BeginTx ¶
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) Exec ¶
Exec executes a query without returning any rows, e.g. an INSERT. The data object is a map/struct for any placeholder parameters in the query.
func (*DB) ExecContext ¶
ExecContext executes a query without returning any rows, e.g. an INSERT. The data object is a map/struct for any placeholder parameters in the query.
func (*DB) Query ¶
Query executes a query that returns rows, typically a SELECT. The data object is a map/struct for any placeholder parameters in the query.
func (*DB) QueryContext ¶
QueryContext executes a query that returns rows, typically a SELECT. The data object is a map/struct for any placeholder parameters in the query.
func (*DB) QueryRow ¶
QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.
QueryRow uses context.Background internally; to specify the context, use QueryRowContext.
func (*DB) QueryRowContext ¶
QueryRowContext executes a query that is expected to return at most one row. QueryRowContext always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.
type ExecerQueryer ¶
ExecerQueryer is a union interface comprising Execer and Queryer.
type Row ¶
type Row struct {
// contains filtered or unexported fields
}
Row is the result of calling QueryRow to select a single row. It's a re-implementation of *sql.Row because the standard type is inaccessible.
func QueryRowContext ¶
func QueryRowContext( db Queryer, ctx context.Context, query string, data interface{}, tpl template.Executer, bvar bindvar.Parser, ) *Row
QueryRowContext executes a query that is expected to return at most one row. QueryRowContext always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.
func (*Row) Err ¶
Err provides a way for wrapping packages to check for query errors without calling Scan. Err returns the error, if any, that was encountered while running the query. If this error is not nil, this error will also be returned from Scan.
func (*Row) Scan ¶
Scan copies the columns from the matched row into the values pointed at by dest. See the documentation on Rows.Scan for details. If more than one row matches the query, Scan uses the first row and discards the rest. If no row matches the query, Scan returns ErrNoRows.
func (*Row) ScanStruct ¶
ScanStruct copies the columns in the current row into the values pointed at by the dest struct.
ScanStruct is like Rows.Scan, but doesn't rely on positional scanning, and instead scans into a struct based on the column names and the db struct tags, e.g. Foo string `db:"foo"`.
type Rows ¶
Rows is the result of a query. Its cursor starts before the first row of the result set. Use Next to advance from row to row.
func QueryContext ¶
func QueryContext( db Queryer, ctx context.Context, query string, data interface{}, tpl template.Executer, bvar bindvar.Parser, ) (*Rows, error)
QueryContext executes a query that returns rows, typically a SELECT. The data object is a map/struct for any placeholder parameters in the query.
func (*Rows) ScanStruct ¶
ScanStruct copies the columns in the current row into the values pointed at by the dest struct.
ScanStruct is like Rows.Scan, but doesn't rely on positional scanning, and instead scans into a struct based on the column names and the db struct tags, e.g. Foo string `db:"foo"`.
type Tx ¶
Tx is an in-progress database transaction.
A transaction must end with a call to Commit or Rollback.
After a call to Commit or Rollback, all operations on the transaction fail with ErrTxDone.
The statements prepared for a transaction by calling the transaction's Prepare or Stmt methods are closed by the call to Commit or Rollback.
func (*Tx) Exec ¶
Exec executes a query without returning any rows. The data object is a map/struct for any placeholder parameters in the query.
func (*Tx) ExecContext ¶
ExecContext executes a query that doesn't return rows. The data object is a map/struct for any placeholder parameters in the query.
Source Files
Directories