zzodb

README

Squalus — SQL made pleasant

Squalus is a package that makes it much easier to perform SQL queries in Go by encapsulating cursor manipulation, error handling and results fetching into a simple API. It is not an ORM, nor an SQL generator. SQL queries are only adapted to make them easier to manage, hiding some of the most annoying differences between SQL drivers and allowing to use named parameters even if the underlying engine does not support them. This project is intended to remain small and easy to use, staying away from feature bloating.

Supported Go version

Squalus is currently tested with Go 1.12.1.

squalus.DB creation

Create an sql.DB as usual, then a squalus.DB from it.

db1, err := sql.Open("driver name", "some connection string")
if err != nil {
	// handle err
}
db, err := squalus.NewDB(db1)
if err != nil {
	// handle err
}
defer db.Close()

Squalus automatically detects the driver type. Supported drivers are:

• Mysql (go-sql-driver/mysql). Supported Mysql versions: 5.7 and 8.0.
• PostgreSQL (lib/pq). Supported Postgresql versions: 10.7 and 11.2.
• SQLite3 (mattn/sqlite3). Supported SQLite version: 3.
• MS SQL Server (denisenkom/go-mssqldb). Supported SQL Server versions: 2017 CU12, 2019 CTP 2.2.

Attempting to create a DB with another driver type results in an error.

Examples setting

The following examples use a table in which data about persons are stored. Here is the corresponding struct:

type Person struct {
	ID        int       `db:"id"`     // notice the db tag
	Name      string    `db:"name"`
	Height    float64   `db:"height"` // in meters
	BirthDate time.Time `db:"birth"`
}

Query execution

Just like sql.DB, squalus.DB provides an Exec method.

db.Exec(ctx, "CREATE TABLE [persons]([id] INT, [name] VARCHAR(128), [height] FLOAT, [birth] DATETIME)", nil)
result, err := db.Exec(
	ctx,
	"INSERT INTO [persons]([id], [name], [height], [birth]) VALUES({id}, {name}, {height}, {birth})",
	map[string]interface{}{
		"id":    1,
		"name": "Alice Abbott",
		"height": 1.65,
		"birth": time.Date(1985, 7, 12, 0, 0, 0, 0, time.UTC),
	},
)
if err != nil {
	// handle err
}
// result is the regular sql.Result

This example shows that Squalus uses square brackets as database, table and field delimiters. They are automatically replaced by whatever the underlying driver requires, and of course, they can be omitted when not needed. MySQL users will appreciate finally being able to use backticks for long queries in their Go code.

It also shows how query parameters work. Only named parameters are supported, and they are passed through a map[string]interface{}, which can be nil if no parameters are provided.

The ctx parameter is a context, context.Background() can be used if nothing else is available. Internally, Squalus uses the Context versions of the Go SQL methods.

Data acquisition

Query is the only method that Squalus provides to read data. Its behaviour depends on the type of the to parameter.

The following examples assume that the table contains the rows below:

ID	Name	Height	Birth
1	Alice Abbott	1.65	1985-07-11
2	Bob Burton	1.59	1977-03-01
3	Clarissa Cooper	1.68	2003-09-30
4	Donald Dock	1.71	1954-12-04

Query to a single value

To read a single value, use a pointer to a basic type as the value of to.

var name string
if err := db.Query(
	ctx,
	`SELECT [name]
	 FROM [persons]
	 WHERE [id]={id}`,
	map[string]interface{}{"id": 3},
	&name,
); err != nil {
	// handle err
}
// name contains "Clarissa Cooper"

If no rows are found, Query returns sql.ErrNoRows.

As a special case, time.Time is treated like a basic type, so it behaves as expected.

var birthDate time.Time
if err := db.Query(
	ctx,
	`SELECT [birth]
	 FROM [persons]
	 WHERE [id]={id}`,
	map[string]interface{}{"id": 3},
	&birthDate,
); err != nil {
	// handle err
}
// birthDate == time.Date(2003, 9, 30, 0, 0, 0, 0, time.UTC)

Query to a struct

You can read one multicolumn row directly into a struct.

var person Person
if err := db.Query(
	ctx,
	`SELECT [name], [id], [birth], [height]
	 FROM [persons]
	 WHERE [id]={id}`,
	map[string]interface{}{"id": 3},
	&person,
); err != nil {
	// handle err
}
// person contains the data for Clarissa Cooper

Struct composition is supported, with the same rules for naming fields as in Go. The one exception is that if a db tag is given, it replaces the field name. This makes it easier to work with joins and other scenarios in which several fields bear the same name. For example, the example above also works with the following definition of Person, because the structs are embedded (anonymous):

type Height struct {
	Height float64 `db:"height"`
}
type NameBirthHeight struct {
	Name      string    `db:"name"`
	BirthDate time.Time `db:"birth"`
	Height
}
type Person struct {
	ID int `db:"id"`
	NameBirthHeight
}

This example illustrates the handling of named structs:

type Height struct {
	Height float64 `db:"height"`
}
type NameBirthHeight struct {
	Name      string    `db:"name"`
	BirthDate time.Time `db:"birth"`
	H         Height    `db:"hh"`
}
type PersonComposed struct {
	ID  int `db:"id"`
	NBH NameBirthHeight
}

var person1 PersonComposed
if err := db.Query(
	ctx,
	`SELECT [name] AS [NBH.name], [id], [birth] AS [NBH.birth], [height] AS [NBH.hh.height]
	 FROM [persons]
	 WHERE [id]={id}`,
	map[string]interface{}{"id": 3},
	&person1,
); err != nil {
	// handle err
}

Query to a slice

