dbq

package module
Version: v1.1.11 Latest Latest
Warning

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

Go to latest
Published: Jan 17, 2020 License: MIT Imports: 15 Imported by: 0

README

dbq - Barbeque the boilerplate code GoDoc Go Report Card

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

Dependencies

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

Installation

go get -u github.com/rocketlaunchr/dbq

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
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.INSERT("users", []string{"name", "age", "created_at"}, len(users))

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

Query

dbq.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:

([]interface {}) (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
 }),
 (*main.user)(0xc00009e5c0)({
  ID: (int) 4,
  Name: (string) (len=4) "Brad",
  Age: (int) 45,
  CreatedAt: (time.Time) 2019-07-24 14:36:58 +0000 UTC
 }),
 (*main.user)(0xc00009e700)({
  ID: (int) 5,
  Name: (string) (len=4) "Ange",
  Age: (int) 36,
  CreatedAt: (time.Time) 2019-07-24 14:36:58 +0000 UTC
 }),
 (*main.user)(0xc00009e840)({
  ID: (int) 6,
  Name: (string) (len=5) "Emily",
  Age: (int) 22,
  CreatedAt: (time.Time) 2019-07-24 14:36:58 +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{})
}

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

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)
defer conn.Close()

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

Other useful packages

  • dataframe-go - Statistics and data manipulation
  • 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 E

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

E is a wrapper around the Q function. It is used for "Exec" queries such as insert, update and delete. It also returns a sql.Result interface instead of an empty interface.

func INSERT

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

INSERT will generate an INSERT statement.

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 Ph

func Ph(columnsN, rows int, incr int, dbtype ...Database) string

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

NOTE: The function panics if either columnsN or rows is 0.

func Q

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

Q is a convenience function that is used for inserting, updating, deleting, and querying a SQL database. For inserts, updates, and deletes, a sql.Result is returned. For queries, a []map[string]interface{} is ordinarily returned. Each result (an item in the slice) contains a map where the keys are the columns, and the values are the data for the column. When a ConcreteStruct is provided via the Options, the mapstructure package is used to automatically return []structs instead. args is a list of values to replace the placeholders in the query. A single slice (of any slice type) can be provided for the first arg. If so, the values will automatically be flattened to a list of interface{}.

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

func Struct added in v1.1.5

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.

Types

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 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.
	//
	// 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
}

Options is used to modify the default behavior.

type PostUnmarshaler added in v1.1.0

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, count 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.

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 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 added in v1.0.3

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.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL