Linked List: Tools for SQL Developers

I love SQL. There is nothing like playing with the data once you know it. I give it all the respect a programming language deserves. I store it in files as .sql files, add them git, use configuration to set schema name, etc. I use quite a few tools to deal with SQL (and databases). Here are my most used.

Dbeaver

Dbeaver is the primary GUI tool to access databases of all kind. Though I do use light weight DB Browser for SQLite or SQLite Studio for accessing SQLite.

Free multi-platform database tool for developers, database administrators, analysts and all people who need to work with databases. Supports all popular databases: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto, etc.

DBeaver

SQLFluff

A good linter and formatter makes the code easy to understand and maintain. SQLFluff is useful if you share SQLs with team members or use it as part of your ETLs.

SQLFluff is a dialect-flexible and configurable SQL linter. Designed with ELT applications in mind, SQLFluff also works with Jinja templating and dbt. SQLFluff will auto-fix most linting errors, allowing you to focus your time on what matters.

SQLFluff

Jinja

Jinja or Jinaj2 is that secret tool that many of us are using to generate parts of SQL based on other variables. Jinja is a templating tool. It used to create documents, like HTML documents in the Python web world. It is used as a Python package in many projects.

Jinja is a fast, expressive, extensible templating engine. Special placeholders in the template allow writing code similar to Python syntax. Then the template is passed data to render the final document.

Jinja

But you can also use it as a CLI. For example, you can use jinja-cli or yasha. They take a data file and a template file to generate the final document. For instance, I can do.

{
    "columns":
    [
        "id",
        "name",
        "phone"
    ],
    "table": "customer"
}

SELECT
{%- for column in columns %}
	{{ column }}{% if not loop.last %}, {% endif %}
{%- endfor %}
FROM 
	{{ table }}

jinja2 example.sql.ji example.json

Of course, this is just a simple and almost useless example. But it proves the point.

If you want more support in terms of sql binding, parameter passing etc, then you can try jinjasql.

JinjaSQL is a template language for SQL statements and scripts. Since it's based in Jinja2, you have all the power it offers - conditional statements, macros, looping constructs, blocks, inheritance, and many more.

JinjaSQL

You can also use Jinja and its macros in dbt. dbt is a data transformation tool that makes it easy to work with warehouses. dbt needs an entirely different blog post. I will write about it later. The lesson here is to learn Jinja2 templating, and it comes to your rescue in strange places.