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

Looks interesting!<p>When we switched to Clojure (from nodejs), we decided against any kind of ORM and use HugSQL which allows us to write &quot;plain SQL&quot; 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).<p>Couldn&#x27;t immediately think of other cases, but that one example sounds useful for this.

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

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

Can you interpolate table and column names? Those usually aren&#x27;t amenable to change in prepared statements

What about using prepared statements?

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

Very nice. I made something similar a while back:<p><a href="https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=CzwikfCAdws" rel="nofollow">https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=CzwikfCAdws</a>

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

How does this protect against SQL injection attacks?

How is this better than SQLAlchemy Core?

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

Python 2.x only?

Reminds me of mybatis.