Is there a way to get the connection a DuckDBPyRelation belongs to?

[FBruzzesi]

Hi there 👋🏼 I am a Narwhals maintainer; Narwhals is a compatibility layer that lets library authors write dataframe-agnostic code, DuckDB included. I have been working on thread-safety and hit the same wall twice, so I'd like to ask whether we're missing an API.

What I am trying to achieve

Given only a DuckDBPyRelation, handed in by the user, I'd like to be able to:

1. obtain the connection it belongs to, so we can open a per-thread .cursor() on it; and
2. failing that, at least tell whether two relations belong to the same connection.

As far as we can tell neither is possible today: nothing on a DuckDBPyRelation refers to its connection.

Why I would like to achieve it

1. So we can follow DuckDB's own threading advice on the user's behalf: the multiple threads guide says each thread must call .cursor() on the connection. When all we hold is a relation there's nothing to call .cursor() on. Concretely: reporting the schema of a TIMESTAMPTZ column requires the connection's TimeZone setting, which we read with rel.query(...). That executes on the relation's connection, so two threads inspecting schemas of relations from one connection collide with InvalidInputException: Attempting to execute an unsuccessful or closed pending query result. We currently serialize that single query behind a module-level lock. A per-relation cursor would be the correct fix, and would also stop us serializing work on connections that have nothing to do with each other.
2. So we can produce a custom error message. join, union and cross all raise Cannot combine LEFT and RIGHT relations of different connections!. Our API takes two user frames and joins them internally, so that error surfaces from deep inside our call stack with no hint that the cause is which connection each frame came from. If we could compare the two relations' connections, or just a stable identity for them, we could raise a clear library-level error up front, pointing at the actual fix.

We don't have a clear workaround for this just yet.

Questions

1. Is there a public (or blessed-private) way to obtain the connection from a relation, or a stable identifier comparable between two relations? If not, would exposing something like DuckDBPyRelation.connection be considered?
2. Failing a full connection handle, would a narrower predicate be acceptable, e.g. rel_a.same_connection_as(rel_b), purely so libraries can raise a better error?
3. Is there an idiomatic pattern we're missing for a library that wants to be thread-safe while only ever receiving relations?

Happy to open issues or (try to) contribute a PR if any of these are wanted.

---

Related: #40

[hamedrabah]

As far as the current public Python API goes, you are not missing a method: DuckDBPyRelation exposes neither its owning DuckDBPyConnection nor a stable connection identity / same_connection_as predicate.

The relation necessarily retains connection context internally, which is why cross-connection combinations can be rejected, but that context is not surfaced through the Python binding. A library also cannot safely create the per-thread cursor recommended by the threading guide from the relation alone.

Given that, the safe choices today are:

1. accept the connection together with the relation (or accept a small wrapper containing both) and create the per-thread cursor from that connection; or
2. if the API contract must accept only a relation, serialize operations that execute through it, as you are doing.

Trying a join merely to discover identity and catching the error is possible but is not a good predicate: it does real binding work and couples the library to an error string. Reaching into pybind internals would be even less stable.

A narrow public API such as same_connection_as would solve the diagnostic case without exposing connection mutation, while a read-only connection property would also enable the threading case. This looks like a legitimate feature request rather than an undocumented workaround; your proposed split between those two capabilities is sensible.