Simple SQL extensions for Go

Related tags

cmt-team realtime-team
Overview

Some simple SQL extensions for Go

GoDoc CircleCI

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.

UPDATE (July 2020): This library is still actively maintained. Contributions are welcome.

Goals

The primary goal of go-sx is to eliminate boilerplate code. Specifically, go-sx attempts to address the following pain points:

  1. Transactions are clumsy. It would be nice to have a simple function to run a callback in a transaction.
  2. Error handling is clumsy. It would be nice to have errors within a transaction automatically exit the transaction and trigger a rollback. (This is nearly always what we want to do.)
  3. Scanning multiple columns is clumsy. It would be nice to have a simple way to scan into multiple struct fields at once.
  4. Constructing queries is clumsy, especially when there are a lot of columns.
  5. Iterating over result sets is clumsy.

Non-goals

These are considered to be out of scope:

  1. Be an ORM.
  2. Write your queries for you.
  3. Suggest that we need 1:1 relationship between struct types and tables.
  4. Maintain database schemas.
  5. Abstract away differences between SQL dialects.
  6. Automatic type-manipulation.
  7. Magic.

Pain point #1: Transactions are clumsy.

go-sx provides a function Do to run a transaction in a callback, automatically committing on success or rolling back on failure.

Here is some simple code to run two queries in a transaction. The second query returns two values, which are read into variables x and y.

tx, err := db.Begin()
if err != nil {
    return err
}
if _, err := tx.Exec(query0); err != nil {
    tx.Rollback()
    return err
}
if err := tx.QueryRow(query1).Scan(&x, &y); err != nil {
    tx.Rollback()
    return err
}
if err := tx.Commit(); err != nil {
    return err
}

Using the Do function, we put the business logic into a callback function and have go-sx take care of the transaction logic.

The sx.Tx object provided to the callback is the sql.Tx transaction object, extended with a few methods. If we call tx.Fail(), then the transaction is immediately aborted and rolled back.

err := sx.Do(db, func (tx *sx.Tx) {
    if _, err := tx.Exec(query0); err != nil {
        tx.Fail(err)
    }
    if err := tx.QueryRow(query1).Scan(&x, &y); err != nil {
        tx.Fail(err)
    }
})

Under the hood, tx.Fail() generates a panic which is recovered by Do.

Pain point #2: Error handling is clumsy.

go-sx provides a collection of Must*** methods which may be used inside of the callback to Do. Any error encountered while in a Must*** method causes the transaction to be aborted and rolled back.

Here is the code above, rewritten to use Do's error handling. It's simple and readable.

err := sx.Do(db, func (tx *sx.Tx) {
    tx.MustExec(query0)
    tx.MustQueryRow(query1).MustScan(&x, &y)
})

Pain point #3: Scanning multiple columns is clumsy.

go-sx provides an Addrs function, which takes a struct and returns a slice of pointers to the elements. So instead of:

row.Scan(&a.Width, &a.Height, &a.Depth)

We can write:

row.Scan(sx.Addrs(&a)...)

Or better yet, let go-sx handle the errors:

row.MustScan(sx.Addrs(&a)...)

This is such a common pattern that we provide a shortcut to do this all in one step:

row.MustScans(&a)

Pain point #4: Constructing queries is clumsy.

We would like go-sx to be able to construct some common queries for us. To this end, we define a simple way to match struct fields with database columns, and then provide some helper functions that use this matching to construct queries.

By default, all exported struct fields match database columns whose name is the the field name snake_cased. The default can be overridden by explicitly tagging fields, much like what is done with the standard json encoder. Note that we don't care about the name of the table at this point.

Here is a struct that can be used to scan columns violin, viola, cello and contrabass.

type orchestra struct {
    Violin string
    Viola  string
    Cello  string
    Bass   string `sx:"contrabass"`
}

We can use the helper function SelectQuery to construct a simple query. Then we can add the WHERE clause that we need and scan the result set into our struct.

var spo orchestra

wantID := 123
query := sx.SelectQuery("symphony", &spo) + " WHERE id=?"  // SELECT violin,viola,cello,contrabass FROM symphony WHERE id=?
tx.MustQueryRow(query, wantID).MustScans(&spo)

Note that a struct need not follow the database schema exactly. It's entirely possible to have various structs mapped to different columns of the same table, or even one struct that maps to a query on joined tables. On the other hand, it's essential that the columns in the query match the fields of the struct, and go-sx guarantees this, as we'll see below.

In some cases it's useful to have a struct that is used for both selects and inserts, with some of the fields being used just for selects. This can be accomplished with the "readonly" tag.

type orchestra1 struct {
    Violin string `sx:",readonly"`
    Viola  string
    Cello  string
    Bass   string `sx:"contrabass"`
}

It's also useful in some cases to have a struct field that is ignored by go-sx. This can be accomplished with the "-" tag.

