Zero boilerplate database operations for Go

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!

Difference between v1 and v2

When a ConcreteStruct is provided, in v1, the Q and MustQ functions return []interface{} while in v2 they return []*struct.

NOTE: v1 is obsolete and will no longer receive updates.

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

Legal Information

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.

Comments
  • Congratulations and benchmarks project

    Congratulations and benchmarks project

    My happiest congratulations on this project! Very good!

    Can I ask you for an opinion on this benchmark project?

    What do you think about adding dbq too?

    It's very easy, just copy one of these files and edit it with dbq code.

    I found dbq today and I still need to understand it.

    Again, thanks for your amazing project!

  • Add support for PostgreSQL arrays

    Add support for PostgreSQL arrays

    Problem

    PostgreSQL has support for arrays. For example, we have this table:

    CREATE TABLE example (
        bytes BYTEA -- byte array
    );
    

    In order to insert data into it, we need this query:

    INSERT INTO example (bytes) VALUES ({0, 1, 2, 3, 4});
    

    or

    INSERT INTO example (bytes) VALUES (ARRAY[0, 1, 2, 3, 4]);
    

    Using dbq that code should've looked like:

    stmt := dbq.INSERT("example", []string{"bytes"}, 1, dbq.PostgreSQL)
    _, _ = dbq.E(context.Background(), db, stmt, nil, []byte{0, 1, 2, 3, 4})
    

    Expected behavior of this code

    It will create statement

    INSERT INTO example (bytes) VALUES ({0, 1, 2, 3, 4});
    

    which will correctly insert array into the table.

    Actual behavior

    It creates statement

    INSERT INTO example (bytes) VALUES (0, 1, 2, 3, 4);
    

    which is invalid and doesn't work because each array element is treated like separate field.

    I've tried wrapping byte array into []interface{} and even [][]interface{} - it doesn't resolve the issue, dbq still treats arrays as lists of fields.

  • Postgres/pq bulk insert syntax issue

    Postgres/pq bulk insert syntax issue

    Dependencies:

    	"github.com/gin-gonic/gin"
    	_ "github.com/lib/pq"
    	"github.com/rocketlaunchr/dbq/v2"
    	"net/http"
    

    Code of issue:

    	psqlInfo := fmt.Sprintf("%s://%s:%s@%s:%s/%s?sslmode=%s",
    		"postgres", "postgres", "postgres", "localhost", "5432", "", "disable",
    	)
    
    	db, _ := sql.Open("postgres", psqlInfo)
    
    	type Row struct {
    		Username string
    		Password string
    	}
    
    	users := []interface{}{
    		dbq.Struct(Row{"John", "abc123"}),
    		dbq.Struct(Row{"Mark", "abc123"}),
    		dbq.Struct(Row{"David", "abc123"}),
    	}
    
    	stmt := dbq.INSERTStmt("accounts", []string{"username", "password"}, len(users))
    	_, err := dbq.E(ctx, db, stmt, nil, users) // <- always results in syntax error
    	if err != nil {
    		panic(err) // <- pq: syntax error at or near ","
    		return
    	}
    

    Output: pq: syntax error at or near ","

    Consistently results in a syntax error. If the cause is the above code, let me know 👍

  • todo

    todo

    • ~add example of how Ph function works~
    • ~add example to readme of how postunmarshaler works. It is currently a "hidden" feature.~
    • ~update godocs links to v2~
    • ~Note that v1 is deprecated and no updates will be done to it~
  • check performance difference

    check performance difference

    Can you benchmark and check the performance difference between the speed-up branch (which will definitely be merged to master) and return-slice-of-struct branch (which will probably go to v2).

    The difference is that when you provide a ConcreteStruct, the speed-up branch returns []interface{} containing *struct whilst return-slice-of-struct returns []*struct

    return-slice-of-struct uses fore reflect pkg and is not backward compatible.

  • Return slice of map string interface{}

    Return slice of map string interface{}

    • this fixes an existing bug (as per documentation)
    • unfortunately it may break existing applications (but that is only because they did a workaround instead of complaining about the bug with respect to documentation)
  • TODO:

    TODO:

    For JSON columns, give option to unmarshal to map[string]interface{} or custom object.

    v3 Relations

    1. If struct contains field which is struct (pointer or concrete), then fetch.
    2. If struct contains field which is slice of struct, then fetch from join table
    3. Ability to escape sql injection protection: "Dangerous"
Database Abstraction Layer (dbal) for Go. Support SQL builder and get result easily (now only support mysql)

