dbq

package module
v2.6.0 Latest Latest
Warning

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

 Go to latest Published: Aug 13, 2020 License: MIT Imports: 16 Imported by: 8

README

dbq

(Now compatible with MySQL and PostgreSQL!)

Everyone knows that performing simple DATABASE queries in Go takes numerous lines of code that is often repetitive. If you want to avoid the cruft, you have two options: A heavy-duty ORM that is not up to the standard of Laraval or Django. Or DBQ!

⚠️ WARNING: You will seriously reduce your database code to a few lines

⭐ the project to show your appreciation.

What is included

  • Supports ANY type of query
  • MySQL and PostgreSQL compatible
  • Convenient and Developer Friendly
  • Accepts any type of slice for query args
  • Flattens query arg slices to individual values
  • Bulk Insert seamlessly
  • Automatically unmarshal query results directly to a struct using mapstructure package
  • Lightweight
  • Compatible with mysql-go for proper MySQL query cancelation
  • Automatically retry query with exponential backoff if operation fails
  • Transaction management (automatic rollback)

Dependencies

NOTE: For mysql driver, parseTime=true setting can interfere with unmarshaling to civil.* types.

Installation

go get -u github.com/rocketlaunchr/dbq/v2

Examples

Let's assume a table called users:

id name age created_at
1 Sally 12 2019-03-01
2 Peter 15 2019-02-01
3 Tom 18 2019-01-01

Query

Q ordinarily returns []map[string]interface{} results, but you can automatically unmarshal to a struct. You will need to type assert the results.


type user struct {
  ID        int       `dbq:"id"`
  Name      string    `dbq:"name"`
  Age       int       `dbq:"age"`
  CreatedAt time.Time `dbq:"created_at"`
}

opts := &dbq.Options{ConcreteStruct: user{}, DecoderConfig:x}

results, err := dbq.Q(ctx, db, "SELECT * FROM users", opts)
results, err := dbq.Qs(ctx, db, "SELECT * FROM users", user{}, nil)

Results:

([]*main.user) (len=6 cap=8) {
 (*main.user)(0xc00009e1c0)({
  ID: (int) 1,
  Name: (string) (len=5) "Sally",
  Age: (int) 12,
  CreatedAt: (time.Time) 2019-03-01 00:00:00 +0000 UTC
 }),
 (*main.user)(0xc00009e300)({
  ID: (int) 2,
  Name: (string) (len=5) "Peter",
  Age: (int) 15,
  CreatedAt: (time.Time) 2019-02-01 00:00:00 +0000 UTC
 }),
 (*main.user)(0xc00009e440)({
  ID: (int) 3,
  Name: (string) (len=3) "Tom",
  Age: (int) 18,
  CreatedAt: (time.Time) 2019-01-01 00:00:00 +0000 UTC
 })
}

Query Single Row

If you know that the query will return at maximum 1 row:

result := dbq.MustQ(ctx, db, "SELECT * FROM users LIMIT 1", dbq.SingleResult)
if result == nil {
  // no result
} else {
  result.(map[string]interface{})
}

Bulk Insert

You can insert multiple rows at once.


db, _ := sql.Open("mysql", "user:password@tcp(localhost:3306)/db")

type Row struct {
  Name      string
  Age       int
  CreatedAt time.Time
}

users := []interface{}{
  dbq.Struct(Row{"Brad", 45, time.Now()}),
  dbq.Struct(Row{"Ange", 36, time.Now()}),
  dbq.Struct(Row{"Emily", 22, time.Now()}),
}

stmt := dbq.INSERTStmt("users", []string{"name", "age", "created_at"}, len(users))

dbq.E(ctx, db, stmt, nil, users)

Flatten Query Args

All slices are flattened automatically.

args1 := []string{"A", "B", "C"}
args2 := []interface{}{2, "D"}
args3 := dbq.Struct(Row{"Brad Pitt", 45, time.Now()})

results := dbq.MustQ(ctx, db, stmt, args1, args2, args3)

// Placeholder arguments will get flattened to:
results := dbq.MustQ(ctx, db, stmt, "A", "B", "C", 2, "D", "Brad Pitt", 45, time.Now())

NOTE: FlattenArgs function can be used more generally.

MySQL cancelation

To properly cancel a MySQL query, you need to use the mysql-go package. dbq plays nicely with it.

import sql "github.com/rocketlaunchr/mysql-go"

pool, _ := sql.Open("user:password@tcp(localhost:3306)/db")

conn, err := pool.Conn(ctx)

opts := &dbq.Options{
  SingleResult: true,
  PostFetch: func(ctx context.Context) error {
    return conn.Close()
  },
}

result := dbq.MustQ(ctx, conn, "SELECT * FROM users LIMIT 1", opts)
if result == nil {
  // no result
} else {
  result.(map[string]interface{})
}

PostUnmarshaler

After fetching the results, you can further modify the results by implementing the PostUnmarshaler interface. The PostUnmarshal function must be attached to the pointer of the struct.

type user struct {
  ID        int       `dbq:"id"`
  Name      string    `dbq:"name"`
  Age       int       `dbq:"age"`
  CreatedAt time.Time `dbq:"created_at"`
  HashedID  string    `dbq:"-"`          // Obfuscate ID
}

func (u *user) PostUnmarshal(ctx context.Context, row, total int) error {
  u.HashedID = obfuscate(u.ID)
  return nil
}

ScanFaster

The ScanFaster interface eradicates the use of the reflect package when unmarshaling. If you don't need to perform fancy time conversions or interpret weakly typed data, then it is more performant.

type user struct {
  ID       int    `dbq:"id"`
  Name     string `dbq:"name"`
}

func (u *user) ScanFast() []interface{} {
  return []interface{}{&u.ID, &u.Name}
}

Retry with Exponential Backoff

If the database operation fails, you can automatically retry with exponentially increasing intervals between each retry attempt. You can also set the maximum number of retries.

opts := &dbq.Options{
  RetryPolicy:  dbq.ExponentialRetryPolicy(60 * time.Second, 3),
}

Transaction Management

You can conveniently perform numerous complex database operations within a transaction without having to worry about rolling back. Unless you explicitly commit, it will automatically rollback.

You have access to the Q and E function as well as the underlying tx for performance purposes.

ctx := context.Background()
pool, _ := sql.Open("mysql", "user:password@tcp(localhost:3306)/db")

dbq.Tx(ctx, pool, func(tx interface{}, Q dbq.QFn, E dbq.EFn, txCommit dbq.TxCommit) {
  
  stmt := dbq.INSERTStmt("table", []string{"name", "age", "created_at"}, 1)
  res, err := E(ctx, stmt, nil, "test name", 34, time.Now())
  if err != nil {
    return // Automatic rollback
  }
  txCommit() // Commit
})

Custom Queries

The v2/x subpackage will house functions to perform custom SQL queries. If they are general to both MySQL and PostgreSQL, they are inside the x subpackage. If they are specific to MySQL xor PostgreSQL, they are in the x/mysql xor x/pg subpackage respectively.

This is your package too!

If you want your own custom functions included, just submit a PR and place it in your own directory inside v2/x. As long as it compiles and is well documented it is welcome.

Bulk Update

As a warmup, I have included a Bulk Update function that works with MySQL and PostgreSQL. It allows you to update thousands of rows in 1 query without a transaction!

Other useful packages

  • dataframe-go - Statistics and data manipulation
  • electron-alert - SweetAlert2 for Electron Applications
  • igo - A Go transpiler with cool new syntax such as fordefer (defer for for-loops)
  • mysql-go - Properly cancel slow MySQL queries
  • react - Build front end applications using Go
  • remember-go - Cache slow database queries

The license is a modified MIT license. Refer to the LICENSE file for more details.

© 2019-20 PJ Engineering and Business Solutions Pty. Ltd.

Final Notes

Feel free to enhance features by issuing pull-requests. Note that the project is written in igo and transpiled into Go.

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var SingleResult = &Options{SingleResult: true}

SingleResult is a convenient option for the common case of expecting a single result from a query.

Functions

func ConstantDelayRetryPolicy added in v2.2.0 

func ConstantDelayRetryPolicy(interval time.Duration, retryAttempts ...uint64) backoff.BackOff

ConstantDelayRetryPolicy is a retry policy with constant intervals between each retry attempt. It will retry forever unless restricted by retryAttempts.

See: https://godoc.org/gopkg.in/cenkalti/backoff.v4#ConstantBackOff

func E 

func E(ctx context.Context, db ExecContexter, query string, options *Options, args ...interface{}) (sql.Result, error)

