Sequel - A Go <-> SQL mapping package
Sequel is similar to SQLx, but with the goal of automating even more of the common operations around Go <-> SQL interaction.
I wanted a very thin mapping between SQL and Go that provides:
SELECT
into arbitrary struct
s.?
) (support for positional placeholders will hopefully come later).I did not want:
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)
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.
Value | Placeholder | Corresponding expansion |
---|---|---|
struct{A, B, C string}{"A", "B", "C"} |
? |
(?, ?, ?) |
[]string{"A", "B"} |
? |
?, ? |
[]struct{A, B string}{{"A", "B"}, {"C", "D"}} |
? |
(?, ?), (?, ?) |
struct{A, B, C string}{"A", "B", "C"} |
** |
a, b, c |
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
.
Tag option | Meaning |
---|---|
managed |
Field is managed by the database. This informs Insert() which fields should not be propagated. |
pk |
Field is the primary key. pk fields will be set after Insert() . Auto-increment pk fields should also be tagged as managed . |
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()
varargs have the same syntax as Insert()
, however in addition it requires a list of
columns to use as the unique constraint check.
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.
**
. This automates the approach of explicitly listing column names.selector := struct{Name, Email string}{"Moe", "[email protected]"}
err := db.Select(&users, `SELECT * FROM users WHERE (name, email) = ?`, selector)
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,
)