Dev Talk: PostgreSQL and SQLServer

All Articles Culture Data Management Level 12 News Python Software Development Testing

Stabilizing PostgreSQL and SQLServer Data Migrations

At a very high level our clients need to have their PostgreSQL and SQLServer databases updated without losing data.

Let’s think through what thinks looks like with an example: our client wants to add a feature to track orders.

To track the orders, we need to add a new data table to take the order information in PostgreSQL or SQLServer and code using SQLAlchemy on the application side.

When we go to update your application on the server, we have to update the databases to make sure that the new table appears. This process is called a data migration.

Sometimes in these migrations updates get missed. In this example, perhaps it is a quantity field that got missed.

The quantity field exists in the code data model, so all of the tests pass. There is no error thrown here even though the database is lacking.

This is a big problem because, even though the code passed its test suite, there will be problems when the users start working with the product. They will not be able to enter in an order quantity in this scenario.

Monitoring migrations

One way of ensuring that all of your data is handled correctly is to manually test the program. A lot of software companies rely solely on this workflows, but it doesn’t scale well.

Some of our code bases have 200k + lines of code. Testing this volume of code manually would be difficult, error prone, and time consuming. The more complex the migration, you will have more data missed and more edge cases to account for.

To automatically test the migration, you must have the before state loaded in your test suite, then write and run specific tests that inspects the items being updated and makes sure edge cases are covered.

These new tests move with your migration and they will pass with the rest of the test suite. So when you run your code, you can see that your data migration was successful and you don’t have errors.

Automated tests = fewer fires

The end result of this is that the end users see a piece of software that runs without errors and little down time.

There are occasionally bugs, but with a robust test suite we are able to identify problems even before the client or user sees an error.

Most clients don’t know to ask for tests on their data migrations (or just about any other development work), but going the extra mile and pursuing Test Driven Development principles delivers high quality code that provides an easy user experience and is reliable for our clients.

There are fewer fires to put out with well tested software.


If you need assistance building a flexible app or database for your software project, please reach out, we would be happy to consult with you.

If you are thinking about building or buying software, take a look at our blog comparing the two options: Custom Software Development Versus Commercial Off The Shelf (COTS).


Originally published on 2020-08-07 by Royce Hall