Pure SQL "words" not requiring any reference to the parameters block

[misner]

Hi Jeremy,

Have been using your wrapper for the past weeks, and it makes working with Aurora serverless a breeze (much easier than native use) ! thanks so much

However, I read the docs and the numerous examples, where you stress the fact some things must be "done" in a certain way (for example the ordering of the parameters), and i could not find a clear idea on the following issue:

• what if one of the parameter is "handled" by a pure SQL command and not the usual =, > ... example: check out in my example below the following lines : author_email IS NOT NULL article_publication_date >= CURDATE() - INTERVAL 180 DAY

  I mean I can't put these "pure sql" words outside the sql statement, so is it OK to leave it like my example ?

• as a consequence of the point above : what about the impact on the rigidity of the "ordering of parameters" you warn about ? Indeed now, as you can see on the example below I end up having 5 constraints/lines inside the WHERE ...but only 2 in the parameters block { } so is this "imbalance" (5 vs 2) important? will it break things? To be as safe as possible I decided to order the 5 constraints inside the WHERE clause like this : start by writing with the 2 constraints where you have something matching inside the parameters block { } and only after this add all the pure sql stuff (IS NULL, CURDATE() - INTERVAL 180 DAY...) .

  What do you think ? Will it enable me to dodge the "ordering issue" you mention here ?

---

EXAMPLE

let existing_author_from_db_query = await data.query(     
      `
        SELECT
          author_email,
          author_full_name,
          author_first_name,
          author_last_name
        FROM 
          results
        WHERE
          status = :status AND
          url_domain = :url_domain AND
          author_email IS NOT NULL AND
          author_full_name IS NOT NULL AND
          article_publication_date >= CURDATE() - INTERVAL 180 DAY
      `,
      {
        status: process.env.CONTEXT === "production" ? "prod" : "dev",         
        url_domain: article_domain,
      }          
    );

What would you recommend in terms of how to use your wrapper in such a case ?

Thanks so much for any help!

[davegariepy]

I think the pure SQL stuff is just that. If it’s a valid MySQL 5.6 keyword it should work.

For the passed parameters, from what I understand they need to be passed into the parameter object in the same order that they appear in the SQL query. If a parameter needs to be used twice I think that means it needs to be included in the passed parameter object twice, therefore maintaining the corresponding order to the SQL query. I haven’t actually tested that out though.

[misner]

Makes sense, thanks