extracting data from MariaDB using a raw KNEX statement-CodePudding

Former sequelize person here.

What is the best method to extract data from MariaDB using a raw Knex statement? Here are the two methods i have tried:

MariaDb:

   SELECT    JSON_OBJECT(
                 ...........
              )  'JSON_OBJECT'

jscript to interpret results:

for ( tmp of result[0] ) {   console.log (JSON.parse(tmp.JSON_OBJECT)) ; }

and this MariaDb:

     SELECT    JSON_ARRAYAGG(
                  JSON_OBJECT(
                      ..........
                  )
                )  'JSON_ARRAYAGG'

jscript:

for ( tmp of JSON.parse(result[0][0]['JSON_ARRAYAGG'] )) { console.log(tmp)  ;  }

Both of these methods work, but there may be a much cleaner way rather than using JSON.parse.

Suggestions?

note: i certainly understand there is a controversy using raw - i have a number of very large sql statements that were in a previous application, and i would rather (for now) just use them as is rather than rewrite them in pure Knex.

CodePudding user response：

To extract data from a MariaDB database using Knex, you can use the .select() method on a Knex query builder object. This method allows you to specify the columns that you want to retrieve from the database, as well as any conditions or filters using the .where() method.

CodePudding user response：

SQL databases are designed to return rows of columns; any query where you route everything through json instead is not going to be "the best method" you ask for.

Your first try at least uses rows, so is better than the second.

That said, if you are just modifying existing code to work with Knex rather than rewriting it, whatever requires minimum changes will lead to fewer bugs and should be preferred.