E is used for "Exec" queries such as insert, update and delete.

args is a list of values to replace the placeholders in the query. When an arg is a slice, the values of the slice will automatically be flattened to a list of interface{}.

func ExponentialRetryPolicy added in v2.2.0 

func ExponentialRetryPolicy(maxElapsedTime time.Duration, retryAttempts ...uint64) backoff.BackOff

ExponentialRetryPolicy is a retry policy with exponentially increasing intervals between each retry attempt. If maxElapsedTime is 0, it will retry forever unless restricted by retryAttempts.

See: https://godoc.org/gopkg.in/cenkalti/backoff.v4#ExponentialBackOff

func FlattenArgs added in v2.5.0 

func FlattenArgs(args ...interface{}) []interface{}

FlattenArgs will accept a list of values and flatten any slices encountered.

Example: 

args1 := []string{"A", "B", "C"}
args2 := []interface{}{2, "D"}
args3 := dbq.Struct(Row{"Brad Pitt", 45, time.Now()})

dbq.FlattenArgs(args1, args2, args3)
// Output: []interface{}{"A", "B", "C", 2, "D", "Brad Pitt", 45, time.Now()}

func INSERT deprecated 

func INSERT(tableName string, columns []string, rows int, dbtype ...Database) string

INSERT is the legacy equivalent of INSERTStmt.

Deprecated: It will be removed in v3. Use INSERTStmt instead.

func INSERTStmt added in v2.2.0 

func INSERTStmt(tableName string, columns []string, rows int, dbtype ...Database) string

INSERTStmt will generate an INSERT statement. It can be used for bulk inserts.

NOTE: You may have to escape the column names. For MySQL, use backticks. Databases also have a limit to the number of query placeholders you can have. This will limit the number of rows you can insert.

func MustE 

func MustE(ctx context.Context, db ExecContexter, query string, options *Options, args ...interface{}) sql.Result

MustE is a wrapper around the E function. It will panic upon encountering an error. This can erradicate boiler-plate error handing code.

func MustQ 

func MustQ(ctx context.Context, db interface{}, query string, options *Options, args ...interface{}) interface{}

MustQ is a wrapper around the Q function. It will panic upon encountering an error. This can erradicate boiler-plate error handing code.

func MustQs added in v2.3.0 

func MustQs(ctx context.Context, db interface{}, query string, ConcreteStruct interface{}, options *Options, args ...interface{}) interface{}

MustQs is a wrapper around the Qs function. It will panic upon encountering an error. This can erradicate boiler-plate error handing code.

func Ph 

func Ph(nCols, nRows int, incr int, dbtype ...Database) string

Ph generates the placeholders for SQL queries. For a bulk insert operation, nRows is the number of rows you intend to insert, and nCols is the number of fields per row. For the IN function, set nRows to 1. For PostgreSQL, you can use incr to increment the placeholder starting count.

NOTE: The function panics if either nCols or nRows is 0.

Example: 

dbq.Ph(3, 1, 0)
// Output: ( ?,?,? )

dbq.Ph(3, 2, 0)
// Output: ( ?,?,? ),( ?,?,? )

dbq.Ph(3, 2, 6, dbq.PostgreSQL)
// Output: ($7,$8,$9),($10,$11,$12)

func Q 

func Q(ctx context.Context, db interface{}, query string, options *Options, args ...interface{}) (out interface{}, rErr error)

Q is used for querying a SQL database. A []map[string]interface{} is ordinarily returned. Each returned row (an item in the slice) contains a map where the keys are the columns, and the values are the data for each column. However, when a ConcreteStruct is provided via the options, the mapstructure package is used to automatically return []*struct instead. To bypass the mapstructure package, ScanFaster interface can be implemented.

args is a list of values to replace the placeholders in the query. When an arg is a slice, the values of the slice will automatically be flattened to a list of interface{}.

NOTE: sql.ErrNoRows is never returned as an error: A slice is always returned, unless the behavior is modified by the SingleResult Option.

func Qs added in v2.3.0 

func Qs(ctx context.Context, db interface{}, query string, ConcreteStruct interface{}, options *Options, args ...interface{}) (out interface{}, rErr error)

Qs operates the same as Q except it requires you to provide a ConcreteStruct as an argument. This allows you to recycle common options and conveniently provide a different ConcreteStruct.

func Struct 