type orchestra2 struct {
    Violin string `sx:",readonly"`
    Viola  string `sx:"-"`
    Cello  string
    Bass   string `sx:"contrabass"`
}

We can construct insert queries in a similar manner. Violin is read-only and Viola is ignored, so we only need to provide values for Cello and Bass. (If you need Postgres-style $n placeholders, see sx.SetNumberedPlaceholders().)

spo := orchestra2{Cello: "Strad", Bass: "Cecilio"}

query := sx.InsertQuery("symphony", &spo)  // INSERT INTO symphony (cello,contrabass) VALUES (?,?)
tx.MustExec(query, sx.Values(&spo)...)

We can contruct update queries this way too, and there is also an option to skip fields whose values are the zero values. (The update structs support pointer fields, making this skip option rather useful.)

spoChanges := orchestra2{Bass: "Strad"}

wantID := 123
query, values := sx.UpdateQuery("symphony", &spoChanges) + " WHERE id=?"  // UPDATE symphony SET contrabass=? WHERE id=?
tx.MustExec(query, append(values, wantID)...)

It is entirely possible to construct all of these queries by hand, and you're all welcome to do so. Using the query generators, however, ensures that the fields match correctly, something that is particularly useful with a large number of columns.

Pain point #5: Iterating over result sets is clumsy.

go-sx provides an iterator called Each which runs a callback function on each row of a result set. Using the iterator simplifies this code:

var orchestras []orchestra

query := "SELECT violin,viola,cello,contrabass FROM symphony ORDER BY viola"  // Or we could use sx.SelectQuery()
rows := tx.MustQuery(query)
defer rows.Close()
for rows.Next() {
    var o orchestra
    rows.MustScans(&o)
    orchestras = append(orchestras, o)
}
if err := rows.Err(); err != nil {
    tx.Fail(err)
}

To this:

var orchestras []orchestra

query := "SELECT violin,viola,cello,contrabass FROM symphony ORDER BY viola"
tx.MustQuery(query).Each(func (r *sx.Rows) {
    var o orchestra
    r.MustScans(&o)
    orchestras = append(orchestras, o)
})

Contributing

Contributions are welcome! Read the Contributing Guide for more information.

Licensing

This project is licensed under the MIT License - see the LICENSE file for details

Issues
  • api improve suggestion

    api improve suggestion

    Actual API

    spo := orchestra2{Cello: "Strad", Bass: "Cecilio"}
    query := sx.InsertQuery("symphony", &spo)  // INSERT INTO symphony (cello,contrabass) VALUES (?,?)
    tx.MustExec(query, sx.Values(&spo)...)
    

    Expected API

    spo := orchestra2{Cello: "Strad", Bass: "Cecilio"}
    tx.MustInsert("symphony", &spo)
    

    And the same for update api.

    opened by wongoo 4
  • Panic: Concurrent map read and map write

    Panic: Concurrent map read and map write

    Hi, great project, but I'm facing a problem.

    Expected Behaviour

    I should be able to run the code without any panic's about concurrency.

    Actual Behaviour

    Sometimes I got a panic from a concurrent map read and map write.

    Steps to Reproduce

    Run this test with go test -race https://github.com/crossworth/go-sx/blob/1c47103bc8c9989002193fe90c37571d0ba24167/concurrent_map_read_write_bug_test.go

    package sx
    
    import (
    	"fmt"
    	"testing"
    )
    
    func Test_matchingOf(t *testing.T) {
    	for i := 0; i < 1000; i++ {
    		t.Run(fmt.Sprintf("test_%d", i), func(t *testing.T) {
    			t.Parallel()
    			type aType struct {
    				Test string
    			}
    
    			var a aType
    			matchingOf(&a)
    		})
    	}
    }
    

    (is this the right way to test concurrency issues?)

    Optional additional info:

    Platform and Version

    go version go1.16.4 windows/amd64

    but the problem happens on linux/amd64 and darwin/amd64 as well.

    Sample Code that illustrates the problem

    https://github.com/crossworth/go-sx/blob/1c47103bc8c9989002193fe90c37571d0ba24167/concurrent_map_read_write_bug_test.go

    Logs taken while reproducing problem

    fatal error: concurrent map read and map write
    
    goroutine 63 [running]:
    runtime.throw(0x11293c2, 0x21)
    	C:/Program Files/Go/src/runtime/panic.go:1117 +0x79 fp=0xc003e41830 sp=0xc003e41800 pc=0x2cbf19
    runtime.mapaccess2(0xe81160, 0xc0001a2330, 0xc003e41998, 0x35bd420, 0xf67160)
    	C:/Program Files/Go/src/runtime/map.go:469 +0x26c fp=0xc003e41870 sp=0xc003e41830 pc=0x2a062c
    github.com/travelaudience/go-sx.matchingOf(0xe6f2c0, 0xc0029c8420, 0x20)
    	C:/Users/Pedro/code/project-name/vendor/github.com/travelaudience/go-sx/matching.go:62 +0x1d7 fp=0xc003e41a20 sp=0xc003e41870 pc=0x987d57
    github.com/travelaudience/go-sx.Addrs(0xe6f2c0, 0xc0029c8420, 0xc003e41b20, 0x29f01f, 0x28)
    	C:/Users/Pedro/code/project-name/vendor/github.com/travelaudience/go-sx/helpers.go:227 +0x54 fp=0xc003e41ae0 sp=0xc003e41a20 pc=0x987874
    github.com/travelaudience/go-sx.(*Rows).MustScans(0xc0005101d0, 0xe6f2c0, 0xc0029c8420)
    	C:/Users/Pedro/code/project-name/vendor/github.com/travelaudience/go-sx/tx.go:187 +0x45 fp=0xc003e41b30 sp=0xc003e41ae0 pc=0x988a65
    ...
    

    I was able to fix the problem using a mutex: https://github.com/crossworth/go-sx/commit/c17a11f021b45740238d429b24231191ca612e7e

    Let me know if I should create a PR from the commit above.

    opened by crossworth 1
  • Add godoc reference

    Add godoc reference

    opened by ders 0
  • Circleci copy

    Circleci copy

    What this PR does / why we need it:

    Special notes for your reviewer:

    If applicable:

    • [ ] this PR contains documentation
    • [ ] this PR contains unit tests
    • [ ] this PR has been tested for backwards compatibility
    opened by varditn 0
  • Add some examples

    Add some examples

    • Refactors tests into their own package.
    • Adds some examples for the helper functions.
    opened by ders 0
  • Add full example

    Add full example

    Adds a full example, showing how to use Do and Each with callbacks.

    opened by ders 0
  • Improved documentation

    Improved documentation

    Some minor edits to make the documentation clearer.

    opened by ders 0
  • Minor documentation fix

    Minor documentation fix

    What this PR does / why we need it:

    • Fixes minor documentation points in README

    Special notes for your reviewer:

    If applicable:

    • [x] this PR contains documentation
    • [ ] this PR contains unit tests
    • [ ] this PR has been tested for backwards compatibility
    opened by svetha-cvl 0
  • Go 1.13

    Go 1.13

    Upgrades to Go 1.13.

    opened by ders 0
