Allow sqlfluff to be installed in a conda (or other) virtual environment about vscode-sqlfluff HOT 10 OPEN

@GClunies It would be great if you could raise a PR with the anaconda setup so that I can link it from the README. I'm unfamiliar with anaconda myself.

from vscode-sqlfluff.

@dorzey I am still playing around with this a bit still, so excuse the "stream of consciousness" comments. But in general it seems like recent fixes are headed in the right direction!

My conda + dbt + sqlfluff + vscode + vscode-sqlfluff + dbt-power-user setup is as follows:

1. My conda env (named surfline-dbt) is defined as:

name: surfline-dbt
channels:
  - conda-forge
  - defaults
dependencies:
  - agate=1.6.1
  - pip=20.1.1
  - python=3.8
  - pip:
    - dbt==0.19.1
    - sqlfluff==0.5.3  # I plan to update this

2. Nothing special about dbt except that I source it from the surfline-dbt conda env defined above.

3. My .sqlfluff is as follows. I use templater = dbt.

   .sqlfluff config file (click dropdown to view)

   # For SQLFluff Rules reference, see:
   # https://docs.sqlfluff.com/en/stable/rules.html#rules-reference
   [sqlfluff]
   verbose = 0
   nocolor = False
   dialect = postgres
   templater = dbt
   rules = None
   exclude_rules = L032,L033,L034,L037,L044
   recurse = 0
   output_line_length = 80
   runaway_limit = 10
   ignore = parsing,templating
   ignore_templated_areas = True
   
   [sqlfluff:indentation]
   indented_joins = False
   template_blocks_indent = True
   
   # Some rules can be configured directly from the config common to other rules.
   [sqlfluff:rules]
   tab_space_size = 4
   max_line_length = 120
   indent_unit = space
   comma_style = trailing
   allow_scalar = True
   single_table_references = consistent
   only_aliases = True
   
   # Some rules have their own specific config.
   # All SQLFluff rules can be found at: https://docs.sqlfluff.com/en/stable/rules.html#rules-reference
   # When a rule in not listed below, we inherit the default behavior from above.
   [sqlfluff:rules:L003]
   lint_templated_tokens = False
   
   [sqlfluff:rules:L010]  # Keywords
   capitalisation_policy = lower
   
   [sqlfluff:rules:L014]  # Unquoted identifiers
   # Note that sqlfluff uses the 'postgres' dialect for Redshift.
   # The postgres keywords used to check against 'unquoted identifiers' can be at:
   #    https://www.postgresql.org/docs/8.1/sql-keywords-appendix.html
   extended_capitalisation_policy = lower
   unquoted_identifiers_policy = all
   
   [sqlfluff:rules:L016]
   # Setting to True allows us to copy/paste long URLs as comments
   ignore_comment_lines = True
   
   [sqlfluff:rules:L030]  # Function names
   capitalisation_policy = lower
   
   [sqlfluff:rules:L038]
   select_clause_trailing_comma = forbid
   
   [sqlfluff:rules:L040]  # Null & Boolean Literals
   capitalisation_policy = lower
   
   [sqlfluff:rules:L042]
   # By default, allow subqueries in from clauses, but not join clauses.
   forbid_subquery_in = join
   
   

4. In my GLOBAL VScode settings, I have some configs for dbt, SQL, Jinja SQL, vscode-sqlfluff extension, and dbt-power-user extension:

{
    "dbt.listModelsDisabled": true,

    "files.associations": {
        "*.sql": "jinja-sql",
        "*.rmd": "markdown"
    },

    "[sql]": {
        "breadcrumbs.showArrays": true,
        "editor.insertSpaces": true,
        "editor.tabSize": 4
    },

    "[jinja-sql]": {
        "breadcrumbs.showArrays": true,
        "editor.insertSpaces": true,
        "editor.tabSize": 4,
    },

    "sql.linter.executablePath": "/opt/anaconda3/envs/surfline-dbt/bin/sqlfluff",
    
}

5. In my repo containing my dbt project, I have the following .vscode/settings.json. This just makes sure I always use the surfline-dbt conda env when working in this repo so that dbt-power-user always finds dbt.

{
    "python.defaultInterpreterPath": "/opt/anaconda3/envs/surfline-dbt/bin/python",
}

I think that's the whole setup right now.

I am still testing functionality still. It's a bit slow at returning linting errors when I save a file, but I suspect thats from the dbt-compiler having to run due to templater = dbt in my .sqlfluff. Not sure.

from vscode-sqlfluff.

I'm having a similar issue with using vscode-sqlfluff in an conda virtual environment. The below error message shows up:

sqlfluff fix sql_code.sql --dialect snowflake works using command line so it's installed correctly.

