Vscode saving sql file always results in error with dbt templater

[FrankTub]

I'm using vscode and SQLFluff to work on my dbt-core project. I'm using the latest versions available at the moment.

dbt --version
Core:
  - installed: 1.5.2
  - latest:    1.5.2 - Up to date!

Plugins:
  - postgres: 1.5.2 - Up to date!

And also for the SQLFluff version:

sqlfluff --version
sqlfluff, version 2.1.2

My .vscode/settings.json looks as below:

{
    "python.defaultInterpreterPath": "${workspaceFolder}/venv/bin",
    "sqlfluff.executablePath": "${workspaceFolder}/venv/bin/sqlfluff",
    "sqlfluff.dialect": "postgres",
    "sqlfluff.linter.run": "onSave",
    "sqlfluff.format.enabled": true,
    "files.associations": {
        "*.sql": "jinja-sql",
        "*.md": "markdown",
        "*.yaml": "jinja-yaml",
        "*.yml": "jinja-yaml",
    },
    "editor.formatOnSave": true
}

When I try to save a very simple SQL model like below I receive an error pop-up in vscode. The sql file:

with
stages as (
    select *
    from {{ ref('stages') }}
)

select *
from stages

And the error:

WARNING File ....../data-warehouse-dbt/stdin was not found in dbt project

At the top of my sql file the following code is inserted by saving the model: 15:10:19 Registered adapter: postgres=1.5.2.

Whatever I try, it seems impossible to save the model without receiving this error. If you try to save a new model it (sometimes) states something like dbt templater fails silently, try dbt compile to verify if you hover over the error in your file very quickly. However when I run dbt compile this runs without errors.

Any help would be highly appreciated.

[RobertOstermann]

Have you tried setting "sqlfluff.experimental.format.executeInTerminal": true in the settings.json? I think that is still required to format dbt files.

You could also check the output tab - sqlfluff channel (next to the terminal tab). That will give you the sqlfluff commands that run, you can use that to grab the format command that is running. Copy that command and try running it in your terminal, if the command still doesn't work in your terminal it might be an issue with sqlfluff and not this extension.

[FrankTub]

Thanks @RobertOstermann , that fixed the issue for me.

[FrankTub]

@RobertOstermann , I jumped to conclusions but this introduces a new issue when I enable this experimental feature.

With .vscode/settings.json:

{
    "python.defaultInterpreterPath": "${workspaceFolder}/venv/bin",
    "sqlfluff.executablePath": "${workspaceFolder}/venv/bin/sqlfluff",
    "sqlfluff.dialect": "postgres",
    "sqlfluff.linter.run": "onSave",
    "sqlfluff.format.enabled": true,
    "sqlfluff.experimental.format.executeInTerminal": true,
    "files.associations": {
        "*.sql": "jinja-sql",
        "*.md": "markdown",
        "*.yaml": "jinja-yaml",
        "*.yml": "jinja-yaml",
    },
    "editor.formatOnSave": true
}

I can modify some of the column names, but as soon as I save it my changes are undone and it is reverted to the previous version.

So then I decided to remove "editor.formatOnSave": true from .vscode/settings.json resulting in:

{
    "python.defaultInterpreterPath": "${workspaceFolder}/venv/bin",
    "sqlfluff.executablePath": "${workspaceFolder}/venv/bin/sqlfluff",
    "sqlfluff.dialect": "postgres",
    "sqlfluff.linter.run": "onSave",
    "sqlfluff.format.enabled": true,
    "sqlfluff.experimental.format.executeInTerminal": true,
    "files.associations": {
        "*.sql": "jinja-sql",
        "*.md": "markdown",
        "*.yaml": "jinja-yaml",
        "*.yml": "jinja-yaml",
    }
}

Now when I save my file it is linted as I would expect when I save my file, but it is not corrected by a sqlfluff fix as I would have expected. Now I have taking a look at this experimental feature and apparently this behavior was already known:

TLDR

Using the experimental feature "sqlfluff.experimental.format.executeInTerminal": true I can save files, but they are not formatted as I would have expected based on the name.

[RobertOstermann]

@FrankTub Could you check the output tab - sqlfluff channel (next to the terminal tab)?

That will give you the sqlfluff commands that are being run by this extension. You can use that to grab the format command that is running. Copy that command and try running it in your terminal, if the command still doesn't work in your terminal it might be an issue with sqlfluff and not this extension.

Also worth noting, I think that if you make any changes while the formatting is occurring you might run into issues as mentioned, is the formatting still not working if you do not change the file contents while the formatting is occurring?

