Update `content/core/sqlite.fsx` with Quick Start

[johnW-ret]

There seem to be quite a few open issues regarding using SQLProvider with SQLite regarding dependencies and DLLs.

I understand that possibilities alone of just

   fsi | fsproj | Polyglot Notebook
   *
   net46 | net8+
   *
   Microsoft.Data.Sqlite | System.Data.SQLite | Mono.Data.SQLite

is quite a lot before you even begin talking about versions, copying dlls, local environments, etc.

However, could we get an addition or rewrite of sqlite.fsx to have a quick-start for a net9 console app?

My particular issue is that I have copied Microsoft.Data.Sqlite.dll and e_sqlite3.dll to my ResolutionPath, and when I exclude SQLitePCLRaw.core.dll, I get ... Could not load file or assembly 'SQLitePCLRaw.core, Version=2.1.2.1721, but when I include it I get You need to call SQLitePCL.raw.SetProvider(). If you are using a bundle package, this is done by calling SQLitePCL.Batteries.Init(). (Docs do not address (?) what to pass to SetProvider and Init does nothing).

In my case though, I don't really care whether I use Microsoft.Data.Sqlite or System.Data.SQLite. I've gotten SQLProvider to work in a notebook but now I can't replicate it. Getting every scenario perfectly working for everyone will probably always be hard to iron out but FSharp.Data is dead simple and I would appreciate having / being able to contribute to an easy onboarding devex for the simple fsproj * net9 * <whatever is easiest> use case and go from there.

As a side note, I am using Ionide, but I am reporting on the output of the dotnet build command. I can figure out tooling once I get this to even compile.

[johnW-ret]

So it turns out... maybe I had a stupid? Anyone else looking to fix this issue, I did the below:

1. dotnet add package SQLProvider
   dotnet add package Microsoft.Data.Sqlite
   

2. Compile the project (irrespective of errors) so all the referenced libs are in my bin/Release/tfm directory
3. Point ResolutionPath to that directory so the necessary libs are referenced.
4. Copy e_sqlite3.dll (from here I think) to the ResolutionPath as instructed here.

And it works!

---

I don't know if my request would necessarily be to add these steps to content/core/sqlite.fsx verbatim, but before I work on a PR - if one was opened that was something to that effect, would it even be considered?

I'd also like to inquire about what the canonical steps are - even if what I did was actually recommended? Something akin to what SqlProvider.Core.Tests does? Because of course bin and obj get deleted often and I still don't know what I did that made it work in a notebook. Something like a PowerShell or fsi script that copies the necessary DLLs from .nuget/packages to a directory of your choice I would be willing to contribute to.

[Thorium]

I recommend copying the dlls to a separate path, not bin/Release, so that they stay there, and commit the proper files to your repo.

The issue is that after .NET Core, the database drivers have become like node modules, referencing half the internet. SQLProvider loads the driver dynamically (there is no fixed dependency on a single driver version), and that flexibility makes usability hard. Depending on your hardware and software environment, you want different versions (x86/x64/..., Mac/Windows/Ubuntu/...).

Also, the documentation is hard to keep up-to-date because these drivers often update, and conventions like "If you are using a bundle package, this is done by calling" come from the underlying database driver, not SQLProvider.

I will definitely accept PRs for improvements on documentation/user experience.