Creating Migration Query in Postgres

During development of an application, its a good practice to have multiple database instances - one for production and one for development/testing. The development/testing database would be the one you will be using for testing.

But what would you do if there is a change required in the database schema itself - like adding a column or changing the data type of a column or deleting a column? Duplicating the changes in multiple instances of the database is error prone and not efficient usage of time. That's where migration query comes in!

Steps to Create Migration Query

1. Open pgAdmin and connect to your database.
2. Right click on the specific table you would like to change and click on Properties.

3. Make necessary changes to the table - Eg: Like adding a new column.

4. Go to SQL tab. This would have generated an equivalent query for the change(s) you have made. Copy the query paste in a Notepad.

5. Do step 4 for all the changes you would like to make in the database. For each change, append the query to the notepad.
6. Once all done, execute the queries in all the database instances. All done!

Know any easier way of creating a migration query in Postgres? Let us know in the comments.