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

https://github.com/hashedin/jinjasql

by ksri • 10 years ago

77 38 10 years ago

github.com

johnwheeler• 10 years ago

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

seabrookmx• 10 years ago

Python 2.x only?

minitech2• 10 years ago

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.

ksri8• 10 years ago

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

ksri8• 10 years ago

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

cygned• 10 years ago

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.

tkyjonathan• 10 years ago

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

nathancahill• 10 years ago

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• 10 years ago

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.

svisser• 10 years ago

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.

asabil2• 10 years ago

What about using prepared statements?

marcloney• 10 years ago

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

asabil2• 10 years ago

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

minitech2• 10 years ago

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

coderzach• 10 years ago

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.

ksri8• 10 years ago

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.

ksri8• 10 years ago

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.

zubairq• 10 years ago

Very nice. I made something similar a while back:

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

lightlyused• 10 years ago

Reminds me of mybatis.

mhd• 10 years ago

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

ksri8• 10 years ago

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.

cauterized• 10 years ago

How does this protect against SQL injection attacks?

ksri8• 10 years ago

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.

joaomsa• 10 years ago

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

ksri8• 10 years ago

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.

gregopet2• 10 years ago

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

vorg• 10 years ago

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.

gregopet2• 10 years ago

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.

bradmwalker• 10 years ago

How is this better than SQLAlchemy Core?

adambratt• 10 years ago

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

teilo2• 10 years ago

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.

toomuchtodo2• 10 years ago

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

anshum4n• 10 years ago

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.

ksri8• 10 years ago

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.

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

Related Stories