Unable to create database in PostreSQL using Npgsq

Question

I am attempting to set up my application to use Entity Framework with PostgreSQL, but I have run up against a problem. I have added Npqsql via nuget, and added the following provider factory to web.config:

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

With the connection string:

    <add name="MyDb" providerName="Npgsql" 
connectionString="Server=localhost;Port=5432;Database=withoomph;User ID=postgres;Password=******;enlist=true" />

It seems to connect to the DB fine, but when I try and do any sort of action on the DB I get the following exception:

FATAL: 3D000: database "withoomph" does not exist

I am setting the database intitializer correctly when the db is set up like so:

static MyDB()
{
    Database.SetInitializer<MyDB>(new CreateDatabaseIfNotExists<MyDB>());
}

So it should just simply create db when I try and do anything with my DbContext right? I don't get it, been pulling my hair out all morning!

Answer 1

Not surprisingly, since your database withoomph is not created yet, you should not able to use connection string where Database=withoomph; is mentioned.

What you can do is to either create this database manually using createdb or psql, or change your connection string temporarily to use Database=postgres; instead.

This should work because on all recent PostgreSQL versions database postgres is guaranteed to exist after vanilla install and should be used just for this purpose - to get initial authenticated connection to create another database and issue CREATE DATABASE withoomph; within your application.

However, after your new database is created, you should immediately disconnect from postgres, connect to new withoomph and continue normally.

Answer 2

In addition to mvp's answer, here's a code snippet I used to create the database. You need to run it before initializing EF. So, the trick is to switch the database name temporarily to 'postgres', which is guaranteed to exists after a vanilla install.

public void CreateDatabase(string connectionString)
{
    var builder = new NpgsqlConnectionStringBuilder(connectionString);
    var databaseName = builder.Database; // REMEMBER ORIGINAL DB NAME
    builder.Database = "postgres"; // TEMPORARILY USE POSTGRES DATABASE

    // Create connection to database server
    using (var connection = new NpgsqlConnection(builder.ConnectionString))
    {
        connection.Open();

        // Create database
        var createCommand = connection.CreateCommand();
        createCommand.CommandText = string.Format(@"CREATE DATABASE ""{0}"" ENCODING = 'UTF8'", databaseName);
        createCommand.ExecuteNonQuery();

        connection.Close();
    }
}

Answer 3

Unfortunately, Npgsql does not have (as of now) automatic schema creation code-first.

You can create your database first, and then connect to it.

Update (2016): Npgsql 3 now implements database creation code-first. Make sure you configure the correct connection factory to use NpgsqlConnections, either via code or via the XML settings, and use an appropriate Connection String.