3 ms·

I don't understand why you shouldn't migrate non-production databases. I agree that there should not be an expectation that they are stable (although it can sti

I don't understand why you shouldn't migrate non-production databases. I agree that there should not be an expectation that they are stable (although it can still be useful to keep data around in test environments), but if you constantly apply migrations upon every deployment (as well as locally), you have a good test bed for the production migration. That gives you at least a base level of sanity checks that your migrations work. Of course, this is no guarantee that your production migration will also work - you may e.g. add a FK constraint but there are violating rows in prod -, but for that you can wrap your migration in a transaction. Upon failure, the migration is rolled back and, of course, you should then also rollback the deployment. Then you can start investigating why the migration went wrong. Also, creating dumps from prod and storing them locally is something I don't recommend (and it's also disallowed at the company I work for) unless you take care to purge/mask all sensitive data (all PII and everything that includes trade secrets of some sort) before it reaches your local machine.

twic 5y ago
I wasn't very clear in my statement about migrations, sorry. I'll put it another way: the only migrations you should run in dev are ones you intend to eventually run in production. The process of loading a base dump and then running SQL scripts is precisely to mimic what will happen when you release to production. As you say, this is a good test bed! What you shouldn't do is try to move between existing intermediate, non-production-like, states of the database by migrating. And really, what i object to are down migrations, because you won't ever use those in production. Migrating up is not so bad. But since running individual up migrations is something you will never do in production, why do it in dev? Indeed, always running your whole queue of up migrations on top of a fresh base dump is a way of catching the kind of problem you hit in production, where existing data violates a new constraint. The nuke-and-pave rebuild approach also avoids the problem of moving between branches and reverting pushed but unreleased schema changes. Those become trivial. As you say, and as i said in my initial comment, there will be data in production that you can't or don't want to use in dev for some reason. But that's just another development task - you need to take your base dumps in such a way that that data is not included. You can exclude certain tables. You can only dump certain rows, corresponding to some kind of testing user etc (one bank i worked with had a "shadow branch" defined in their production systems, a made-up branch with made-up customers, that they could use for final testing in the actual production environment). You can dump into an intermediate database in production, anonymise and delete any data you need to, and then take the base dump. I'm sure there are loads of ways to do this.
- Tainnor 5y ago
  Ah yes, I generally agree with what you said (although I think it's fine to occasionally do destructive and weird things to your local DB and roll them back if you're working iteratively; you can either recreate from scratch if you want to make sure you didn't break anything and otherwise you might still have some pre-production stage where you would notice it if the migrations didn't work).

twic 5y ago

I wasn't very clear in my statement about migrations, sorry. I'll put it another way: the only migrations you should run in dev are ones you intend to eventually run in production. The process of loading a base dump and then running SQL scripts is precisely to mimic what will happen when you release to production. As you say, this is a good test bed! What you shouldn't do is try to move between existing intermediate, non-production-like, states of the database by migrating. And really, what i object to are down migrations, because you won't ever use those in production. Migrating up is not so bad. But since running individual up migrations is something you will never do in production, why do it in dev? Indeed, always running your whole queue of up migrations on top of a fresh base dump is a way of catching the kind of problem you hit in production, where existing data violates a new constraint. The nuke-and-pave rebuild approach also avoids the problem of moving between branches and reverting pushed but unreleased schema changes. Those become trivial. As you say, and as i said in my initial comment, there will be data in production that you can't or don't want to use in dev for some reason. But that's just another development task - you need to take your base dumps in such a way that that data is not included. You can exclude certain tables. You can only dump certain rows, corresponding to some kind of testing user etc (one bank i worked with had a "shadow branch" defined in their production systems, a made-up branch with made-up customers, that they could use for final testing in the actual production environment). You can dump into an intermediate database in production, anonymise and delete any data you need to, and then take the base dump. I'm sure there are loads of ways to do this.

Tainnor 5y ago
Ah yes, I generally agree with what you said (although I think it's fine to occasionally do destructive and weird things to your local DB and roll them back if you're working iteratively; you can either recreate from scratch if you want to make sure you didn't break anything and otherwise you might still have some pre-production stage where you would notice it if the migrations didn't work).