Introducing JinjaSQL – Generate SQL Using Jinja Templates

Harish Thyagarajan

24 Jul 2018

https://static.hashedin.com/wp-content/uploads/2018/08/Harish-Profile-Pic-150x150.jpg

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

 

Why should you use JinjaSQL?

At HashedIn, Django is used extensively. The Django ORM is great for most of the use cases, however, 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.

 

For those use cases, JinjaSQL helps you maintain the queries in an external template file. You can put in placeholder 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. It tracks them and binds them appropriately.

 

Prevention of the SQL Injection

As a matter of fact templates are not a new idea, however, 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.
Take a look at an example:

SELECT username, sum(spend)
FROM transactions
WHERE start_date >
AND end_date <

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

SELECT username, sum(spend)
FROM transactions
WHERE start_date > '2016-01-01'
AND end_date < '2016-12-31'

With JinjaSQL, you get back two things,

SELECT username, sum(spend)
FROM transactions
WHERE start_date > %s
AND end_date < %s

and a list of bind parameters:


['2016-01-01' ,'2016-12-31']

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

 

Give JinjaSQL a try!

Try using it in your projects. In case you have any questions/comments – create an issue in GitHub. View discussion on HackerNews.


Have a question?

Need Technology advice?

Connect

+1 669 253 9011

contact@hashedin.com

facebook twitter linkedIn youtube