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.

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