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
581 stars 146 forks source link

.NET Core/.NET Standard support (Tracking issue) #575

Open dsyme opened 6 years ago

dsyme commented 6 years ago

I'm creating a tracking issue to help assess the status of .NET Core/.NET Standard support for SQLprovider. If people can help me define what we need to assess that would be great.

Below I've partially listed some of the scenarios. The matrix is unforuntately large because of the variety of compile-time tools that may end up hosting the type provider TPDTC, but hey.

If there is a ? it means I don't know what is the highest priority thing to assess for that scenario.

What I'm really looking for is help to know what 1 or 2 scenarios we should aim to "complete" next

Description Compile-time Tool Runtime target Databases OS Works? Docs? CI?
Target .NET Framework 4.x using VS2017 devenv.exe net461 postgres, mssql, ... Windows 👍 👍 👍
fsc.exe (desktop) 👍 👍 👍
fsc.exe (dotnet) ? Windows
Target .NET Core App 2.0 using VS2017 devenv.exe netcoreapp2.0 ? Windows
fsc.exe (desktop) ?
fsc.exe (dotnet) ?
Target .NET Standard 2.0 using VS2017 devenv.exe netstandard2.0 ?
fsc.exe (desktop) ?
fsc.exe (dotnet) ?
Target .NET Core App 2.0 using .NET SDK fsc.exe (dotnet) netcoreapp2.0 ? Linux
OSX
Target Mono using Mono fsc.exe (desktop) net461 postgres Linux 👍 👍 👍
OSX 👍 👍
Thorium commented 6 years ago

.NET Framework supported databases:

.NET Core:

charlesroddie commented 6 years ago

I would guess (MSSQL, Postgres, MySQL) would be the most popular and worth investigating first.

"Support" I would take to mean: install a nuget package into a project, get typed access to the db in vs2017/vsmac/ vscode, and be able to run run locally or in asp.net/.net core/azure functions. "Support with workarounds" would require additional documented steps.

Edit: sql type provider for MSSQL using .net core is at the "support with workarounds" stage.

Thorium commented 6 years ago

Documentation running on .NET Core and .NET Standard: http://fsprojects.github.io/SQLProvider/core/netstandard.html https://github.com/fsprojects/SQLProvider/tree/master/tests/SqlProvider.Core.Tests

charlesroddie commented 6 years ago

Thanks. @j-sauer @monkieboy if the instructions in the link are not good then please leave a separate issue here.

jlogar commented 6 years ago

Hey, not sure where to put this... I tried out Firebird driver with .net core. Everything seems fine. Anything else needed to get those checks ticked? :)

Thorium commented 6 years ago

@jlogar great! Did you use Windows, Mac or Ubuntu?

jlogar commented 6 years ago

Oh, sorry, was sloppy with that. Was using Windows... Went on to create a repo to make it repeatable. https://github.com/jlogar/FbNetCoreTypeProvider

rudihorn commented 5 years ago

