You are viewing the legacy version of AdonisJS. Visit https://adonisjs.com for newer docs. This version will receive security patches until the end of 2021.

Query builder

AdonisJs query builder gives you a unified syntax to interact with SQL databases using Javascript methods. This guide is a reference to all the available methods on query builder.

Checkout database introduction guide to check the list of supported databases, configuration options and debugging queries.

Introduction

Writing SQL queries can be tedious in so many ways, even if you are good with SQL.

Let’s imagine you write all of your queries for MySQL, and after some time your manager asks you to migrate everything to PostgreSQL. Now you have to re-write/amend your MySQL queries to make sure they work well with PostgreSQL.

Another issue can be of building incremental queries with conditional blocks.

Without Query Builder
const sql = 'SELECT * FROM `users`'

if (username) {
  sql += ' WHERE `username` = ' + username
}
With Query Builder
const query = Database.table('users')

if (username) {
  query.where('username', username)
}

Basic example

Let’s review a basic example of working with the query builder by chaining different methods.

const Database = use('Database')

class UserController {

  async index (request, response) {
    return await Database
      .table('users')
      .where('username', 'john')
      .first()
  }

}

Selects

The select method defines the fields to be selected for a given query.

await Database.select('id', 'username').from('users')
// or
await Database.select('*').from('users')
SQL Output
select `id`, `username` from `users`
select * from `users`

Also, you can define aliases

await Database.select('username as uname')

Where Clauses

Query builder offers a bunch of dynamic methods to add where clauses. Also, it supports sub-queries by passing a closure or another query instead of the actual value.

Also make sure to reference the knex documentation

Passing undefined to the where clause causes an error during SQL compilation. Make sure that dynamic values are not undefined before passing them.

where

const users = await Database.from('users').where('id', 1)
// Or
const users = await Database.from('users').where({ id: 1 })

Also, you can define the comparison operator to the where clause.

const adults = await Database
  .from('users')
  .where('age', '>', 18)

where ( with callback )

You can also add a callback to the where clause. Callback outputs a little different SQL query by grouping all the where clauses.

await Database.from('users').where(function () {
  this.where('id', 1)
})
select * from `users` where (`id` = 1)

whereNot

await Database
  .from('users')
  .whereNot('age', '>', 15)

// or
await Database
  .from('users')
  .whereNot({username: 'foo'})

whereIn

await Database
  .from('users')
  .whereIn('id', [1,2,3])

whereNotIn

await Database
  .from('users')
  .whereNotIn('id', [1,2,3])

whereNull

await Database
  .from('users')
  .whereNull('deleted_at')

whereNotNull

await Database
  .from('users')
  .whereNotNull('created_at')

whereExists

await Database.from('users').whereExists(function () {
  this.from('accounts').where('users.id', 'accounts.user_id')
})

whereNotExists

await Database.from('users').whereNotExists(function () {
  this.from('accounts').where('users.id', 'accounts.user_id')
})

whereBetween

await Database
  .table('users')
  .whereBetween('age',[18,32])

whereNotBetween

await Database
  .table('users')
  .whereNotBetween('age',[45,60])

whereRaw

Convenience helper for .where(Database.raw(query))

await Database
  .from('users')
  .whereRaw('id = ?', [20])

Joins

innerJoin

await Database
  .table('users')
  .innerJoin('accounts', 'user.id', 'accounts.user_id')

Also, you can pass a closure to construct the join.

await Database
  .table('users')
  .innerJoin('accounts', function () {
    this
      .on('users.id', 'accounts.user_id')
      .orOn('users.id', 'accounts.owner_id')
  })

leftJoin

Database
  .select('*')
  .from('users')
  .leftJoin('accounts', 'users.id', 'accounts.user_id')

leftOuterJoin

await Database
  .select('*')
  .from('users')
  .leftOuterJoin('accounts', 'users.id', 'accounts.user_id')

rightJoin

await Database
  .select('*')
  .from('users')
  .rightJoin('accounts', 'users.id', 'accounts.user_id')

