Skip to main content

Database: Query Builder

See how to use the Athenna database query builder.

Introduction​

Athenna database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application and works perfectly with all of Athenna supported database drivers.

Running database queries​

Retrieve all rows from a table​

You may use the table() method provided by the Database facade to begin a query. The table() method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally retrieve the results of the query using the findMany() method:

import { Database } from '@athenna/database'

const users = await Database.table('users')
  .select('id', 'name')
  .whereILike({ name: '%Valmir Barbosa%' })
  .orderBy('name', 'ASC')
  .findMany()

Athenna also provides the collection() method that returns a Collection instance containing the results of the query. You may access each column's value using the all() method:

const collection = await Database.table('users')
  .select('id', 'name')
  .whereILike({ name: '%Valmir Barbosa%' })
  .orderBy('name', 'ASC')
  .collection()

const users = collection.all()
tip

Athenna collections provide a variety of extremely powerful methods for mapping and reducing data. For more information on Athenna collections, check out the collection documentation.

Retrieve a single row​

If you just need to retrieve a single row from a database table, you may use the Database facade's find() method. This method will return a single object:

const user = await Database.table('users')
  .select('id', 'name')
  .where({ name: 'Rodrigo Kamada' })
  .find()

Validate if exists​

If you just need to validate if exists any row with determined constraints, you may use the Database facade's exists() method. This method will return true if the row exists:

const hasUser = await Database.table('users')
  .select('id', 'name')
  .where({ name: 'Rodrigo Kamada' })
  .exists()

Get the client and query builder of a driver​

To get the vanilla client or query builder of your connection driver, you can use the getClient() and getQueryBuilder() method:

// Knex client
const client = Database.connection('postgres').getClient()

await client.close()

// Knex query builder
const queryBuilder = Database.connection('postgres').getQueryBuilder()

const result = await queryBuilder
  .where({ id: 1, status: 'ACTIVE' })
  .andWhere('status', 'PENDING')

Agreggates​

The query builder also provides a variety of methods for retrieving aggregate values like count, max, min, avg, and sum. You may call any of these methods after constructing your query:

const numberOfUsers = await Database.table('users').count()
const maxPriceOrder = await Database.table('orders').max('price')

Of course, you may combine these methods with other clauses to fine-tune how your aggregate value is calculated:

const priceAverage = await Database.table('orders')
  .where('finalized', true)
  .avg('price')

Select statements​

You may not always want to select all columns from a database table. Using the select() method, you can specify a custom "select" clause for the query:

const { id, name } = await Database.table('users')
  .select('id', 'name')
  .find()

If you want to select all fields you can use the * operator:

const { id, name, email } = await Database.table('users')
  .select('*')
  .find()

Raw expressions​

Sometimes you may need to insert an arbitrary string into a query. To create a raw string expression, you may use the raw() method provided by the Database facade:

const users = await Database.table('users')
  .select(Database.raw('count(*) as users_count, status'))
  .where('status', '<>', 1)
  .groupBy('status')
  .findMany()

You can also use await to wait your query to finish:

const users = await Database.raw('SELECT * FROM users')
caution

You should be extremely careful to avoid creating SQL injection vulnerabilities using the raw() method.

Raw methods​

Instead of using the Database.raw() method, you may also use the following methods to insert a raw expression into various parts of your query. Remember, Athenna can not guarantee that any query using raw expressions is protected against SQL injection vulnerabilities.

selectRaw()​

The selectRaw() method can be used in place of select(Database.raw(/* ... */)). This method accepts an optional array of bindings as its second argument:

const orders = await Database.table('orders')
  .selectRaw('price * ? as price_with_tax', [1.0825])
  .findMany()

whereRaw() / orWhereRaw()​

The whereRaw() and orWhereRaw() methods can be used to inject a raw "where" clause into your query. These methods accept an optional array of bindings as their second argument:

const orders = await Database.table('orders')
  .whereRaw('price > IF(state= "TX", ?, 100)', [200])
  .findMany()

havingRaw() / orHavingRaw()​

The havingRaw() and orHavingRaw() methods may be used to provide a raw string as the value of the "having" clause. These methods accept an optional array of bindings as their second argument:

const orders = await Database.table('orders')
  .select('department', Database.raw('SUM(price) as total_sales'))
  .groupBy('department')
  .havingRaw('SUM(price) > ?', [2500])
  .findMany()