func Struct(strct interface{}, tagName ...string) []interface{}

Struct converts the fields of the struct into a slice of values. You can use it to convert a struct into the placeholder arguments required by the Q and E function. tagName is used to indicate the struct tag (default is "dbq"). The function panics if strct is not an actual struct.

func Tx added in v2.2.0 

func Tx(ctx context.Context, db interface{}, fn func(tx interface{}, Q QFn, E EFn, txCommit TxCommit), retryPolicy ...backoff.BackOff) error

Tx is used to perform an arbitrarily complex operation and not have to worry about rolling back a transaction. The transaction is automatically rolled back unless explicitly committed by calling txCommit. tx is only exposed for performance purposes. Do not use it to commit or rollback.

NOTE: Until this note is removed, this function is not necessarily backward compatible.

Example: 

ctx := context.Background()
pool, _ := sql.Open("mysql", "user:password@tcp(localhost:3306)/db")

dbq.Tx(ctx, pool, func(tx interface{}, Q dbq.QFn, E dbq.EFn, txCommit dbq.TxCommit) {
   stmt := dbq.INSERTStmt("table", []string{"name", "age", "created_at"}, 1)
   res, err := E(ctx, stmt, nil, "test name", 34, time.Now())
   if err != nil {
      return // Automatic rollback
   }
   txCommit()
})

Types

type BeginTxer added in v2.2.0 

type BeginTxer interface {
	BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)
}

BeginTxer is an object than can begin a transaction.

type Database 

type Database int

Database is used to set the Database. Different databases have different syntax for placeholders etc. 

const (
	// MySQL database
	MySQL Database = 0
	// PostgreSQL database
	PostgreSQL Database = 1
)

type EFn added in v2.2.0 

type EFn func(ctx context.Context, query string, options *Options, args ...interface{}) (sql.Result, error)

EFn is shorthand for E. It will automatically use the appropriate transaction.

type ExecContexter 

type ExecContexter interface {
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
}

ExecContexter is for modifying the database state.

type Options 

type Options struct {

	// ConcreteStruct can be set to any concrete struct (not a pointer).
	// When set, the mapstructure package is used to convert the returned
	// results automatically from a map to a struct. The `dbq` struct tag
	// can be used to map column names to the struct's fields.
	//
	// See: https://godoc.org/github.com/mitchellh/mapstructure
	ConcreteStruct interface{}

	// DecoderConfig is used to configure the decoder used by the mapstructure
	// package. If it's not supplied, a default StructorConfig is assumed. This means
	// WeaklyTypedInput is set to true and no DecodeHook is provided. Alternatively, if you require
	// a configuration for common time-based conversions, StdTimeConversionConfig is available.
	//
	// See: https://godoc.org/github.com/mitchellh/mapstructure
	DecoderConfig *StructorConfig

	// SingleResult can be set to true if you know the query will return at most 1 result.
	// When true, a nil is returned if no result is found. Alternatively, it will return the
	// single result directly (instead of wrapped in a slice). This makes it easier to
	// type assert.
	SingleResult bool

	// PostFetch is called after all results are fetched but before PostUnmarshaler is called (if applicable).
	// It can be used to return a database connection back to the pool.
	PostFetch func(ctx context.Context) error

	// ConcurrentPostUnmarshal can be set to true if PostUnmarshal must be called concurrently.
	ConcurrentPostUnmarshal bool

	// RawResults can be set to true for results to be returned unprocessed ([]byte).
	// This option does nothing if ConcreteStruct is provided.
	RawResults bool

	// RetryPolicy can be set if you want to retry the query in the event of failure.
	//
	// Example:
	//
	//  dbq.ExponentialRetryPolicy(60 * time.Second, 3)
	//
	RetryPolicy backoff.BackOff
}

Options is used to modify the default behavior.

func (*Options) MustQ added in v2.3.0 

func (o *Options) MustQ(ctx context.Context, db interface{}, query string, args ...interface{}) interface{}

MustQ is a convenience function that calls dbq.MustQ. It allows you to recycle common options.

func (*Options) MustQs added in v2.3.0 

func (o *Options) MustQs(ctx context.Context, db interface{}, query string, ConcreteStruct interface{}, args ...interface{}) interface{}

MustQs is a convenience function that calls dbq.MustQs. It allows you to recycle common options.

func (*Options) Q added in v2.3.0 

