Issue with DuckDB file locking when running SQL query in DBT by the Execute dbt SQL Feature

[xrafhue]

Hi everyone,

I'm currently working on a DBT project that connects to a DuckDB database file. As you may know, the .duckdb file extension can only be opened by a single process at a time, unless in ReadOnly mode.

When I execute an SQL query in a model using the "Execute dbt SQL (Ctrl+Enter)" button, it opens the "QUERY RESULT" tab and displays the results, which is perfect! However, the connection to the database remains active and the .duckdb file is locked.

For now, I've been using the lsof command to identify the process that is using the file, and then killing it. After that, the "Execute dbt SQL" function no longer works, and I have to reload the workspace to use it again.

Has anyone encountered this issue before, and if so, is there a better way to handle this file locking problem when running SQL queries in DBT? Any help or insights would be greatly appreciated.

Thanks in advance!

[mdesmet]

Can you try to fix the issue by making some changes in the dbt_integration.py.

It is very easy to try and debug the extension by opening the project in vscode and launching it in debug in your favourite project. On first sight the connection is closed but the connection management code is actually dbt code. See https://github.com/innoverio/vscode-dbt-power-user/blob/ed533111e7e4e524e0b633c9733dba0949a80eb6/dbt_integration.py#L403-L415

[mdesmet]

It's a bit fishy to see this line in duckdb adapter: https://github.com/jwills/dbt-duckdb/blob/5662b5f84c20729636c2c6f54bb5ca6c48afc4e9/dbt/adapters/duckdb/connections.py#L94

This makes me think this is rather a duckdb adapter issue as not any other adapters have this issue.

[matsonj]

FYI - closed by duckdb/dbt-duckdb#163

[avyfain]

Brought this up with @jwills and it looks like this issue was fixed in dbt-duckdb 1.5.1 with duckdb/dbt-duckdb#163