Sq: A type-safe SQL query builder and struct mapper for Go

[bokwoon95 (bokwoon)]

bokwoon95/go-structured-query

Working with SQL directly (via database/sql) in Go is simple and straightforward. However database/sql itself has some flaws. Here are some reasons why you might want to use the sq query builder, which is a thin wrapper over database/sql, instead:

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. sq uses the type-safe definitions it generates from your tables.
  • 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.

• sq is a query builder, so it handles building queries. Check out what it can do.

Example 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.