Using PostgreSQL

Version Support

MicroLite is built to work with any version of PostgreSQL from 9.2 onwards but requires Npgsql to be installed as the .NET framework does not ship a native provider for PostgreSQL.

Installing the Provider

Install the Npgsql nuget package:

Install-Package Npgsql

Register the Provider

Add the section to your app.config/web.config so that the .NET framework knows about the Npgsql provider:

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

Make sure that the version specified matches the version you have installed or you will get an exception thrown by the .NET runtime at startup when it tries to resolve the assembly.

Connection String

Add a named connection string for your Database specifying the provider name.

<connectionStrings>
    <add name="PostgreSqlTest"
         connectionString="Server=localhost;Database=database;User Id=User;Password=Pswd;"
         providerName="Npgsql" />
</connectionStrings>

Configuring the Connection

Create a session factory using the ForPostgreSqlConnection extension method supplying the name of the connection string you added to the app.config/web.config.

using MicroLite;
using MicroLite.Configuration;

static void Main(string[] args)
{
    var sessionFactory = Configure
        .Fluently()
        .ForPostgreSqlConnection("PostgreSqlTest")
        .CreateSessionFactory();

    ...
}

Supported Identifier Strategies

MicroLite supports the following Identifier Strategies for Postgre SQL:

• Assigned
• DbGenerated (using identity columns)

Additional Notes

• PostgreSQL is very strict around the casing of column names in SQL when the column names are escaped - since MicroLite always escapes column and table names when generating SQL statements, ensure that your mapping is configured correctly.

See the Getting Started guide for further details on using MicroLite.