When we switched to Clojure (from nodejs), we decided against any kind of ORM and use HugSQL which allows us to write "plain SQL" in .sql files while still using parameters.
I'll take the bait. It's easy to write a slow SQL query, and equally easy to write a fast ORM query. In the long run, for readability and maintainability, ORMs win most of the time. I've rarely found a slow ORM query that couldn't be fixed without resorting to SQL (half the time it's bad indexes, half the time it's doing something un-performant in the ORM).
The few times when I do need to force a certain SQL syntax that the ORM won't give me, I drop down to SQL. But doing that doesn't magically make all queries faster.
My favorite trick is binding a hook in unit test modules that basically runs an "explain q" for every query "q", and asserts that it found an appropriate index to use. Then as long as your unit tests exercise all queries your app needs, you know that your database indices are appropriate for your app's usages.
> While JinjaSQL can handle insert/update statements, you are better off using your ORM to handle such statements. JinjaSQL is mostly meant for dynamic select statements that an ORM cannot handle as well.
This library doesn't run SQL, its only a template engine that gives you back a parameterized query and parameter array back. It's up to you to actually use them with an adapter, which means you can use prepared statements.
Prepared statements can't have things like dynamic columns. I.e. imagine the user specifies a list of fields they want to see. Although, in that case you still probably want to lookup that the field exists.
If you have dynamic table or column names, you can use the |sqlsafe filter. The library will not bind the values in that case, but you've to be sure there's no sql injection in there.
It doesn't prevent you from using them. You simply get a sql statement with appropriate place holders. You can pass that sql statement to your database library and it will do the right thing - including binding the parameters and creating prepared statements as necessary.
Any variables are automatically replaced by placeholder strings. You also get an array of bind parameters. You then use your database library to bind the parameters into the placeholders.
You can, the tool won't prevent you from doing so. It does however give you the |sqlsafe filter. Using that filter will append the value directly. You can use it to insert dynamic table or column names. Of course you've to be sure those table or column names are safe from sql injection.
It allows you to simply write SQL in a GString using placeholders that get replaced with parameters before being sent to the database, here's an example from the Javadoc:
That's a rather short-sighted comment. We use similar functionality to build an end-user reporting system which queries multiple DBs, many of which are part of vendor applications. Trusted users write the SQL, parameterize it with django-templates. Users enter parameters in widgets. SQL is generated using the templates, executed on the target DB with RO credentials, and returned in a grid/csv/excel/json.
Would be great if you can put in some examples where we can optimise parametric queries using this. One common instance is IN queries where if the number of params for IN clause is small, it might make sense to blow it up into multiple equals clauses, allowing better use of indexes. MySQL query optimiser is horrible with IN clauses and causes table-scans. I have seen table scans and skipping other indexes even when IN clause has zero cardinality (in which case its better to just remove it altogether).
Couldn't immediately think of other cases, but that one example sounds useful for this.
38 comments
[ 4.2 ms ] story [ 87.8 ms ] threadWhen we switched to Clojure (from nodejs), we decided against any kind of ORM and use HugSQL which allows us to write "plain SQL" in .sql files while still using parameters.
The few times when I do need to force a certain SQL syntax that the ORM won't give me, I drop down to SQL. But doing that doesn't magically make all queries faster.
It depends. Anyone working on that code now needs to learn the ORM, the SQL and how to let the ORM generate that SQL for anything beyond the basics.
[1] https://github.com/hashedin/jinjasql#when-to-use-jinjasql
https://www.youtube.com/watch?v=CzwikfCAdws
Any variables are automatically replaced by placeholder strings. You also get an array of bind parameters. You then use your database library to bind the parameters into the placeholders.
It allows you to simply write SQL in a GString using placeholders that get replaced with parameters before being sent to the database, here's an example from the Javadoc:
sql.execute "insert into PROJECT (id, name, url) values ($map.id, $map.name, $map.url)"
Works great with Groovy's multiline strings.
Couldn't immediately think of other cases, but that one example sounds useful for this.
Skipping in clauses altogether - for now, you can handle that using an if condition.