orderByRaw()​

The orderByRaw() method may be used to provide a raw string as the value of the "order by" clause:

const orders = await Database.table('orders')
  .orderByRaw('updated_at - created_at DESC')
  .findMany()

groupByRaw()​

The groupByRaw() method may be used to provide a raw string as the value of the "group by" clause:

const orders = await Database.table('orders')
  .select('city', 'state')
  .groupByRaw('city, state')
  .findMany()

Joins​

Inner join clause​

The query builder may also be used to add join clauses to your queries. To perform a basic "inner join", you may use the join method on a query builder instance. The first argument passed to the join method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join. You may even join multiple tables in a single query:

const users = await Database.table('users')
  .join('contacts', 'users.id', '=', 'contacts.user_id')
  .join('orders', 'users.id', '=', 'orders.user_id')
  .select('users.*', 'contacts.phone', 'orders.price')
  .findMany()

Other join clauses​

If you would like to perform a "left join" or "right join" instead of an "inner join", use the leftJoin() or rightJoin() methods. They have the same signature of join method:

const users = await Database.table('users')
  .leftJoin('contacts', 'users.id', '=', 'contacts.user_id')
  .rightJoin('orders', 'users.id', '=', 'orders.user_id')
  .select('users.*', 'contacts.phone', 'orders.price')
  .findMany()

You can use any of the join types bellow in your queries:

  • leftJoin()
  • rightJoin()
  • crossJoin()
  • fullOuterJoin()
  • leftOuterJoin()
  • rightOuterJoin()

Advanced join clauses​

You may also specify more advanced join clauses using the Database facade:

const users = await Database.table('users')
  .join('contacts', join => join.on('users.id', '=', 'contacts.user_id').orOn(/* ... */))
  .findMany()

Basic where clauses​

Where clauses​

You may use the query builder's where() method to add "where" clauses to the query. The most basic call to the where() method requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. The third argument is the value to compare against the column's value.

For example, the following query retrieves users where the value of the votes column is equal to 100 and the value of the age column is greater than 35:

const user = await Database.table('users')
  .where('votes', '=', 100)
  .where('age', '>', 35)
  .find()

For convenience, if you want to verify that a column is = to a given value, you may pass the value as the second argument to the where() method. Athenna will assume you would like to use the = operator:

const users = await Database.table('users')
  .where('votes', 100)
  .findMany()

As previously mentioned, you may use any operator that is supported by your database system:

const users = await Database.table('users')
  .where('votes', '>=', 100)
  .findMany()

const users = await Database.table('users')
  .where('votes', '<>', 100)
  .findMany()

const users = await Database.table('users')
  .where('name', 'like', 'J%')
  .findMany()

You may also pass an object of conditions, but remember that when using objects the operation is always going to be =:

const users = await Database.table('users')
  .where({ name: 'João Lenon', deletedAt: null })
  .findMany()

Or where clauses​

When chaining together calls to the query builder's where() method, the "where" clauses will be joined together using the and operator. However, you may use the orWhere() method to join a clause to the query using the or operator. The orWhere() method accepts the same arguments as the where() method:

const users = await Database.table('users')
  .where('votes', '>', 100)
  .orWhere('name', 'João')
  .findMany()

Where not clauses​

The whereNot() and orWhereNot() methods may be used to negate a given constraint. For example, the following query excludes the product with id ten:

const products = await Database.table('products')
  .whereNot('id', 10)
  .findMany()

Additional where clauses​

whereBetween() / orWhereBetween()​

The whereBetween() or orWhereBetween() methods verifies that a column's value is between two values:

const users = await Database.table('users')
  .whereBetween('votes', [1, 100])
  .findMany()

whereNotBetween() / orWhereNotBetween()​

The whereNotBetween() or orWhereNotBetween() methods verifies that a column's value lies outside two values:

const users = await Database.table('users')
  .whereNotBetween('votes', [1, 100])
  .findMany()

whereIn() / orWhereIn()​

The whereIn() or orWhereIn() methods verifies that a given column's value is contained within the given array:

const users = await Database.table('users')
  .whereIn('id', [1, 2, 3])
  .findMany()

whereNotIn() / orWhereNotIn()​

