12 ms·
Show HN: JinjaSQL – Generate SQL Queries using a Jinja Template
- lightlyused 10y agoReminds me of mybatis.
- mhd 10y agoHmm, no convenience macros for common SQL situations (e.g. building WHERE-AND parts out of arrays)?
- ksri 10y agoI 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 10y agoHow does this protect against SQL injection attacks?
- ksri 10y agoIt 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 10y agoCan you interpolate table and column names? Those usually aren't amenable to change in prepared statements
- ksri 10y agoYou 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.
- johnwheeler 10y agoLooks like a fantastic use of Jinja templates. This is cool.
- seabrookmx 10y agoPython 2.x only?
- minitech 10y agoDoesn’t seem to be – https://github.com/hashedin/jinjasql/blob/master/setup.py#L56 https://github.com/hashedin/jinjasql/blob/master/setup.py#L5... – so I don’t know why the README implies that.
- ksri 10y agoIt does support 3.x, there are test cases for it as well. I'll fix the readme, thanks for pointing that out!
- ksri 10y agoUpdated readme to reflect that 3.x is supported as well.
- cygned 10y agoLooks 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 10y agoThis is good. SQL templates are usually much faster for database queries than using ORMs.
- nathancahill 10y agoI'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.
- svisser 10y 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.
- traviscj 10y agoMy 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 10y agoI wish laravel had this and an automated SQL debug comment feature.
- asabil 10y agoWhat about using prepared statements?
- marcloney 10y 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 https://github.com/hashedin/jinjasql#when-to-use-jinjasql
- asabil 10y agoSorry, but this doesn't answer my question. Prepared statements have nothing to do with ORMs.
- jlogsdon 10y agoThis 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.
- minitech 10y agoWhat about using them? Doesn’t look like this will stop you, if that’s what you mean.
- coderzach 10y agoPrepared 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.
- ksri 10y agoIf 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.
- zubairq 10y agoVery nice. I made something similar a while back: https://www.youtube.com/watch?v=CzwikfCAdws https://www.youtube.com/watch?v=CzwikfCAdws
- gregopet 10y agoGroovy has had similar functionality in its standard library for years now, very handy!
- vorg 10y agoAre you refering to this http://groovy.329449.n5.nabble.com/Groovy-and-SQL-tt5710894.html#a5710895 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.
- gregopet 10y agoNo, I'm refering to the groovy.sql.Sql class: http://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html 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 10y agoHow is this better than SQLAlchemy Core?
- adambratt 10y agoBecause we totally needed stored procedures to be re-implemented outside of the database in a templating language. /s
- teilo 10y agoThat'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 10y agoSo we've reinvented Tableau and other data analytics packages instead.
- teilo 10y agoNow you're just trolling.
- toomuchtodo 10y agoNot at all! Just expressing that we keep showing off reinventions of the wheel.
- anshum4n 10y agoWould 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 10y agoIn 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.