I'm having quite a headache deploying and maintaining my three projects:
- Azure SQL Server/Database
- Azure App Service/Backend
- Azure Static Web App/Frontend
My codebase is pushed to GitHub, and via GitHub Actions, compiled and deployed to both my frontend and backend. This works well.
My backend retrieves the connection string for my database via an App Service environment variable. This also functions correctly.
My main issue lies in updating the database schema. Previously, I had the database connection string in my "appsettings.Production.Json," but I removed it due to security reasons. At that time, I would use ">dotnet ef database update" to resolve it.
From here, I have two options:
- Fetching the connection string from the Azure App Service in my build process. However, this approach was unsuccessful.
- Duplicating the connection string as a secret in GitHub Secrets. Although I was able to make this work, the Azure server responds with: "Database 'database' on server 'server' is not currently available." I assume this is because I did not create a firewall exception. Unfortunately, I have no clue what the IP ranges are for the runners performing the build process. I was not able to find them.
What is the most optimal and secure way of updating the database schema? Where should I store the connection string while still being able to easily migrate schema updates? Is there a preferred method for handling this?