version 1.0.0

Database: Query Builder

Introduction
Running database queries
- Get the client of driver
Aggregates
Select statements
Raw expressions
Joins
Basic where clauses
Advanced where clauses
- Where exists clauses
Ordering, grouping, limit & skip
Conditional clauses
Pagination
Insert statements
Update statements
Delete statements
Debugging

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 an 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()

Get the client and query builder of 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' })
        .orWhere('status', 'PENDING')

Aggregates

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 including the hidden 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 execute your query in that moment:

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

Database: Query Builder

Introduction​

Running database queries​

Retrieve all rows from a table​

tip

Retrieve a single row​

Get the client and query builder of driver​

Aggregates​

Select statements​

Raw expressions​

caution

Raw methods​

selectRaw​

whereRaw / orWhereRaw​

havingRaw / orHavingRaw​

orderByRaw​

groupByRaw​

Joins​

Inner join clause​

Other join clauses​

Advanced join clauses​

Basic where clauses​

Where clauses​

Or where clauses​

Where not clauses​

Additional where clauses​

Logical grouping​

warning

Advanced where clauses​

Where exists clauses​

Ordering, grouping, limit & skip​

Ordering​

The orderBy method​

The latest & oldest methods​

Grouping​

The groupBy & having methods​

Limit & Offset​

tip

Conditional clauses​

Pagination​

Insert statements​

Create or update (Upsert)​

Update statements​

Incrementing & decrementing​

Delete statements​

Debugging​

Introduction

Running database queries

Retrieve all rows from a table

Retrieve a single row

Get the client and query builder of driver

Aggregates

Select statements

Raw expressions

Raw methods

`selectRaw`

`whereRaw / orWhereRaw`

`havingRaw / orHavingRaw`

`orderByRaw`

`groupByRaw`

Joins

Inner join clause

Other join clauses

Advanced join clauses

Basic where clauses

Where clauses

Or where clauses

Where not clauses

Additional where clauses

Logical grouping

Advanced where clauses

Where exists clauses

Ordering, grouping, limit & skip

Ordering

The `orderBy` method

The `latest` & `oldest` methods

Grouping

The `groupBy` & `having` methods

Limit & Offset

Conditional clauses

Pagination

Insert statements

Create or update (Upsert)

Update statements

Incrementing & decrementing

Delete statements

Debugging