strapi + knex to execute complex Queries

user_484865 0
Viewed 4038

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('.... ?? )', '...'))
strapi
5 Answers

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.

user_92081660

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;
  }
}
user_20111900

in strapi V4 use this:

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

With Strapi v4 use:

strapi.db.connection.raw

instead of

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