If to is a pointer to a slice, Squalus fills the slice with all the data returned by the query. The rules for handling basic types and structs are applied to the slice type.

var people []Person
if err := db.Query(
	ctx,
	`SELECT [name], [id], [birth], [height]
	 FROM [persons]
	 ORDER BY [id]`,
	nil,
	&people,
); err != nil {
	// handle err
}
// people contains all four persons

Notice how there is still exactly one place where an error may be returned, even though several rows were read from database.

Query to a channel

If to is a channel, every row will be read and sent to that channel. Squalus closes the channel when there are no more data.

ch := make(chan Person)

go func() {
	for p := range ch {
		fmt.Println(p)
	}
}()

if err := db.Query(
	ctx,
	`SELECT [name], [id], [birth], [height]
	 FROM [persons]
	 ORDER BY [id]`,
	nil,
	ch,
); err != nil {
	// handle err
}
// all people are printed to stdout

Query using a callback

If to is a function, it is called once for each row. Columns and callback parameters are matched by rank only, not by name: each column, in the order of the SELECT clause, matches the corresponding function parameter. Struct parameters are scanned directly, without applying the mechanism described above to match columns to struct fields.

if err := db.Query(
	ctx,
	`SELECT [name], [id], [birth], [height]
	 FROM [persons]
	 ORDER BY [id]`,
	nil,
	func(name string, id int, birthDate time.Time, height float64) {
		fmt.Printf("%v has ID %v, birth date %v and height %v\n", name, id, birthDate, height)
	},
); err != nil {
	// handle err
}
// all people are printed to stdout

If the callback returns a value, it must be of type error. In that case, returning a non-nil error stops the query execution and causes that error to be returned as the result of Query.

if err := db.Query(
	ctx,
	`SELECT [name], [id], [birth], [height] FROM [persons]`,
	nil,
	func(name string, id int, birthDate time.Time, height float64) error {
		if name == "Donald Dock" {
			return errors.New("found an intruder")
		}
		return nil
	},
); err != nil {
	// handle err
}
// Query returns an error with message "found an intruder".

Structs that have a Scan method

If a struct has a Scan method with a pointer receiver, it is treated like a basic type, so it behaves as expected.

type NameResult struct {
	First string
	Last  string
}

func (nr *NameResult) Scan(src interface{}) error {
	// some drivers return a string here, some return a []byte
	s := ""
	switch val := src.(type) {
	case string:
		s = val
	case []uint8:
		s = string(val)
	default:
		return fmt.Errorf("could not acquire field value (type %T) as string or []byte", src)
	}
	t := strings.Split(s, " ")
	if len(t) != 2 {
		return fmt.Errorf("format of %s is wrong: it should contain exactly one space", s)
	}
	nr.First, nr.Last = t[0], t[1]
	return nil
}

func getNames() {
	var names []NameResult
	if err := db.Query(
		ctx,
		`SELECT [name]
		 FROM [persons]
		 ORDER BY [id]`,
		nil,
		&names,
	); err != nil {
		// handle err
    }
    // names contains the names of everybody
}

Writing IN clauses

Squalus makes it easy to perform a SELECT with an IN clause: if the value of a parameter is a slice, it is expanded automatically.

var people []Person
if err := db.Query(
	ctx,
	`SELECT [name], [id], [birth], [height]
	 FROM [persons]
	 WHERE [id] IN ({ids})
	 ORDER BY [id]`,
	map[string]interface{}{"ids": []int{1, 3, 4}},
	&people,
); err != nil {
	// handle err
}
// people contains Alice Abbott, Clarissa Cooper and Donald Dock

This rule does not apply to byte slices (and uint8 slices, since Go does not distinguish internally between byte and uint8), in order to facilitate loading and storing data between []byte and blob.

Transactions

Transactions are created as follows:

tx, err := db.Begin(ctx, opts)

where opts is an *sql.TxOptions (nil selects the default values). The return type is squalus.Tx, which has the following methods:

• Exec and Query are identical to the corresponding methods in DB,
• Commit() commits the transaction,
• Rollback() aborts the transaction.

License

Squalus is released under the MIT license, as found in the LICENSE file and below.

Copyright (C) 2017 QOS Energy

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Documentation

Index

• func MapStruct(v interface{}) map[string]interface{}
• type DB
  • func NewDB(db *sql.DB) (*DB, error)
  • func OpenDB(dlc, dsn string) (*DB, error)
  • func (db *DB) Fetch(sel *builder.Select, res interface{}) error
  • func (db *DB) Insert(table string, params map[string]interface{}) (id int64, err error)
  • func (db *DB) InsertEntity(entity Entity) error
  • func (db *DB) InsertModel(model Model) (int64, error)
  • func (db *DB) SaveEntity(entity Entity) error
• type Entity
• type Model

Functions

func MapStruct

func MapStruct(v interface{}) map[string]interface{}

Types

type DB

type DB struct {
	*squalus.Connection
}

func NewDB

func NewDB(db *sql.DB) (*DB, error)

NewDB returns a new database connection.

func OpenDB

func OpenDB(dlc, dsn string) (*DB, error)

func (*DB) Fetch

func (db *DB) Fetch(sel *builder.Select, res interface{}) error

func (*DB) Insert

func (db *DB) Insert(table string, params map[string]interface{}) (id int64, err error)

func (*DB) InsertEntity

func (db *DB) InsertEntity(entity Entity) error

func (*DB) InsertModel

func (db *DB) InsertModel(model Model) (int64, error)

func (*DB) SaveEntity

func (db *DB) SaveEntity(entity Entity) error

type Entity

type Entity interface {
	Primarykey() string
	GetId() int
	SetId(id int)
	Model
}

type Model

type Model interface {
	TableName() string
}