Photo by If you’re anything like me, you set up your Entity Framework Core DbContext and your connectionString once in a blue moon and forget about it. Connection strings are essential parts of a functioning application, but we forget the nuance in setting them up since we don’t interact with them very often.
We’ll walk through setting up the connection strings for SQL Server, Azure SQL, SQLite, PostgreSQL, MySQL, and MariaDB in this post. These are the most popular databases supported by Entity Framework Core and likely what most developers are reaching for when starting a new application.
SQL Server and Azure SQL
Likely the most common database used in the .NET space, SQL Server connection strings can range from straightforward to amazingly complex. I’ve tried to list what I think the most common configurations will be for most folks.
It’s also important to note that the SQL Server data provider uses System.Data.SqlClient to communicate with the database instance. In addition, the library has resiliency measures built-in to handle transient errors that are almost guaranteed to occur in a cloud environment.
For Azure SQL users, you’ll likely want to use the connection string provided in the environment created by your Azure administrators. Later, you’ll see how to pull a connection string from an application’s configuration.
SQL Server Standard Connection
This connection string is for folks who use a locally installed SQL Server instance with all the out-of-the-box configuration. Change the values for the server location, database name, user, and password.
public class Database: DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) =>
optionsBuilder
.UseSqlServer("Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;");
}
SQL Server Connection With Port
One of life’s greatest annoyances is the choice for SQL Server to adopt the comma (,) as a port separator. As a result, ports can change depending on the hosting environment. An example might be folks hosting SQL Server inside of a Docker container.
public class Database: DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) =>
optionsBuilder
// change the port number
.UseSqlServer("Server=myServerAddress,11433;Database=myDataBase;User Id=myUsername;Password=myPassword;");
}
SQL Server LocalDB Connection
LocalDB is still popular amongst developers doing local development in a Windows environment. LocalDB uses the SQL Express database engine and allows users to manage and create database instances without the added trouble of setting up a new server every time.
Be sure that the instance name matches your locally installed instance name. In the example below, the name of our LocalDB installation is v11.0, but this might be different in your development environment.
public class Database: DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) =>
optionsBuilder
.UseSqlServer("Server=(localdb)\v11.0;Initial Catalog=myDataBase;Integrated Security=true;");
}
SQLite Data Provider
In my opinion, SQLite is the most flexible database option on the list in this blog post. The database can run purely from a native driver on multiple platforms with little more than a connection string.
Before setting up the connection string for our SQLite database, we’ll need to install the Microsoft.EntityFrameworkCore.Sqlite Nuget package.
SQLite Standard Connection
Unlike other database connection strings, SQLite is relatively straightforward. We only need to have a Data Source and the path to our SQLite database file. Of course, there are other settings, but they are generally unnecessary when using EF Core early on.
public class Database: DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) =>
optionsBuilder
.UseSqlite("Data Source=database.db");
}
SQLite with Password Protection
If we store sensitive information in our SQLite database, we may want to add a password to access the database. The password protection prevents other processes from finding our database and altering the data without our consent.
public class Database: DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) =>
optionsBuilder
.UseSqlite("Data Source=database.db; Password = securePassword");
}
SQLite In-Memory Connection String
While Entity Framework Core has an in-memory database provider, it utilizes LINQ to Objects. Therefore, any behavior coded against the in-memory provider will be different when run against another database provider. When using SQLite, we can run the database provider in-memory while also using the same engine to write to disk. This SQLite feature has clear advantages over the built-in EF Core in-memory provider as we can write to disk when we are ready to, or we can utilize the in-memory version of SQLite for speedier tests.
public class Database : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) =>
optionsBuilder
.UseSqlite(SqliteInMemoryDatabase.Connection);
}
public static class SqliteInMemoryDatabase
{
private static object key = new();
static SqliteInMemoryDatabase()
{
lock (key)
{
Connection = new SqliteConnection("Filename=:memory:");
// We want to keep the connection open
// and reuse it since our database is in-memory
// Warning: don't use this with multiple clients
// and closing the connection destroy the database
Connection.Open();
}
}
public static SqliteConnection Connection { get; }
}
This approach should only be used with a single client or within test scenarios. All DbContext instances will share the same connection. In the following example, we have two instances of Database with a Person entity. We are able to seed the in-memory database, dispose the first instance, then read the data from the second instance.
using (var loader = new Database())
{
var people = new Faker<Person>()
.Rules((f, p) => p.Name = f.Person.FullName)
.Generate(100);
await loader.Database.MigrateAsync();
await loader.People.AddRangeAsync(people);
await loader.SaveChangesAsync();
loader.ChangeTracker.Clear();
}
using (var database = new Database())
{
var results = await database.People.Take(10).ToListAsync();
foreach (var result in results)
{
Console.WriteLine(result.Name);
}
}
PostgreSQL Data Provider
PostgreSQL is the most versatile server-based database .NET developers can utilize when building applications. In addition to relational features, .NET developers have access to full-text search, JSON features, real-time notifications, and more.
To start using PostgreSQL with Entity Framework Core, we’ll need to install the Npgsql.EntityFrameworkCore.PostgreSQL nuget package.
PostgreSQL Standard Connection
A standard PostgreSQL connection string looks very similar to a SQL Server connection string.
public class Database: DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.UseNpgsql("Host=my_host;Database=my_db;Username=my_user;Password=my_pw");
}
PostgreSQL with Port Connection
Unlike SQL Server connection strings, ports are are a standalone flag passed into the connection string.
public class Database: DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.UseNpgsql("Host=my_host;Database=my_db;Port=5432;Username=my_user;Password=my_pw");
}
MySQL Data Provider
MySQL is popular amongst folks with a PHP background and is still the world’s most used database. MySQL and MariaDB share similar interfaces and syntax, and in most cases, MariaDB is a drop-in replacement for MySQL.
To use MySQL with EF Core, we’ll need to install the Pomelo.EntityFrameworkCore.MySQL provider. There are other MySQL database providers, but the one developed by Pomelo is recommended in the official Microsoft documentation.
MySQL Standard Connection
The Pomelo data provider supports both MySQL and MariaDB, and this means we need to tell it which database we are using through either MySqlServerVersion or MariaDbServerVersion.
public class Database: DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var serverVersion = new MySqlServerVersion(new Version(8, 0, 21));
optionsBuilder.UseMySql(
"Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;",
serverVersion
);
}
}
ASP.NET Core Setup For All Database Providers
We’ve seen how to overload the OnConfiguring method in DbContext, but this configuration option might not be flexible enough for our needs. So instead, when we use ASP.NET Core, we can take advantage of the built-in IConfiguration interface to retrieve connection string data from our configuration providers.
Below we’ll see an example of configuring the options for EF Core using the AddDbContext method inside of ConfigureServices. Note that if our DbContext class already implements OnConfiguring, ASP.NET Core will call both configuration methods, the one in our Startup class and the one in the DbContext implementation.
public IConfiguration Configuration { get; }
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext<Database>(options =>
{
var connectionString =
Configuration.GetConnectionString("sql");
// change your database provider here
options.UseSqlite(connectionString);
});
}
We will need to modify our DbContext class to have a constructor that accepts a DbContextOptions<T> where T is our implementation. The additional constructor allows ASP.NET Core to inject our configuration.
public class Database: DbContext
{
public Database(DbContextOptions<Database> options)
: base(options)
{
}
}
Conclusion
Entity Framework Core has come a long way from only supporting Microsoft SQL Server. More database providers are available, but this post tries to show you how to configure some of the more popular databases in the .NET ecosystem. If you need any more information on a database provider, please check the NuGet package sites for each package mentioned and go to the authors’ GitHub pages.
As always, thanks for reading.