rightOuterJoin

await Database
  .select('*')
  .from('users')
  .rightOuterJoin('accounts', 'users.id', 'accounts.user_id')

outerJoin

await Database
  .select('*')
  .from('users')
  .outerJoin('accounts', 'users.id', 'accounts.user_id')

fullOuterJoin

await Database
  .select('*')
  .from('users')
  .fullOuterJoin('accounts', 'users.id', 'accounts.user_id')

crossJoin

await Database
  .select('*')
  .from('users')
  .crossJoin('accounts', 'users.id', 'accounts.user_id')

joinRaw

await Database
  .select('*')
  .from('accounts')
  .joinRaw('natural full join table1').where('id', 1)

Ordering and Limits

distinct

await Database
  .table('users')
  .distinct('age')

groupBy

await Database
  .table('users')
  .groupBy('age')

groupByRaw

await Database
  .table('users')
  .groupByRaw('age, status')

orderBy(column, [direction=asc])

await Database
  .table('users')
  .orderBy('id', 'desc')

orderByRaw(column, [direction=asc])

await Database
  .table('users')
  .orderByRaw('col NULLS LAST DESC')

having(column, operator, value)

groupBy() clause is always required before making use of having() method. 
await Database
  .table('users')
  .groupBy('age')
  .having('age', '>', 18)

offset/limit(value)

await Database
  .table('users')
  .offset(11)
  .limit(10)

Inserts

Insert operation returns the id of the inserted row. In the case of bulk inserts, the id of the first record is returned, and it is more of a limitation with MYSQL itself. LAST_INSERT_ID.

insert(values)

const userId = await Database
  .table('users')
  .insert({username: 'foo', ...})

// BULK INSERT
const firstUserId = await Database
  .from('users')
  .insert([{username: 'foo'}, {username: 'bar'}])

into(tableName)

Method into is more readable than table/from when insert rows to the database.

const userId = await Database
  .insert({username: 'foo', ...})
  .into('users')

PostgreSQL only

For PostgreSQL, you have to define the returning column explicitly. All other database clients ignore this statement.

const userId = await Database
  .insert({ username: 'virk' })
  .into('users')
  .returning('id')

Updates

All update operations returns the number of affected rows.

const affectedRows = await Database
  .table('users')
  .where('username', 'tutlage')
  .update('lastname', 'Virk')

Pass an object for multiple columns.

const affectedRows = await Database
  .table('users')
  .where('username', 'tutlage')
  .update({ lastname: 'Virk', firstname: 'Aman' })

Deletes

Delete operations also returns the number of affected rows.

delete

Also, you can make use of del(), since delete is a reserved keyword in Javascript.

const affectedRows = await Database
  .table('users')
  .where('username', 'tutlage')
  .delete()

truncate

Truncate removes all the rows from a database and set auto increment id back to 0.

await Database.truncate('users')

Pagination

Query builder provides a handful of convenient ways to paginate results from the database.

forPage(page, [limit=20])

const users = await Database
  .from('users')
  .forPage(1, 10)

paginate(page, [limit=20])

const results = await Database
  .from('users')
  .paginate(2, 10)
The output of the paginate method is different from the forPage method.
Output
{
  pages: {
    total: 0,
    currentPage: 2,
    perPage: 10,
    lastPage: 0,
  },
  rows: [{...}]
}

Database Transactions

Database transactions are safe operations, which are not reflected in the database until and unless you explicitly commit your changes.

beginTransaction

The beginTransaction method returns the transaction object, which can be used to perform any queries.

const trx = await Database.beginTransaction()
await trx.insert({username: 'virk'}).into('users')

trx.commit() // insert query will take place on commit
trx.rollback() // will not insert anything

transaction

