FSharp type data provider for Postgresql

Question

I tried to use the FSharp data provider, but against Postgresql using npgsql. And I went broke on the first line.

When I try to create a SqlDataConnection, it throws an error with a message that the connection string is incorrect.

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

Now I am testing the connection string as well as the data using Servicestack.Ormlite. It mainly uses IdbConnection. So the connection is all right. But I do not know why the Type Provider is not working.

Here is the 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 the above code, the first line with comments does not work, while the same settings below the code work. This means that the problem is only with the type provider, and not with IMHO connections.

Do I need to make any other settings.

Please let me know if any other details are required.

UPDATE

After kvb comment I tried both. Here is the updated web configuration code.

 //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 the 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 the assembly in appconfig. I don’t think it will be in the 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> 

Both are commented above, and the other, without which it is not commented, does not work with an error. First error with error

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

and the second with this error

A provider like 'Microsoft.FSharp.Data.TypeProviders.DesignTime.DataProviders' reported an error: Error reading schema. error 7001: the provider did not return the 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 available. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: named pipe provider, error: 40 - Could not open connection to SQL Server). No network path was found

I still don't understand why it is looking for a SQL server.

Please let me know if further information is required.

Answer 1

I am going to post a partial answer in the hope that someone can decide how to make the next bit.

The following code compiles:

 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 the test_table in the testdb database created with

 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 in which Npgsql.dll was loaded using NuGet
• Add DbProviderFactory to your 64-bit machine.config.NET ( "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config" ). This is the same as in your app.config, but added to the corresponding section in the machine.config file, I have one entry at the moment for the 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.

SqlEntityConnection now compile during development, and you will be able to see all the available tables. It also compiles successfully to an executable file.

This answers your question; but now for a weird bit that will mean that you are still not happy. When you run this code, it throws an ArgumentException as soon as dbSchema.GetDataContext() is called saying:

The connection string must be either a valid provider connection string or a valid connection string accepted by EntityClient.

Internal exception states

The keyword 'server' is not supported.

with stack trace

in System.Data.EntityClient.EntityConnectionStringBuilder.set_Item (String keyword, Object value) in System.Data.Common.DbConnectionStringBuilder.set_ConnectionString (string value) in System.Data.EntityClient.EntityConnectionStringBuilder..ctor (SqtectionString). dbSchema.GetDataContext ()

I tried messing up the connection string to make it work, but I think this should be a mistake in how the provider creates the connection string at run time and development time. Since this code is emitted into a dynamic assembly, it is not clear how you could look at the code to see what was going on.

Answer 2

I face the same difficulty. This is fixed by adding this to 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>