FSharp data type provider for Postgresql

Question

I was trying FSharp data provider but against the Postgresql using npgsql. And I busted at very first line.

When I am trying to create SqlDataConnection it is throwing error with message the connection string is not correct.

The type provider 'Microsoft.FSharp.Data.TypeProviders.DesignTime.DataProviders' reported an error: Keyword not supported: 'port:5432;database'.

Now, I test connection string and also data using Servicestack.Ormlite. That basically uses IdbConnection. So, connection is all correct. But I don't know why Type Provider is not working.

Here is code.

    //type dbSchema = SqlDataConnection<ConnectionString = "Server=localhost;Port=5432; Database=TestDB;User Id=postgres;Password=g00gle*92;" >
[<CLIMutable>]
type Person = 
    { ID : int;
      FirstName : string;
      LastName : string }

[<EntryPoint>]
let main args = 
    let dbFactory = 
        OrmLiteConnectionFactory
            (
             "Server=localhost;Port=5432; Database=TestDB;User Id=postgres;Password=*****;", 
             PostgreSqlDialect.Provider)
    use dbConnection = dbFactory.OpenDbConnection()
    Console.WriteLine dbConnection.State
    let persons = dbConnection.Select<Person>()
    persons.ForEach(fun p -> Console.WriteLine p.FirstName)
    Console.Read() |> ignore
    0

In above code first commented line is not working while with same settings below code is working. That means issue is only with type provider not with connections IMHO.

Do I need to do any other settings.

Please let me know if any other details are required.

UPDATE

After kvb's comment I tried both. Here is updated code with web config.

//type dbSchema = SqlEntityConnection<ConnectionStringName = "TestDB", Provider="Npgsql">
    type dbSchema = SqlEntityConnection< ConnectionStringName="TestDB" >

    [<CLIMutable>]
    type Person = 
        { ID : int;
          FirstName : string;
          LastName : string }

    [<EntryPoint>]
    let main args = 
        let dbFactory = 
            OrmLiteConnectionFactory
                (
                 "Server=localhost;Port=5432; Database=TestDB;User Id=postgres;Password=*******;", 
                 PostgreSqlDialect.Provider)
        use dbConnection = dbFactory.OpenDbConnection()
        Console.WriteLine dbConnection.State
        let persons = dbConnection.Select<Person>()
        persons.ForEach(fun p -> Console.WriteLine p.FirstName)
        Console.Read() |> ignore
        0

And here is web config

  <system.data>
    <DbProviderFactories>
      <add name="Npgsql Data Provider"
            invariant="Npgsql"
            description="Data Provider for PostgreSQL"
            type="Npgsql.NpgsqlFactory, Npgsql" />
    </DbProviderFactories>
  </system.data>
  <connectionStrings>
    <add name="TestDB"
          connectionString="Server=localhost:5432; Database=TestDB;User Id=postgres;Password=******;"
          providerName="Npgsql" />

  </connectionStrings>

and here is assembly in appconfig. I don't think it will be in GAC as I added via nuget

 <dependentAssembly>
    <assemblyIdentity name="Npgsql" publicKeyToken="5d8b90d52f46fda7" culture="neutral" />
    <bindingRedirect oldVersion="0.0.0.0-2.0.12.0" newVersion="2.0.12.0" />
 </dependentAssembly>

Above both one is commented and another one without which is not commented both is failing with different error. First one is failing with error

The type provider 'Microsoft.FSharp.Data.TypeProviders.DesignTime.DataProviders' reported an error: Error reading schema. error 7001: The specified store provider 'Npgsql' cannot be found in the configuration, or 'Npgsql' is not valid. Unable to find the requested .Net Framework Data Provider. It may not be installed.

and second one is with this error

The type provider 'Microsoft.FSharp.Data.TypeProviders.DesignTime.DataProviders' reported an error: Error reading schema. error 7001: The provider did not return a ProviderManifestToken string. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) The network path was not found

I still not understand why it is searching for SQL server.

Please let me know if any further infromation required.

Answer 1

I'm going to post a partial answer in the hope that someone can work out how to do the next bit.

The following code will compile:

open Microsoft.FSharp.Data.TypeProviders
open System.Data.Entity // this is important -- you cannot see any tables without it

type internal dbSchema = 
    SqlEntityConnection<
        ConnectionString="Server=localhost;Database=testdb;User Id=postgres;Password=password;", 
        Provider="Npgsql">

[<EntryPoint>]
let main argv = 
    let context = dbSchema.GetDataContext()
    query { for item in context.test_table do
            select item }
    |> Seq.iter (fun item -> printfn "%A" item)
    0

For a table test_table in database testdb created via

CREATE TABLE test_table
(
  id integer NOT NULL,
  value text,
  CONSTRAINT "PK_test_x_Id" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE test_table
  OWNER TO postgres;

To do this you need to do four things:

• GAC Npgsql.dll ("C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\x64\gacutil.exe" /i [filename])
• GAC Mono.Security.dll (in the same directory that Npgsql.dll was downloaded to by NuGet
• Add the DbProviderFactory to your .NET 4 64-bit machine.config ("C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config"): that is the same thing you have in your app.config, but added to the appropriate section in machine.config, mine has one entry at the moment for Microsoft SQL Server Compact Data Provider. Remember to include the correct public key token.

<system.data>
  <DbProviderFactories>
    <add name="Npgsql Data Provider"
      invariant="Npgsql"
      description="Data Provider for PostgreSQL"
      type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.12.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
</DbProviderFactories>
</system.data>

• Restart visual studio.

Now the SqlEntityConnection compile at design time and you will be able to see all of the tables that are available. This will also happily compile into an executable.

That answers your question; but now for the weird bit which will mean you are still not happy. When you run this code it will throw an ArgumentException as soon as dbSchema.GetDataContext() is called saying:

The supplied connection string should be either a valid provider-specific connection string or a valid connection string accepted by the EntityClient.

The inner exception states

The 'server' keyword is not supported.

with stack-trace

at System.Data.EntityClient.EntityConnectionStringBuilder.set_Item(String keyword, Object value) at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value) at System.Data.EntityClient.EntityConnectionStringBuilder..ctor(String connectionString) at SqlEntityConnection1.dbSchema.GetDataContext()

I've tried frigging the connection string to get it to work, but I think that this must be a bug in how the provider is creating the connection string at run-time vs design-time. Since this code is emitted into a dynamic assembly it isn't obvious how you could look at the code to see what is going on.

Answer 2

I meet the same difficulty. This is fixed by adding this in the App.config file :

<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
  <parameters>
    <parameter value="v11.0" />
  </parameters>
</defaultConnectionFactory>
<providers>
  <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, Npgsql.EntityFramework" />
</providers>