Extend with Go - Database - Docs

The main interface to interact with your application database is via the Dao abstraction .

app.Dao() provides read and write helpers (see Collection operations and Record operations) and it is responsible for triggering the onModel* event hooks.

It also exposes app.Dao().DB() builder that allows executing various SQL statements (including raw queries). Most of the common DB operations are listed below, but you can find further information in the dbx package godoc .

Executing queries

To execute DB queries you can start with the NewQuery("...") statement and then call one of:

Execute() - for any query statement that is not meant to retrieve data:

res, err := app.Dao().DB(). NewQuery("CREATE INDEX name_idx ON users (name)"). Execute()
One() - to populate a single row into a struct:

type User struct { Id string `db:"id" json:"id"` Status bool `db:"status" json:"status"` Age int `db:"age" json:"age"` Roles types.JsonArray `db:"roles" json:"roles"` } user := User{} err := app.Dao().DB(). NewQuery("SELECT id, status, age, roles FROM users WHERE id=1"). One(&user)
All() - to populate multiple rows into a slice of structs:

type User struct { Id string `db:"id" json:"id"` Status bool `db:"status" json:"status"` Age int `db:"age" json:"age"` Roles types.JsonArray `db:"roles" json:"roles"` } users := []User{} err := app.Dao().DB(). NewQuery("SELECT id, status, age, roles FROM users LIMIT 100"). All(&users)

Binding parameters

To prevent SQL injection attacks, you should use named parameters for any expression value that comes from user input. This could be done using the named {:paramName} placeholders in your SQL statement and then define the parameter values for the query with Bind(params). For example:

type Post struct {
    Name     string         `db:"name" json:"name"`
    Created  types.DateTime `db:"created" json:"created"`
}

posts := []Post{}

err := app.Dao().DB().
    NewQuery("SELECT name, created FROM posts WHERE created >= {:from} and created <= {:to}").
    Bind(dbx.Params{
        "from": "2023-06-25 00:00:00.000Z",
        "to":   "2023-06-28 23:59:59.999Z",
    }).
    All(&posts)

Query builder

Instead of writting plain SQLs, you can also compose SQL statements programmatically using the db query builder.
Every SQL keyword has a corresponding query building method. For example, SELECT corresponds to Select(), FROM corresponds to From(), WHERE corresponds to Where(), and so on.

users := []struct {
    Id    string `db:"id" json:"id"`
    Email string `db:"email" json:"email"`
}{}

app.Dao().DB().
    Select("id", "email").
    From("users").
    AndWhere(dbx.Like("email", "example.com")).
    Limit(100).
    OrderBy("created ASC").
    All(&users)

Select(), AndSelect(), Distinct()

The Select(...cols) method initializes a SELECT query builder. It accepts a list of the column names to be selected.
To add additional columns to an existing select query, you can call AndSelect().
To select distinct rows, you can call Distinct().

app.Dao().DB().
    Select("id", "avatar as image").
    AndSelect("(firstName || ' ' || lastName) as fullName").
    Distinct()
    ...

From()

The From(...tables) method specifies which tables to select from (plain table names are automatically quoted).

app.Dao().DB().
    Select("table1.id", "table2.name").
    From("table1", "table2")
    ...

Join()

The Join(type, table, on) method specifies a JOIN clause. It takes 3 parameters:

type - join type string like INNER JOIN, LEFT JOIN, etc.
table - the name of the table to be joined
on - optional dbx.Expression as an ON clause

For convenience, you can also use the shortcuts InnerJoin(table, on), LeftJoin(table, on), RightJoin(table, on) to specify INNER JOIN, LEFT JOIN and RIGHT JOIN, respectively.

app.Dao().DB().
    Select("users.*").
    From("users").
    InnerJoin("profiles", dbx.NewExp("profiles.user_id = users.id")).
    Join("FULL OUTER JOIN", "department", dbx.NewExp("department.id = {:id}", dbx.Params{ "id": "someId" }))
    ...

Where(), AndWhere(), OrWhere()

