Database

The database module exposes the APIs to interact with the SQL databases. You can import the module as follows:

import Database from '@ioc:Adonis/Lucid/Database'

Methods/Properties

Following is the list of methods/properties available on the database module.

connection

Returns the query client for a given connection. Uses the default connection, when no explicit connection name is defined.

Database.connection()

// named connection
Database.connection('pg')

You can also get the query client instance for a specific mode.

Database.connection('pg', { mode: 'write' })

// Write queries are not allowed in read mode
Database.connection('pg', { mode: 'read' })

beginGlobalTransaction

Begin a global transaction. All queries after beginning the global transaction will be executed within the transaction.

await Database.beginGlobalTransaction()

// for a named connection
await Database.beginGlobalTransaction('pg')

commitGlobalTransaction

Commit a previously created global transaction

await Database.commitGlobalTransaction()
await Database.commitGlobalTransaction('pg')

rollbackGlobalTransaction

Rollbacks a previously created global transaction

await Database.rollbackGlobalTransaction()
await Database.rollbackGlobalTransaction('pg')

report

Returns the health check report for all the registered connections.

const report = await Database.report()

console.log(report.name)
console.log(report.health.healthy)

query

Alias for the client.query method.

Database.query()

insertQuery

Alias for the client.insertQuery method.

Database.insertQuery()

modelQuery

Alias for the client.modelQuery method.

import User from 'App/Models/User'
const query = Database.modelQuery(User)

rawQuery

Alias for the client.rawQuery method.

await Database
  .rawQuery('select * from users where id = ?', [1])

knexQuery

Alias for the client.knexQuery method.

Database.knexQuery()

knexRawQuery

Alias for the client.knexRawQuery method.

Database
  .knexRawQuery('select * from users where id = ?', [1])

ref

The ref method allows you to reference a database column name as a value. For example:

Database
  .from('users')
  .where('users.id', '=', Database.ref('user_logins.user_id'))

raw

The raw method creates an instance of the RawBuilder . This query is meant to be used as a reference inside another query.

What is the difference between rawQuery and raw?

You can execute the query created using rawQuery method. Whereas, the query created using raw method can only be passed as a reference.

Database
  .from('users')
  .select('*')
  .select(
    Database
      .raw('select "ip_address" from "user_logins" where "users.id" = "user_logins.user_id" limit 1')
      .wrap('(', ')')
  )

from

A shortcut method to get an instance of the Query builder for the primary connection.

Database.from('users')
// Is same as
Database.connection().from('users')

table

A shortcut method to get an instance of the Insert Query builder for the primary connection.

Database.table('users')
// Is same as
Database.connection().table('users')

transaction

Alias for the client.transaction method.

await Database.transaction()

prettyPrint

A helper method to pretty print the query log emitted as db:query event.

import Event from '@ioc:Adonis/Core/Event'
Event.on('db:query', Database.prettyPrint)

hasHealthChecksEnabled

A boolean to know if health checks is enabled for at least one connection or not.

console.log(Database.hasHealthChecksEnabled)

primaryConnectionName

Returns the name of the default/primary connection name defined inside the config/database file.

console.log(Database.primaryConnectionName)

manager

Returns reference to the connections manager

console.log(Database.manager)