I've added the below to .vscode/settings.json within project:
{ "python.defaultInterpreterPath": "c:/Users/user/Anaconda3/envs/sql_development/python", "sqlfluff.executablePath": "c:/Users/user/Anaconda3/envs/sql_development/Scripts/sqlfluff" }

And I've also added the below to user settings:
"sqlfluff.executablePath": "c:/Users/user/Anaconda3/envs/sql_development/Scripts/sqlfluff"
"sqlfluff.linter.run": "OnSave"

channels:
  - conda-forge
  - defaults
dependencies:
  - appdirs=1.4.4
  - attrs=22.2.0
  - backports.cached-property=1.0.2
  - bzip2=1.0.8
  - ca-certificates=2022.12.7
  - certifi=2022.12.7
  - chardet=5.1.0
  - click=8.1.3
  - colorama=0.4.6
  - diff-cover=7.3.0
  - exceptiongroup=1.1.0
  - importlib-metadata=6.0.0
  - importlib_metadata=6.0.0
  - iniconfig=1.1.1
  - jinja2=3.1.2
  - libffi=3.4.2
  - markupsafe=2.1.1
  - openssl=1.1.1s
  - packaging=22.0
  - pathspec=0.10.3
  - pip=22.3.1
  - pluggy=1.0.0
  - pygments=2.14.0
  - pytest=7.2.0
  - python=3.10.8
  - python_abi=3.10
  - pyyaml=6.0
  - regex=2022.7.9
  - setuptools=65.5.0
  - sqlfluff=1.4.5
  - sqlite=3.40.0
  - tblib=1.7.0
  - tk=8.6.12
  - toml=0.10.2
  - tomli=2.0.1
  - tqdm=4.64.1
  - typing=3.10.0.0
  - typing_extensions=4.4.0
  - tzdata=2022g
  - vc=14.2
  - vs2015_runtime=14.27.29016
  - wheel=0.37.1
  - wincertstore=0.2
  - xz=5.2.8
  - yaml=0.2.5
  - zipp=3.11.0
  - zlib=1.2.13

from vscode-sqlfluff.

Running up against the same issue. Any ongoing work on this?

from vscode-sqlfluff.

The latest version (0.0.5) fixed the definition of the executable in "sql.linter.executablePath". It doesn't recognise the venv directly but the extension works when the path has been added manually.

from vscode-sqlfluff.

@b-per, what do you mean by "when the path has been added manually"?

Are you referring to...

because this still doesn't work for me. Would be very interested to know your venv setup and VScode settings.

from vscode-sqlfluff.

So it seems this is working for me with this configuration.

vscode-sqlfluff extension settings:

Not sure why onSave works but onType doesn't?

VScode settings found in .vscode/setting.json located in my dbt project repo, they override any settings in my global vscode settings.json.

{
    "python.defaultInterpreterPath": "/opt/anaconda3/envs/surfline-dbt/bin/python",
}

Hope this helps others. Thank you @dorzey and others for updates to the plugin 🙏🏽 .

from vscode-sqlfluff.

Thanks for this. I will try and find time to replicate your setup.

from vscode-sqlfluff.

I have the same issue where lint warnings are not showing up in the problems pane, but the formatting on save works??? I've tried clean installing, removing all my sqlfluff user settings, toggling them on/off. Here are my sql related settings:

    "[sql]": {
        "editor.defaultFormatter": "dorzey.vscode-sqlfluff"
    },
    "sql.linter.executablePath": "C:\\Users\\Vikesh\\miniconda3\\Scripts\\sqlfluff.exe",
    "files.associations": {
        "*.sql": "sql"
    },

Whats strange is the linting was working yesterday, and I had not changed any settings. So I have no clue why its not working today. I have the sqltools extension too but uninstalling that makes no difference either.

I'm on vscode-sqlfluff v0.0.5 and sqlfluff 0.6.6.

from vscode-sqlfluff.

Somehow, I eventually had this working on a previous machine. New machine and I can't get the vscode extension to work.
I can successfully lint files from the cli.

in the virtual environment:

(env) % which sqlfluff
/Users/danielbartley/my_project/env/bin/
(env) % sqlfluff --version
sqlfluff, version 0.8.2
(env) % cd '/Users/danielbartley/my_project/env/bin/'
(env) % ls | grep sqlfluff
sqlfluff

.vscode/settings.json
{
"sql.linter.executablePath": "/Users/danielbartley/my_project/env/bin/",
"sql.linter.run": "onType"
}

right click on a file, select 'format document' and I get:
"spawn "/Users/danielbartley/my_project/env/bin/" EACCES"
I don't know what that means.

I wrote a note to myself last time that alternating between onType and onSave was enough to get it unstuck. Doesnt work this time.

I would really appreciate more detailed docs for the setup. Willing to help.

from vscode-sqlfluff.