dali

package module
v0.5.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jul 25, 2023 License: MIT Imports: 12 Imported by: 0

README

DALí Logo   GoDoc Build Status

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:

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

Constants

This section is empty.

Variables

This section is empty.

Functions

func LastInsertID 

func LastInsertID(res sql.Result, err error) (int64, error)

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 

type DB struct {
	DB *sql.DB
	// contains filtered or unexported fields
}

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 NewDB 

func NewDB(db *sql.DB, d dialect.Dialect) *DB

NewDB instantiates DB from the given database/sql DB handle in the particular dialect.

func Open 

func Open(driverName, dataSourceName string) (*DB, error)

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) Begin 

func (db *DB) Begin() (*Tx, error)

Begin starts a transaction. The isolation level is dependent on the driver.

func (*DB) BeginTx added in v0.4.0 

func (db *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error)

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) Close 

func (db *DB) Close() error

Close closes the database, releasing any open resources.

func (*DB) Ping 

func (db *DB) Ping() error

Ping verifies a connection to the database is still alive, establishing a connection if necessary.

func (*DB) Prepare 

func (db *DB) Prepare(query string, args ...interface{}) (*Stmt, error)

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 

func (db *DB) PrepareContext(ctx context.Context, query string, args ...interface{}) (*Stmt, error)

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 

func (db *DB) Query(query string, args ...interface{}) *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 

func (db *DB) QueryWithContext(ctx context.Context, query string, args ...interface{}) *Query

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 

func (db *DB) SetMiddlewareFunc(f func(Execer) Execer)

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 Map 

type Map map[string]interface{}

Map is just an alias for map[string]interface{}.

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 

func (q *Query) All(dest interface{}) error

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 

func (q *Query) Exec() (sql.Result, error)

Exec executes the query that shouldn't return rows. For example: INSERT or UPDATE.

func (*Query) One 

func (q *Query) One(dest interface{}) error

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) Rows 

func (q *Query) Rows() (*sql.Rows, error)

Rows executes that query that should return rows, typically a SELECT.

func (*Query) ScanAllRows added in v0.3.0 

func (q *Query) ScanAllRows(dests ...interface{}) error

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 

func (q *Query) ScanRow(dest ...interface{}) error

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.

func (*Query) String 

func (q *Query) String() string

type Stmt 

type Stmt struct {
	// contains filtered or unexported fields
}

Stmt is a prepared statement.

func (*Stmt) Bind 

func (s *Stmt) Bind(args ...interface{}) *Query

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 

func (s *Stmt) BindContext(ctx context.Context, args ...interface{}) *Query

BindContext binds args to the prepared statement and returns a Query struct ready to be executed. See (*DB).Query method.

func (*Stmt) Close 

func (s *Stmt) Close() error

Close closes the statement.

func (*Stmt) String 

func (s *Stmt) String() string

type Translator added in v0.3.0 

type Translator struct {
	// contains filtered or unexported fields
}

A Translator translates SQL queries using a dialect.

func (Translator) Translate added in v0.3.0 

func (t Translator) Translate(sql string, args []interface{}) (string, error)

Translate processes sql and args using the dialect specified in t. It returns the resulting SQL query and an error, if there is one.

type Tx 

type Tx struct {
	Tx *sql.Tx
	// contains filtered or unexported fields
}

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) Commit 

func (tx *Tx) Commit() error

Commit commits the transaction.

func (*Tx) Prepare 

func (tx *Tx) Prepare(query string, args ...interface{}) (*Stmt, error)

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 

func (tx *Tx) PrepareContext(ctx context.Context, query string, args ...interface{}) (*Stmt, error)

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) Query 

func (tx *Tx) Query(query string, args ...interface{}) *Query

Query is a (*DB).Query equivalent for transactions.

func (*Tx) QueryWithContext added in v0.4.0 

func (tx *Tx) QueryWithContext(ctx context.Context, query string, args ...interface{}) *Query

QueryWithContext is a (*DB).Query equivalent for transactions.

func (*Tx) Rollback 

func (tx *Tx) Rollback() error

Rollback aborts the transaction.

func (*Tx) Stmt added in v0.2.0 

func (tx *Tx) Stmt(stmt *Stmt) *Stmt

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 

func (tx *Tx) StmtContext(ctx context.Context, stmt *Stmt) *Stmt

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

View all Source files

Directories

Path Synopsis

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.