blog

12020-10-17

SEAN K.H. LIAO

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.

package main

import (
        "database/sql"
)

type Result struct {
        Value1 string
        Value2 string
}

func ExampleSQL() {
        db, _ := sql.Open("", "")
        db.Exec(`CREATE TABLE mytable (key INTEGER, value1 TEXT, value2 TEXT)`)

        db.Exec(`INSERT INTO mytable VALUES ($1, $2, $3)`, 1, "hello", "world")
        db.Exec(`INSERT INTO mytable VALUES ($1, $2, $3)`, 2, "foo", "bar")
        db.Exec(`INSERT INTO mytable VALUES ($1, $2, $3)`, 3, "fizz", "buzz")

        rows, _ := db.Query(`SELECT value1, value2 FROM mytable WHERE key < $1`, 5)
        var rs []Result
        for rows.Next() {
                var r Result
                rows.Scan(&r.Value1, &r.Value2)
                rs = append(rs, r)
        }
}

squirrel

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

func ExampleSquirrel() {
        db, _ := sql.Open("", "")
        db.Exec(`CREATE TABLE mytable (key INTEGER, value1 TEXT, value2 TEXT)`)

        squirrel.Insert("mytable").
                PlaceholderFormat(squirrel.Dollar).
                Values(1, "hello", "world").
                Values(2, "foo", "bar").
                Values(3, "fizz", "buzz").
                RunWith(db).Exec()

        rows, _ := squirrel.Select("value1, value2").
                PlaceholderFormat(squirrel.Dollar).
                From("mytable").
                Where(squirrel.Lt{"key": 5}).
                RunWith(db).Query()
        var rs []Result
        for rows.Next() {
                var r Result
                rows.Scan(&r.Value1, &r.Value2)
                rs = append(rs, r)
        }
}

sqlx

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

func ExampleSqlx() {
        db, _ := sqlx.Connect("", "")
        db.MustExec(`CREATE TABLE mytable (key INTEGER, value1 TEXT, value2 TEXT)`)

        db.MustExec(`INSERT INTO mytable VALUES ($1, $2, $3)`, 1, "hello", "world")
        db.MustExec(`INSERT INTO mytable VALUES ($1, $2, $3)`, 2, "foo", "bar")
        db.MustExec(`INSERT INTO mytable VALUES ($1, $2, $3)`, 3, "fizz", "buzz")

        var rs []Result
        db.Select(&rs, `SELECT value1, value2 FROM mytable WHERE key < $1`, 5)

        // or
        rows, _ := db.Queryx(`SELECT value1, value2 FROM mytable WHERE key < $1`, 5)
        for rows.Next() {
                var r Result
                rows.StructScan(&r)
                rs = append(rs, r)
        }
}

combined

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

func ExampleCombined() {
        db, _ := sqlx.Connect("", "")
        db.MustExec(`CREATE TABLE mytable (key INTEGER, value1 TEXT, value2 TEXT)`)

        s, a, _ := squirrel.Insert("mytable").
                PlaceholderFormat(squirrel.Dollar).
                Values(1, "hello", "world").
                Values(2, "foo", "bar").
                Values(3, "fizz", "buzz").
                ToSql()
        db.MustExec(s, a...)

        s, a, _ = squirrel.Select("value1, value2").
                PlaceholderFormat(squirrel.Dollar).
                From("mytable").
                Where(squirrel.Lt{"key": 5}).
                ToSql()
        var rs []Result
        db.Select(&rs, s, a...)
}

other