Query builder
Lucid query builder allows you to write and execute SQL queries. It is built on top of Knex.js with few opinionated changes.
We have divided the query builders into following categories
- The standard query builder allows you to construct SQL queries for select, update and delete operations.
- The insert query builder allows you to construct SQL queries for the insert operations.
- The raw query builder let you write and execute queries from a raw SQL string.
Select queries
You can perform select operations by creating a query builder instance using the .query
method.
import Database from '@ioc:Adonis/Lucid/Database'
const users = await Database
.query() // 👈 gives an instance of select query builder
.from('users')
.select('*')
You can also create the query builder instance by directly calling the .from
method.
import Database from '@ioc:Adonis/Lucid/Database'
const users = await Database
.from('users') // 👈 gives an instance of select query builder
.select('*')
Insert queries
The insert query builder exposes the API to insert new rows to the database. You can get an instance of the query builder using the .insertQuery
method.
import Database from '@ioc:Adonis/Lucid/Database'
await Database
.insertQuery() // 👈 gives an instance of insert query builder
.table('users')
.insert({ username: 'virk', email: 'virk@adonisjs.com' })
You can also create the query builder instance by directly calling the .table
method.
await Database
.table('users') // 👈 gives an instance of insert query builder
.insert({ username: 'virk', email: 'virk@adonisjs.com' })
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' },
])
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.
You can create an instance of the raw query builder using the .rawQuery
method. It accepts the SQL string as the first argument and its positional/named bindings as the second argument.
import Database from '@ioc:Adonis/Lucid/Database'
const user = await Database
.rawQuery('select * from users where id = ?', [1])
Extending query builders
You can extend the query builder classes using macros and getters. The best place to extend the query builders is inside a custom service provider.
Open the pre-existing providers/AppProvider.ts
file and write the following code inside the boot
method.
import { ApplicationContract } from '@ioc:Adonis/Core/Application'
export default class AppProvider {
constructor(protected app: ApplicationContract) {}
public async boot() {
const {
DatabaseQueryBuilder
} = this.app.container.use('Adonis/Lucid/Database')
DatabaseQueryBuilder.macro('getCount', async function () {
const result = await this.count('* as total')
return BigInt(result[0].total)
})
}
}
In the above example, we have added a getCount
method on the database query builder
. The method adds a count
function to the query, executes it right away and returns the result back as a BigInt.
Informing TypeScript about the method
The getCount
property is added at the runtime, and hence TypeScript does not know about it. To inform the TypeScript, we will use declaration merging
and add the property to the DatabaseQueryBuilderContract
interface.
Create a new file at path contracts/database.ts
(the filename is not important) and paste the following contents inside it.
declare module '@ioc:Adonis/Lucid/Database' {
interface DatabaseQueryBuilderContract<Result> {
getCount(): Promise<BigInt>
}
}
Test run
Let's try using the getCount
method as follows:
await Database.query().from('users').getCount()
Extending ModelQueryBuilder
Similar to the DatabaseQueryBuilder
, you can also extend the ModelQueryBuilder
as follows.
Runtime code
const {
ModelQueryBuilder
} = this.app.container.use('Adonis/Lucid/Database')
ModelQueryBuilder.macro('getCount', async function () {
const result = await this.count('* as total')
return BigInt(result[0].$extras.total)
})
Extending the type definition
declare module '@ioc:Adonis/Lucid/Orm' {
interface ModelQueryBuilderContract<
Model extends LucidModel,
Result = InstanceType<Model>
> {
getCount(): Promise<BigInt>
}
}
Usage
import User from 'App/Models/User'
await User.query().getCount()
Extending InsertQueryBuilder
Finally you can also extend the InsertQueryBuilder as follows.
Runtime code
const {
InsertQueryBuilder
} = this.app.container.use('Adonis/Lucid/Database')
InsertQueryBuilder.macro('customMethod', async function () {
// implementation
})
Extending the type definition
declare module '@ioc:Adonis/Lucid/Database' {
interface InsertQueryBuilderContract<Result = any> {
customMethod(): Promise<any>
}
}
Usage
import Database from '@ioc:Adonis/Lucid/Database'
await Database.insertQuery().customMethod()