Hi Rajeeb!

Console logging a call to toSQL() as you have should work, you should be seeing something like the below in your console.

However, there is a nicer way to do this, using pretty printed query logging. Within your config/database.ts, set prettyPrintDebugQueries: true.

const dbConfig = defineConfig({
  prettyPrintDebugQueries: true, // 👈
  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'),
      },
      migrations: {
        naturalSort: true,
        paths: ['database/migrations'],
      },
    },
  },
})

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
config
database.ts

Then, you can either globally enable query logging, by adding a debug: true to your driver object.

const dbConfig = defineConfig({
  prettyPrintDebugQueries: true,
  connection: 'postgres',
  connections: {
    postgres: {
      debug: true, // 👈
      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'),
      },
      migrations: {
        naturalSort: true,
        paths: ['database/migrations'],
      },
    },
  },
})

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
config
database.ts

Or, you can enable this this on a per-query basis by adding debug(true) to the query.

await Collection.query().where('slug', params.slug).debug(true).firstOrFail()

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

With that set, you should see something like below in your console

Now, unfortunately, this pretty print can't be logged to a file. You can still log the query to a file though. Within your config/logger, set the non-production logger target to a file at the destination you'd like. If that's a file in storage, it'd look something like:

const loggerConfig = defineConfig({
  default: 'app',

  loggers: {
    app: {
      enabled: true,
      name: env.get('APP_NAME'),
      level: env.get('LOG_LEVEL'),
      transport: {
        targets: targets()
          // 👇
          .pushIf(!app.inProduction, targets.file({ destination: './storage/local.log' }))
          .pushIf(app.inProduction, targets.file({ destination: 1 }))
          .toArray(),
      },
    },
  },
})

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
config
logger.ts

Then, add a global listener for the query within a preload.

node ace make:preload events

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

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

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
start
events.ts

With this all your logs, including queries, will be written to the file at the location you've specified! Hope this helps!! 😊