Show HN: JinjaSQL – Generate SQL Queries using a Jinja Template | Hacker News Reader

Show HN: JinjaSQL – Generate SQL Queries using a Jinja Template

github.com

77 pointsksri38 comments

Looks interesting!

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.

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.

In clauses are blown up. I have now added that to the documentation - see https://github.com/hashedin/jinjasql#handling-in-clauses

Skipping in clauses altogether - for now, you can handle that using an if condition.

Because we totally needed stored procedures to be re-implemented outside of the database in a templating language. /s

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.

So we've reinvented Tableau and other data analytics packages instead.

Now you're just trolling.

Not at all! Just expressing that we keep showing off reinventions of the wheel.

Groovy has had similar functionality in its standard library for years now, very handy!

Are you refering to this http://groovy.329449.n5.nabble.com/Groovy-and-SQL-tt5710894.... August 2012 mailing list claim about Apache Groovy? There isn't any GROUP BY in that one.

No, I'm refering to the groovy.sql.Sql class: http://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.h...

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.

Can you interpolate table and column names? Those usually aren't amenable to change in prepared statements

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.

What about using prepared statements?

> 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.

[1] https://github.com/hashedin/jinjasql#when-to-use-jinjasql

Sorry, but this doesn't answer my question. Prepared statements have nothing to do with ORMs.

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.

What about using them? Doesn’t look like this will stop you, if that’s what you mean.

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.

This is good. SQL templates are usually much faster for database queries than using ORMs.

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.

I wish laravel had this and an automated SQL debug comment feature.

> In the long run, for readability and maintainability, ORMs win most of the time.

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.

Very nice. I made something similar a while back:

https://www.youtube.com/watch?v=CzwikfCAdws

Looks like a fantastic use of Jinja templates. This is cool.

How does this protect against SQL injection attacks?

It doesn't append the strings blindly.

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.

How is this better than SQLAlchemy Core?

Hmm, no convenience macros for common SQL situations (e.g. building WHERE-AND parts out of arrays)?

I plan to add those macros in due course. Common where/and macros, utilities for date time manipulation are the ones that top the list.

Python 2.x only?

Doesn’t seem to be – https://github.com/hashedin/jinjasql/blob/master/setup.py#L5... – so I don’t know why the README implies that.

It does support 3.x, there are test cases for it as well. I'll fix the readme, thanks for pointing that out!

Updated readme to reflect that 3.x is supported as well.

Reminds me of mybatis.

Reply on news.ycombinator.com