Zero boilerplate database operations for Go

Overview

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:[email protected](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:[email protected](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:[email protected](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.

Issues
  • 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!

    opened by frederikhors 29
  • - add concurrent post unmarshal feature

    - add concurrent post unmarshal feature

    requires testing @propersam

    opened by rocketlaunchr-cto 8
  • 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.

    opened by Rirush 8
  • Accept structs

    Accept structs

    opened by rocketlaunchr-cto 2
  • -  unexport interfaces

    - unexport interfaces

    @propersam Can you do a basic test of this.

    opened by rocketlaunchr-cto 2
  • 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~
    opened by pjebs 2
  • - bug fix: make compatible with github.com/rocketlaunchr/mysql-go

    - bug fix: make compatible with github.com/rocketlaunchr/mysql-go

    @propersam Can you test this.

    opened by rocketlaunchr-cto 1
  • Add tx and retry

    Add tx and retry

    https://github.com/golang/go/issues/37995

    opened by rocketlaunchr-cto 1
  • Speed up

    Speed up

    Can you do a code-review and basic test. No need for unit tests.

    opened by rocketlaunchr-cto 0
  • 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"
    opened by pjebs 6
Releases(v1.1.0)
Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server

About xo xo is a command-line tool to generate Go code based on a database schema or a custom query. xo works by using database metadata and SQL intro

XO 2.9k Sep 15, 2021
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

CockroachDB 771 Aug 23, 2021
Type safe SQL query builder and struct mapper for Go

sq (Structured Query) ?? ?? sq is a code-generated, type safe query builder and struct mapper for Go. ?? ?? Documentation • Reference • Examples This

null 120 Sep 5, 2021
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

Genji 732 Sep 7, 2021
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

null 316 Sep 8, 2021
igor is an abstraction layer for PostgreSQL with a gorm like syntax.

igor igor is an abstraction layer for PostgreSQL, written in Go. Igor syntax is (almost) compatible with GORM. When to use igor You should use igor wh

Paolo Galeone 84 Aug 2, 2021
Simple SQL extensions for Go

go-sx provides some extensions to the standard library database/sql package. It is designed for those who wish to use the full power of SQL without a heavy abstraction layer.

travel audience 178 Aug 24, 2021
Go database query builder library for PostgreSQL

buildsqlx Go Database query builder library Installation Selects, Ordering, Limit & Offset GroupBy / Having Where, AndWhere, OrWhere clauses WhereIn /

Arthur Kushman 38 Sep 2, 2021
GraphJin - Build APIs in 5 minutes with GraphQL. An instant GraphQL to SQL compiler.

GraphJin - Build APIs in 5 minutes GraphJin gives you a high performance GraphQL API without you having to write any code. GraphQL is automagically co

Vikram Rangnekar 1.5k Sep 14, 2021
💥 A lightweight DSL & ORM which helps you to write SQL in Go.

sqlingo is a SQL DSL (a.k.a. SQL Builder or ORM) library in Go. It generates code from the database and lets you write SQL queries in an elegant way.

Qishuai Liu 155 Aug 29, 2021
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 (

Free and open source software developed at Mail.Ru 149 Aug 28, 2021
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

Yandex 84 Sep 7, 2021
A Go library for collecting sql.DBStats in Prometheus format

sqlstats A Go library for collecting sql.DBStats and exporting them in Prometheus format. A sql.DB object represents a pool of zero or more underlying

Daniel Middlecote 128 Aug 8, 2021
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

徐佳军 52 Apr 22, 2021
An early PostgreSQL implementation in Go

gosql An early PostgreSQL implementation in Go. Example $ git clone [email protected]:eatonphil/gosql $ cd gosql $ go run cmd/main.go Welcome to gosql. #

Phil Eaton 526 Sep 14, 2021
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

PingCAP 6.5k Sep 6, 2021
Go fearless SQL. Sqlvet performs static analysis on raw SQL queries in your Go code base.

Sqlvet Sqlvet performs static analysis on raw SQL queries in your Go code base to surface potential runtime errors at build time. Feature highlights:

QP Hou 427 Sep 6, 2021
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

Tom Arrell 373 Aug 4, 2021
a golang library for sql builder

Gendry gendry is a Go library that helps you operate database. Based on go-sql-driver/mysql, it provides a series of simple but useful tools to prepar

DiDi 1.3k Sep 6, 2021