go sql qol

quality of life libraries when working with sql in go

SEAN K.H. LIAO

go sql qol

quality of life libraries when working with sql in go

sql in go

Databases, somehow we all settled on using SQL.

database/sql

We could just write SQL in strings, but having done that, I make way too many syntax errors.

 1package main
 2
 3import (
 4        "database/sql"
 5)
 6
 7type Result struct {
 8        Value1 string
 9        Value2 string
10}
11
12func ExampleSQL() {
13        db, _ := sql.Open("", "")
14        db.Exec(`CREATE TABLE mytable (key INTEGER, value1 TEXT, value2 TEXT)`)
15
16        db.Exec(`INSERT INTO mytable VALUES ($1, $2, $3)`, 1, "hello", "world")
17        db.Exec(`INSERT INTO mytable VALUES ($1, $2, $3)`, 2, "foo", "bar")
18        db.Exec(`INSERT INTO mytable VALUES ($1, $2, $3)`, 3, "fizz", "buzz")
19
20        rows, _ := db.Query(`SELECT value1, value2 FROM mytable WHERE key < $1`, 5)
21        var rs []Result
22        for rows.Next() {
23                var r Result
24                rows.Scan(&r.Value1, &r.Value2)
25                rs = append(rs, r)
26        }
27}

squirrel

squirrel is a SQL builder and it does just that, create the SQL string. Still need to scan out the values yourself

 1func ExampleSquirrel() {
 2        db, _ := sql.Open("", "")
 3        db.Exec(`CREATE TABLE mytable (key INTEGER, value1 TEXT, value2 TEXT)`)
 4
 5        squirrel.Insert("mytable").
 6                PlaceholderFormat(squirrel.Dollar).
 7                Values(1, "hello", "world").
 8                Values(2, "foo", "bar").
 9                Values(3, "fizz", "buzz").
10                RunWith(db).Exec()
11
12        rows, _ := squirrel.Select("value1, value2").
13                PlaceholderFormat(squirrel.Dollar).
14                From("mytable").
15                Where(squirrel.Lt{"key": 5}).
16                RunWith(db).Query()
17        var rs []Result
18        for rows.Next() {
19                var r Result
20                rows.Scan(&r.Value1, &r.Value2)
21                rs = append(rs, r)
22        }
23}

sqlx

sqlx wraps the standard db with some extra conveniences, like scanning into a slice or struct, and apparently named params(?)

 1func ExampleSqlx() {
 2        db, _ := sqlx.Connect("", "")
 3        db.MustExec(`CREATE TABLE mytable (key INTEGER, value1 TEXT, value2 TEXT)`)
 4
 5        db.MustExec(`INSERT INTO mytable VALUES ($1, $2, $3)`, 1, "hello", "world")
 6        db.MustExec(`INSERT INTO mytable VALUES ($1, $2, $3)`, 2, "foo", "bar")
 7        db.MustExec(`INSERT INTO mytable VALUES ($1, $2, $3)`, 3, "fizz", "buzz")
 8
 9        var rs []Result
10        db.Select(&rs, `SELECT value1, value2 FROM mytable WHERE key < $1`, 5)
11
12        // or
13        rows, _ := db.Queryx(`SELECT value1, value2 FROM mytable WHERE key < $1`, 5)
14        for rows.Next() {
15                var r Result
16                rows.StructScan(&r)
17                rs = append(rs, r)
18        }
19}

combined

seeing as squirrel and sqlx are orthogonal, you can of course combine them

 1func ExampleCombined() {
 2        db, _ := sqlx.Connect("", "")
 3        db.MustExec(`CREATE TABLE mytable (key INTEGER, value1 TEXT, value2 TEXT)`)
 4
 5        s, a, _ := squirrel.Insert("mytable").
 6                PlaceholderFormat(squirrel.Dollar).
 7                Values(1, "hello", "world").
 8                Values(2, "foo", "bar").
 9                Values(3, "fizz", "buzz").
10                ToSql()
11        db.MustExec(s, a...)
12
13        s, a, _ = squirrel.Select("value1, value2").
14                PlaceholderFormat(squirrel.Dollar).
15                From("mytable").
16                Where(squirrel.Lt{"key": 5}).
17                ToSql()
18        var rs []Result
19        db.Select(&rs, s, a...)
20}

other