Any thoughts/future plans for using DuckDB?

[jcmkk3]

I think that SQLite is fantastic and a well proven technology choice. However, I was curious if Datasette has ever been tested using DuckDB? I know that they use/test against some of the SQLite test suite, but I'm unsure how much of a "drop-in" replacement that it would be.

Some of the "potential" advantages that I see for being able to use DuckDB:

• Performance increases for analytics type workloads
• Smaller database file sizes because of the columnar oriented data storage
• Simpler data modeling as there wouldn't really be a need to create lookup tables with the columnar compression
• Richer analytical functions (this would especially be useful around dates/timeseries analysis)
• Support for Arrow memory format

Whether it would ever be worthwhile to support is totally based on the level of effort that it would take, but I just wanted to throw it out there in case it hasn't been on your radar.

[simonw]

I'd not seen DuckDB before. It looks like it's built in top of SQLite and has a similar looking Python API, which is definitely interesting since it suggests running Datasette against it might work without any substantial code changes.

I've been mentally going back and forth on having Datasette work with other databases for quite a while now. I'm really nervous about supporting any kind of database abstraction layer as I expect it will greatly reduce the rate at which I can ship features. But... I want the tool to be as useful to as many projects as possible, and being able to talk to eg PostgreSQL would unlock a whole range of exciting new possibilities - especially around handling larger datasets.

I'm also interested in the potential of running Datasette on top of something like Amazon Athena, which would enable almost unlimited "big data" operations.

Since Datasette has a well established plugin mechanism now it's possible that this would be less difficult than I expect, given the right design of the associated plugin hooks.

So no promises yet, but I'm still thinking it over.

[Riezebos]

In case you're still thinking it over, duckdb has become even more interesting since 2020 :)
https://duckdb.org/news/

Duckdb can already import data from csv, json, parquet, arrow, sqlite and postgres so in theory it could even solve your problem of preferring not to wrap a bunch of databases!

[mrchrisadams]

For those curious - there's a plugin already for using DuckDB with datasette:

https://github.com/cldellow/datasette-parquet

When I used it I came across a few differences in the syntax between duckdb and SQLlite, which are referenced in the latest release:

https://github.com/cldellow/datasette-parquet/releases

TBH while I think there maybe scope for deeper integration, having the plugin has met my needs so far, and it was fairly quick to start messing around with parquette files from datasette once I found it.

[justquick]

fantastic! im just learning about datasette but have used duckdb for a while. its big in the science community. for ref duckdb uses Postgres SQL dialect so its a bit different from sqlite. would b great to use duckdb over sqlite imao but im not sure whether adding a orm to datasette maeks sense

[snth]

You could use sqlglot to translate between the dialects.

[mcint]

I'm quite interested in exploring its use, but these seems like a good balance point. In theory, a swapable backing-database, SQLite vs DuckDB, would be cool. In practice, the subtle differences would make for a lot of work ensuring it works, for no yet articulated nor hinted benefit. Try the plugin! See it's documented accidental and essential differences.

[virtadpt]

As a sort of universal database REST API, it's a killer app. I honestly don't know how many places I'm using it in my research.

As long as SQLite support didn't go away, I would advocate for adding additional RDBMS servers to Datasette.

[snth]

I'm currently using PostgREST and Postgresql for REST APIs on Postgres. However datasette and DuckDB might work even better for my use cases so I'm excited to see whether this will become a possibility soon.