Releases(v1.1.1)
Owner
travel audience
Demand Side Platform built specifically for the travel industry
travel audience
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
golang orm and sql builder

gosql gosql is a easy ORM library for Golang. Style: var userList []UserModel err := db.FetchAll(&userList, gosql.Columns("id","name"), gosql.

RushTeam 146 Sep 9, 2021
SQL builder and query library for golang

__ _ ___ __ _ _ _ / _` |/ _ \ / _` | | | | | (_| | (_) | (_| | |_| | \__, |\___/ \__, |\__,_| |___/ |_| goqu is an expressive SQL bu

Doug Martin 1.3k Sep 13, 2021
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
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
Fluent SQL generation for golang

sqrl - fat-free version of squirrel - fluent SQL generator for Go Non thread safe fork of squirrel. The same handy fluffy helper, but with extra lette

Ivan Kirichenko 230 Sep 6, 2021
Fast SQL query builder for Go

sqlf A fast SQL query builder for Go. sqlf statement builder provides a way to: Combine SQL statements from fragments of raw SQL and arguments that ma

Vlad Glushchuk 22 Sep 5, 2021
Query git repositories with SQL. Generate reports, perform status checks, analyze codebases. 🔍 📊

askgit askgit is a command-line tool for running SQL queries on git repositories. It's meant for ad-hoc querying of git repositories on disk through a

Augmentable 2.5k Sep 5, 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
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
Fluent SQL generation for golang

Squirrel is "complete". Bug fixes will still be merged (slowly). Bug reports are welcome, but I will not necessarily respond to them. If another fork

null 4.2k Sep 14, 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
gosq is a parsing engine for a simplicity-focused, template-based SQL query builder for Go.

gosq is a parsing engine for a simplicity-focused, template-based SQL query builder for Go.

Sang-gon Lee 29 Sep 5, 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
Write your SQL queries in raw files with all benefits of modern IDEs, use them in an easy way inside your application with all the profit of compile time constants

About qry is a general purpose library for storing your raw database queries in .sql files with all benefits of modern IDEs, instead of strings and co

Sergey Treinis 20 Feb 3, 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 Golang library for using SQL.

dotsql A Golang library for using SQL. It is not an ORM, it is not a query builder. Dotsql is a library that helps you keep sql files in one place and

Gustavo Chaín 598 Sep 4, 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
Golang Sequel ORM that support Enum, JSON, Spatial and many more

sqlike A golang SQL ORM which anti toxic query and focus on latest features. Installation go get github.com/si3nloong/sqlike Fully compatible with nat

SianLoong 14 Sep 6, 2021