My experience of testing a fundamental DB change in the presence of cloud product

How do you handle a situation of changing the basement of your 30-story apartment which is constructed many years back? 

Making changes to the DB of a complex Cloud platform or an existing cloud application is similar to the above situation. Isn't it? 

Let's assume we are working on a cloud platform, which provides many features to develop, deploy & monitor applications. We also keep on adding new features to the platform to maintain & increase the market share. When we introduce a new feature, we generally write a spec for it. We also write a test plan/test strategy for it. We do manual testing, automation, performance testing... etc. Finally, we certify and release it. Assume our cloud platform running on a java platform. Generally, a java based web application requires a database and a Web server. Whenever we introduce a new feature it may introduce new columns to the existing tables or adds a new table and also an extra piece of code for new functionality. To test these kind of features we generally certify SQL Scripts, new functionality and we test backward compatibility.

After a few years, the platform may require some database level change. If we come to this kind of situation, we should carefully test the entire platform. In this case, QA doesn't get any functional spec because it is not a new feature.

E.g. Database level change is modifying a column type from BIGINT to STRING.

This is a small change but it affects the whole platform. Generally, the platform provides features like UI, API & business logic. This small change affects all areas of the product like UI, API & Existing business logic. We recently tested this kind of change. We followed the below approach.

Certifying SQL Scripts

Migration case - Certifying update SQL Scripts.  We took the last released version of the product with some applications. Executed the SQL scripts. Observe that no errors displayed. We simulated the production environment and executed the SQL scripts. By doing this exercise, we can also calculate the production downtime. We also support multiple databases like MySQL, MSSQL Server, Oracle... etc. we have separate SQL Script files for each database. We certified them individually.

Normal case - Certifying create SQL Scripts. We took a fresh database and executed the SQL Scripts. We have executed SQL scripts on all supported databases.

In both cases, we defiantly get some defects like Syntax errors and some other errors because database uses different data types.

Verifying artifacts/business logic

This is the critical part. To test this, we should have an idea of the entire platform. We should be able to identify the areas/features going to be affected. In our case, to identify affected features we have gone through all check-ins made for this feature. We went through each file affected and noted down the affected features. Once we identified the features, we tested the functionality of each feature separately. Again here two cases we need to consider, existing artifacts and newly generated artifacts. As part of backward compatibility testing, existing business logic or artifacts should work fine after upgrade without any modifications. We can use all existing automation tests here. 

APIs Certification

As part of API certification, we identified the affected APIs. Most of the affected APIs will be APIs which accepts this affected column as argument and APIs which returns this column value in response. We executed all automation tests with existing configuration and with a new configuration.

Moving application/project between public and private cloud environments

This is one of the major use cases. Most of the cloud platforms are providing private cloud support along with public cloud to align with government rules. The private cloud also enables customers to customize the product (E.g. White labeling), so that they can increase their business. As we are supporting both public and private cloud environments, we can allow customers to move their applications between any environment. When we move applications between two different environments we definitely hit some issues.

This kind of database change looks like a simple change but validation is very hard.