Arrow Database Connectivity (ABDC) driver-based sql connections

[corleyma]

Description

I want to use Marimo's data source connections with engines that aren't currently supported. In particular, I want to use it with analytics-focused query engines, ideally maintaining good performance even when retrieving large result sets.

Suggested solution

Supporting ADBC driver-based connections, so any db/query engine with an adbc driver could be used as a SQL connection in Marimo.

From the ADBC docs:

ADBC is a set of APIs and libraries for Arrow-native access to databases. Execute SQL and Substrait queries, query database catalogs, and more, all using Arrow data to eliminate unnecessary data copies, speed up access, and make it more convenient to build analytical applications.

Aside from expanding supported engines in Marimo, ADBC-based connections could also potentially improve performance if used as an alternative to some existing sqlalchemy-based connections.

Are you willing to submit a PR?

• Yes

Alternatives

No response

Additional context

I haven't yet looked deeply at how SQL connections are implemented in Marimo today. If there is aligned interest in this feature, I would appreciate any guidance/potential footguns to consider from any maintainers familiar with the implementation.

[Light2Dark]

Any specific databases you have in mind? We support sqlalchemy, sqlmodel, ibis and some custom engines today.
And some sqlalchemy engines may support adbc connections, eg. https://github.com/michalc/pgarrow

[corleyma]

The engine I am interested in is Dremio, which does have an unofficial sqlalchemy driver that I would rather not depend on.

That adbc-based postgresql sqlalchemy driver is an interesting, newer project; thanks for sharing.

I may have some conceptual gaps since I haven't looked yet looked deeply at how marimo integrates other sources. I suppose the right thing to do depends on how much marimo depends on sqlalchemy to abstract details of interacting with backends vs merely as a connection engine. If it's more the latter, then adding support for adbc in general makes sense, but if it's more the former then it might require more thought.

[corleyma]

Ok, I did a little bit more homework, and it looks like Marimo does support the notion of SQLConnections with just a query engine (no catalog) component, and even already uses this interface to supports any DBAPI2.0 connection.

It seems like that would be a query engine-only SQL connection would be a reasonable target for generic "support any ADBC driver", and wouldn't be very hard to contribute. Do you have any interest in accepting a contribution like that?

For my usecase, I actually do want the catalog integration as well, and for that I think I'd need to add a dedicated dremio connection type. It could possibly leverage base adbc driver support described above, and only need to implement the catalog interfaces. Would you be willing to accept a contribution like that?

[mscolnick]

@corleyma we'd gladly accept a PR to support ADBC Apis. Is this an agreed upon spec? I couldn't find the source of it.

For a dremio catalog, can we revisit in a follow up? We haven't been doing many one-off catalog support and want to avoid the additional maintenance. We been leaning on sqlalchemy for this.