Sequel - A Go <-> SQL mapping package (Status: ALPHA)

Sequel is similar to SQLx, but with the goal of automating even more of the common operations around Go <-> SQL interaction.

Why?

I wanted a very thin mapping between SQL and Go that provides:

1. SELECT into arbitrary structs.
2. Query parameters populated from arbitrary Go types - structs, slices, etc.
3. Normalised sequential placeholders across SQL dialects (?) (support for positional placeholders will hopefully come later).
4. Try to be as type safe as possible.

I did not want:

1. A query DSL - I already know SQL.
2. Migration support - there are much better external tools for this.

Tutorial / example

Open a DB connection:

db, err := sequel.Open("mysql", "root@/database")

Insert some users:

type dbUser struct {
	ID int            `db:",managed"`
    Created time.Time `db:",managed"`
	Name string
	Email string
}

users := []dbUser{
    {Name: "Moe", Email: "[email protected]"},
    {Name: "Larry", Email: "[email protected]"},
    {Name: "Curly", Email: "[email protected]"},
}
_, err = db.Insert("users", users)

Selecting uses a similar approach:

users := []dbUser{}
err = db.Select(&users, `
    SELECT ** FROM users WHERE id IN (
        SELECT user_id FROM group_members WHERE group_id = ?
    )
`, groupID)

Placeholders

Each placeholder symbol ? in a query string maps 1:1 to a corresponding argument in the Select() or Exec() call.

The placeholder ** will expand to the set of unmanaged fields in your data model. Managed fields are those managed by the database, such as auto-increment keys, fields with auto-update values, etc. See section below on "Dealing with schema changes" for why this placeholder is useful.

Arguments are expanded recursively. Structs map to a parentheses-enclosed, comma-separated list. Slices map to a comma-separated list.

Struct tag format

Struct fields may be tagged with db:"..." to control how Sequel maps fields. The tag has the following syntax:

db:"[<name>][,<option>,...]"

To omit a field from mapping use:

db:"-"

If a field name is not explicitly provided the lower-snake-case mapping of the Go field name will be used. eg. MyIDField -> my_id_field.

Insert

It accepts a list of rows (Insert(table, rows)), or a vararg sequence (Insert(table, row0, row1, row2)). Column names are reflected from the first row.

Upsert

Upsert() varargs have the same syntax as Insert(), however in addition it requires a list of columns to use as the unique constraint check.

Dealing with schema changes

For minimum disruption, best practice for schema changes (in general, not specifically with Sequel) is to write DDL that does not require corresponding DML changes. This means having sane default values for new columns, and the schema change should be applied prior to code deployment. For column removal, code should be modified and deployed prior to schema changes.

Some queries are problematic in the face of column additions, in particular the use of SELECT *. If an additional column exists in the schema but does not exist in your model, the result rows will fail to deserialise.

There are two options here.

1. Explicitly list columns in your query.
2. Use **. This automates the approach of explicitly listing column names.

Examples

A simple select with parameters populated from a struct

selector := struct{Name, Email string}{"Moe", "[email protected]"}
err := db.Select(&users, `SELECT * FROM users WHERE (name, email) = ?`, selector)

A complex multi-row select

For example, given a query like this:

SELECT * FROM users WHERE (name, email) IN
    ("Moe", "[email protected]"),
    ("Larry", "[email protected]"),
    ("Curly", "[email protected]")

Sequel allows the equivalent query with dynamic inputs to be expressed like so. First, with the input data:

// For the purposes of this example this is a static list, but in "real" code this would typically be the result
// of another query, or user-provided.
matches := []struct{Name, Email string}{
    {"Moe", "[email protected]"},
    {"Larry", "[email protected]"},
    {"Curly", "[email protected]"},
}

The Sequel query to match all rows with those columns is this:

err := db.Select(&users, `SELECT * FROM users WHERE (name, email) IN ?`, matches)

Which is equivalent to the following SQLx code:

placeholders := []string{}
args := []interface{}
for _, match := range matches {
    placeholders = append(placeholders, "?", "?")
    args = append(args, match.Name, match.Email)
}
err := db.Select(&users,
    ` SELECT * FROM users WHERE email IN (` + strings.Join(placeholders, ",") + `)`,
    args...,
)

Or manually expanded:

err := db.Select(&users, `SELECT * FROM users WHERE (name, email) IN (?, ?), (?, ?), (?, ?)`,
    matches[0].Name, matches[0].Email,
    matches[1].Name, matches[1].Email,
    matches[2].Name, matches[2].Email,
)