The Where(exp) method specifies the WHERE condition of the query.
You can also use AndWhere(exp) or OrWhere(exp) to append additional one or more conditions to an existing WHERE clause.
Each where condition accepts a single dbx.Expression (see below for full list).

/*
SELECT users.*
FROM users
WHERE id = "someId" AND
    status = "public" AND
    name like "%john%" OR
    (
        role = "manager" AND
        fullTime IS TRUE AND
        experience > 10
    )
*/
app.Dao().DB().
    Select("users.*").
    From("users").
    Where(dbx.NewExp("id = {:id}", dbx.Params{ "id": "someId" })).
    AndWhere(dbx.HashExp{"status": "public"}).
    AndWhere(dbx.Like("name", "john")).
    OrWhere(dbx.And(
        dbx.HashExp{
            "role":     "manager",
            "fullTime": true,
        },
        dbx.NewExp("experience > {:exp}", dbx.Params{ "exp": 10 })
    ))
    ...

The following dbx.Expression methods are available:

dbx.NewExp(raw, optParams)
Generates an expression with the specified raw query fragment. Use the optParams to bind dbx.Params to the expression.
dbx.NewExp("status = 'public'") dbx.NewExp("total > {:min} AND total < {:max}", dbx.Params{ "min": 10, "max": 30 })
dbx.HashExp{k:v}
Generates a hash expression from a map whose keys are DB column names which need to be filtered according to the corresponding values.
// slug = "example" AND active IS TRUE AND tags in ("tag1", "tag2", "tag3") AND parent IS NULL dbx.HashExp{ "slug": "example", "active": true, "tags": []any{"tag1", "tag2", "tag3"}, "parent": nil, }
dbx.Not(exp)
Negates a single expression by wrapping it with NOT().
// NOT(status = 1) dbx.Not(dbx.NewExp("status = 1"))
dbx.And(...exps)
Creates a new expression by concatenating the specified ones with AND.
// (status = 1 AND username like "%john%") dbx.And( dbx.NewExp("status = 1"), dbx.Like("username", "john"), )
dbx.Or(...exps)
Creates a new expression by concatenating the specified ones with OR.
// (status = 1 OR username like "%john%") dbx.Or( dbx.NewExp("status = 1"), dbx.Like("username", "john") )
dbx.In(col, ...values)
Generates an IN expression for the specified column and the list of allowed values.
// status IN ("public", "reviewed") dbx.In("status", "public", "reviewed")
dbx.NotIn(col, ...values)
Generates an NOT IN expression for the specified column and the list of allowed values.
// status NOT IN ("public", "reviewed") dbx.NotIn("status", "public", "reviewed")
dbx.Like(col, ...values)
Generates a LIKE expression for the specified column and the possible strings that the column should be like. If multiple values are present, the column should be like all of them.
By default, each value will be surrounded by "%" to enable partial matching. Special characters like "%", "\", "_" will also be properly escaped. You may call Escape(...pairs) and/or Match(left, right) to change the default behavior.
// name LIKE "%test1%" AND name LIKE "%test2%" dbx.Like("name", "test1", "test2") // name LIKE "test1%" dbx.Like("name", "test1").Match(false, true)
dbx.NotLike(col, ...values)
Generates a NOT LIKE expression in similar manner as Like().
// name NOT LIKE "%test1%" AND name NOT LIKE "%test2%" dbx.NotLike("name", "test1", "test2") // name NOT LIKE "test1%" dbx.NotLike("name", "test1").Match(false, true)
dbx.OrLike(col, ...values)
This is similar to Like() except that the column must be one of the provided values, aka. multiple values are concatenated with OR instead of AND.
// name LIKE "%test1%" OR name LIKE "%test2%" dbx.OrLike("name", "test1", "test2") // name LIKE "test1%" OR name LIKE "test2%" dbx.OrLike("name", "test1", "test2").Match(false, true)
dbx.OrNotLike(col, ...values)
This is similar to NotLike() except that the column must not be one of the provided values, aka. multiple values are concatenated with OR instead of AND.
// name NOT LIKE "%test1%" OR name NOT LIKE "%test2%" dbx.OrNotLike("name", "test1", "test2") // name NOT LIKE "test1%" OR name NOT LIKE "test2%" dbx.OrNotLike("name", "test1", "test2").Match(false, true)
dbx.Exists(exp)
Prefix with EXISTS the specified expression (usually a subquery).
// EXISTS (SELECT 1 FROM users WHERE status = 'active') dbx.Exists(dbx.NewExp("SELECT 1 FROM users WHERE status = 'active'"))
dbx.NotExists(exp)
Prefix with NOT EXISTS the specified expression (usually a subquery).
// NOT EXISTS (SELECT 1 FROM users WHERE status = 'active') dbx.NotExists(dbx.NewExp("SELECT 1 FROM users WHERE status = 'active'"))
dbx.Between(col, from, to)
Generates a BETWEEN expression with the specified range.
// age BETWEEN 3 and 99 dbx.Between("age", 3, 99)
dbx.NotBetween(col, from, to)
Generates a NOT BETWEEN expression with the specified range.
// age NOT BETWEEN 3 and 99 dbx.NotBetween("age", 3, 99)

