Debugging

You can debug SQL queries by first enabling the debug mode and then listen for the db:query event to get notified as SQL queries are executed.

The debug mode can be enabled globally for a database connection by setting the debug flag to true inside the config/database.ts file. For example:

import env from '#start/env'
import { defineConfig } from '@adonisjs/lucid'

const dbConfig = defineConfig({
  connection: 'postgres',
  connections: {
    postgres: {
      client: 'pg',
      connection: {
        host: env.get('DB_HOST'),
        port: env.get('DB_PORT'),
        user: env.get('DB_USER'),
        password: env.get('DB_PASSWORD'),
        database: env.get('DB_DATABASE'),
      },
      debug: true
    },
  },
})

Or, you can enable it for an individual query using the debug method on the query builder.

db
  .query()
  .select('*')
  .debug(true)

db
  .insertQuery()
  .debug(true)
  .insert({})

db
  .rawQuery('select * from users')
  .debug(true)

Pretty printing debug queries

Once the debugging has been enabled you can set the prettyPrintDebugQueries flag to true within the config/database.ts file.

This flag will register an event listener for the db:query event and will print the SQL queries to the console.

import env from '#start/env'
import { defineConfig } from '@adonisjs/lucid'

const dbConfig = defineConfig({
  prettyPrintDebugQueries: true,
  connection: 'postgres',
  connections: {
    postgres: {
      client: 'pg',
      connection: {
        // ...
      },
      debug: true
    },
  },
})

Manually listening for the event

If you do not want to pretty print SQL queries and write them to the console, then you can self listen for the db:query event and self handle the treatment of debug logs.

In the following example, we use the application logger to log the queries.

import emitter from '@adonisjs/core/services/emitter'

emitter.on('db:query', function (query) {
  logger.debug(query)
})