.NET with PostgreSQL, MySQL or SQL Server: providers

.NET works well with all three. SQL Server has the deepest tooling and the provider Microsoft writes itself, PostgreSQL has Npgsql, which is excellent and as fast as anything in the ecosystem, and MySQL has MySqlConnector underneath the community Pomelo provider for Entity Framework Core. The choice is about the database rather than the driver: SQL Server Express is free but capped at 10 GB per database and about 1.4 GB of buffer memory; PostgreSQL has no edition limits and the richest feature set; MySQL is everywhere and the lightest to run. Once you have picked, the parts that cause trouble are the same for each - the connection string, pool sizing, how dates and strings behave, and keeping migrations provider-specific. This post covers all of them side by side.

The providers and drivers#

Every .NET database stack has two layers. At the bottom is an ADO.NET driver that speaks the database's wire protocol and implements DbConnection and DbCommand. On top, optionally, sits an Entity Framework Core provider that translates LINQ into that database's SQL dialect. Dapper and hand-written SQL use the driver directly.

A few notes on that table:

• `System.Data.SqlClient` is the old SQL Server driver and is deprecated. New code and every current EF Core version use Microsoft.Data.SqlClient. Copying a sample that uses the old namespace still compiles, and gives you a driver with older defaults and no new fixes.
• For MySQL, prefer MySqlConnector. It is MIT-licensed, truly asynchronous (Oracle's MySql.Data historically implemented its async methods synchronously), and it is what Pomelo uses. Oracle's packages are GPL with a FOSS exception, which matters for some commercial projects.
• Pomelo releases follow EF Core majors, sometimes months behind. Before upgrading EF Core to a new major version, check that a matching Pomelo release exists. Npgsql has usually shipped its EF Core provider close to Microsoft's release.

Wiring each one up with EF Core#

The registration looks almost identical across the three, which is the point of EF Core:

// PostgreSQLbuilder.Services.AddDbContext<AppDbContext>(o =>    o.UseNpgsql(builder.Configuration.GetConnectionString("Default")));// MySQL via Pomelovar mysql = builder.Configuration.GetConnectionString("Default");builder.Services.AddDbContext<AppDbContext>(o =>    o.UseMySql(mysql, ServerVersion.AutoDetect(mysql)));// SQL Serverbuilder.Services.AddDbContext<AppDbContext>(o =>    o.UseSqlServer(builder.Configuration.GetConnectionString("Default")));

Pomelo needs the server version because MySQL and MariaDB differ in the SQL they accept. ServerVersion.AutoDetect opens a connection at startup to ask, which fails the app's startup if the database is unreachable at that moment; new MySqlServerVersion(new Version(8, 4, 0)) avoids the round trip and is the better choice in production.

For Npgsql, version 7 and later recommend building an NpgsqlDataSource once and handing it to EF Core or using it directly, which is where type mappings, enums and logging are configured:

var dataSource = new NpgsqlDataSourceBuilder(    builder.Configuration.GetConnectionString("Default")).Build();builder.Services.AddDbContext<AppDbContext>(o => o.UseNpgsql(dataSource));

GetConnectionString("Default") reads ConnectionStrings:Default from configuration, which you set in production with an environment variable named ConnectionStrings__Default. The connection string is a secret; it does not belong in appsettings.json in a repository. ASP.NET Core configuration and secrets covers the layering.

Connection strings side by side#

PostgreSQL (Npgsql)Host=db.example.net;Port=5432;Database=app;Username=app;Password=...;SSL Mode=PreferMySQL (MySqlConnector)Server=db.example.net;Port=3306;Database=app;User ID=app;Password=...;SslMode=PreferredSQL Server (Microsoft.Data.SqlClient)Server=tcp:db.example.net,1433;Database=app;User ID=app;Password=...;Encrypt=True;TrustServerCertificate=True

The differences that trip people up:

• SQL Server puts the port after a comma, not a colon, and has no separate Port keyword. db.example.net:1433 is read as a host name and fails. The same comma appears in SQL Server Management Studio's server name field.
• SQL Server encrypts by default. Since Microsoft.Data.SqlClient 4.0 (and so since EF Core 7), Encrypt defaults to True, and the driver validates the server's certificate. A server with a self-signed certificate - which is what SQL Server generates for itself when none is configured - fails with "The certificate chain was issued by an authority that is not trusted" until you add TrustServerCertificate=True or install a trusted certificate. SQL Server connection strings goes through every driver's version of this.
• MySQL 8.4 authenticates with `caching_sha2_password`. Over an unencrypted connection, the first login needs the server's RSA public key, and MySqlConnector refuses with "Retrieval of the RSA public key is not enabled for insecure connections" unless you either use TLS (SslMode=Required) or add AllowPublicKeyRetrieval=True. Prefer TLS.
• Npgsql's default is `SSL Mode=Prefer`, which uses TLS when the server offers it without validating the certificate. Use Require to refuse plaintext, and VerifyFull with a CA you trust when you need the certificate checked.

Passwords containing ; or = must be quoted in ADO.NET connection strings - Password="a;b=c" - or built with the driver's ConnectionStringBuilder class, which escapes for you.

Connection pooling and timeouts#

All three drivers pool connections by default, one pool per distinct connection string per process. Opening a connection takes one from the pool; disposing it returns it. That is why the standard pattern - a DbContext per request, disposed at the end - is cheap.

Values are in seconds. A hundred connections per process is far more than a small database server can use well. PostgreSQL in particular spends real memory on every connection, and a database on a 1 or 2 GB plan has a max_connections well below what three app instances with default pools could open. Size the pool to the database, not the other way round: twenty to thirty connections is plenty for most apps on a small server, and an app that needs more is usually holding connections too long. Connection pools and limits explains why a smaller pool is often faster.

When the pool is exhausted, requests wait for the connect timeout and then fail with a pool timeout error. That error almost never means the pool is too small. It means connections are leaking (not disposed) or held across slow work - an HTTP call made in the middle of a transaction, for example.

EF Core has its own, separate pool: AddDbContextPool<AppDbContext>() reuses DbContext instances (up to 1024 by default) to save allocation. It does not change how many database connections exist, and it requires that your context holds no per-request state.

Retries and transient failures#

Networks drop packets and databases restart. EF Core can retry failed operations automatically:

o.UseSqlServer(conn, sql => sql.EnableRetryOnFailure(    maxRetryCount: 5, maxRetryDelay: TimeSpan.FromSeconds(10), errorNumbersToAdd: null));o.UseNpgsql(conn, pg => pg.EnableRetryOnFailure());o.UseMySql(conn, version, my => my.EnableRetryOnFailure());

With retries on, a transaction you open yourself with BeginTransactionAsync throws, because EF Core cannot replay half of a transaction. Wrap the whole unit in the execution strategy instead:

var strategy = db.Database.CreateExecutionStrategy();await strategy.ExecuteAsync(async () =>{    await using var tx = await db.Database.BeginTransactionAsync();    // ... several SaveChangesAsync calls    await tx.CommitAsync();});

Retries hide brief blips. They make a database that is genuinely down look like a very slow app for the duration of the retries, so keep the counts modest and log each retry.

Differences that bite when you switch#

EF Core hides the SQL dialect. It does not hide the database's behaviour, and these are the ones people meet when moving an app from one engine to another or writing tests against SQLite and running production on something else:

• String comparison and case. SQL Server's default collation (SQL_Latin1_General_CP1_CI_AS) and MySQL 8's default (utf8mb4_0900_ai_ci) compare case-insensitively, so WHERE Email = 'Bob@x.com' finds bob@x.com. PostgreSQL compares case-sensitively. A login lookup that worked on SQL Server silently fails on PostgreSQL. Normalise emails on write, or use a case-insensitive collation or the citext type on PostgreSQL.
• Dates and time zones. Npgsql 6 and later map DateTime to timestamp with time zone and refuse to write a DateTime whose Kind is not Utc, with "Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone'". Store UTC everywhere, or use DateTimeOffset. SQL Server's datetime2 and MySQL's datetime store no zone at all, so the same code writes local times without complaint and the bug shows up later.
• Unicode. On SQL Server, EF Core maps string to nvarchar, which is Unicode. Hand-written tables with varchar columns can lose characters outside the collation's code page. On MySQL, use utf8mb4 - the older utf8 alias is three-byte and cannot store emoji. SQL Server collations and Unicode and MySQL utf8mb4 and collations go deeper.
• String length defaults. An unbounded string property becomes nvarchar(max) on SQL Server, longtext on MySQL and text on PostgreSQL. On SQL Server, nvarchar(max) columns cannot be index keys; give properties you will filter on a [MaxLength].
• Identity values. All three generate keys, through IDENTITY, AUTO_INCREMENT or identity columns backed by sequences. Gaps are normal on all of them after rollbacks and restarts; never use the key as a count.
• Migrations are provider-specific. A migration generated against SQL Server contains SQL Server column types and annotations. Switching engines means regenerating the migrations from scratch for the new provider, not reusing the folder.

Choosing the database for a .NET app#

On features alone, any of the three will run a typical web app. The choice usually comes down to limits, tooling and what you already know.

SQL Server Express suits teams already living in SQL Server Management Studio, apps that use SQL Server-specific features (temporal tables, T-SQL stored procedures, full SQL Server tooling), and anything that may later move to a paid SQL Server edition or Azure SQL. Its limits are real: each database is capped at 10 GB of data, the buffer pool at about 1,410 MB, and compute at the lesser of one socket or four cores, with no SQL Server Agent for scheduled jobs. SQL Server Express hosting covers when that is enough.

PostgreSQL is the default recommendation for a new project with no other constraint: no edition limits, excellent JSON support, rich indexing, and Npgsql is first-rate. PostgreSQL remote connections covers the first connection.

MySQL is the lightest of the three to run and familiar to almost everyone. It is a fine choice when the app already targets it or shares a database with PHP software.

The longer comparison, including MongoDB and Valkey, is in which database should I use.

On RE:NODE, each of these is a separate database plan with credentials generated per server and reached on the plan's host and port: PostgreSQL from $6 a month, MySQL 8.4 LTS from $4 with a root password, an application database and an application user generated, and SQL Server 2022 Express from $6 with an sa password and a database created and set as sa's default. Database plans have no proxy slot - the app connects straight to the host and port. C# / .NET app plans also include database slots of their own, created in the panel with a generated host, user and password.

Troubleshooting#

`A network-related or instance-specific error occurred` (SQL Server). Wrong host or port, a colon instead of a comma before the port, or a firewall. Test the port first.

`The certificate chain was issued by an authority that is not trusted` (SQL Server). Encryption is on by default and the server's certificate is self-signed. Add TrustServerCertificate=True, or install a certificate from a trusted CA.

`Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool`. Connections are leaking or held too long. Look for contexts or connections created outside using, and work done inside open transactions.

`53300: sorry, too many clients already` (PostgreSQL) or `Too many connections` (MySQL). The pools of all your app instances together exceed the server's limit. Lower Maximum Pool Size.

`Unable to connect to any of the specified MySQL hosts`. Host, port or firewall. If the host is right, check whether the user is allowed to connect from your address.

FAQ#

Is Entity Framework Core slower than Dapper?

For simple queries, modern EF Core is close, especially with AsNoTracking() and projections. Dapper still wins on raw mapping overhead and gives you exact control over SQL. Many apps use EF Core for writes and migrations and Dapper for a few heavy read queries, on the same connection string.

Can I use SQLite in tests and SQL Server in production?

You can, and it will hide bugs: case sensitivity, date handling, transaction behaviour and SQL translation all differ. Run integration tests against the same engine as production, in a container if need be.

Which MySQL provider should I use with EF Core?

Pomelo, on top of MySqlConnector. It is the most widely used and most complete. Check that its release for your EF Core major version exists before upgrading EF Core.

Do I need TLS between my app and the database?

If the connection crosses any network you do not control - which includes the internet between an app server and a database server - yes. All three drivers support it; SQL Server's driver turns it on by default.

How many connections should my pool allow?

Fewer than you think. Start at twenty to thirty per app instance on a small database, make sure the total across instances stays under the database's limit, and only raise it if you can show requests waiting on the pool while the database itself is idle.