The whereNotIn() or orWhereNotIn() methods verifies that the given column's value is not contained in the given array:

const users = await Database.table('users')
  .whereNotIn('id', [1, 2, 3])
  .findMany()

whereNull() / orWhereNull()​

The whereNull() or orWhereNull() methods verifies that the value of the given column is NULL:

const users = await Database.table('users')
  .whereNull('deletedAt')
  .findMany()

whereNotNull() / orWhereNotNull()​

The whereNotNull() or orWhereNotNull() methods verifies that the column's value is not NULL:

const users = await Database.table('users')
  .whereNotNull('deletedAt')
  .findMany()

Logical grouping​

Sometimes you may need to group several "where" clauses within parentheses in order to achieve your query's desired logical grouping. In fact, you should generally always group calls to the orWhere() method in parentheses in order to avoid unexpected query behavior. To accomplish this, you may pass a closure to the where method:

const users = await Database.table('users')
  .where('name', '=', 'João')
  .where(query => {
    query
      .where('votes', '>', 100)
      .orWhere('title', '=', 'Admin')
  })
  .findMany()

As you can see, passing a closure into the where() method instructs the query builder to begin a constraint group. The closure will receive a query builder instance which you can use to set the constraints that should be contained within the parenthesis group. The example above will produce the following SQL:

select * from users where name = 'João' and (votes > 100 or title = 'Admin')
warning

You should always group orWhere() calls in order to avoid unexpected behavior when global scopes are applied.

Advanced where clauses​

Where exists clauses​

The whereExists(), orWhereExists(), whereNotExists() and orWhereNotExists() methods allows you to write "where exists" SQL clauses. They accept a closure which will receive a query builder instance, allowing you to define the query that should be placed inside the "exists" clause:

const users = await Database.table('users')
  .whereExists(Database.table('orders')
                       .selectRaw(1)
                       .whereRaw("`orders`.`user_id` = `users`.`id`"))
  .findMany()

The query above will produce the following SQL:

select * from users
where exists (
    select 1
    from orders
    where orders.user_id = users.id
)

Ordering, grouping, limit & skip​

Ordering​

The orderBy() method​

The orderBy() method allows you to sort the results of the query by a given column. The first argument accepted by the orderBy() method should be the column you wish to sort by, while the second argument determines the direction of the sort and may be either asc, ASC, desc or DESC:

const users = await Database.table('users')
  .orderBy('name', 'desc')
  .findMany()

To sort by multiple columns, you may simply invoke orderBy() as many times as necessary:

const users = await Database.table('users')
  .orderBy('name', 'desc')
  .orderBy('email', 'asc')
  .findMany()

The latest() & oldest() methods​

The latest() and oldest() methods allow you to easily order results by date. By default, the result will be ordered by the table's createdAt column:

const user = await Database.table('users')
  .latest()
  .find()

Or, you may pass the column name that you wish to sort by:

const user = await Database.table('users')
  .oldest('updatedAt')
  .find()

Grouping​

The groupBy() & having() methods​

As you might expect, the groupBy() and having() methods may be used to group the query results. The having method's signature is similar to that of the where() method:

const users = await Database.table('users')
  .groupBy('account_id')
  .having('account_id', '>', 100)
  .findMany()

You can use the havingBetween() method to filter the results within a given range:

const users = await Database.table('users')
  .selectRaw('count(id) as number_of_users, account_id')
  .groupBy('account_id')
  .havingBetween('number_of_users', [0, 100])
  .findMany()

You may pass multiple arguments to the groupBy() method to group by multiple columns:

const users = await Database.table('users')
  .groupBy('first_name', 'status')
  .having('account_id', '>', 100)
  .findMany()

To build more advanced having() statements, see the havingRaw() method.

Limit & Offset​

You may use the offset() and limit() methods to limit the number of results returned from the query or to skip a given number of results in the query:

const users = await Database.table('users')
  .offset(10)
  .limit(5)
  .findMany()
tip

The offset() method is equivalent to skip() and the limit() method is equivalent to take(). take() and skip() are usually used by other query builders.

Conditional clauses​

Sometimes you may want certain query clauses to apply to a query based on another condition. For instance, you may only want to apply a where() statement if a given input value is present on the incoming HTTP request. You may accomplish this using the when() method:

const role = request.payload('role')

