We're multiple users working on multiple apps using one same database. The database schema is used in production, preproduction and development environments.Right now, we're keeping it updated using migrations across all environments, but this leaves us with hundreds of migration files and it's incredibly messy.Is there a way to keep a database schema synced across environments, without having the schema split up in dozens of files?
How would you keep databases synced and versioned across multiple environments?
database;versioning
If you want to minimize the amount of files you need to perform your database changes and prefer a single big file, you could use the following workflow:Write a 'baseline' file to create your database tables as they are in production Develop your application's next version, using one or more files for your changes to the database in that versionDeploy from scratch to development by running the baseline file, along with the change filesDeploy to preproduction by running the files containing the changes on the preproduction database, which is the same schema version as your baseline fileDeploy to production by running the files containing the changes, which is the same schema version as your baseline fileOnce the changes make it to production, create a new 'baseline' file by running the existing baseline, applying the changes and creating a new baseline from that database.Repeat from step 2This is not something I'd personally do. There is nothing inherently wrong with having a lot of small files for database changes because: Version control can easily tell you why a change occurred because you commit the change script, which can be very helpfulReading a baseline script for a big application is painful, a good database visualisation tool makes it a lot easierThe impact performance-wise in running the small script should be negligibleIf you ever have an old database version updating to the latest version is easy because you still have the individual change scriptsTooling exists to help you run those files and track which ones still need to run as part of the deployment, which helps if you bump an environment to a version that skips some versions inbetweenTo give you an idea, one of the applications I work on has more than 14.000 lines of SQL across 34 files to create a baseline database and uses more than 1.000 change scripts from that baseline. Good luck reading that baseline script.