
Show HN: JinjaSQL – Generate SQL Queries using a Jinja Template - ksri
https://github.com/hashedin/jinjasql
======
cygned
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.

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

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

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

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

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

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

~~~
teilo
Now you're just trolling.

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

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

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

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

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.

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

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

------
asabil
What about using prepared statements?

~~~
marcloney
> 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](https://github.com/hashedin/jinjasql#when-to-use-jinjasql)

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

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

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

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

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

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

------
zubairq
Very nice. I made something similar a while back:

[https://www.youtube.com/watch?v=CzwikfCAdws](https://www.youtube.com/watch?v=CzwikfCAdws)

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

------
cauterized
How does this protect against SQL injection attacks?

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

------
bradmwalker
How is this better than SQLAlchemy Core?

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

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

------
seabrookmx
Python 2.x only?

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

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

------
lightlyused
Reminds me of mybatis.

