As an organisation we are (finally) moving to a good CI/CD setup. We use Github for development and now have a nice CI setup where we auto deploy specific tags to different environments.
Now I want to setup a system where we have a live environment for every pull request untill it it either merged or closed. But I'm not entirely sure how I would get to creating a database per environment with good data. I've tought of multiple solutions myself, but maybe people here have different insights.
Things I've thought of so far.
Just connect to the staging database. Pros: probably easiest to setup. Cons: Very hard to manage with database migrations.
Create a copy of the staging database, and delete it when done with the branch. Pros: probably the best database state management. Cons: I'm not sure how to automate creation of access credentials to the new database.
Have excelent seed data and create a new database with that. Pros: Probably also very easy to setup. Cons: Developers need to make sure the seed data is up to date.
To give a bit more context, we are a project based organisation so we have a lot of different projects for different clients going on.
I was wondering how other companies manage this.