Support for DuckDB
Closed this issue · 2 comments
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!
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
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).