Converting Entity Framework MSSQL to PostgreSQL
I've used MSSQL with Entity Framework for many years. I watched the good and bad blossom from strongly typed datasets, then Linq2Sql, and finally to Entity Framework. I've recently spent time working with Postgres, and I was curious if Entity Framework worked with Postgres in the same way it works with MSSQL. To test this, I modified a project already configured with MSSQL.
Running the database
Running new databases used to be annoying, but Docker makes this very simple.
docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres
I used the JetBrains Rider database tool to connect and verify it was running.
Update migrations
With the database running, I removed the existing migrations. This project's never connected to Postgres, so we're at no risk of hurting data. I'm on Mac, so I install Entity Framework Migrations globally.
dotnet tool install --global dotnet-ef
Now I remove the existing migrations from the database.
dotnet ef migrations remove
I got an error because it's already connected to a SQL Server, so I fixed it by rolling back the migration and then updating it.
dotnet ef database update 0 && dotnet ef migrations remove
Code updates
Next, I took a look at the table setup. This one is simple, with only one table. I specifically didn't modify code to see if everything worked out of the box.
[Table("PageMasks")]
public class PageMaskDto
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid PageMaskId { get; set; }
[Required]
[MaxLength(2083)]
public string TargetUrl { get; set; }
[MaxLength(60)]
public string Title { get; set; }
[MaxLength(160)]
public string Description { get; set; }
[MaxLength(2083)]
public string Image { get; set; }
public DateTime CreateDateUtc { get; set; }
public DateTime UpdateDateUtc { get; set; }
}
The real update requires replacing the database connector to use Postgres. Opening the csproj shows the following entries.
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.1.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.0" />
Replace Microsoft.EntityFrameworkCore.SqlServer with Npgsql.EntityFrameworkCore.PostgreSQL. You can edit the csproj directly, but I opted to use the CLI.
dotnet remove package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
From here it's just hunting down code specific to the SqlServer connector. I have a SqlContextFactory used to build the database from the project configuration for migrations.
public class DesignTimeSqlContextFactory
: IDesignTimeDbContextFactory<MetamaskSqlContext>
{
/// <summary>
/// Builds a ServiceSqlContext by looking at app settings json file and
/// uses the connection string.
/// </summary>
/// <param name="args">Arguments passed in from the ef pipeline.</param>
/// <returns>A new ServiceSqlContext for scaffolding.</returns>
public MetamaskSqlContext CreateDbContext(string[] args)
{
var projectDir = Directory.GetCurrentDirectory();
var projectName = Path.GetFileName(projectDir);
var webProjectPath = Path.Combine(projectDir, $"../{projectName}");
var configuration = new ConfigurationBuilder()
.SetBasePath(webProjectPath)
.AddJsonFile("appsettings.json")
.Build();
var builder = new DbContextOptionsBuilder<MetamaskSqlContext>();
var connectionString = configuration.GetConnectionString("SqlDatabaseConnection");
builder.UseSqlServer(connectionString);
return new MetamaskSqlContext(builder.Options);
}
}
The builder.UseSqlServer line is the offender. I replaced that line with builder.UseNpgsql(connectionString);. Easy enough.
public class DesignTimeSqlContextFactory
: IDesignTimeDbContextFactory<MetamaskSqlContext>
{
/// <summary>
/// Builds a ServiceSqlContext by looking at app settings json file and
/// uses the connection string.
/// </summary>
/// <param name="args">Arguments passed in from the ef pipeline.</param>
/// <returns>A new ServiceSqlContext for scaffolding.</returns>
public MetamaskSqlContext CreateDbContext(string[] args)
{
var projectDir = Directory.GetCurrentDirectory();
var projectName = Path.GetFileName(projectDir);
var webProjectPath = Path.Combine(projectDir, $"../{projectName}");
var configuration = new ConfigurationBuilder()
.SetBasePath(webProjectPath)
.AddJsonFile("appsettings.json")
.Build();
var builder = new DbContextOptionsBuilder<MetamaskSqlContext>();
var connectionString = configuration.GetConnectionString("SqlDatabaseConnection");
builder.UseNpgsql(connectionString);
return new MetamaskSqlContext(builder.Options);
}
}
The next issue is my Startup.cs file. I use dependency injection to bring in the DbContext, and it needs to be updated.
services
.AddDbContext<MetamaskSqlContext>(opts =>
{
opts.UseSqlServer(connectionStrings.SqlDatabaseConnection,
sqlServerOptionsAction: sqlOptions =>
{
sqlOptions.EnableRetryOnFailure(3,
TimeSpan.FromSeconds(5),
policies.GetSqlRetryCodes());
});
});
There's a replacement for this as well.
services
.AddDbContext<MetamaskSqlContext>(opts =>
{
opts.UseNpgsql(connectionStrings.SqlDatabaseConnection);
});
You'll notice I removed EnableRetryOnFailure. Not because Npgsql doesn't support Polly retry policies. It does. I need to look into which error codes specifically I want to check for before I re-implement them. Now I need to update the connection strings to work with Postgres.
{
"ConnectionStrings": {
"SqlDatabaseConnection": "Server=127.0.0.1;Database=metamask;User=sa;Password=yourStrong(!)Password;MultipleActiveResultSets=true"
}
}
Classic Sql Server connection string becomes:
{
"ConnectionStrings": {
"SqlDatabaseConnection": "Server=127.0.0.1;Database=metamask;Username=postgres;Password=mysecretpassword"
}
}
Note: These connection strings are based on the default docker images for SqlServer and Postgres, so they're easy to test.
At this point, the project builds. The only thing left to do is run the migration. I know the casing isn't going to be standard to Postgres, so I added a method to the model builder. I added a quick string extension class.
using System.Text.RegularExpressions;
public static class StringExtensions
{
public static string ToSnakeCase(this string input)
{
if (string.IsNullOrEmpty(input)) { return input; }
var startUnderscores = Regex.Match(input, @"^_+");
return startUnderscores + Regex.Replace(input, @"([a-z0-9])([A-Z])", "$1_$2").ToLower();
}
}
I can update the model to use the correct casing.
dotnet add package Microsoft.EntityFrameworkCore.Relational
This package gives some extra tools for messing with the model builder. Open the DBContext class and override when the model's created.
protected override void OnModelCreating(ModelBuilder builder)
{
foreach(var entity in builder.Model.GetEntityTypes())
{
entity.SetTableName(entity.GetTableName().ToSnakeCase());
foreach(var property in entity.GetProperties())
property.SetColumnName(property.GetColumnName().ToSnakeCase());
foreach(var key in entity.GetKeys())
key.SetName(key.GetName().ToSnakeCase());
foreach(var key in entity.GetForeignKeys())
key.SetConstraintName(key.GetConstraintName().ToSnakeCase());
foreach(var index in entity.GetIndexes())
index.SetName(index.GetName().ToSnakeCase());
}
}
This code globally iterates the generated table names and formats them to the correct casing. Thank you Animesh Bulusu for pointing me in the right direction. At this point, I can re-execute the migrations.
dotnet ef migrations add Initial
The moment of truth. Update the database.
dotnet ef database update
I took the JetBrains Rider database tool and connected it. Sure enough, the tables are there. When I ran the application, everything worked perfectly.
Although the project's small, it seems like Entity Framwork works similar to MSSQL when using Postgres. I'll continue to use Postgres, and if I see any apparent issues, I'll update the article.