Local PostgreSQL Database with Entity Framework Migrations

I like to run a local copy of my database when I am developing. Mostly so that I can quickly make changes, and so that I'm not incurring charges for running a dev database somewhere in the cloud. I'm a fan of using PostgreSQL because it has some really cool features as well as being free and open source. I'll quickly show you how to set up a .Net Core 6 API to use a Local PostgreSQL Database using Entity Framework Migrations.

Setup API to use PostgreSQL Database

If you'd like to view the finished code example, it is located here in the branch named 'migrations-postgres'. Be sure to check that branch as this repo will probably be used for more examples in the future and the main branch may change.

If you are interested in a more in-depth video, check it out here:

First we need to set up our API so that Entity Framework can use Postgres. Right click on your solution and select Manage Nuget Packages for Solution. Then go to Browse and search for PostgreSQL. Select the package called Npgsql.EntityFrameworkCore.PostgreSQL and install the latest stable version to your project. As of this writing that is version 6.0.3.

Once that is complete, update your database context and tell it to use the package we just installed:

optionsBuilder.UseNpgsql(
                Environment.GetEnvironmentVariable(
                    _config.GetValue<string>("EnvKeys:DbUserConn")));

The parameter here for UseNpgsql is the connection string. I'm storing my connection string in an environment variable, but because that variable name can change between environments, I store the Name of the environment variable in my appsettings.json and use the injected _config to retrieve it. Here is how you set the environment variable in Windows:

setx TodoUserConn Host=localhost;Port=5432;Database=todo;Username=todo;Password=youpasswordhere

Then in your appsettings.json files, store a reference to the environment variable name that you use. In my case, TodoUserConn:

"EnvKeys": {
    "DbUserConn": "TodoUserConn"
}

Running Entity Framework Migrations

The migrations process for Postgres is no different than for any other sql database. To create a migration, open the Package Manager Console in Visual Studio and run Add-Migration MyFirstMigration. This will create the migration with the name MyFirstMigration and put it into a folder called Migrations at the root of your project. This migration will contain all of the data to create your tables, columns, constraints, keys, relationships, etc.

Once you are ready to apply this migration to your database, run the command Update-Database. This will apply any migrations that have not yet been applied, in the order they are created. By default this command will use the connection string that you specified above in UseNpgsql in the Program.cs file. I generally like to limit the permissions of the user that my database logs in as so that the user can't make database object changes. Luckily you can also pass a connection string to the Update-Database command. I also store this 2nd connection string as an environment variable, and the user has elevated database permissions so it can create, drop, and alter tables, views, users, roles, etc. The Package Manager Console is basically a Powershell console, so to use the environment variable you use the syntax $env:VariableName:

Update-Database -Connection $env:TodoAdminConn

For more info on the migration commands, check out the Microsoft documentation page.

Congratulations, learned how to use a PostgreSQL Database with Entity Framework Migrations!