Simple SQL extensions for Go

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

You might also like...
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

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

💥 A lightweight DSL & ORM which helps you to write SQL in Go.
💥 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.

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

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

GraphJin - Build APIs in 5 minutes with GraphQL. An instant GraphQL to SQL compiler.
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

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.

Analyzer: helps uncover bugs by reporting a diagnostic for mistakes of *sql.Rows usage.

sqlrows sqlrows is a static code analyzer which helps uncover bugs by reporting a diagnostic for mistakes of sql.Rows usage. Install You can get sqlro

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

Comments
  • 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
  • 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
  • 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
Releases(v1.1.2)
Owner
travel audience
Demand Side Platform built specifically for the travel industry
travel audience
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 455 Sep 22, 2022
Squat is an application that provides simple SQL data generation functionality.

Squat Squat is an application that provides simple SQL data generation functionality. It generates synthetic SQL data based on the table definition, t

Mateusz Urbanek 9 Sep 22, 2022
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 650 Sep 26, 2022
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.4k Sep 26, 2022
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

徐佳军 53 Jan 23, 2022
SQL builder and query library for golang

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

Doug Martin 1.7k Sep 22, 2022
SQL query builder for Go

GoSQL Query builder with some handy utility functions. Documentation For full documentation see the pkg.go.dev or GitBook. Examples // Open database a

Travis Harmon 26 Sep 26, 2022
Type safe SQL builder with code generation and automatic query result data mapping

Jet Jet is a complete solution for efficient and high performance database access, consisting of type-safe SQL builder with code generation and automa

null 795 Sep 26, 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

Ozzo Framework 578 Sep 29, 2022
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 23 Sep 26, 2022