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:
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
- 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
- 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)
- 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.