Query Builder
The Database query builder is the first step towards constructing and executing SQL queries. Instead of writing SQL DSL by hand, you make use of the Javascript API for constructing queries.
By the end of this guide, you will know:
- How to construct and execute SQL queries
- Using different types of query builders for executing insert, select or raw queries
Using the Query Builder
You can get an instance of the query builder using the Database module. For example:
import Database from '@ioc:Adonis/Lucid/Database'
const users = await Database.query().select('*').from('users')
- The
Database.query
method creates a new query builder instance. - The
select
method is used to select the columns. - Finally the
from
method specifies the database table for the query. - The result of the query is always an array of objects, unless the
.first
method is used.
Just like the select
and the from
methods, there are many more methods on the query builder to construct advanced and complex SQL queries.
Types of Query Builders
The Database.query
method creates a query builder instance for selecting, updating or deleting rows. Whereas, to insert new data, you have to make use of the insert query builder
.
Following is the list different query builders instances.
Query builder for selecting, updating or deleting rows.
Database.query().from('users') // Shortcut Database.from('users')
Query builder for inserting new rows.
Database.insertQuery().table('users') // Shortcut Database.table('users')
Raw query builder for executing raw SQL queries. You will learn about raw queries later in this guide.
Database.rawQuery('select * from users;')
Fetching Rows
Fetching rows is as simple as executing a select query using the Database query builder. The result of a query is always an array of objects, even when a single row is returned from the database.
const users = await Database.query().select('*').from('users')
// an array of users
If you always want a single row from the result set, then you can make use of the first
method.
The first
method applies a LIMIT
clause to the query.
const user = await Database.query().select('*').from('users').first()
Inserting Rows
You make use of the insert query builder for inserting new rows to the database. For example:
await Database
.insertQuery() // 👈 gives an instance of insert query builder
.table('users')
.insert({ username: 'virk', email: '[email protected]' })
The return value of the insert
query is dependent on the database server in use.
MySQL and SQLite will return the last inserted row id as an array with just one item. For example:
const [ lastInsertId ] = await Database.table('users').insert({})
For PostgreSQL, MSSQL and Oracle, you can make use of the
returning
method. The returning method can return value for a single column or multiple columns. For example:const [ id ] = await Database .table('users') .returning('id') 👈 .insert({}) // multiple columns const [{ username, id }] = await Database .table('users') .returning(['id', 'username']) 👈 .insert({})
Multi Insert
You can make use of the multiInsert
method in order to insert multiple rows in a single insert query.
MySQL and SQLite only returns the id for the last row and not all the rows.
await Database.table('users').multiInsert([
{ username: 'virk' },
{ username: 'romain' },
])
Updating and Deleting Rows
You can update and delete rows by using the standard query builder. For example:
await Database
.from('users')
.where('username', 'virk')
.update({ account_status: 'verified' })
Or delete
await Database
.from('posts')
.where('slug', 'dummy-post')
.delete()
Executing Raw Queries
Raw queries allows to execute a SQL statement from a string input. This is usually helpful, when you want to execute complex queries that are not supported by the standard query builder.
Unlike the standard query builder response, the response of the rawQuery
is not normalized. You must read the documentation of the underlying npm driver for same.
import Database from '@ioc:Adonis/Lucid/Database'
const user = await Database
.rawQuery('select * from users where id = ?', [1])
The
rawQuery
method accepts a total of two arguments.First argument is the SQL query.
2nd argument is an array of values to substitute the
?
placeholders inside the SQL string. To prevent SQL injection, you must always define values as bindings and do not inline them inside the SQL string. For example:Prone to SQL injectionDatabase.rawQuery('select * from users where id = 1')
Safe from SQL injectionDatabase.rawQuery('select * from users where id = ?', [1])
Aggregates
The aggregate methods like count
, min
, avg
returns an array with the aggregate key and its value. For example:
import Database from '@ioc:Adonis/Lucid/Database'
const total = await Database.query().count('*').from('users')
// SQLITE: [{ "count(*)": 4 }]
// POSTGRESQL: [{ "count": "4" }]
As you can notice, the output of PostgreSQL
and SQLite
is different and hence not predictable. To encounter this behavior, it is recommended to always alias your aggregates.
await Database.query().count('* as total').from('users')
// SQLITE: [{ "total": 4 }]
// POSTGRESQL: [{ "total": "4" }]
What's Next?
This guide introduces you to the concept of Query builder and how to use it inside your application. However, the API surface of the query builder is quite big and we recommend you reading the API docs for same.