func (o *Options) Q(ctx context.Context, db interface{}, query string, args ...interface{}) (out interface{}, rErr error)

Q is a convenience function that calls dbq.Q. It allows you to recycle common options.

func (*Options) Qs added in v2.3.0 

func (o *Options) Qs(ctx context.Context, db interface{}, query string, ConcreteStruct interface{}, args ...interface{}) (out interface{}, rErr error)

Qs is a convenience function that calls dbq.Qs. It allows you to recycle common options.

type PostUnmarshaler 

type PostUnmarshaler interface {

	// PostUnmarshal is called for each row after all results have been fetched.
	// You can use it to further modify the values of each ConcreteStruct.
	PostUnmarshal(ctx context.Context, row, total int) error
}

PostUnmarshaler allows you to further modify all results after unmarshaling. The ConcreteStruct pointer must implement this interface to make use of this feature.

Example: 

type user struct {
   ID       int    `dbq:"id"`
   Name     string `dbq:"name"`
   HashedID string `dbq:"-"` // Obfuscated ID
}

func (u *user) PostUnmarshal(ctx context.Context, row, total int) error {
   u.HashedID = obfuscate(u.ID)
   return nil
}

type QFn added in v2.2.0 

type QFn func(ctx context.Context, query string, options *Options, args ...interface{}) (interface{}, error)

QFn is shorthand for Q. It will automatically use the appropriate transaction.

type QueryContexter 

type QueryContexter interface {
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
}

QueryContexter is for querying the database.

type SQLBasic 

type SQLBasic interface {
	ExecContexter
	QueryContexter
}

SQLBasic allows for querying and executing statements.

type ScanFaster added in v2.4.0 

type ScanFaster interface {

	// ScanFast is used to directly scan the results from the query to the ConcreteStruct pointer.
	// The number of columns returned from the query must match the length of the slice returned.
	//
	// See: https://golang.org/pkg/database/sql/#Rows.Scan
	//
	// WARNING: "SELECT * FROM ..." may return more columns in the future if the table structure changes.
	ScanFast() []interface{}
}

ScanFaster eradicates the use of the reflect package when unmarshaling. The ConcreteStruct pointer must implement this interface to make use of this feature. If you don't need to perform fancy time conversions or interpret weakly typed data (via mapstructure pkg), then this is the recommended approach as it is more performant.

Example: 

type user struct {
   ID       int    `dbq:"id"`
   Name     string `dbq:"name"`
}

func (u *user) ScanFast() []interface{} {
   return []interface{}{&u.ID, &u.Name}
}

type StructorConfig 

type StructorConfig struct {

	// DecodeHook, if set, will be called before any decoding and any
	// type conversion (if WeaklyTypedInput is on). This lets you modify
	// the values before they're set down onto the resulting struct.
	//
	// If an error is returned, the entire decode will fail with that
	// error.
	DecodeHook mapstructure.DecodeHookFunc

	// If WeaklyTypedInput is true, the decoder will make the following
	// "weak" conversions:
	//
	//   - bools to string (true = "1", false = "0")
	//   - numbers to string (base 10)
	//   - bools to int/uint (true = 1, false = 0)
	//   - strings to int/uint (base implied by prefix)
	//   - int to bool (true if value != 0)
	//   - string to bool (accepts: 1, t, T, TRUE, true, True, 0, f, F,
	//     FALSE, false, False. Anything else is an error)
	//   - empty array = empty map and vice versa
	//   - negative numbers to overflowed uint values (base 10)
	//   - slice of maps to a merged map
	//   - single values are converted to slices if required. Each
	//     element is weakly decoded. For example: "4" can become []int{4}
	//     if the target type is an int slice.
	//
	WeaklyTypedInput bool
}

StructorConfig is used to expose a subset of the configuration options provided by the mapstructure package.

See: https://godoc.org/github.com/mitchellh/mapstructure#DecoderConfig

func StdTimeConversionConfig 

func StdTimeConversionConfig(dbtype ...Database) *StructorConfig

StdTimeConversionConfig provides a standard configuration for unmarshaling to time-related fields in a struct. It properly converts timestamps and datetime columns into time.Time objects. It assumes a MySQL database as default.

type TxCommit added in v2.2.0 

type TxCommit func() error

TxCommit will commit the transaction.

Source Files

View all Source files

Directories

Path Synopsis
x

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.