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.19 and above.

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

Features

• IN
  • In Slice - a IN (1, 2, 3)
  • In RowValues - (a, b, c) IN ((1, 2, 3), (4, 5, 6), (7, 8, 9))
  • In Subquery - (a, b) IN (SELECT a, b FROM tbl WHERE condition)
• CASE
  • Predicate Case - CASE WHEN a THEN b WHEN c THEN d ELSE e END
  • Simple case - CASE expr WHEN a THEN b WHEN c THEN d ELSE e END
• EXISTS
  • Where Exists
  • Where Not Exists
  • Select Exists
• Subqueries
• WITH (Common Table Expressions)
• Aggregate functions
• Window functions
• UNION, INTERSECT, EXCEPT
• INSERT from SELECT
• RETURNING
  • SQLite RETURNING
  • Postgres RETURNING
• LastInsertId
  • SQLite LastInsertId
  • MySQL LastInsertId
• Insert ignore duplicates
  • SQLite Insert ignore duplicates
  • Postgres Insert ignore duplicates
  • MySQL Insert ignore duplicates
  • SQL Server Insert ignore duplicates
• Upsert
  • SQLite Upsert
  • Postgres Upsert
  • MySQL Upsert
  • SQL Server Upsert
• Update with Join
  • SQLite Update with Join
  • Postgres Update with Join
  • MySQL Update with Join
  • SQL Server Update with Join
• Delete with Join
  • SQLite Delete with Join
  • Postgres Delete with Join
  • MySQL Delete with Join
  • SQL Server Delete with Join
• Bulk Update
  • SQLite Bulk Update
  • Postgres Bulk Update
  • MySQL Bulk Update
  • SQL Server Bulk Update

SELECT example (Raw SQL)

db, err := sql.Open("postgres", "postgres://username:password@localhost:5432/sakila?sslmode=disable")

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 {
        return 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"),
        }
    },
)

SELECT example (Query Builder)

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

type ACTOR struct {
    sq.TableStruct
    ACTOR_ID    sq.NumberField
    FIRST_NAME  sq.StringField
    LAST_NAME   sq.StringField
    LAST_UPDATE sq.TimeField
}

db, err := sql.Open("postgres", "postgres://username:password@localhost:5432/sakila?sslmode=disable")

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 {
        return 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),
        }
    },
)

INSERT example (Raw SQL)

db, err := sql.Open("postgres", "postgres://username:password@localhost:5432/sakila?sslmode=disable")

_, 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.

type ACTOR struct {
    sq.TableStruct
    ACTOR_ID    sq.NumberField
    FIRST_NAME  sq.StringField
    LAST_NAME   sq.StringField
    LAST_UPDATE sq.TimeField
}

db, err := sql.Open("postgres", "postgres://username:password@localhost:5432/sakila?sslmode=disable")

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.

Project Status

sq is done for my use case (hence it may seem inactive, but it's just complete). At this point I'm just waiting for people to ask questions or file feature requests under discussions.

Contributing

See START_HERE.md.