Basics: Tables, fields and predicates

Overview
Comparison Operator Reference
Query Builder Reference

Overview

contents

In sq, there are three entities that you will be interacting with the most: a table, a field and a predicate.

A Table is: anything that you can SELECT FROM or JOIN.
```
users := tables.USERS()
// FROM public.users
From(users)
//   └───┘
//   Table

// JOIN (SELECT users.name FROM users) AS subquery ON 1 = 1
selectQuery := Select(users.NAME).From(users).As("subquery")
Join(selectQuery, Int(1).EqInt(1))
//   └─────────┘
//      Table
```
- Tables can be aliased
```
u := tables.USERS().As("u")
// FROM public.users AS u
From(u)
```
- There are two specialisations of Table: BaseTable and Query.
  - A BaseTable is a table that actually exists in the database, and not some subquery or common table expression. These are code generated, so you don't have to worry about creating them.
  - A Query is an instance of the SELECT, INSERT, UPDATE or DELETE query builder.

A Field is: any SQL expression that can be present in the SELECT clause. This is often a Table column, but it can also be a literal value or an expression made up of other expressions.

users := tables.USERS()
// SELECT users.user_id, users.name
Select(users.USER_ID, users.NAME)
//     └───────────┘  └────────┘
//         Field         Field

// SELECT 'lorem ipsum', COUNT(*)
Select(String("lorem ipsum"), Count())
//     └───────────────────┘  └─────┘
//             Field           Field

// SELECT COALESCE(users.score, users.previous_score))
Select(Coalesce(users.SCORE, users.PREVIOUS_SCORE))
//     │        └─────────┘  └──────────────────┘│
//     │           Field             Field       │
//     └─────────────────────────────────────────┘
//                        Field

Like Tables, Fields can also be aliased

u := tables.USERS().As("u")
// SELECT u.user_id AS uid FROM public.users AS u
Select(u.USER_ID.As("uid")).From(u)

A Predicate is: something that evaluates to true or false (or NULL) in SQL. A Predicate is often made up of Fields, but a Predicate is also a Field itself.

u, s := tables.USERS(), tables.STUDENTS()
// WHERE users.user_id = students.user_id
Where( u.USER_ID.Eq(s.USER_ID) )
//     └─────────────────────┘
//            Predicate

// WHERE users.user_id = students.user_id AND users.user_id <> 33
Where( u.USER_ID.Eq(s.USER_ID), u.USER_ID.NeInt(33) )
//     └─────────────────────┘  └─────────────────┘
//            Predicate              Predicate

// WHERE (users.user_id = 1 OR users.user_id > students.user_id) AND 1 = 1
Where( Or( u.USER_ID.EqInt(1), u.USER_ID.Gt(s.USER_ID) ), Int(1).EqInt(1) )
//     │   └────────────────┘  └─────────────────────┘ │  └─────────────┘
//     │        Predicate             Predicate        │     Predicate
//     └───────────────────────────────────────────────┘
//                       Predicate

Predicates passed to the WHERE clause are implictly AND-ed together. If you want to use OR, wrap the predicates in sq.Or().
To invert a Predicate, use sq.Not e.g. A.Eq(B) => sq.Not(A.Eq(B))

* A boolean column, e.g. users.is_active, is both a Field and a Predicate. Hence it can be used anywhere where a Field or Predicate is expected.

To get a better idea of where tables, fields and predicates are used, just look at the method signatures for each query builder.

Comparison Operator Reference

contents

This is the comparison operator terminology that sq uses throughout its API. It should be familiar to anyone has used bash's comparison operators.

Method Name	SQL Equivalent
Eq	=
Ne	<>
Gt	>
Ge	>=
Lt	<
Le	<=

Additionally, a method may also have a primitive-typed variants that specifically only accepts arguments of that type e.g.

Eq accepts Fields
EqInt accepts ints
EqString accepts strings
etc

Query Builder Reference

contents

Prerequisite: understand what a Table, Field and Predicate are.

Postgres
- SELECT
- INSERT
- UPDATE
- DELETE
MySQL
- SELECT
- INSERT
- UPDATE
- DELETE

Postgres

Postgres SELECT

query builder reference

// The following function(s) create a new SelectQuery:
From(table Table) SelectQuery
Select(fields ...Field) SelectQuery
SelectOne() SelectQuery
SelectDistinct(fields ...Field) SelectQuery
SelectDistinctOn(distinctFields ...Field) func(fields ...Field) SelectQuery
// For more information on Selectx and SelectRowx, see Struct Mapping.
Selectx(mapper func(*Row), accumulator func()) SelectQuery
SelectRowx(mapper func(*Row)) SelectQuery