[FrankTub]

@RobertOstermann , I've made sure that I don't make any changes to my files while it is formatting.

I tried to add it once more to my .vscode/settings.json but it saves the previous version of my file after formatting it.

I've tried to check the output in the output tab of vscode, however I don't see any sqlfluff commands printed there. This is what I see:

Is this the correct location where I could find the sqlfluff command that is run in the terminal?

[RobertOstermann]

@FrankTub That is the correct output tab, but you have to change the channel to see the SQLFluff commands. It should be on the right side of the panel

[FrankTub]

@RobertOstermann, aah never knew how that worked. I did some testing with my ~/.vscode/settings.json as following:

{
    "python.defaultInterpreterPath": "${workspaceFolder}/venv/bin",
    "sqlfluff.executablePath": "${workspaceFolder}/venv/bin/sqlfluff",
    "sqlfluff.dialect": "postgres",
    "sqlfluff.linter.run": "onSave",
    "sqlfluff.format.enabled": true,
    "sqlfluff.experimental.format.executeInTerminal": true,
    "files.associations": {
        "*.sql": "jinja-sql",
        "*.md": "markdown",
        "*.yaml": "jinja-yaml",
        "*.yml": "jinja-yaml",
    },
    "editor.formatOnSave": true
}

Created a new test.sql:

select
    customer_organization_id,
    vendor_organization_id
from {{ ref('lease_contracts') }}

This was saved correctly. Then I tried to modify my file to:

select
    customer_organization_id,
    vendor_organization_id,
    {{ current_timestamp() }} as dbt_created_at
from {{ ref('lease_contracts') }}

However when I press save it is changed back to what it was, ie:

select
    customer_organization_id,
    vendor_organization_id
from {{ ref('lease_contracts') }}

In the Output tab of Vscode I can find:

------------------------------------------------------------

Format triggered for /Users/frank.tubbing/source/repos/data-warehouse-etl-dbt/models/test.sql

------------------------------------------------------------

Reading from file, not stdin

--------------------Executing Command--------------------

/Users/frank.tubbing/source/repos/data-warehouse-etl-dbt/venv/bin/sqlfluff fix --force --dialect postgres models/test.sql

------------------------------------------------------------

Received close event, code 0 signal null
Raw stdout output:

------------------------------------------------------------

==== finding fixable violations ====
FORCE MODE: Attempting fixes...
=== [dbt templater] Sorting Nodes...
07:11:14  Registered adapter: postgres=1.5.2
=== [dbt templater] Compiling dbt project...
=== [dbt templater] Project Compiled.
==== no fixable linting violations found ====
All Finished!

So the statement that is logged is /Users/frank.tubbing/source/repos/data-warehouse-etl-dbt/venv/bin/sqlfluff fix --force --dialect postgres models/test.sql. So now I removed in .vscode/settings.json the line "editor.formatOnSave": true again and modified my file exactly the same to as above.

Then I ran below command in my terminal:

(venv) .. data-warehouse-etl-dbt % /Users/frank.tubbing/source/repos/data-warehouse-etl-dbt/venv/bin/sqlfluff fix --force --dialect postgres models/test.sql
==== finding fixable violations ====
FORCE MODE: Attempting fixes...
=== [dbt templater] Sorting Nodes...
07:17:02  Registered adapter: postgres=1.5.2
=== [dbt templater] Compiling dbt project...
=== [dbt templater] Project Compiled.
==== no fixable linting violations found ====                                                                                                                                                                                
All Finished 📜 🎉!

Now my file is still as I would expect it to be, namely:

select
    customer_organization_id,
    vendor_organization_id,
    {{ current_timestamp() }} as dbt_created_at
from {{ ref('lease_contracts') }}

So my guess is that this is some weird behavior of the SQLFluff plugin in Vscode.

[RobertOstermann]

@FrankTub Does the extension work when you have "editor.formatOnSave": false? Setting that to true while also having "sqlfluff.experimental.format.executeInTerminal": true causes weird behavior and I haven't been able to find a way to get those two settings to work well together.

[FrankTub]

@RobertOstermann , when we set "editor.formatOnSave": false does not format my SQL files when I save a file in Visual studio code. The linting works as expected. Using VSCode's inbuilt Format Document works.

[RobertOstermann]

@FrankTub Unfortunately that is currently the only way for the extension to work with DBT at the moment. I will take another look and see if I can improve the way the executeInTerminal setting works so that it doesn't break with formatOnSave enabled. For now though you will just have to manually format the document.