Sample Header Ad - 728x90

Adding data to a DB as migrations

2 votes
1 answer
1561 views
**Some Background** We currently have a *PostgreSQL* DB that is "read-only", i.e. no user is allowed to write to it and all insertions are done in a controlled manner. For a while, these were done by manually adding this data or creating a copy of the DB locally and using pgdump to put it into a production environment. There were obvious limitations with that and since there was the need for versioning the DB too (i.e. an addition to the contents or change of structure means we need to update the version number) we decided to use [flyway](https://flywaydb.org/) . This was good since we would get a versioning "for free" by using the migrations. We were not only using Flyway to modify the structure of tables and DB but we've been using it for inserting new data too. **The Problem** The migration files are getting quite big and if we need to apply the migrations or something similar it is becoming quite a time-intensive process, especially if we apply from scratch. Since DB management/engineering is not my forte I've decided to reach out: **The Question** Are there best practices or a way to better handle this situation? I.e. structural changes AS WELL as data addition. My best guess was to use the migrations files only for structural changes and have the data into files (CSV or other) that get added programmatically after the migrations run. But this is ok for the first time, after that the file would be obsolete and subsequent files would be used for new data that would fit possible structural changes...
Asked by fditz (121 rep)
Jan 13, 2022, 12:20 PM
Last activity: Feb 8, 2025, 05:05 PM