// Available methods on a SelectQuery:
type SelectQuery struct {
    // For more infomation on WITH, see Common Table Expressions.
    With(ctes ...CTE) SelectQuery
    // Select
    Select(fields ...Field) SelectQuery
    SelectOne() SelectQuery
    SelectAll() SelectQuery
    SelectCount() SelectQuery
    SelectDistinct(fields ...Field) SelectQuery
    SelectDistinctOn(distinctFields ...Field) func(fields ...Field) SelectQuery
    // From + Joins
    From(table Table) SelectQuery
    Join(table Table, predicate Predicate, predicates ...Predicate) SelectQuery
    LeftJoin(table Table, predicate Predicate, predicates ...Predicate) SelectQuery
    RightJoin(table Table, predicate Predicate, predicates ...Predicate) SelectQuery
    FullJoin(table Table, predicate Predicate, predicates ...Predicate) SelectQuery
    CustomJoin(joinType JoinType, table Table, predicates ...Predicate) SelectQuery
    // Where
    Where(predicates ...Predicate) SelectQuery
    // Group By + Having
    GroupBy(fields ...Field) SelectQuery
    Having(predicates ...Predicate) SelectQuery
    // For more information on Windows, see Window Functions.
    Window(windows ...Window) SelectQuery
    // Order By
    OrderBy(fields ...Field) SelectQuery
    // Limit + Offset
    Limit(limit int) SelectQuery
    Offset(offset int) SelectQuery
    // Alias
    As(alias string) SelectQuery
    // For more information on Selectx and SelectRowx, see Struct Mapping.
    Selectx(mapper func(*Row), accumulator func()) SelectQuery
    SelectRowx(mapper func(*Row)) SelectQuery
    // For more information on Fetch and Exec, see Fetch and Exec.
    Fetch(db DB) error
    FetchContext(ctx context.Context, db DB) error
    Exec(db DB, flag ExecFlag) (rowsAffected int64, err error)
    ExecContext(ctx context.Context, db DB, flag ExecFlag) (rowsAffected int64, err error)
    // Serialize SelectQuery into query and args
    ToSQL() (query string, args []interface{})
}

Postgres INSERT

query builder reference

// The following function(s) create a new InsertQuery:
InsertInto(table BaseTable) InsertQuery

// Available methods on an InsertQuery:
type InsertQuery struct {
    // For more infomation on WITH, see Common Table Expressions.
    With(ctes ...CTE) InsertQuery
    // Insert Into
    InsertInto(table Table) InsertQuery
    Columns(fields ...Field) InsertQuery
    // Values
    Values(values ...interface{}) InsertQuery
    InsertRow(sets ...FieldAssignment) InsertQuery
    // Select
    Select(selectQuery SelectQuery) InsertQuery
    // On Conflict
    // For more information on using `ON CONFLICT ... DO UPDATE SET` see Upsert.
    OnConflict(fields ...Field) insertConflict
    OnConflictOnConstraint(name string) insertConflict
    (insertConflict).Where(predicates ...Predicate) insertConflict
    (insertConflict).DoNothing() InsertQuery
    (insertConflict).DoUpdateSet(sets ...FieldAssignment) InsertQuery
    // Where
    Where(predicates ...Predicate) InsertQuery
    // Returning
    Returning(fields ...Field) InsertQuery
    ReturningOne() InsertQuery
    // Alias
    As(alias string) InsertQuery
    // For more information on Returningx and ReturningRowx, see Struct Mapping.
    Returningx(mapper func(*Row)) InsertQuery
    ReturningRowx(mapper func(*Row), accumulator func()) InsertQuery
    // For more information on Fetch and Exec, see Fetch and Exec.
    Fetch(db DB) error
    FetchContext(ctx context.Context, db DB) error
    Exec(db DB, flag ExecFlag) (rowsAffected int64, err error)
    ExecContext(ctx context.Context, db DB, flag ExecFlag) (rowsAffected int64, err error)
    // Serialize InsertQuery into query and args
    ToSQL() (query string, args []interface{})
}

Postgres UPDATE

query builder reference

// The following function(s) create a new UpdateQuery:
Update(table BaseTable) UpdateQuery

