Original post

Here are some reasons for using the sq query builder:

database/sql queries are susceptible to typos and tedious to refactor if you change a column name or type in a migration.

  • sq generates struct definitions from your tables so that you can reference table columns in a type-safe way.
    • If a migration changes a column name or type, it would cause a compile time error in your application until you change all relevant sections in your code.

database/sql queries involve a lot of boilerplate especially when it comes to scanning columns into struct fields.

  • sq lets you declaratively map table columns to struct fields in a mapper function and reuse it in queries.
    • yes, just like sqlx. Except sqlx involves writing string-based struct annotations, which are still susceptible to typos. Furthermore sqlx still requires you to write out the columns to SELECT manually, the only thing it automates is the mapping of selected columns to struct fields.
    • sq’s mapper functions serve the purpose of both SELECTing the columns and mapping them to struct fields, which is like sqlx on steroids.
    • more info

database/sql (and sqlx) do not handle dynamically building SQL queries.

SELECT

-- SQL
SELECT u.user_id, u.name, u.email, u.created_at
FROM public.users AS u
WHERE u.name = 'Bob';

// Go
u := tables.USERS().As("u") // table is code generated
var user User
var users []User
err := sq.
    From(u).
    Where(u.NAME.EqString("Bob")).
    Selectx(func(row *sq.Row) {
        user.UserID = row.Int(u.USER_ID)
        user.Name = row.String(u.NAME)
        user.Email = row.String(u.EMAIL)
        user.CreatedAt = row.Time(u.CREATED_AT)
    }, func() {
        users = append(users, user)
    }).
    Fetch(db)
if err != nil {
    // handle error
}

INSERT

-- SQL
INSERT INTO public.users (name, email)
VALUES ('Bob', 'bob@email.com'), ('Alice', 'alice@email.com'), ('Eve', 'eve@email.com');

// Go
u := tables.USERS().As("u") // table is code generated
users := []User{
    {Name: "Bob",   Email: "bob@email.com"},
    {Name: "Alice", Email: "alice@email.com"},
    {Name: "Eve  ", Email: "eve@email.com"},
}
rowsAffected, err := sq.
    InsertInto(u).
    Valuesx(func(col *sq.Column) {
        for _, user := range users {
            col.SetString(u.NAME, user.Name)
            col.SetString(u.EMAIL, user.Email)
        }
    }).
    Exec(db, sq.ErowsAffected)
if err != nil {
    // handle error
}

UPDATE

-- SQL
UPDATE public.users
SET name = 'Bob', password = 'qwertyuiop'
WHERE email = 'bob@email.com';

// Go
u := tables.USERS().As("u") // table is code generated
user := User{
    Name:     "Bob",
    Email:    "bob@email.com",
    Password: "qwertyuiop",
}
rowsAffected, err := sq.
    Update(u).
    Setx(func(col *sq.Column) {
        col.SetString(u.NAME, user.Name)
        col.SetString(u.PASSWORD, user.Password)
    }).
    Where(u.EMAIL.EqString(user.Email)).
    Exec(db, sq.ErowsAffected)
if err != nil {
    // handle error
}

DELETE

-- SQL
DELETE FROM public.users AS u
USING public.user_roles AS ur
JOIN public.user_roles_students AS urs ON urs.user_role_id = ur.user_role_id
WHERE u.user_id = ur.user_id AND urs.team_id = 15;

// Go
u   := tables.USERS().As("u")                 // tables are code generated
ur  := tables.USER_ROLES().As("ur")           // tables are code generated
urs := tables.USER_ROLES_STUDENTS().As("urs") // tables are code generated
rowsAffected, err := sq.
    DeleteFrom(u).
    Using(ur).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Where(
        u.USER_ID.Eq(ur.USER_ID),
        urs.TEAM_ID.EqInt(15),
    ).
    Exec(db, sq.ErowsAffected)
if err != nil {
    // handle error
}

For more information, check out the Basics.

For a list of example queries, check out Query Building.