Introducing JinjaSQL - Generate SQL Using Jinja Templates | HashedIn

Introducing JinjaSQL - Generate SQL Using Jinja Templates

Technology - 03 Jan 2017
Harish Thyagarajan

JinjaSQL is our new open source library to generate SQL using a Jinja template.

Why JinjaSQL?

At HashedIn, we use Django pretty extensively. The Django ORM is great for most of the use cases, but there are times when you just need to write a raw SQL query and bypass the ORM altogether. The most common use cases are reports and listing pages that need complex joins.

When you hit a 5% use case that requires the expressiveness and power of raw sql query, it is unlikely that your query is a simple one liner. Had it been, you could have very well written it using django orm.

For those use cases, JinjaSQL helps you maintain the queries in an external template file. You can put in place holder variables, add if/else conditions, use macros and all the power that is available to a regular Jinja template. You don’t have to manually track your bind parameters – JinjaSQL tracks them and binds them appropriately.

Preventing SQL Injection

Templates are not a new idea, but they haven’t been popular because they are vulnerable to SQL Injection. JinjaSQL never inserts values directly into the query. Instead, it gives you the generated SQL query, and a list of bind parameters. It is then up to you to use them to execute the query.

Here’s an example –

If you execute this template using plain-old Jinja2, you’d get :

With JinjaSQL, you get back two things,

and a list of bind parameters:

The list of bind parameters can be strings, integers or even python datetime objects.

Try out JinjaSQL!

Try out JinjaSQL in your projects. If you have questions/comments – create an issue in GitHub. View discussion on HackerNews.

Free tag for commerce

E-book on Digital Business Transformation