How would I run migrations from two directories? Or should I not be doing that...

[tahoeschrader]

What do you do if you have migrations you want to run for production... but also want a different set of migrations to add "dummy" data simply for the sake of testing the frontend of my app? Am I having a misconception about what migrations/SQLX should be used for?

Are migrations not supposed to be used for dummy data at all? And is it best to just add some .SQL script to the entrypoint that runs in the test environment to populate the "migrated" database with dummy data.

What i was imagining was running:

sqlx migrate run
sqlx migrate run -source tests/dummy_data

Or something like that...

[nk9]

Migrations are used for the DB schema, which should be the same on your local and production databases. Of course, the local version will be changed from time to time as you develop the site, but as soon as a new version is ready, you should push it so the production schema and the local schema are the same again.

Migrations are not a place to populate dummy data. A migration is a series of SQL files describing the changes needed to get to the next schema version. If you inserted dummy data using a migration file, then your next schema change would assume that the dummy data was there. You'd have to manually remove all the SQL files inserting the data, or remember to delete the dummy sections before pushing it to production… It would be a recipe for disaster. Once you generate a new migration file, you should assume it will never change because each file relies on all of the previous ones, similar to how git or a blockchain works.

The good new is, inserting dummy data can be done in lots of ways, including from CSV files, via pg_dump or by simply running an .sql file. If you find yourself needing to do it a lot, you could write some SQLx to add the data for you.