core.App is the main interface to interact with the database.

App.DB() returns a dbx.Builder that can run all kinds of 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 .

For more details and examples how to interact with Record and Collection models programmatically you could also check Collection operations and Record operations sections.

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

  • - for any query statement that is not meant to retrieve data:

  • - to populate a single row into a struct:

  • - to populate multiple rows into a slice of structs:

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:

= {: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) `} />

Instead of writing 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.

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(true).

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

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.

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

10 ) */ app.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:


  • Generates an expression with the specified raw query fragment. Use the optParams to bind dbx.Params to the expression. {:min} AND total < {:max}", dbx.Params{ "min": 10, "max": 30 }) `} />

  • Generates a hash expression from a map whose keys are DB column names which need to be filtered according to the corresponding values.

  • Negates a single expression by wrapping it with NOT().

  • Creates a new expression by concatenating the specified ones with AND.

  • Creates a new expression by concatenating the specified ones with OR.

  • Generates an IN expression for the specified column and the list of allowed values.

  • Generates an NOT IN expression for the specified column and the list of allowed 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.

  • Generates a NOT LIKE expression in similar manner as Like().

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

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

  • Prefix with EXISTS the specified expression (usually a subquery).

  • Prefix with NOT EXISTS the specified expression (usually a subquery).

  • Generates a BETWEEN expression with the specified range.

  • Generates a NOT BETWEEN expression with the specified range.

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.

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.

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.

{:sum}", dbx.Params{ sum: 10 })) ... `} />

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

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