godbal Database Abstraction Layer (dbal) for go (now only support mysql) Motivation I wanted a DBAL that No ORM、No Reflect、Concurrency Save, support S

Nov 17, 2022
A Go (golang) package that enhances the standard database/sql package by providing powerful data retrieval methods as well as DB-agnostic query building capabilities.

ozzo-dbx Summary Description Requirements Installation Supported Databases Getting Started Connecting to Database Executing Queries Binding Parameters

Dec 31, 2022
open source training courses about distributed database and distributed systemes
open source training courses about distributed database and distributed systemes

Welcome to learn Talent Plan Courses! Talent Plan is an open source training program initiated by PingCAP. It aims to create or combine some open sour

Jan 3, 2023
Bulk query SQLite database over the network

SQLiteQueryServer Bulk query SQLite database over the network. Way faster than SQLiteProxy!

May 20, 2022
LBADD: An experimental, distributed SQL database
LBADD: An experimental, distributed SQL database

LBADD Let's build a distributed database. LBADD is an experimental distributed SQL database, written in Go. The goal of this project is to build a dat

Nov 29, 2022
Mocking your SQL database in Go tests has never been easier.

copyist Mocking your SQL database in Go tests has never been easier. The copyist library automatically records low-level SQL calls made during your te

Dec 19, 2022
Additions to Go's database/sql for super fast performance and convenience. (fork of gocraft/dbr)

dbr (fork of gocraft/dbr) provides additions to Go's database/sql for super fast performance and convenience. Getting Started // create a connection (

Dec 31, 2022
Go library for accessing multi-host SQL database installations

hasql hasql provides simple and reliable way to access high-availability database setups with multiple hosts. Status hasql is production-ready and is

Dec 28, 2022
Document-oriented, embedded SQL database

Genji Document-oriented, embedded, SQL database Table of contents Table of contents Introduction Features Installation Usage Using Genji's API Using d

Jan 1, 2023
Database access layer for golang

grimoire ⚠️ Grimoire V2 is available as REL and Changeset package. Grimoire is a database access layer inspired by Ecto. It features a flexible query

Dec 21, 2022
Generate a Go ORM tailored to your database schema.
Generate a Go ORM tailored to your database schema.

SQLBoiler is a tool to generate a Go ORM tailored to your database schema. It is a "database-first" ORM as opposed to "code-first" (like gorm/gorp). T

Jan 9, 2023
Zero boilerplate database operations for Go
Zero boilerplate database operations for Go

(Now compatible with MySQL and PostgreSQL!) Everyone knows that performing simple DATABASE queries in Go takes numerous lines of code that is often re

Jan 2, 2023
subtraction operations and also parentheses to indicate order of operations

basic parsing expose a Calculate method that accepts a string of addition / subtraction operations and also parentheses to indicate order of operation

Feb 22, 2022
Distributed File Store Application Consist of API Server to handle file operations and command line tool to do operations

Filestore Distributed File Store Application Consist of API Server to handle file operations and command line tool to do operations (store named binar

Nov 7, 2021
Gin-boilerplate - This repository contains boilerplate code of a REST service using Gin (golang) framework.

Boilerplate REST service using Gin web framework (golang) Introduction This repository contains a boilerplate REST API service using Gin web framework

Apr 28, 2022
Gin-boilerplate - Gin boilerplate preconfigured with basic rest api features

Gin Boilerplate Build apis with gin faster with this template Features Validatio

Jun 24, 2022
Owl is a db manager platform,committed to standardizing the data, index in the database and operations to the database, to avoid risks and failures.

Owl is a db manager platform,committed to standardizing the data, index in the database and operations to the database, to avoid risks and failures. capabilities which owl provides include Process approval、sql Audit、sql execute and execute as crontab、data backup and recover .

Nov 9, 2022
Beerus-DB: a database operation framework, currently only supports Mysql, Use [go-sql-driver/mysql] to do database connection and basic operations

Beerus-DB · Beerus-DB is a database operation framework, currently only supports Mysql, Use [go-sql-driver/mysql] to do database connection and basic

Oct 29, 2022
Ekliptic - Primitives for cryptographic operations on the secp256k1 curve, with zero dependencies and excellent performance

Ekliptic This package provides primitives for cryptographic operations on the se

Sep 7, 2022
Simple project in Go to play around with some CRUD operations using a PostgreSQL database and pgx

Record Store November 2021 I started learning Go a few weeks ago and this is my first proper project using Go. I wanted to use it to get to grips with

Nov 26, 2021