OrderBy(), AndOrderBy()

The OrderBy(...cols) specifies the ORDER BY clause of the query.
A column name can contain "ASC" or "DESC" to indicate its ordering direction.
You can also use AndOrderBy(...cols) to append additional columns to an existing ORDER BY clause.

app.Dao().DB().
    Select("users.*").
    From("users").
    OrderBy("created ASC", "updated DESC").
    AndOrderBy("title ASC")
    ...

GroupBy(), AndGroupBy()

The GroupBy(...cols) specifies the GROUP BY clause of the query.
You can also use AndGroupBy(...cols) to append additional columns to an existing GROUP BY clause.

app.Dao().DB().
    Select("users.*").
    From("users").
    GroupBy("department", "level")
    ...

Having(), AndHaving(), OrHaving()

The Having(exp) specifies the HAVING clause of the query.
Similarly to Where(exp), it accept a single dbx.Expression (see all available expressions listed above).
You can also use AndHaving(exp) or OrHaving(exp) to append additional one or more conditions to an existing HAVING clause.

app.Dao().DB().
    Select("users.*").
    From("users").
    GroupBy("department", "level").
    Having(dbx.NewExp("sum(level) > {:sum}", dbx.Params{ sum: 10 }))
    ...

Limit()

The Limit(number) method specifies the LIMIT clause of the query.

app.Dao().DB().
    Select("users.*").
    From("users").
    Limit(30)
    ...

Offset()

The Offset(number) method specifies the OFFSET clause of the query. Usually used together with Limit(number).

app.Dao().DB().
    Select("users.*").
    From("users").
    Offset(5).
    Limit(30)
    ...

Transaction

To execute multiple queries in a transaction you can use app.Dao().RunInTransaction()

You can nest Dao.RunInTransaction() as many times as you want.

The transaction will be committed only if there are no errors.

app.Dao().RunInTransaction(func(txDao *daos.Dao) error {
    // update a record
    record, err := txDao.FindRecordById("articles", "RECORD_ID")
    if err != nil {
        return err
    }
    record.Set("status", "active")
    if err := txDao.SaveRecord(record); err != nil {
        return err
    }

    // run some custom raw query
    rawQuery := "DELETE FROM articles WHERE status = 'pending'"
    if _, err := txDao.DB().NewQuery(rawQuery).Execute(); err != nil {
        return err
    }

    return nil
})

Dao without event hooks

By default all Dao write operations (create, update, delete) trigger the onModel* event hooks.
If you don't want this behavior, you can create a new Dao without hooks from an existing one by calling Dao.WithoutHooks() or instantiate a new one with daos.New(db)/daos.NewMultiDB(concurrentDB, nonconcurrentDB):

record, _ := app.Dao().FindRecordById("articles", "RECORD_ID")

// the below WILL fire the OnModelBeforeUpdate and OnModelAfterUpdate hooks
app.Dao().SaveRecord(record)

// the below WILL NOT fire the OnModelBeforeUpdate and OnModelAfterUpdate hooks
dao := app.Dao().WithoutHooks()
dao.saveRecord(record)