How can I view the query `knex` builds?

Question

For debugging purposes, I want to see the SQL query knex is executing. For example, I want to view SQL that knex produces for this code:

 knex('statistics')
    .del()
    .where({
        'stats': 'reddit',
    });

Mikael Lepistö · Accepted Answer · 2022-08-10T11:23:29.863

42

https://knexjs.org/guide/interfaces.html#other

knex('statistics')
    .del()
    .where({
        'stats': 'reddit',
    }).toSQL().toNative()

edited Aug 10 '22 at 11:23

answered Apr 30 '18 at 08:56

Mikael Lepistö

18,909
3
68
70

2

To get query string is `toNative().sql` – Hill Dec 08 '21 at 04:45
1

And to get parameter bindings for that query is `toNative().bindings` – Mikael Lepistö Dec 15 '21 at 11:18
This does not return the SQL command as a string, it returns an object. To get the raw SQL as a string, you want `.toString()` or `.toQuery()` (see my answer as it is more comprehensive) – Jeremy Bernier Jul 04 '22 at 22:20
@JeremyBernier No... if you call `.toString()` / `.toQuery()` that is not the query that knex runs when the builder is triggered. Outputted string might be working SQL some times and sometimes it is not. Knex does not do value escaping correctly in every case when rendering string from builder. `.toQuery()` and `.toString()` are aliases to each other. Both should be used only for debugging. `.toSQL().toNative()` returns the templated SQL query string for the used dialect and corresponding bindings. That should be the exact query that is executed by the knex when the builder is triggered. – Mikael Lepistö Jul 05 '22 at 19:20
1

Link is broken. Now both toString() and toNative() are documented in http://knexjs.org/guide/interfaces.html#other. They both seem to be good for debugging purposes. – Tola Jul 27 '22 at 07:37

score 18 · Answer 2 · answered Dec 15 '20 at 18:27

18

knex('statistics')
    .del()
    .where({
        'stats': 'reddit',
    }).toString();

answered Dec 15 '20 at 18:27

Alex Gusev

1,526
3
16
35

I do not think this an improvement over the accepted answer. – greybeard Dec 15 '20 at 21:55
2

Guys, I tried to find the way to get raw SQL. `toString()` is the same way as `.toSQL().toNative()` but shorter. Do you really think that there is one right answer only? – Alex Gusev Dec 16 '20 at 05:00
1

This works in knex 0.21 – James T. Dec 13 '21 at 23:40
2

@greybeard I think it is an improvement – ibodi Jul 18 '22 at 11:45

score 10 · Answer 3 · answered Jul 04 '22 at 22:18

.toString() and .toQuery() return the full SQL query as a string

For your example:

knex('statistics')
    .del()
    .where({
        'stats': 'reddit',
    }).toString();

returns:

delete from "statistics" where "stats" = 'reddit'

Whereas .toSQL().toNative() returns an object:

knex('statistics')
    .del()
    .where({
        'stats': 'reddit',
    }).toSQL().toNative();

{
  sql: 'select * from "books" where "author" = $1',
  bindings: [ 'John Doe' ]
}

Not clear which things the OP wanted, but it's super useful to have these options laid out. — hartshoj, Jun 25 '23 at 19:44

score 4 · Answer 4 · answered Dec 26 '21 at 16:57

To print all queries then passing a debug: true flag on your initialization object will turn on debugging for all queries.

knex({
    client: 'mysql',
    debug: true,
    connection: {
        host: '127.0.0.1',
        port: 3306,
        user: 'root',
        password: '',
        database: ''
    }
})

Ref:https://knexjs.org/#Installation-debug

score 3 · Answer 5 · answered Mar 15 '21 at 11:23

3

In my case toSQL()... does not produce the "parsed" SQL string, only toString() works, I'm not sure if this depends on one's specific usage of the Query Builder.

answered Mar 15 '21 at 11:23

Christof Coetzee

79
6

score 1 · Answer 6 · answered May 23 '22 at 13:37

1

you can use toKnexQuery().toSQL()

console.log(query.toKnexQuery().toSQL())

answered May 23 '22 at 13:37

Ali Faris

17,754
10
45
70

How can I view the query `knex` builds?

6 Answers6

Linked