search

fsprojects / SQLProvider

A general F# SQL database erasing type provider, supporting LINQ queries, schema exploration, individuals, CRUD operations and much more besides.
https://fsprojects.github.io/SQLProvider
Other
580 stars 146 forks source link

Support for DuckDB #819

Closed NatElkins closed 5 months ago

NatElkins commented 7 months ago

Is your feature request related to a problem? Please describe. Just that I'd like to be able to use this library with DuckDB, which is an embedded OLAP database (similar to SQLite).

Describe the solution you'd like Full support for DuckDB.

Describe alternatives you've considered Don't support it.

Additional context This .NET library exists for interfacing with DuckDB. https://github.com/Giorgi/DuckDB.NET

One thing I don't fully understand is how LINQ would get translated to the SQL that gets executed against the DB. I thought it might require something like https://github.com/dotnet/efcore/tree/main/src/EFCore.Sqlite.Core , but I couldn't find any reference to something like that in the code base.

Honestly I don't have a huge amount of time to work on this but if pointed in the right direction I might take a crack at it.

Thank you!

Thorium commented 7 months ago

The first thing SQLProvider will need is a way to query information schema: table names, column names and column types. And some kind of db-type <-> .Net type map.

This doesn't use EF (it was way too big, complex and slow, and it compiled the whole DB, not only part you need), instead this translates LINQ tree to it's own simple SQL AST (type SqlExpr) common to all providers and a provider just translates that to an SQL clause.

Typically I've seen people to create new db providers just by copying an existing one and then rewriting the function content. Here are some design info, but a lot of that is not even needed to know for writing a new provider: https://fsprojects.github.io/SQLProvider/core/techdetails.html

Thorium commented 6 months ago

Initial implementation added. Maybe some more testing needed; e.g. There wasn't schema of DataTypes in the .NET side driver, so I had to map types manually, but I wasn't really familiar how the data-types should be mapped so that they would be correct but also make usability in F# nice (here), and also what .NET side functions translated in SQL-side are best supported by DuckDB (here).