Favorite Ways to Initialize K8 PersistentVolumes for Database creation for Entity Framework Core Code First?

Question

If I am hosting a ASP.NET Core Web App that is using Entity Framework Core (EF) inside a Kubernetes (K8) Replica set, what needs to be done to configure the persistent volume (claim) and the database?

For example: For EF to run it should have a database created and preferably a special web server account set up with a password. What are the favorite approaches to this given that deployment to kubernetes should be automated with scripts?

Can we make the script database neutral so it works with MySQL and PostgreSQL and MSSQL Server?

(1) We could create an init.sql to create the database and the account and the password and store this init.sql in the persistent volume and specify the init.sql in the docker compose file as described here: create-database-on-docker-compose-startup. This is messy because I believe I must create a custom docker image and I believe I cannot use this helm chart for mysql or [this helm chart for postgresql3. Is this true?

(1a) How do I copy this init.sql to a persistent volume?

(2) Can I write the C# code in the startup for the WebApp to create the database as exemplified here: creating-a-database-using-connector-net-programming? I think this is a .NET framework example... would it work in C# .NET Core? I guess this is similar code to create an account. This also looks very mysql specific! Any chance of having C# code that works with MySQL, PostgreSQL and MS SQL Svr? This might be OK for creating the database, but not creating the web server user. Hardcoding the webserver user account/password might defeat the merit of having a non-admin user account for the web server.

(3) I guess I could create a K8 job to execute the init.sql using the mysql client (or maybe some scripting language like perl or groovy) and then I could use a standard docker image via helm like the bitnami helm charts I mentioned above. I guess I would create a K8 job of type MySQLClient and use mysql.exe to execute the init.sql and launch this as part of my deployment script. Does anyone have an example I could follow?

Are there other options? I hope so...

None of these look very attractive to me... Seems to me this is a very common problem and there should be some easier approaches...

What is your favorite? Do you have any examples?

What else needs to be done for a code first EF web app to run besides creating a database and a database username/password for it to use? For development I can create seed data to test my code... Does anything else need to be done?

Oh, and another question: Are these bitnami helm charts for mysql and postgreSQL appropriate for singleton stateful replica sets for production? Do they create persistent volumes and claims? I was hoping I could look at the source code but I cannot find it. I'm worried they just use a database file specific to the pod they create and that the database contents would be lost if the pod died.

Thanks

Siegfried

Answer 1

Hi @Siegfried Heintze ,

What I was able to gather is that as long as all the features being used are cross-compatible then that part is mostly fine. If you are using EF Code First that should handle most of the cross-compatibility issues as the SQL code is all being generated by EF. I haven’t messed around with migrations with MySQL or Postgres, but it should work the same. You do need to tell which database type EF is connecting to during startup, but you could read that from an app setting at startup and use that to determine which provider to use.

Now since initial creation is different than migrations and the initial setup is a run-once thing, I would lump the db creation process in with that as opposed to trying to make it part of app deployment.

• If your setting up your cluster manually, then I would setup the DB/users manually as well. This would avoid a hard-coded password at that point.
• If your automating cluster setup, then it depends on how your automating it, but whatever installed the DB software should usually also be able to run the scripts to setup an app user.

If you want to set it up as a C# solution, then you should create a separate, small console app (again separating initial creation from deployment) to run as part of initial setup. Adding the password(s) as command line arguments avoids hard-coded DB passwords at this stage. The console app doesn't need to know anything about what the tables will look like so you don't necessarily need to do all the setup for EF inside the console app.

If your using migrations than all you need is an empty database, an admin user that can apply migrations, and an app user - this will need to be server-type specific as create user syntax is different in MySQL, Postgres and MSSQL. If you are are using the console app option, the type could be an argument or something. The first migration will create everything else it needs.

A peer of mine pointed to these 3rd party guides with k8 that look sound from the .NET side:

https://abelsquidhead.com/index.php/2017/07/31/deploying-dbs-in-your-cicd-pipeline-with-ef-core-code-first/

https://andrewlock.net/deploying-asp-net-core-applications-to-kubernetes-part-7-running-database-migrations/

---

Overall there are three options for automatic deployment of a database in .NET, all should work with a K8 Persistent Volume but the 2nd or 3rd would be the recommended options

1. Have the application handle it using Code First migrations. This is the simplest, but not often the best choice, especially for a complex scenario like K8. One of the guides goes over it, but essentially you can just setup the migration scripts inside the project and run db.Database.Migrate(); during startup. Using a build server like ADO or Octopus deploy. The specific implementation will be based on what build server is used. In this case, neither K8 or the app itself has to have admin privileges to the database- those get limited to the builder server. The .NET CLI can be used to automate the process of generating the db scripts from the project using dotnet ef migrations script. https://learn.microsoft.com/en-us/ef/core/miscellaneous/cli/dotnet
2. Setting up the application user during initial database creation from a build server is a bit trickier since EF doesn’t handle this if you need this automated as well. That part might have to involve a db-type specific script, but that’s pretty small when compared to scaffolding out the whole database. (ex. For MSSQL: https://stackoverflow.com/questions/34748589/how-to-create-a-database-user-in-entity-framework)
3. Using K8 init scripts. The second guide I linked at the beginning digs into this a bit. The idea would be pretty similar to the build server process. The main difference being your using k8 instead of an external entity. This would be how I would do it if I were you.

I know that's a lot of information, I did my best to answer your questions. In the future feel free to make multiple Q&A Questions as that would allow us to better digest and understand your question. Thanks for giving me time, I had to speak with fellow dev who works more with EF than I do.