sq (Structured Query)

one-page documentation

sq is a type-safe data mapper and query builder for Go. Its concept is simple: you provide a callback function that maps a row to a struct, generics ensure that you get back a slice of structs at the end. Additionally, mentioning a column in the callback function automatically adds it to the SELECT clause so you don't even have to explicitly mention what columns you want to select: the act of mapping a column is the same as selecting it. This eliminates a source of errors where you have specify the columns twice (once in the query itself, once to the call to rows.Scan) and end up missing a column, getting the column order wrong or mistyping a column name.

Notable features:

• Works across SQLite, Postgres, MySQL and SQL Server. [more info]
• Each dialect has its own query builder, allowing you to use dialect-specific features. [more info]
• Declarative schema migrations. [more info]
• Supports arrays, enums, JSON and UUID. [more info]
• Query logging. [more info]

Installation

This package only supports Go 1.18 and above.

$ go get github.com/bokwoon95/sq
$ go install -tags=fts5 github.com/bokwoon95/sqddl@latest

Features

SELECT example (Raw SQL)

actors, err := sq.FetchAll(db, sq.
    Queryf("SELECT {*} FROM actor AS a WHERE a.actor_id IN ({})",
        []int{1, 2, 3, 4, 5},
    ).
    SetDialect(sq.DialectPostgres),
    func(row *sq.Row) (Actor, error) {
        actor := Actor{
            ActorID:     row.Int("a.actor_id"),
            FirstName:   row.String("a.first_name"),
            LastName:    row.String("a.last_name"),
            LastUpdate:  row.Time("a.last_update"),
        }
        return actor, nil
    },
)

SELECT example (Query Builder)

To use the query builder, you must first define your table structs.

a := sq.New[ACTOR]("a")
actors, err := sq.FetchAll(db, sq.
    From(a).
    Where(a.ACTOR_ID.In([]int{1, 2, 3, 4, 5})).
    SetDialect(sq.DialectPostgres),
    func(row *sq.Row) (Actor, error) {
        actor := Actor{
            ActorID:     row.IntField(a.ACTOR_ID),
            FirstName:   row.StringField(a.FIRST_NAME),
            LastName:    row.StringField(a.LAST_NAME),
            LastUpdate:  row.TimeField(a.LAST_UPDATE),
        }
        return actor, nil
    },
)

INSERT example (Raw SQL)

_, err := sq.Exec(db, sq.
    Queryf("INSERT INTO actor (actor_id, first_name, last_name) VALUES {}", sq.RowValues{
        {18, "DAN", "TORN"},
        {56, "DAN", "HARRIS"},
        {166, "DAN", "STREEP"},
    }).
    SetDialect(sq.DialectPostgres),
)

INSERT example (Query Builder)

To use the query builder, you must first define your table structs.

a := sq.New[ACTOR]("a")
_, err := sq.Exec(db, sq.
    InsertInto(a).
    Columns(a.ACTOR_ID, a.FIRST_NAME, a.LAST_NAME).
    Values(18, "DAN", "TORN").
    Values(56, "DAN", "HARRIS").
    Values(166, "DAN", "STREEP").
    SetDialect(sq.DialectPostgres),
)

For a more detailed overview, look at the Quickstart.

Contributing

See START_HERE.md.