Also, you can wrap your transactions inside a callback. The major difference is, you do not have to call commit or `rollback manually. If any of your queries throws an error, the transaction rolls back automatically. Otherwise, it is committed.

await Database.transaction(async (trx) => {
  await trx.insert({username: 'virk'}).into('users')
})

Aggregates

The Lucid Query Builder exposes the full power of knex’s aggregate methods.

count()

const count = await Database
  .from('users')
  .count()                                      // returns array

const total = count[0]['count(*)']              // returns number

// COUNT A COLUMN
const count = await Database
  .from('users')
  .count('id')                                  // returns array

const total = count[0]['count("id")']           // returns number

// COUNT COLUMN AS NAME
const count = await Database
  .from('users')
  .count('* as total')                          // returns array

const total = count[0].total                    // returns number

countDistinct

The countDistinct is same as count, but adds distinct expression.

const count = await Database
  .from('users')
  .countDistinct('id')                          // Returns array

const total = count[0]['count(distinct "id")']  // returns number

min

await Database.from('users').min('age')         // returns array
await Database.from('users').min('age as a')    // returns array

max

await Database.from('users').max('age')         // returns array
await Database.from('users').max('age as a')    // returns array

sum

await Database.from('cart').sum('total')        // returns array
await Database.from('cart').sum('total as t')   // returns array

sumDistinct

await Database.from('cart').sumDistinct('total')      // returns array
await Database.from('cart').sumDistinct('total as t') // returns array

avg

await Database.from('users').avg('age')         // returns array
await Database.from('users').avg('age as age')  // returns array

avgDistinct

await Database.from('users').avgDistinct('age')         // returns array
await Database.from('users').avgDistinct('age as age')  // returns array

increment

Increment the column existing value by 1.

await Database
  .table('credits')
  .where('id', 1)
  .increment('balance', 10)

decrement

Opposite of increment.

await Database
  .table('credits')
  .where('id', 1)
  .decrement('balance', 10)

Aggregate Helpers

Query Builder also extends knex’s query aggregates with helpful shortcut methods for common aggregate queries.

Helper methods end the query builder chaining and return a value. All helpers accept a column name to be used for aggregation. When possible Lucid will choose a default for the column name. Some methods, such as sum(), require a column name.

The knex query builder underlying Lucid defines methods count(), countDistinct(), avg(), avgDistinct(), sum(), sumDistinct(), min(), and max(). To avoid confusion and naming collisions, Lucid prefixes its aggregate helper methods with get.

getCount(columnName = '*')

const total = await Database
  .from('users')
  .getCount()                                   // returns number

getCountDistinct(columnName)

const total = await Database
  .from('users')
  .countDistinct('id')                          // returns number

getMin(columnName)

await Database.from('users').getMin('age')      // returns a number

getMax(columnName)

await Database.from('users').getMax('age')      // returns number

getSum(columnName)

await Database.from('cart').getSum('total')     // returns number

getSumDistinct(columnName)

await Database.from('cart').getSumDistinct('total')   // returns number

getAvg(columnName)

await Database.from('users').getAvg('age')      // returns number

getAvgDistinct(columnName)

await Database.from('users').getAvgDistinct('age')      // returns number

Helpers

pluck(column)

The pluck method will return an array of values for the selected column.

const usersIds = await Database.from('users').pluck('id')

first

The first method adds a limit 1 clause to the query.

await Database.from('users').first()

clone

Clone the current query chain for re-usability.

const query = Database
  .from('users')
  .where('username', 'virk')
  .clone()

// later
await query

columnInfo

Returns information for a given column.

const username = await Database
  .table('users')
  .columnInfo('username')

Sub queries

const subquery = Database
  .from('accounts')
  .where('account_name', 'somename')
  .select('account_name')

const users = await Database
  .from('users')
  .whereIn('id', subquery)
select * from `users` where `id` in (select `account_name` from `accounts` where `account_name` = 'somename')

Raw Queries

The Database.raw method should be used for running raw SQL queries.

await Database
  .raw('select * from users where username = ?', [username])

Closing connection

The database connections can be closed by calling the close method. By default this method all close all open database connections. To close selected connections, make sure to pass an array of connection names.

Database.close() // all

Database.close(['sqlite', 'mysql'])