Page 10 - MSDN Magazine, May 2019

Data Points JULIE LERMAN
EF Core in a Docker Containerized App, Part2
In last month’s column (msdn.com/magazine/mt833405), I created an ASP.NET Core API project to run inside a Docker container. The project used EF Core to persist data. In that column, I began by using SQL Server LocalDB, but when it came time to run the app inside the Docker container, I hit a snag because the current setup assumed the SQL Server LocalDB existed inside the container. As a quick solution, I switched to using SQLite, which would be installed when the new project got built within the container. With this approach, you could see the full solution succeeding during debug.
Switching to SQLite was simply a means for instant gratification. Let’s continue the journey by learning about proper production- worthy solutions for targeting SQL Server when publishing an API inside a Docker image. This column will focus on targeting a consis- tently available Azure SQL Database that can be used from anywhere.
Pointing the API to Azure SQL Database
My starting point is the solution as I left it at the end of my previous column. If you haven’t read that yet, you might want to start there for context. Throughout this article, I’ll evolve that solution bit by bit.
While EF Core can create a database for you on Azure SQL Data- base, the SQL Server must pre-exist. I already have a few SQL Servers set up on Azure, so I’ll use one of these for this column’s demos.
First, I’ll add a new connection string named MagsConnection- AzSql—pointing to that server—into appsettings.json, which already contains connection strings I used in Part 1. I’ve wrapped the long lines for readability although JSON doesn’t honor returns. I’m also including fake credentials:
"ConnectionStrings": { "MagsConnectionMssql":"Server=(localdb)\\mssqllocaldb;
Database=DP0419Mags;Trusted_Connection=True;",
"MagsConnectionSqlite": "Filename=DP0419Mags.db;",
"MagsConnectionAzSql": "Server=tcp:msdnmaglerman.database.windows.net,1433;
Initial Catalog=DP0419Mags;Persist Security Info=False; User ID=lerman;Password=eiluj; MultipleActiveResultSets=False;Encrypt=True; TrustServerCertificate=False;Connection Timeout=30;"
Next, I’ll change the DbContext configuration in the startup file’s ConfigureServices method to use the SQL Server provider and this connection string:
services.AddDbContext<MagContext>(options => options.UseSqlServer(
Configuration.GetConnectionString("MagsConnectionAzSql")));
This will allow EF Core migrations to find the connection string at design time so I can run any needed commands. At run time,
the app will be able to find the connection string, as well. In fact, because my last work was with SQLite, I need to reset the migra- tions, which in my demo means deleting the Migrations folder and running add-migration initSqlAz to get the correctly described migration for the database.
While EF Core can create a database for you on Azure SQL Database, the SQL Server must pre-exist.
Once the migration exists, I’ll run the app using the Docker pro- file. This will prove to me that the Migrate method in program.cs is able to create the database in the cloud and the controller is able to query it—all from within the Docker container—as expected. The first time the app runs Migrate and must create the data- base, expect a short delay. When done, not only does the browser relay the three magazines with which I seeded the database (in the previous column), as Figure 1 shows, but I can see the data- base listed in both the Azure Portal and in the Visual Studio SQL Server Object Explorer.
Note that the default configuration of the database created by EF Core was set up with the pricing tier: Standard S0: 10 DTUs. You can make changes to that in the Portal or when using Azure CLI for a pro- duction app. In fact, for production, you probably want to create the database explicitly in order to ensure its settings are aligned to your needs. Then you can use EF Core migrations to manage the schema.
Considerations for Handling Secrets
While this worked so nicely, it’s not yet production-ready. There are a few problems to consider.
The first is that the connection string and secrets are hardcoded into the appsettings.json file. It’s easy enough to modify that con- nection string in the JSON file without having to recompile the project, so “hardcoded” is a bit of an exaggeration. But it’s not dynamic as far as Docker is concerned because the appsettings file will be “baked in” to the Docker image. You’ll probably want to have more control over connection strings for development, stag- ing, testing and production. This isn’t a new problem and various
Code download available at msdn.com/magazine/0519magcode.
6 msdn magazine