const sql = 'SELECT * FROM `users`'
if (username) {
sql += ' WHERE `username` = ' + username
}
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.
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.
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.
const sql = 'SELECT * FROM `users`'
if (username) {
sql += ' WHERE `username` = ' + username
}
const query = Database.table('users')
if (username) {
query.where('username', username)
}
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()
}
}
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')
select `id`, `username` from `users`
select * from `users`
Also, you can define aliases
await Database.select('username as uname')
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.
|
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)
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)
await Database
.from('users')
.whereNot('age', '>', 15)
// or
await Database
.from('users')
.whereNot({username: 'foo'})
await Database
.from('users')
.whereIn('id', [1,2,3])
await Database
.from('users')
.whereNotIn('id', [1,2,3])
await Database
.from('users')
.whereNull('deleted_at')
await Database
.from('users')
.whereNotNull('created_at')
await Database.from('users').whereExists(function () {
this.from('accounts').where('users.id', 'accounts.user_id')
})
await Database.from('users').whereNotExists(function () {
this.from('accounts').where('users.id', 'accounts.user_id')
})
await Database
.table('users')
.whereBetween('age',[18,32])
await Database
.table('users')
.whereNotBetween('age',[45,60])
Convenience helper for .where(Database.raw(query))
await Database
.from('users')
.whereRaw('id = ?', [20])
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')
})
Database
.select('*')
.from('users')
.leftJoin('accounts', 'users.id', 'accounts.user_id')
await Database
.select('*')
.from('users')
.leftOuterJoin('accounts', 'users.id', 'accounts.user_id')
await Database
.select('*')
.from('users')
.rightJoin('accounts', 'users.id', 'accounts.user_id')
await Database
.select('*')
.from('users')
.rightOuterJoin('accounts', 'users.id', 'accounts.user_id')
await Database
.select('*')
.from('users')
.outerJoin('accounts', 'users.id', 'accounts.user_id')
await Database
.select('*')
.from('users')
.fullOuterJoin('accounts', 'users.id', 'accounts.user_id')
await Database
.select('*')
.from('users')
.crossJoin('accounts', 'users.id', 'accounts.user_id')
await Database
.select('*')
.from('accounts')
.joinRaw('natural full join table1').where('id', 1)
await Database
.table('users')
.distinct('age')
await Database
.table('users')
.groupBy('age')
await Database
.table('users')
.groupByRaw('age, status')
await Database
.table('users')
.orderBy('id', 'desc')
await Database
.table('users')
.orderByRaw('col NULLS LAST DESC')
groupBy() clause is always required before making use of having() method.
|
await Database
.table('users')
.groupBy('age')
.having('age', '>', 18)
await Database
.table('users')
.offset(11)
.limit(10)
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.
const userId = await Database
.table('users')
.insert({username: 'foo', ...})
// BULK INSERT
const firstUserId = await Database
.from('users')
.insert([{username: 'foo'}, {username: 'bar'}])
Method into
is more readable than table/from
when insert rows to the database.
const userId = await Database
.insert({username: 'foo', ...})
.into('users')
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')
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' })
Delete operations also returns the number of affected rows.
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 removes all the rows from a database and set auto increment id back to 0.
await Database.truncate('users')
Query builder provides a handful of convenient ways to paginate results from the database.
const users = await Database
.from('users')
.forPage(1, 10)
const results = await Database
.from('users')
.paginate(2, 10)
The output of the paginate method is different from the forPage method.
|
{
pages: {
total: 0,
currentPage: 2,
perPage: 10,
lastPage: 0,
},
rows: [{...}]
}
Database transactions are safe operations, which are not reflected in the database until and unless you explicitly commit your changes.
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
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')
})
The Lucid Query Builder exposes the full power of knex’s aggregate methods.
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
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
await Database.from('users').min('age') // returns array
await Database.from('users').min('age as a') // returns array
await Database.from('users').max('age') // returns array
await Database.from('users').max('age as a') // returns array
await Database.from('cart').sum('total') // returns array
await Database.from('cart').sum('total as t') // returns array
await Database.from('cart').sumDistinct('total') // returns array
await Database.from('cart').sumDistinct('total as t') // returns array
await Database.from('users').avg('age') // returns array
await Database.from('users').avg('age as age') // returns array
await Database.from('users').avgDistinct('age') // returns array
await Database.from('users').avgDistinct('age as age') // returns array
Increment the column existing value by 1.
await Database
.table('credits')
.where('id', 1)
.increment('balance', 10)
Opposite of increment
.
await Database
.table('credits')
.where('id', 1)
.decrement('balance', 10)
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
.
const total = await Database
.from('users')
.getCount() // returns number
const total = await Database
.from('users')
.countDistinct('id') // returns number
await Database.from('users').getMin('age') // returns a number
await Database.from('users').getMax('age') // returns number
await Database.from('cart').getSum('total') // returns number
await Database.from('cart').getSumDistinct('total') // returns number
await Database.from('users').getAvg('age') // returns number
await Database.from('users').getAvgDistinct('age') // returns number
The pluck
method will return an array of values for the selected column.
const usersIds = await Database.from('users').pluck('id')
Clone the current query chain for re-usability.
const query = Database
.from('users')
.where('username', 'virk')
.clone()
// later
await query
Returns information for a given column.
const username = await Database
.table('users')
.columnInfo('username')
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')
The Database.raw
method should be used for running raw SQL queries.
await Database
.raw('select * from users where username = ?', [username])
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'])