strapi + knex to execute complex Queries

Question

How do I get the knex object to execute custom or complex queries within my strapi service?

My Strapi version has the strapi-hook-knex and strapi-hook-bookshelf installed but when I run qb.raw it is an undefined object.

This is to run queries like this:

qb.select(knex.raw('.... ?? )', '...'))

Answer 1

You will find it in strapi.connections.default

default if you don't change your connection name. If you did you will have to replace default by your connection name.

Answer 2

Took me awhile to pin down exact syntax for this. Here it is in case helpful to others. Could not have gotten there without Jim LAURIE's answer.

module.exports = {
  async findCustom(ctx) {
    const rawBuilder = strapi.connections.default.raw(
      "select field1 from mytable where field1 = 'x'"
    );
    const resp = await rawBuilder.then();
    return resp.rows;
  }
}

Answer 3

in strapi V4 use this:

await strapi.db.connection.select("*").from("demos")

Answer 4

With Strapi v4 use:

strapi.db.connection.raw 

instead of

strapi.connections.default.raw

Answer 5

console.log(strapi.db.connection.raw("SELECT COUNT(*) FROM public.mytable").then(r => { console.log(r) }, error => {console.log(error)}))