Get Your SQL Statements Right The First Time With SQL Lint

What’s your average success rate of getting a SQL statement right on the first try? In best case, you botched a simple statement without side effects and just have to try again with correct syntax or remove that typo from a table name, but things can easily go wrong fast here. But don’t worry, the days of fixing it on the fly can be over, thanks to [Joe Reynolds] who wrote a linter for SQL.

A linter parses code to tell you where you screwed up. While checking SQL syntax itself is somewhat straightforward, [Joe]’s sql-lint tool will also check the semantics of it by looking up the actual database and performing sanity checks on it. Currently supporting PostgreSQL and MySQL, it can be either run on a single SQL file or a directory of files, or take input directly from the command line. Even better, it also integrates within your editor of choice — assuming it supports external plugins — and the documentation shows how to do that specifically for Vim.

If you can look past the fact that it’s written in TypeScript and consequentially results in a rather large executable (~40 MB), it might serve as an interesting starting point for the language itself, or adds a new perspective on writing this type of analyzer. And if databases aren’t your terrain, how about shell scripts?

sql-lint in action
Example use of sql-lint

11 thoughts on “Get Your SQL Statements Right The First Time With SQL Lint

  1. Those databases are tiny toys for little boys and are utterly incapable of handling serious loads without falling over. Look to the Gartner Group or KLAS findings on enterprise SQL for real solutions that will not corrupt your critical data.

    1. > gee thanks!

      ROFL!

      Seriously, syntax errors are not a real problem in any programming language, even for beginners… you just have to compare your static code to the reference docs to see where you went wrong.

      The real problems in SQL are those pesky semantic errors; e.g., a join that returns too few or too many or the wrong rows.

      After that, comes the timing errors; e.g., where isolation level 3 was used instead of snapshot isolation and phantom locks are blocking too many users.

      Or the maintenance errors; e.g., where a schema change affected 10 sections of application code but you only found nine of them.

    2. Hello!
      Author of sql-lint here. I agree those errors are unhelpful but they’re also the ones that the SQL server (MySQL in this case) come back with. sql-lint just displays them and its own more useful error messages on top

  2. “If you can look past the fact that it’s written in TypeScript and consequentially results in a rather large executable (~40 MB), ”

    A true testament to the decadence of modern programmers.

  3. Ooph, one can write a volume on how NOT to use SQL (me, but I’ll pas the honors to someone with a better command of english).

    Seriously, once you are pass A Certain Threshold you basically don’t need “checkers” as they tend to come with idiogotchas of their own (Oracle’s SQLDeveloper anyone? anyone?).

    As a self-taught SQL heavy peruser who used to command-line querying, sometimes with users breathing rather heavily down my neck (not fun) and otherwise getting into my hair “trying to help”, I can tell that the best thing you can do is learn how to SQL, joins and all (btw, joins, inner, outer, left, right, whatever, are NOT the most complicated part; so are cubes – they are somewhat complicated, but not impossible to master).

    If you are into serious heavy data lifting (say, within Oracle enterprise-caliber database numbering hundreds of interlinked tables with millions of records) might as well invest into quality learning, no, not “paid-for courses”, those tend to play with oversimplified “examples” that teach you how to like SQLeing, but you are in for a nasty surprise once you deal with real databases with their own catches 22, 33, 44 and basically all the way to the catch 100100.

    What do you do? Crack open Them Internets and off you go, practice-practice-practice-study-practice-practice-practice. Yep. (and welcome to the DBA’s hell – not, I am not a DBA, never wanted to be).

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.