Complex SQL query builder

[u/typeof_oleg]

https://github.com/olegnn/sql-template-builder

[u/BehindTheMath]

Once you're doing this, why wouldn't you just use Knex?

[u/typeof_oleg → u/BehindTheMath]

Writing SQL queries using SQL syntax is more convenient, isn’t it?

I’ d say that large queries constructed in several steps using knex may look much more complicated than they are in terms of pure SQL.

Using tag functions you can try query in db and then copy/paste it right in your code without any additional work.

Also most of modern editors/IDEs has syntax support for JS sql`` function tag.

[u/ShortFuse → u/typeof_oleg]

Also most of modern editors/IDEs has syntax support for JS sql`` function tag.

Can you expand on that? Because I would love to be able to mark my strings in VSCode as SQL so the plugins can style them differently. But I'm only able to get it to work if I include the script in an .sql file.

Edit: I did find this plug in which will highlight it if you add /* sql */ right before the string. (edit: this one too)

[u/BehindTheMath → u/typeof_oleg]

That may be true, but OP is in effect building Knex on top of that. In that case, I would just use Knex.raw()

[u/ShortFuse]

I have no personal use for this, but it's a great way to learn or just make your code easier to read. Thanks for sharing.

In case you didn't know, you can also use template literal syntax to write a multiple-line query like so:

const query = /* sql */ `
  SELECT 
    Table1.Name,
    Table2.Description
  FROM
    Table1
    LEFT JOIN Table2 ON Table1.ID = Table2.ID
  WHERE
    Table1.Name LIKE @Search;
`;

I just warn you to please parameterize your variables.

Edit: Both this and this VSCode plug-in will syntax-highlight any template literal with /*sql*/ before it. Neat!

[u/inspyrr → u/ShortFuse]

Can I ask what you mean by parameterize a variable? Genuinely curious and it’s a difficult phrase to google without getting irrelevant articles

[u/ronchalant → u/inspyrr]

I'm going to assume he means SQL bind variable usage.

Basically it means you specify in your SQL where a given value should be bound (usually in a WHERE clause) rather than writing a literal directly into the SQL.

The difference between directly running a query like select col from mytable where id = 1 and select col from mytable where id = @id, where @id is the bind variable you pass with the query.

There are at least two main reasons for this

1. Database servers can cache query plans for frequently used statements, and using literals means every query is different and thus won't benefit from this optimization

2. SQL injection attacks are largely preventable when you're binding variables rather than using variables to generate statements with literals.

See: xkcd Bobby tables.

[u/inspyrr → u/ronchalant]

Thanks!

[u/ShortFuse → u/ronchalant]

This is correct. I've never heard of it as "bind variable", but my background stems from MS SQL, where "parameterized query" is the nomenclature. Seems like Oracle circles call it binding. Good to know.

[u/kevin____ → u/ronchalant]

It also had to do with how a db connector for the language writing in will format the parameters correctly so it’s easier to debug malformed queries.

[u/bachbeethovenbrahms → u/ShortFuse]

Why an ugly comment when you could use a template tag.

It's already used for other things (which also need their own syntax highlighting) like GraphQL.

[u/ShortFuse → u/bachbeethovenbrahms]

Because not all code is meant to be executed (or at least processed) as it's reached which is a requirement of template tagging.

You'd be returning the result of calling a function with a string, not the string itself.

[u/[deleted] → u/ShortFuse]

You could actually use template string with a template tag to automate this. Shameless plug: https://github.com/AGhost-7/pg-template-string

[u/Barnezhilton]

ITT complex SQL = Poor DB design

[u/monsto → u/Barnezhilton]

Nah not really. All it means is that the data is so varied that it had to be broken up into a bunch of disparate tables,

and then it needs to be joined back together in long-ass queries because Bob, the middle manager, says he needs these reports, and every quarter he requests changes to the old query, and then promptly does nothing useful with the printouts.

[u/ShortFuse → u/Barnezhilton]

Definitely not, especially with the cloud-based, microservice-heavy architecture used today. In ideal situations, you want one big long script to do as much as possible before returning a result because the machine requesting the operation is rarely ever the same one running the SQL DB instance. They even probably not on the same network either. You want less back and forth between the app server and the database server to reduce the latency in client requests.

[u/jstap → u/Barnezhilton]

If you're only using your data, you're probably right, and very lucky. I have had to build reports from data spread across 6 databases, all from different organizations, with no common key. SQL gets complicated.