| This can be a major pain for larger software projects. At work we have to contend with: * A database so large that even a minimally empty one cannot be created from scratch in less than 10-15 minutes. This creates problems for CI, integration testing, etc. * The developers spent a good chunk of the late 90s/2000s writing Oracle PL/SQL code; hundreds of packages and thousands of stored procedures with oodles of business logic. * We store reports, pdf attachments and other documents etc all in the DB as well. * Since we put so much stuff on the database, small problems and schema fudges tend to creep in over the years, which makes every customer database a little bit different. * Oracle licensing can be very unkind and the upper management mandate that we can't use the oracle XE version even in development/testing. We ended up using a combination of Flyway for schema changes, hand-rolled scripts to apply stored procedures and packages, and we had to roll a database provisioning pool as-a-service for developers, and it's still a massively janky and fragile setup. We really need better tooling for this. |
When we ran into this issue we would take period snapshots of the schema / data dump so that it could be recreated rapidly at a certain point. For example, we would create a DB creation and data insert script at version 2.0, then update scripts would be applied starting at 2.1, 2.2 etc.