Getting access to the default DuckDB connection.

[kyrre]

The context here is using multiple backends and then copying the result in the in-memory DuckDB for further processing. For example, first perform a Splunk query, convert the result to a PyArrow table, and pass that table to DuckDB. Then, I want to run queries with Ibis using the Databricks SQL backend and the store resulting arrow data into the same in-memory table.

How can I access the DuckDB connection that marimo uses for the default in-memory database?

When using Ibis I want to avoid having to create a new connection: con = ibis.duckdb.connect(":memory:")

Looking at the source it seems you can use

marimo/marimo/_sql/utils.py

Line 31 in 84f263e

if connection is None:

and just pass in the duckdb module:

import duckdb
con = ibis.duckdb.from_connection(duckdb)
con.create_table("insiders", arrow_table, overwrite=True)

but then you get multiple data sources in marimo:

However, these are really the same in-memory DuckDB database and if you execute the three commands below they all return the same result.

_df = mo.sql(
    f"""
    select * from insiders
    """
)

_df = mo.sql(
    f"""
    select * from insiders
    """,
    engine=con
)

_df = mo.sql(
    f"""
    select * from insiders
    """,
    engine=duckdb
)

Is this the right way to do it?

[mscolnick]

you should be able to access the default duckdb instance with just import duckdb

[kyrre]

Yes, that works, but as shown above when you create a table via Ibis using that connection it doesn't show up under sql in the data sources section. Maybe I am just confused here 😅

[mscolnick]

Oh I see, I'm not sure what ibis does under the hood but I'd guess it makes its own connection

[kyrre]

Ok, I will investigate a bit more!

[kyrre]

This will get the connection to the same in-memory DuckDB instances marimo uses.

import duckdb
con = ibis.duckdb.from_connection(duckdb)