Support python interpreter per workspace folder

[yassun7010]

Expected behavior

In our project, we use multi-root workspace.

The folder structure of this project is like this.

.
├── project.code-workspace
├── dbt
│  ├── dbt_project.yml
│  ├── pyproject.toml
│  ├── ...
├── other-workspace-folder
│  ├── ...

As a priority for resolving dbt command paths, we expect the following,

1. If it is set, the settings of dbt-power-user extension.
2. Python virtual environment on each workspace folder like dbt folder.
3. Python virtual environment on workspace root.
4. Global environment ($PATH)

Actual behavior

The path finding for dbt power user is as follows:

1. If it is set, the settings of dbt-power-user extension.
2. Python virtual environment on workspace root.

This python enviroment is determined the first time VSCode is opened, so moving workspace folders does not change the Python virtual environment settings.

Steps To Reproduce

1. use multi-workspace.
2. add sample workspace-folder and create dbt project on it.
3. create python virtual environment on the sample workspace.
4. set python virtual environment on the sample workspace.
5. install dbt-core in the python virtual environment on the sample workspace.
6. open sample/dbt_project.yml

Log output/Screenshots

https://private-user-images.githubusercontent.com/47286750/289367172-311f6d7f-f917-469a-8a9a-4f547416d3ba.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTEiLCJleHAiOjE3MDIyNjEzOTEsIm5iZiI6MTcwMjI2MTA5MSwicGF0aCI6Ii80NzI4Njc1MC8yODkzNjcxNzItMzExZjZkN2YtZjkxNy00NjlhLThhOWEtNGY1NDc0MTZkM2JhLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFJV05KWUFYNENTVkVINTNBJTJGMjAyMzEyMTElMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjMxMjExVDAyMTgxMVomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWFjZTQ1MjYzOGExMDdhYzM4MjM1Zjk2NjNhMzI1NzA3MmYwZDRkMGIzMjFiNWRmOWY0MThiZjhlZjllY2U0N2QmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.S7E_pLOh74ScMYS_NFfMVzTlBsqXSUiaTFTZdnNSJ7Q

Operating System

Mac OS 14.1.1（23B81）

dbt version

1.7.3

dbt Adapter

1.7.0

dbt Power User version

v0.27.3

Are you willing to submit PR?

• [X] Yes I am willing to submit a PR!

[yassun7010]

I created vscode-shandy-sqlfmt to verify that I could correctly utilize the Python virtual environment on the multi-root workspace project.

This VSCode extension behaves exactly as I intended and will be helpful for this project.

[Startouf]

I'd also like to get DBT working for multi-root workspaces. I recently sent a message to the SQLfluff (Slack) thread asking if this tool was also compatible with multi-root workspaces.

In my case, the first folder of my workspace is not a DBT folder and the extension does not seem to be able to pick my python at all; and indicates no DBT is available.

I have another workspace with just my DBT and everything works well, but it is tedious to switch workspaces. I've tried using folder-specific settings but most are greyed out.

[mdesmet]

How do you associate a python environment with a specific workspace folder. Can you provide reproduction steps?

[Startouf]

@mdesmet this choice is automatically suggested when using the python extension and choosing the interpreter

After pressing enter :

[yassun7010]

I have created a simple project so that this issue can be examined.

https://github.com/yassun7010/vscode-multi-workspace-dbt-sample/tree/main

Indeed, it seems that features such as linage are not working.

I will take some time over the weekend to find the cause and look into it.

[mdesmet]

@yassun7010: we had to disable the code you submitted because now it has to go through the dbtCoreIntegration class. It seems a bit tricky as well. What if next some user has 2 workspaces with different dbt versions. :)

Current workaround is maybe to associate the python interpreter with the first workspace folder.

[yassun7010]

Ah, I see.

This extension determines one dbt environment at activation, so it will not work if we have multiple dbt projects.

To support multiple workspaces, for each command or event, the extension needs to find out which workspace dbt is installed in.

For the repository root, vacode.workspace.workspaceFolders is [rootPath], and each workspace folder will contain [folderPath, rootPath]. So the dbt detection logic can be common.

Or vscode.workspace.getWorkspaceFolder detects workspaceFolder to which the current file belongs. vscode-shandy-sqlfmt does this by determining the dbt execution path.

[yassun7010]

Anybody working on this issue?

Perhaps the dbt detection mechanism needs to be changed significantly.

[anandgupta42]

@yassun7010 Not sure why I didn't get a notification for this comment.

No one is working on it yet.

[yassun7010]

@anandgupta42

Certainly that was not necessary.

I would consider modifying the code, and I think a major modification is needed to properly handle multi-workspace.

No one is currently working on this issue.

[anandgupta42]

@yassun7010 let us know if you need help making these changes

[mdesmet]

@yassun7010 : Wouldn't it be just easier to use dbtPythonOverride and set it to your preferred python interpreter? This would not require any code changes on your part.

[yassun7010]

@mdesmet

Our team could not adopt it.

The reason is that the path to the virtual environment is different for each user, and .vscode/settings.json or $PROJECT.code-workspace files are maintained in Git.

[Startouf]

I just wanted to add a small note regarding what was said previously :

When using multi-root workspace folders, if the first folder is a DBT project, then the extension seems to work fine for that project. So in your VS Code Project definition, re-ordering folders to put the DBT project as the first folder fixes things for the said DBT environment.

It's a quite nice workaround unless you have other projects/plugins that are not multi-root compatible like DBT power user, or unless you have multiple DBT projects with different configs

[yassun7010]

Ah, I was unaware of this because I had gotten into the convention of routing the first workspace.

That is certainly a valid method if only vscode-dbt-power-user does not support multi-workspace.

[Startouf]

It's a quite nice workaround unless you have other projects/plugins that are not multi-root compatible like DBT power user, or unless you have multiple DBT projects with different configs

Well it seems like I called the devil. A few weeks after writing this message we decided to start an overhaul of our dbt-models and decided to start a new DBT project... so now we're in the configuration where we have 2 DBT projects to maintain, and the extension does not work so well with that....

Anyways I'm hoping there will progress on this. Unfortunately I don't think I can help too much apart from giving feedback at the moment.