Doing the Impossible, using ASSISTANT to make a SQL Linter (and how you can make it lint whatever you want)
Tags: sql, vscode, tutorial, productivity
Published: 2020-08-13 12:51:44

***

*I currently work for a great company called Xero doing all sorts of fun data things. When you're reading my articles I need you to understand that my words are my own, I'm not speaking on behalf of my employer and If i'm talking about something negative in the field that may not be indicative of Xero, I've worked many interesting roles and I read a lot about my field.*

***
## First of all what is a linter?
For those who don't know a Linter is a tool that analyses code to flag errors, bugs, and stylistic errors

It's a great way to ensure that people are coding in a similar and consistent manner and not like a psychopath......

![Psycho](https://media.giphy.com/media/KBg4LUuxOzGNi/giphy.gif)

These sadly tend not to exist for SQL. 

The highly contextual, and wildly variable nature of data stores means that while we all use the same keywords, but the devil is in the details. 

For example it's quite common to champion the KISS principle in SQL code, just because you can join 15 tables together in one super query doesn't mean you should, it makes it difficult to test and maintain, and a clear sign you're a psychopath......

And while we could potentially build a coding framework or a linter to enforce that, there are just times where you have no choice but to join 15 tables together as its all down to how the data works together. Can you see the dilemma? You really can't tell people off if they have no choice.

In practice this generally means a data team may agree on a set of coding standards, which will differ in their level of comprehensiveness, and are left enforcing it based on how vigilant the peer reviews are.

***
## They don't exist but you have one now?  

Enter stage left!

Assistant by Tomasz Smykowski

{% user tomaszs2 %}

A generic linter VSCODE extension that allows you to program in your own custom rules using regex.  

I was genuinely very excited when I first saw this. I was in a bar at 2am and had seen the article and sent it to my work email right there right then. My friends where quite rightly judging my commitment to sparkle motion.

But I digress!

How did it go? 

Well at first, not well! 

I gave it a whirl and found that it wasn't working for the sorts of things I wanted it to catch. I quickly discovered that it didn't evaluate rules across multiple lines, which is important in SQL because we are forever writing:
```sql
Select 	  column_name,
          column_name, 

From      table_name; 
```
And then running it, and then it errors, and then we change it, and then we do it again. 

So I emailed Tomasz just to confirm whether that was intended functionality. That was on the 10th of July.

On the 13th of July Tomasz responded confirming that the linter does not work across multiple lines and that he had created an issue to get that changed as a feature request. 

I thought, that's nice of him, but I was realistic. We all groom backlogs for a living and we all know that some backlogs last multiple presidencies. 

Then Friday July 17th, 5:45pm, I'm drunk, work drinks, great time, email from Tomasz.

He did it! 

Houston we have multi line support. 

And yes I couldn't wait until Monday to try it out.

So how did it go? (The sequel)

Amazing....

We've loaded in fifteen rules in the past month and they're working well. 

## How to add rules
Well first you need to download the [extension in VSCODE](https://marketplace.visualstudio.com/items?itemName=tomasz-smykowski.assistant). 

Then you need to go to your settings.json file, either in your global or workspace area, depending on how you work. 

Add the following code to start loading in rules in regex:
```json
"assistant": {
      "rules": 
      [
        load rules here :) 
      ]
      }
}

```
Note:
- You will need to double escape regex syntax i.e \b should be ```\\b```
- You may need to change the modifiers however, i = case insensitive, s = multiline, and g = global so I would only remove them if you 100% know what you're doing.

***
## Some Examples
### Comma on the last column
As mentioned earlier this is the the number one mistake we commonly make. 
![COMMA](https://dev-to-uploads.s3.amazonaws.com/i/mz0bx5vphls82pmh273x.JPG)
To add this rule to your linter:
```json
       {
        "regex": "(,)(\\s*\\W*)(from)",
        "message": "Reminder: you don't need a comma on the last column",
        "modifiers": "sig"
       },
```

### Don't forget the ON after a join
Sometimes I go straight into adding 'and' conditions after my join and forget the on. (I've also been guilty of having more than one ON clause but that's not covered here.)
![JOIN](https://dev-to-uploads.s3.amazonaws.com/i/odm35faitzmkjvjjj9u3.JPG)
To add this rule to your linter:
```json
       {
        "regex": "(join)(\\s*\\w*)(\\s*\\w*\\s*)(and)",
        "message": "An ON keyword must follow a join condition",
        "modifiers": "sig"
       },
```
### Ban people from using CTE's
In our environments CTE's aren't great for performance. However I still stand by never using them in production code even if they perform well. Something I'm not alone on:
{% link https://dev.to/seattledataguy/how-to-write-better-sql-advanced-sql-episode-2-please-stop-using-so-many-ctes-4p5g %}
![CTE](https://dev-to-uploads.s3.amazonaws.com/i/4trx9oggour2j5uwbfbi.JPG)
```json
       {
        "regex": "(with\\s)(\\w*\\s*)(as)",
        "message": "Please refrain from using CTE's in production code. They are bad for redshift.",
        "modifiers": "sig"
       },
```
### Don't use USING in joins
Other than trying to avoid an accidental bad join, we ban the use of USING as ON is just what we decided is best practice.
![USING](https://dev-to-uploads.s3.amazonaws.com/i/201972levpbo0ignmbg2.JPG)
```json
        "regex": "(join\\s)(\\w*\\s*\\w*\\s*)(using)",
        "message": "Do not use USING when joining tables, use ON.",
        "modifiers": "sig"
       },
```
### Don't get your keywords out of order
How many times have you gotten your order and limit the wrong way around? Or Group / Having / order?
![LIMIT](https://dev-to-uploads.s3.amazonaws.com/i/ld0g67opkbyreb2wsl18.JPG)
```json
       {
        "regex": "(limit\\s*\\d*\\s*)(order|group|where|having)",
        "message": "Limit should be the last keyword in the query.",
        "modifiers": "sig"
       },
```
### To reassure commitment...
In modern environments you don't need to commit your code any more.
![Commit](https://dev-to-uploads.s3.amazonaws.com/i/nepvgma7psq30gwnv0dy.JPG)
```json
       {
        "regex": "commit;",
        "message": "I can ensure you that Redshift does not have commitment issues, you do not need to write commit.",
        "modifiers": "sig"
       },
```
### Prevent UNION
You should only ever use UNION ALL. A union scans for duplicates which BURNS compute and it will throw out duplicate rows. My advice is that if you want to exclude duplicates there are better ways.
![union](https://dev-to-uploads.s3.amazonaws.com/i/sxy1al6luntxofij9plb.JPG)
```json
       {
        "regex": "(union\\s*)(select)",
        "message": "I can't think a valid reason to use union. You should always be using union all. ",
        "modifiers": "sig"
       },
```
### Curb some bad compression
Only compression nerds like me know that a varchar(1) in redshift takes up over 7 bytes of space, when you could just use BOOL for a small 1 byte. 
![bool](https://dev-to-uploads.s3.amazonaws.com/i/jjjb0ch2eqt8hs2fwlib.JPG)
```json
       {
        "regex": "char\\W1\\W",
        "message": "You should be using a boolean if you are building a char with 1 space.",
        "modifiers": "sig"
       },
```
More info about Redshift Compression here:
{% link https://dev.to/ronsoak/the-r-a-g-redshift-analyst-guide-data-types-and-compression-4a4e %}

## And More
We've made rules to catch out common spelling mistakes like 'selct' or to enforce certain practices like using '!=' over '<>'!

***
# So how do I make my own?
So as mentioned earlier you need to download the extension in VSCODE and then set up your settings.json

Now you need to set up a rule, use this template:
```JSON
       {
        "regex": "",
        "message": "",
        "modifiers": ""
       }
```
Don't forget to add a , after each rule, i.e
```JSON
       {
        "regex": "",
        "message": "",
        "modifiers": ""
       },
       {
        "regex": "",
        "message": "",
        "modifiers": ""
       }
```
Then figure out what you want to catch out. 

**Beware:** You are limited to what you can detect using a REGEX pattern. So you'll need to think about how you will do that and just accept that some rules you won't be able to catch. 

For example not closing brackets ( ), it's hard to write a REGEX rule to catch that as what you really want is an exception rule. 

To test whether I can do what I want to do I use something like [rubular](https://rubular.com/) to test my regex.
![Rubular](https://dev-to-uploads.s3.amazonaws.com/i/gdt6zv1dtro55ysfqqaj.JPG)
If I get it working, I load it into the settings file and away we go!

## Things to Note:
- It's got its limitations, it won't work for ever rule, primarily due to the limitations of REGEX not the extension. 
- It works all over VSCODE (even in the settings file) so if that annoys you, make sure to use the workspace feature, though there is a feature request to be able to lock the rules to specific file types (this will be handy for those of us who switch coding languages)
- If you make your rule too generic you risk crashing VSCODE as the rule will scan as much as the rule allows. 

# Share your findings!
Tomasz has a github for this extension where he asks that you submit your own rule file to help others! CSS, HTML, Ruby, Python!
I've already added my sql rules as a pull request :) 
{% github tomaszs/Assistant %}

***
*Reminder: All views expressed here are my own and do not represent my employer, Xero.*
***
####Who am I?
{% twitter 1277560230725906432 %}
####You should read....
{% link  https://dev.to/ronsoak/what-they-don-t-tell-you-about-being-an-analyst-4f90 %}