// Available methods on an UpdateQuery:
type UpdateQuery struct {
    // For more infomation on WITH, see Common Table Expressions.
    With(ctes ...CTE) UpdateQuery
    // Update
    Update(table BaseTable) UpdateQuery
    // Set
    Set(sets ...FieldAssignment) UpdateQuery
    // From + Joins
    From(table Table) UpdateQuery
    Join(table Table, predicate Predicate, predicates ...Predicate) UpdateQuery
    LeftJoin(table Table, predicate Predicate, predicates ...Predicate) UpdateQuery
    RightJoin(table Table, predicate Predicate, predicates ...Predicate) UpdateQuery
    FullJoin(table Table, predicate Predicate, predicates ...Predicate) UpdateQuery
    CustomJoin(joinType JoinType, table Table, predicates ...Predicate) UpdateQuery
    // Where
    Where(predicates ...Predicate) UpdateQuery
    // Returning
    Returning(fields ...Field) UpdateQuery
    ReturningOne() UpdateQuery
    // Alias
    As(alias string) UpdateQuery
    // For more information on Returningx and ReturningRowx, see Struct Mapping.
    Returningx(mapper func(*Row)) UpdateQuery
    ReturningRowx(mapper func(*Row), accumulator func()) UpdateQuery
    // For more information on Fetch and Exec, see Fetch and Exec.
    Fetch(db DB) error
    FetchContext(ctx context.Context, db DB) error
    Exec(db DB, flag ExecFlag) (rowsAffected int64, err error)
    ExecContext(ctx context.Context, db DB, flag ExecFlag) (rowsAffected int64, err error)
    // Serialize UpdateQuery into query and args
    ToSQL() (query string, args []interface{})
}

Postgres DELETE

query builder reference

// The following function(s) create a new DeleteQuery:
DeleteFrom(table BaseTable) DeleteQuery

// Available methods on an DeleteQuery:
type DeleteQuery struct {
    // For more infomation on WITH, see Common Table Expressions.
    With(ctes ...CTE) DeleteQuery
    // Delete From
    DeleteFrom(table BaseTable) DeleteQuery
    // Using + Joins
    Using(table Table) DeleteQuery
    Join(table Table, predicate Predicate, predicates ...Predicate) DeleteQuery
    LeftJoin(table Table, predicate Predicate, predicates ...Predicate) DeleteQuery
    RightJoin(table Table, predicate Predicate, predicates ...Predicate) DeleteQuery
    FullJoin(table Table, predicate Predicate, predicates ...Predicate) DeleteQuery
    CustomJoin(joinType JoinType, table Table, predicates ...Predicate) DeleteQuery
    // Where
    Where(predicates ...Predicate) DeleteQuery
    // Returning
    Returning(fields ...Field) DeleteQuery
    ReturningOne() DeleteQuery
    // Alias
    As(alias string) DeleteQuery
    // For more information on Returningx and ReturningRowx, see Struct Mapping.
    Returningx(mapper func(*Row)) DeleteQuery
    ReturningRowx(mapper func(*Row), accumulator func()) DeleteQuery
    // For more information on Fetch and Exec, see Fetch and Exec.
    Fetch(db DB) error
    FetchContext(ctx context.Context, db DB) error
    Exec(db DB, flag ExecFlag) (rowsAffected int64, err error)
    ExecContext(ctx context.Context, db DB, flag ExecFlag) (rowsAffected int64, err error)
    // Serialize DeleteQuery into query and args
    ToSQL() (query string, args []interface{})
}

MySQL

MySQL SELECT

query builder reference

// The following function(s) create a new SelectQuery:
From(table Table) SelectQuery
Select(fields ...Field) SelectQuery
SelectOne() SelectQuery
SelectDistinct(fields ...Field) SelectQuery
// For more information on Selectx and SelectRowx, see Struct Mapping.
Selectx(mapper func(*Row), accumulator func()) SelectQuery
SelectRowx(mapper func(*Row)) SelectQuery

// Available methods on a SelectQuery:
type SelectQuery struct {
    // For more infomation on WITH, see Common Table Expressions.
    With(ctes ...CTE) SelectQuery
    // Select
    Select(fields ...Field) SelectQuery
    SelectOne() SelectQuery
    SelectAll() SelectQuery
    SelectCount() SelectQuery
    SelectDistinct(fields ...Field) SelectQuery
    // From + Joins
    From(table Table) SelectQuery
    Join(table Table, predicate Predicate, predicates ...Predicate) SelectQuery
    LeftJoin(table Table, predicate Predicate, predicates ...Predicate) SelectQuery
    RightJoin(table Table, predicate Predicate, predicates ...Predicate) SelectQuery
    FullJoin(table Table, predicate Predicate, predicates ...Predicate) SelectQuery
    CustomJoin(joinType JoinType, table Table, predicates ...Predicate) SelectQuery
    // Where
    Where(predicates ...Predicate) SelectQuery
    // Group By + Having
    GroupBy(fields ...Field) SelectQuery
    Having(predicates ...Predicate) SelectQuery
    // For more information on Windows, see Window Functions.
    Window(windows ...Window) SelectQuery
    // Order By
    OrderBy(fields ...Field) SelectQuery
    // Limit + Offset
    Limit(limit int) SelectQuery
    Offset(offset int) SelectQuery
    // Alias
    As(alias string) SelectQuery
    // For more information on Selectx and SelectRowx, see Struct Mapping.
    Selectx(mapper func(*Row), accumulator func()) SelectQuery
    SelectRowx(mapper func(*Row)) SelectQuery
    // For more information on Fetch, see Fetch and Exec.
    Fetch(db DB) error
    FetchContext(ctx context.Context, db DB) error
    // Serialize SelectQuery into query and args
    ToSQL() (query string, args []interface{})
}