If I understand correctly, it is currently still necessary to have mono installed to compile projects for .net core. At least currently the .NET Core page (https://github.com/fsprojects/SQLProvider/tree/master/tests/SqlProvider.Core.Tests) still specifies:

FSharp and Dotnet installed. On Mac and Linux: Have also Mono installed.

As it currently stands at least, just trying to compile / run it on .NET Core makes it fail with:

error FS1108: The type 'Object' is required here and is unavailable. You must add a reference to assembly 'System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e'.

Is this particular issue currently being tracked? I find it a big hurdle to get started with SqlProviders on .NET Core, and it seems to be a 'hack' that has been present for well over a year. Is there a particular reason why this must be handled like this?

Thorium commented 5 years ago

As far as I know, this is a limitation of all typeproviders: if you want design time intellisense (which is the main benefit using a typeprovider in the first place), you need Mono. Or has this changed recently?

rudihorn commented 5 years ago

This depends on how Intellisense is used, if it is done by a project like IfSharp or with the VS Code F# Language Server addon then Intellisense is performed by dotnet core as well.

You are probably right though that this might be an actual issue with compatability with .NET core though, as I cannot get it to compile at all on .NET Core even with trying to add the fsc tool info. It doesn't seem to work for Sqlite or Npgsql, and surprisingly it is not the actual type provider stage that fails but only when the types are referenced (i.e. I get intellisense for the tablename, but it has issues compiling it).

Other type providers do work on dotnet core I believe, as I had used the CSV type provider within IfSharp.

This is essentially what happens: image

rudihorn commented 5 years ago

@Thorium @dsyme seemingly this is an issue related to the default behaviour of types. There are a few locations where ProvidedTypeDefiniton(...name..., None) is used, which causes ProvidedTypes.fs to pick the .NET framework Object type rather than typeof<obj>. Explicitly specifying ProvidedTypeDefinition(...name..., Some typeof<obj>) makes it compile and work fine on .NET Core.

I've been trying to somewhat follow the ProvidedTypes.fs file, but it seems like a lot of logic crammed into a single file making it a little difficult. There seems to be some old code which was commented out and handled some type of mapping from type names to F# types, but I think this is related to something else (txILTypeDef function). There also seems to be some low level assembly parsing code in that file.

rudihorn commented 5 years ago

After the latest update I'm happy to say that postgresql works targeting .NET Core on Linux. SQLite semi works, it doesn't return errors during compilation and actually finds the tables, but the tables seem to weirdly not have any columns.

It would be nice to track the support for completion in ionide F#, the F# language server and IFSharp. It seems to work with ionide F#, but I have reported an issue for the F# language server (https://github.com/fsprojects/fsharp-language-server/issues/52). I'm not entirely sure about IFSharp, but I assume it has similar issues as it did with other type providers (probably this: https://github.com/fsprojects/IfSharp/issues/213).

I think especially IFSharp and F# language server are important going forward, as they both help discoverability of the language.

Thorium commented 5 years ago

@rudihorn a few questions with your SQLite:

rudihorn commented 5 years ago

@Thorium I should be using System.Data.SQLite.Core version 1.0.110. I think it was ~/.nuget/packages/system.data.sqlite.core/1.0.110/build/net46/x64/SQLite.Interop.dll which I copied into the referenced libraries as well. As such it should also be the x64 variant. I am using CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL. I haven't really played around with this much though, as I only quickly tride SQLite to see if it works any different to Npgsql. Let me know if I can help test anything else though.

drk-mtr commented 5 years ago

I'm not sure whether this is relevant, but when using:

I need to apply this workaround: https://github.com/fsprojects/FSharp.TypeProviders.SDK/issues/244#issuecomment-485762600

As a general aside, support in Ionide tends to be good for me with recent MsSql and Sqlite providers and the fsacRuntime mode on netcore, it's usually the runtime side that's tricky from my (limited) experience.

allistercsmith commented 5 years ago

Hey all :wave:

It seems like things have improved drastically on .NET Core 2 support (2.2 to be specific) for PostgreSQL (Npgsql). I created a simple project to test this out:

Test.fsproj:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp2.2</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <Compile Include="Program.fs" />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="Npgsql" Version="4.0.7" />
    <PackageReference Include="SQLProvider" Version="1.1.65" />
  </ItemGroup>

</Project>

Program.fs:

open FSharp.Data.Sql

[<Literal>]
let ConnStr = "<DB string here>"

type SQL = SqlDataProvider<
            Common.DatabaseProviderTypes.POSTGRESQL,
            ConnStr,
            Owner="public, admin, references",
            UseOptionTypes=true>

[<EntryPoint>]
let main _ =
    let ctx = SQL.GetDataContext()
    let (firstName, lastName) =
        query {
            for e in ctx.Public.Employees do
            select (e.FirstName, e.LastName)
        } |> Seq.head
    printfn "Hello %s %s!" firstName lastName
    0

DB SQL:

CREATE TABLE employees (
    first_name text NOT NULL,
    last_name text NOT NULL
);

This seems to build and run correctly for me on Mac OS, with no additional setup required.

I'm fairly new to the F# scene, so please forgive me if I've missed something obvious. However, assuming things do truly work out-the-box these days, it would be very worthwhile to update the docs accordingly, to encourage more adoption of this package. I know from my own experience that the extra setup described in the tests and docs can be off-putting for new F# developers, and the perception around the web is that it requires a bit of setup to get working too.

If someone else can confirm that this now works out-the-box for the above scenario, I'm happy to contribute to updated documentation where possible.

Thanks for all the hard work! 🙂

knocte commented 2 years ago

I can confirm @allistercsmith experience, when I tried with .NET6.0 yesterday myself except for one extra detail that I had to do to make it work: specify the ResolutionPath (after UseOptionTypes=true), pointing to a directory where I had to copy manually 2 libraries from nuget (transitive dependencies of either Npgsql or SQLProvider or both): Microsoft.Bcl.HashCode.dll and Microsoft.Bcl.AsyncInterfaces.dll .