Repository files navigation

Flexible SQL query builder for Go

sqlz (pronounced "sequelize") is an un-opinionated, un-obtrusive SQL query builder for Go projects, based onsqlx.

As opposed to other query builders, sqlz does not mean to bridge the gap between different SQL servers and implementations byproviding a unified interface. Instead, it aims to support an extended SQL syntax that may be implementation-specific. Forexample, if you wish to use PostgreSQL-specific features such as JSON operators and upsert statements, sqlz means to supportthese without caring if the underlying database backend really is PostgreSQL. In other words, sqlz builds whatever queriesyou want it to build.

sqlz is easy to integrate into existing code, as it does not require you to create your database connections through thesqlz API; in fact, it doesn't supply one. You can either use your existing*sql.DB connection or an*sqlx.DB connection,so you can start writing new queries with sqlz without having to modify any existing code.

sqlz leveragessqlx for easy loading of query results. Please make sure you are familiar withhow sqlx worksin order to understand how row scanning is performed. You may need to adddb struct tags to your Go structures.

sqlz provides a comfortable API for running queries in a transaction, and will automatically commit or rollback thetransaction as necessary.

goget-ugithub.com/ido50/sqlz

Once installed, you can import sqlz into your Go packages. To build and execute queries withsqlz, you need to pass the underlying*sql.DB or*sqlx.DB objects. If usingdatabase/sql,you'll need to tell sqlz the name of the driver (so that it knows which placeholders to usewhen building queries); if usinggithub.com/jmoiron/sqlx, this is not necessary.

package mainimport ("fmt""database/sql""github.com/ido50/sqlz"    _"sql driver of choice")funcmain() {driver:="postgres"db,err:=sql.Open(driver,"dsn")iferr!=nil {panic(err)    }// find one row in the database and load it// into a struct variablevarrowsomeStructerr=sqlz.New(db,driver).// if using sqlx: sqlz.Newx(dbx)Select("*").From("some-table").Where(sqlz.Eq("id",1)).GetRow(&row)iferr!=nil {panic(err)    }fmt.Printf("%+v\n",row)}

varrowsomeStructerr=sqlz.New(db,driver).Select("*").From("some-table").Where(Sqlz.Eq("id",1)).GetRow(&row)

Generated SQL (disregarding placeholders):

SELECT*FROM some-tableWHERE id=1

varrows []struct{maxValint64sumCountuint64}err=sqlz.New(db,driver).Select("MAX(t.col1) maxVal","SUM(t.col2) sumCount").From("some-table t").LeftJoin("other-table o",sqlz.Eq("o.id",sqlz.Indirect("t.id"))).// there's also RightJoin, InnerJoin, FullJoinGroupBy("t.col3","t.col4").Having(sqlz.Gte("maxVal",3)).OrderBy(sqlz.Desc("maxVal"),sqlz.Asc("sumCount")).Limit(5).Offset(10).Where(sqlz.Or(sqlz.Eq("t.col3",5),sqlz.IsNotNull("t.col4"))).GetAll(&rows)

Generated SQL (disregarding placeholders):

SELECTMAX(t.col1) maxVal,SUM(t.col2) sumCountFROM some-table tLEFT JOIN other-table oONo.id=t.idWHEREt.col3=5ORt.col4IS NOT NULLGROUP BYt.col3,t.col4HAVING maxVal>3ORDER BY maxValDESC, sumCountASCLIMIT5      OFFSET10,20

When paginating results, sqlz provides a nice feature to also calculate thetotal number of results matching the query, regardless of limiting and offsets:

varrows []struct{maxValint64sumCountuint64}query:=sqlz.New(db,driver).Select("MAX(t.col1) maxVal","SUM(t.col2) sumCount").// rest of the query as beforecount,err:=query.GetCount()// returns total number of results available, regardless of limits and offsetserr=query.GetAll(&rows)// returns actual results according to limits and offsets

res,err:=sqlz.New(db,driver).InsertInto("table").Columns("id","name").Values(1,"My Name").Exec()// res is sql.Result

Generated SQL:

INSERT INTO table (id, name)VALUES (?, ?)

res,err:=sqlz.New(db,driver).InsertInto("table").ValueMap(map[string]interface{}{"id":1,"name":"My Name",    }).Exec()

Generates the same SQL as forsimple inserts.

varidint64err:=sqlz.New(db,driver).InsertInto("table").Columns("name").Values("My Name").Returning("id").GetRow(&id)

res,err:=sqlz.New(db,driver).Update("table").Set("col1","some-string").SetMap(map[string]interface{}{"col2":true,"col3":5,    }).Where(sqlz.Eq("id",3)).Exec()

Generated SQL:

UPDATE tableSET col1= ?, col2= ?, col3= ?WHERE id= ?

Updates support the RETURNING clause just like inserts.

res,err:=sqlz.New(db,driver).DeleteFrom("table").Where(sqlz.Eq("id",3)).Exec()

Generated SQL:

DELETEFROM tableWHERE id= ?

sqlz makes it easy to run multiple queries in a transaction, and will automatically rollback or commit as necessary:

sqlz.New(db,driver).Transactional(func(tx*sqlz.Tx)error {varidint64err:=tx.InsertInto("table").Columns("name").Values("some guy").Returning("id").GetRow(&id)iferr!=nil {returnfmt.Errorf("failed inserting row: %w",err)        }_,err=tx.Update("other-table").Set("some-col",4).Exec()iferr!=nil {returnfmt.Errorf("failed updating row: %w",err)        }returnnil    })

If the function provided to theTransactional method returns an error, thetransaction will be rolled back. Otherwise, it will be committed.

If you need to compare columns, call database functions, modify columns based on their(or other's) existing values, and any place you need strings to be used as-is and notreplaced with placeholders, use the Indirect function:

• To compare two columns in a WHERE clause, usesqlz.Eq("column-one", sqlz.Indirect("column-two"))
• To increase a column in a SET clause, usesqlz.Set("int-column", sqlz.Indirect("int-column + 1"))
• To set a columm using a database function (e.g.LOCALTIMESTAMP), usesqlz.Set("datetime", sqlz.Indirect("LOCALTIMESTAMP"))

The only non-standard library package used isjmoiron/sqlx.The test suite, however, usesDATA-DOG/sqlmock.

sqlz was inspired bygocraft/dbr.