Using toNative to show Native SQL | 點燈坊

Sometimes we need to show native SQL for debugging, we can use .toSQL().toNative() to show native SQL.

Version

Knex 0.95.12
PostgreSQL 14.0
MSSQL 2017

PostgreSQL

import Knex from 'knex'

let knex = Knex ({
  client: 'pg',
  connection: {
    host: 'localhost',
    port: 5432,
    user: 'admin',
    password: '12345',
    database: 'DBLab'
  },
  searchPath: ['public']
})

let sql = knex ('articles')
  .insert ({
    'title': 'Title 1',
    'content': 'Content 1'
  }, ['*']).toSQL().toNative()

console.log (sql)

Line 15

let sql = knex ('articles')
  .insert ({
    'title': 'Title 1',
    'content': 'Content 1'
  }, ['*']).toSQL().toNative()

Simple insert with returning whole object. We can use .toSQL().toNative() to know native SQL for PostgreSQL.

MSSQL

import Knex from 'knex'

let knex = Knex ({
  client: 'mssql',
  connection: {
    host: 'mssql-2017',
    port: 1433,
    user: 'sa',
    password: '111111',
    database: 'DBLab'
  }
})

let sql = knex ('articles')
  .insert ({
    'title': 'Title 1',
    'content': 'Content 1'
  }, ['*']).toSQL().toNative()

console.log (sql)

Line 14

let sql = knex ('articles')
  .insert ({
    'title': 'Title 1',
    'content': 'Content 1'
  }, ['*']).toSQL().toNative()

Same Knex insert statement as PostgreSQL, but this time is for MSSQL.

We can see different native SQL between PostgreSQL and MSSQL.

Conclusion

• If we want to know the magic of Knex, we can use toSQL().toNative to show native SQL

Reference

Knex, toSQL().toNative()