Let me start my answer with a Highlight.
Best technique is always context-driven. No technique will be best in all circumstances.
As @Micahel Durrant as pointed out, you may need to do pros, cons, time and cost analysis for all available options. IMHO I'm not a big fan of restoring the DB approach because of the following reasons and I may be wrong here.
- Having empty schema is not a great strategy for edit scenarios. Not sure your test cases have this constraint but let me elaborate with an example for you.
Suppose you created a new user account with code that was deployed yesterday and account creation and updation feature was working perfectly fine yesterday. Today, you have deployed new code, account creation is working fine, even updation is working fine for users created today but what if updation is not working anymore for the accounts that got created with old code? You won't be able to test this kind of scenarios if you are flushing the data from Database.
- Another shortcoming with CI/CD and data restoration approach is for every deploy before you trigger the scripts, you will have
pre-step to restore 'standard DB schema' on the database server and
post-step after the execution of scripts, you will delete the data again. What if some issue is found but the team is not able to see it in action or reproduce it as you already deleted the data. Of course, this can be handled if you delete and install new schema in pre-step but then you expect this issue reproduction or exploration by developer team should happen before you trigger next execution (or ideally by next deployment) which won't happen most of the time.
If you are executing the end-to-end checks, please think about if you can set up data in the script itself, using services or even UI. It may cost you some execution time but with parallel execution, it shouldn't be a problem. Also, you can create scripts in such way that data created in one script can be used in multiple scripts. Test Runner like TestNG support
dependsOnMethods annotations for such a need,