I have added many new columns to a table in my database in my local environment with ADD COLUMN statements, over 30. My application is split into multiple environments (UAT, STAG, PROD) and we are NOT using a DB toolkit like RedShift or SQAlchemy or DjangoORM. I need to get those 30 columns (no data, just the blank column definitions) added to the same table in the databases on the UAT environment, the staging environment, and the production environment.

Is there an easy way to do this? I could of course write 30 ALTER TABLE xxx ADD COLUMN xxx statements and run those statements in each environment, but isn't there a less tedious way? Perhaps I can dump the table in my local environment as ADD COLUMN statements and then just copy and run the statements corresponding to the new columns I added in each of the environments?

  • You should really use a schema migration tool like Liquibase or Flyway for things like that. – a_horse_with_no_name Mar 24 '20 at 18:45
  • @a_horse_with_no_name so I take it the only way to do it without setting up a special tool like that is to manually write the ADD COLUMN statements? – Dominic Napoleon Mar 24 '20 at 18:56
  • Yes, pretty much. You could use something like RedGate's SQL Compare tool - I think it can compare two schemas and generate the SQL statements, and then run those manually, but unless this is the only time you think you will ever be changing the schema, it is probably better to start using a tool like Liquibase. See medium.com/@stevedonie/… – SteveDonie Mar 24 '20 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.