Create "where" from object...

[QAnders]

So, thanks a million for this, very useful!

We are currently migrating a lot of queries from knex to use Aurora Data API and as knex is always using objects as "conditions" I put together a little "helper" to be able to create the "where" condition from objects.

The idea is that you create an object which matches the DB table column names and add the values you need for the condition(s).

I am no Node.js talent but seems to do the trick... :)

I couldn't push any new branch so here's the solution I made in snippets!

Code added:

  // Return public methods
  return {
    // Query method, pass config and parameters
    query: (...x) => query(config,...x),
    // Query method from object
    queryFromObject:  (...x) => queryFromObject(config,...x),  // <-- New AW, 2021-01-18

/*
 Create an "and" or "or" where condition from object keys
 Object key names must obviously match the table columns

 queryFromObject(
   'SELECT * FROM table',
   {
     col1: 'val1',
     col2: 'val2'
   },
   'or'
 )

 Will execute query with:
   "SELECT * FROM table WHERE col1='val' or col2='val2'"
*/
const queryFromObject = async function(config,..._args) {
  if (_args[0] && _args[0].toLowerCase().match(/ where /))
    error('queryFromObject must not contain \'where\' in SQL!');
  if (!_args[1]) {
    // There's no condition object... just run a regular query:
    return query(config,..._args);
  }
  // Check if we got an operator, else default to "and"
  const operator = _args[2] || 'and';
  let sqlWhere = Object.keys(_args[1]).map(key => {
    return `${key}=:${key}`;
  });
  sqlWhere = sqlWhere.join().replace(/,/g, ` ${operator} `);
  _args[0] += ` WHERE ${sqlWhere}`;
  return query(config,..._args);
} // end queryFromObject

Tests added under Querying:

describe('querying', () => {

  describe('query', () => {

    const query = dataApiClient.__get__('query')
    const queryFromObject = dataApiClient.__get__('queryFromObject')  // <-- New AW, 2021-01-18

    test('simple query from object', async () => {

      let result = await queryFromObject(config,'SELECT * FROM table',{ id: 3, category: 'Category 2' })

      expect(parameters).toEqual({
        secretArn: 'secretArn',
        resourceArn: 'resourceArn',
        database: 'db',
        sql: 'SELECT * FROM table WHERE id=:id and category=:category',
        parameters: [ { name: 'id', value: { longValue: 3 } }, { name: 'category', value: { stringValue: 'Category 2' } } ]
      })

    })

    test('No "where" in query from object', async () => {

      try {
        let result = await queryFromObject(config,'SELECT * FROM table where id=:id',{ id: 3 })
      } catch(error) {
        expect(error.message).toEqual('queryFromObject must not contain \'where\' in SQL!')
      }

    })

[ffxsam]

I'll defer to @jeremydaly here, but my gut says we should keep the Data API Client as unopinionated as possible, so people can bring their own supplementary tools (like knex, or an ORM). There's certainly nothing wrong with using knex in conjunction with data-api-client.

[QAnders]

So, no, there's no "supplementary tools" it's more like a "helper" function so that I don't have to type in the WHERE condition twice. What I need to do by using parameters (the original code) is something like:

let result = await query(
  'SELECT * FROM table where id=:id and category=:category and whatevs=:whatevs',
  { id: 3, category: 'Category 2', whatevs: 'here' }
);

With my addition you don't have to add the parameters as the "object" adds them so with my "helper" the same code would be:

let result = await queryFromObject(
  'SELECT * FROM table',
  { id: 3, category: 'Category 2', whatevs: 'here' }
);

The result is the exact same between these two, just that I don't have to type the where id=:id and category=:category and whatevs=:whatevs as it is generated from the object itself.

[ffxsam]

I wasn't referring to your code as a supplementary tool. My point was that it might be better to keep data-api-client unopinionated, so people can decide what tools (like knex) to supplement as far as query-building goes.

BTW, you're missing some use cases, e.g. WHERE x IN y, WHERE x IS NOT NULL, WHERE x >= y, etc.. not to mention even more complex cases such as subqueries.

Is there any reason you're ditching knex? As I mentioned, there's nothing wrong with using knex's query-builder in conjunction with data-api-client. I'm doing so myself, and it's a huge time-saver.

But this is Jeremy's thing so I'll let him weigh in. 🙂

[QAnders]

Sorry if I am thick but IMO my addition is "unopinionated" as it has no reference or connection to any other "tools". It's just a "lazy-mans" try to avoid writing the same code twice... :)

I am aware of the missing use-cases, it's just to serve as a simple solution for now. INSERT is not working either of course...

Ideally I'd like to add something more similar to what knexhas (again not as opinionated) but to borrow their idea of having, e.g. .select(), .update(), insert() and .delete() as functions and the same as knex, e.g.:

const result = await dbApiClient('table').select('*', {id: 3}); // ==> 'SELECT * FROM table WHERE id = 3'

const result = await dbApiClient('table').update({name: 'Anders'}, {id: 3}); // ==> 'UPDATE table SET name='Anders' WHERE id = 3'

[ffxsam]

I guess I don't see the point in reinventing the wheel, when knex already exists and is a very extensive query-builder.

[QAnders]

Hmm... I'd just like to make it "convenient"... my use-case is that I'd like to be able to run the Data API only in a AWS Lambda and be able to use it in the "simplest" way possible (and that to me is with as few lines of code as possible). That I accomplish by using the "data object" to also set the column names, the same way knexis doing it.

Sure, knex is awesome at generating SQL but adding knex to my project is yet another module that needs to be loaded and risk messing things up, further knex requires a connection object to work so I have to instantiate knex with the DB connection as well, "stealing" DB connections unnecessarily and I would also have to add the DB params (and password) for the knexconfig. As the Data API SDK supports using Secrets out-of-the-box there is no need for me to add the DB credentials at all in a "pure" Data API solution...

[ffxsam]

You don't need to instantiate a connection in knex. In my code, I'm simply doing this:

import Knex from 'knex';

const knex = Knex({ client: 'pg' });

And then I can freely build queries with it.

[QAnders]

Sorry, and for your question if we are ditching knex, no, we are not but we have a few "high load" Lambdas running large batch jobs that is messing with our Aurora Serverless (PG) and it is dying because it runs out of connections and can't scale up in time because of a large number of INSERT and UPDATE's...

AWS Support guided me to this repo and told me to try and run the "batch loads" using the Data API only and not a native DB connection (=knex)

[QAnders]

You don't need to instantiate a connection in knex. In my code, I'm simply doing this:

import Knex from 'knex';

const knex = Knex({ client: 'pg' });

And then I can freely build queries with it.

Ah, thanks, didn't know that! Then it makes more sense... still a bit of code overload though... ;)

[ffxsam]

What if you shoved all these jobs into SQS, and had Lambda pull from that queue and then insert records from there? Sounds like an architectural issue that I'm thinking could be solved in a creative way using AWS's other services.

But I'm just spitballing randomly here, as I don't know exactly what you're trying to do.

[QAnders]

What if you shoved all these jobs into SQS, and had Lambda pull from that queue and then insert records from there? Sounds like an architectural issue that I'm thinking could be solved in a creative way using AWS's other services.

But I'm just spitballing randomly here, as I don't know exactly what you're trying to do.

Thanks, problem is that even with SQS "offloading" all objects has to be created in sequence as they are related in DB and I need to keep each object (Invoices, Orders, Order responses, etc.) in their own transactions so it's a bit of a challenge... :)

For now, I have created my suggested PR as a function in my own code to use that in turn calls the data-api-client after parsing the SQL. If you don't like the idea I might create a "wrapper" that'll use the data-api-client and if it is any good I might put it onto npm publicly if anyone else see the benefit of it... :)

Thanks for your time!