await Database.table('users')
  .when(role, (query, role) => query.where('roleId', role))
  .findMany()

The when() method only executes the given closure when the first argument is true. If the first argument is false, the closure will not be executed. So, in the example above, the closure given to the when() method will only be invoked if the role field is present on the incoming request and evaluates to a truthy value.

You can also add two when() methods to your query to execute a different closure when the role IS NOT present in your query:

const role = request.payload('role')

await Database.table('users')
  // Executes if role is present
  .when(role, (query, role) => query.where('roleId', role))
  // Executes if role is not present
  .when(!role, (query, role) => query.where('roleId', role))
  .findMany()

Pagination​

You can paginate the results of your database using the paginate() method. This method support 3 arguments, the first argument is the page (default value is 0) it defines the page where your pagination will start, the second is the limit (default value is 10) it defines the limit of data that will be retrieved per page and the third one defines the resource url that Athenna will use to create the pagination links:

const { data, meta, links } = await Database.table('users')
  .whereNull('deletedAt')
  .paginate(0, 10, '/users')

The data param is where all the data retrieved from database will stay:

console.log(data) // -> [{...}]

The meta param will have information about the pagination such as the total of items finds using that query, items per page, total pages left, current page and the number of itens in that specific execution:

console.log(meta)
/**
* {
*    totalItems: 10,
*    itemsPerPage: 10,
*    totalPages: 10,
*    currentPage: 1,
*    itemCount: 10
* }
*/

The links object will help ho is consuming you API to know what is the next resource to call to go through your paginated data:

console.log(links)
/**
* {
*   next: '/users?page=2&limit=10',
*   previous: '/users?page=0&limit=10',
*   last: '/users?page=10&limit=10,
*   first: '/users?&limit=10'
* }
*/

Insert statements​

The query builder also provides the create() and createMany() methods that may be used to insert records into the database table. The create() method accepts a record with columns names and values:

const user = await Database.table('users').create({
    name: 'Valmir Barbosa',
    email: 'valmirphp@gmail.com'
})

The createMany() method accepts an array of records:

const users = await Database.table('users').createMany([
    {
        name: 'Valmir Barbosa',
        email: 'valmirphp@gmail.com'
    },
    {
        name: 'Danrley Morais',
        email: 'danrley.morais@gmail.com'
    }
])

Create or update (Upsert)​

The createOrUpdate() method will insert records that do not exist and update the records that already exist with new values that you may specify. The method's first argument consists of the values to insert or update, while the second argument is the column that uniquely identify records within the associated table (the default is id). In the example above we are going to create a new record in the users table only if the txsoura@athenna.io email is not already registered in users table:

const user = await Database.table('users')
  .createOrUpdate({
    name: 'Victor Tesoura',
    email: 'txsoura@athenna.io'
  }, 'email') // <- The uniquely identifier

Update statements​

In addition to inserting records into the database, the query builder can also update existing records using the update() method. The update() method, like the create() method, accepts a record with columns names and values indicating the columns to be updated. You may constrain the update query using where clauses. In the example above we are going to "undo" the soft delete by searching for all records where the deletedAt column is not null and setting it to null:

const user = await Database.table('users')
  .whereNotNull('deletedAt')
  .update({ deletedAt: null })

Incrementing & decrementing​

The query builder also provides convenient methods for incrementing or decrementing the value of a given column. Both of these methods accept at least one argument: the column to modify:

await Database.table('users').increment('votes')
await Database.table('users').where('id', 1).increment('votes')

await Database.table('users').decrement('votes')
await Database.table('users').where('id', 1).decrement('votes')

Delete statements​

The query builder's delete() method may be used to delete records from the table. You may constrain delete statements by adding "where" clauses before calling the delete() method:

await Database.table('users').delete()
await Database.table('users').where('votes', '>', 100).delete()

If you wish to truncate an entire table, which will remove all records from the table and reset the auto-incrementing ID to zero, you may use the truncate method:

const tableName = 'users'

await Database.truncate(tableName)

Debugging​

You may use the dump() method while building a query to dump the current query bindings and SQL. The dump() method will display the debug information and continue executing the code:

const users = await Database.table('users')
  .whereNull('deletedAt')
  .dump() // <- Will log in the terminal your query until this point
  .oldest('deletedAt')
  .dump() // <- Will log in the terminal your query until this point
  .findMany()