Syntax highlighting / SQL Linting

We’ve gotten this questions a bunch of times in the past and this stub is to provide a consolidated place for the conversation on this topic. This post was prompted by a recent email where I was asked:

Syntax highlighting mostly worked in my stock neovim. I think it was confused about jinja but not enough to be a real problem. I tried using sqlformat to enforce a particular style, but it broke a jinja macro (specifically sort became SORT). Similarly, sqlint chokes on jinja. I can imagine hacking up sqlformat and sqlint to ignore jinja, but do you have existing solutions you can recommend for formatting and linting dbt codebases?

We (Fishtown Analytics) have thought a lot about this. Syntax highlighting and linting are core parts of the software development workflow; they make both personal productivity and code quality higher. That matters.

Existing SQL highlighting and linting tools we’ve found all choke on dbt’s combination of Jinja + SQL. To-date, we haven’t seen any solutions that work as-is, and so if we care about these things as a community there will be work involved in building them. Here are our thoughts on each:

  • There are many text editors, and almost as many syntax highlighting specification formats. The ideal solution to syntax highlighting, therefore, will likely be community-driven. Our hope is that members of the community will maintain syntax highlighting packages for their favorite text editors. We may maintain one or two of these packages for our favorite text editors but don’t have plans to go beyond that.
  • Linting is a somewhat more tractable as a problem (or, at least, an 80% good version of it is). We’ve hacked around on this topic before and have some initially promising results, but nothing ready to share yet. It’s possible that some version of linting could be baked into dbt at some point, although this is not something we’ve made a hard-and-fast decision on.

If you find useful highlighting and linting tools in your travels, please post them here. Additionally, if you write any code on either topic, please post it here. Thanks!

6 Likes

I have Vim syntax highlighting working pretty well. Hopefully this will be useful for others as well. Here’s the content of my dbt syntax file, which should be placed in ~/.vim/syntax/dbt.vim:

" inclue sql syntax
runtime! syntax/sql.vim

unlet b:current_syntax

" set region for jinja syntax
syntax include @jinja syntax/jinja.vim
syntax region jinjaSyntax start=/{[{#\%\[]/ skip="(\/\*|\*\/|--)" end=/[}#\%\]]}/ contains=@jinja

let b:current_syntax='dbt'

Then in your .vimrc file, add this line:

au BufNewFile,BufRead *.sql set ft=dbt

I’ve experienced some weirdness where Jinja in block comments aren’t shown as being commented out, but otherwise it works pretty well.

edit: forgot to add that you also need to install this plugin: https://github.com/lepture/vim-jinja

2 Likes

There’s now a dbt highlighter for atom! Check it out here

1 Like

Google open sourced the zetasql parser and analyzer last month:

The “one pager” docs located here: https://github.com/google/zetasql/blob/master/docs/one-pager.md

I imagine one could use this library to build a plugin for modern text editors (atom, vscode, etc)

1 Like

Very interested in this - ideally would like to create some auto-format-on-save plugin. I wonder if zetasql is behind the “format query” functionality on the BigQuery UI, and if so, if this could be utilized programmatically.