MySQL INSERT

query builder reference

// The following function(s) create a new InsertQuery:
InsertInto(table BaseTable) InsertQuery
InsertIgnoreInto(table BaseTable) InsertQuery

// Available methods on an InsertQuery:
type InsertQuery struct {
    // Insert Into
    InsertInto(table Table) InsertQuery
    Columns(fields ...Field) InsertQuery
    // Values
    Values(values ...interface{}) InsertQuery
    InsertRow(sets ...FieldAssignment) InsertQuery
    // Select
    Select(selectQuery SelectQuery) InsertQuery
    // On Duplicate Key Update
    // For more information on using `ON DUPLICATE KEY UPDATE` see Upsert.
    OnDuplicateKeyUpdate(sets ...FieldAssignment) InsertQuery
    // Where
    Where(predicates ...Predicate) InsertQuery
    // Alias
    As(alias string) InsertQuery
    // For more information on Exec, see Fetch and Exec.
    Exec(db DB, flag ExecFlag) (lastInsertID, rowsAffected int64, err error)
    ExecContext(ctx context.Context, db DB, flag ExecFlag) (lastInsertID, rowsAffected int64, err error)
    // Serialize InsertQuery into query and args
    ToSQL() (query string, args []interface{})
}

MySQL UPDATE

query builder reference

// The following function(s) create a new UpdateQuery:
Update(table BaseTable) UpdateQuery

// Available methods on an UpdateQuery:
type UpdateQuery struct {
    // For more infomation on WITH, see Common Table Expressions.
    With(ctes ...CTE) UpdateQuery
    // Update
    Update(table BaseTable) UpdateQuery
    // Set
    Set(sets ...FieldAssignment) UpdateQuery
    // Joins
    Join(table Table, predicate Predicate, predicates ...Predicate) UpdateQuery
    LeftJoin(table Table, predicate Predicate, predicates ...Predicate) UpdateQuery
    RightJoin(table Table, predicate Predicate, predicates ...Predicate) UpdateQuery
    FullJoin(table Table, predicate Predicate, predicates ...Predicate) UpdateQuery
    CustomJoin(joinType JoinType, table Table, predicates ...Predicate) UpdateQuery
    // Where
    Where(predicates ...Predicate) UpdateQuery
    // Order By
    OrderBy(fields ...Field) UpdateQuery
    // Limit
    Limit(limit int) UpdateQuery
    // Alias
    As(alias string) UpdateQuery
    // For more information on Exec, see Fetch and Exec.
    Exec(db DB, flag ExecFlag) (rowsAffected int64, err error)
    ExecContext(ctx context.Context, db DB, flag ExecFlag) (rowsAffected int64, err error)
    // Serialize UpdateQuery into query and args
    ToSQL() (query string, args []interface{})
}

MySQL DELETE

query builder reference

// The following function(s) create a new DeleteQuery:
DeleteFrom(table BaseTable) DeleteQuery

// Available methods on an DeleteQuery:
type DeleteQuery struct {
    // For more infomation on WITH, see Common Table Expressions.
    With(ctes ...CTE) DeleteQuery
    // Delete From
    DeleteFrom(tables ...BaseTable) DeleteQuery
    // Using + Joins
    Using(table Table) DeleteQuery
    Join(table Table, predicate Predicate, predicates ...Predicate) DeleteQuery
    LeftJoin(table Table, predicate Predicate, predicates ...Predicate) DeleteQuery
    RightJoin(table Table, predicate Predicate, predicates ...Predicate) DeleteQuery
    FullJoin(table Table, predicate Predicate, predicates ...Predicate) DeleteQuery
    CustomJoin(joinType JoinType, table Table, predicates ...Predicate) DeleteQuery
    // Where
    Where(predicates ...Predicate) DeleteQuery
    // Order By
    OrderBy(fields ...Field) DeleteQuery
    // Limit
    Limit(limit int) DeleteQuery
    // Alias
    As(alias string) DeleteQuery
    // For more information on Exec, see Fetch and Exec.
    Exec(db DB, flag ExecFlag) (rowsAffected int64, err error)
    ExecContext(ctx context.Context, db DB, flag ExecFlag) (rowsAffected int64, err error)
    // Serialize DeleteQuery into query and args
    ToSQL() (query string, args []interface{})
}

< Prev: Generating table types

Up: Basics

Next: SQL escape hatch >