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.
We recommend using this method only during the tests.
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)