SEANK.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