Connection pane duckdb schema identification error

[alearrigo]

System details:

Positron and OS details:

Positron Version: 2025.02.0 (Universal) build 171
Code - OSS Version: 1.96.0
Commit: 55f7331
Date: 2025-02-10T16:03:19.797Z
Electron: 32.2.6
Chromium: 128.0.6613.186
Node.js: 20.18.1
V8: 12.8.374.38-electron.0
OS: Darwin arm64 24.3.0

Interpreter details:

R 4.4.2

Describe the issue:

I created a new database with duckdb with two separate schemas. The code is reported below. I can see the tables I created in the connection pane, but there is no detail about the schema containing these tables. Moreover, I can only explore the table after switching to the correct schema. I'm doing something wrong or is this the expected behaviour?

EDIT: The same happens in Rstudio.

Steps to reproduce the issue:

library(DBI)
library(connections)
library(duckdb)
library(tidyverse)


db_file <- "~/Desktop/DB_TEST.duckdb"

con <- connection_open(duckdb(), db_file)


dbExecute(con, "CREATE SCHEMA IF NOT EXISTS SCHEMA_1;") # Create schema 1

dbExecute(con, "USE SCHEMA_1;") # switch to schema 1

dbWriteTable(con, "df1", mtcars) #write table

dbExecute(con, "CREATE SCHEMA IF NOT EXISTS SCHEMA_2;") # Create schema 2

dbExecute(con, "USE SCHEMA_2;") # switch to schema 2

dbWriteTable(con, "df2", iris) #write table

dbExecute(con, "USE main;") #switch to main

connection_close(con)

Expected or desired behavior:

I expected the UI to show the table inside the corresponding schema and schema awareness when trying to open a table from the connection pane. That's how is represented in motherduck:

Were there any error messages in the UI, Output panel, or Developer Tools console?

In the screenshot above.

[dfalbel]

Hi @alearrigo ,

Thanks for reporting! I believe this is an issue with the connections package: https://github.com/rstudio/connections since it also happens in RStudio. We should probably special case duckdb there, to make sure it works correctly with it.

[dfalbel]

@alearrigo I figured that duckdb has built-in support for the connections pane but it's not enabled by default. You need to set:

options("duckdb.enable_rstudio_connection_pane" = TRUE)
con <- DBI::dbConnect(duckdb(), db_file)

So you shouldn't need the connections package. However it has a bug that prevents it to work correctly. I have submitted a PR in: duckdb/duckdb-r#1063

[dfalbel]

Hi @alearrigo !

I opened rstudio/connections#19 to track the problem in the connections package, as there's no code change we can do in Positron to fix this issue.

I also sent a PR to duckdb-r: duckdb/duckdb-r#1063 which might make built-in support better.