Database migration testing in Testing Fundamentals - Full Explanation

Introduction

When a database changes its structure, like adding or removing tables or columns, it can cause problems for the applications that use it. Database migration testing helps catch these problems early by checking that the changes work correctly and do not break anything.

Explanation

Purpose of Migration Testing

Migration testing ensures that changes to the database structure do not cause errors or data loss. It verifies that the new database version works well with existing applications and that all data is correctly moved or transformed.

Migration testing protects data integrity and application functionality during database changes.

Types of Migration Testing

There are several types, including schema testing to check the structure, data testing to verify data correctness, and performance testing to ensure the database still runs efficiently after changes.

Different testing types cover structure, data, and performance aspects of migration.

Testing Process

The process usually starts with backing up data, applying the migration scripts, then running tests to check if the schema and data are correct. Finally, performance and application compatibility are tested to confirm smooth operation.

A careful step-by-step process ensures safe and effective database migration.

Common Tools Used

Tools like Flyway, Liquibase, or custom scripts help automate migration and testing. These tools track changes and make it easier to apply and verify migrations consistently.

Automation tools simplify and standardize migration testing.

Real World Analogy

Imagine moving all your furniture from an old house to a new one. You need to check that everything fits, nothing is broken, and the new house works well with your belongings. Migration testing is like inspecting the move to make sure nothing is lost or damaged.

Purpose of Migration Testing → Checking that all furniture arrives safely and fits in the new house.

Types of Migration Testing → Inspecting furniture condition, room sizes, and how easily you can move around.

Testing Process → Planning the move, packing, transporting, unpacking, and arranging furniture.

Common Tools Used → Using moving boxes, trucks, and checklists to organize the move.

Diagram

Diagram

┌─────────────────────────────┐
│      Database Migration      │
│          Testing             │
├─────────────┬───────────────┤
│ Schema Test │ Data Test     │
├─────────────┼───────────────┤
│ Performance │ Compatibility │
└─────────────┴───────────────┘

Diagram showing the main areas covered in database migration testing.

Key Facts

Database Migration → The process of changing a database's structure or moving data to a new system.

Schema Testing → Testing to verify the database structure matches the expected design.

Data Testing → Checking that data is correctly transferred and remains accurate after migration.

Performance Testing → Ensuring the database operates efficiently after changes.

Migration Tools → Software that helps automate and manage database changes and testing.

Code Example

Testing Fundamentals

import unittest
import sqlite3

class TestDatabaseMigration(unittest.TestCase):
    def setUp(self):
        self.conn = sqlite3.connect(':memory:')
        self.cur = self.conn.cursor()
        # Initial schema
        self.cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
        self.cur.execute("INSERT INTO users (name) VALUES ('Alice')")
        self.conn.commit()

    def test_migration_add_column(self):
        # Migration: add email column
        self.cur.execute('ALTER TABLE users ADD COLUMN email TEXT')
        # Check new column exists
        self.cur.execute("PRAGMA table_info(users)")
        columns = [col[1] for col in self.cur.fetchall()]
        self.assertIn('email', columns)

    def test_data_preserved(self):
        # Check existing data still present
        self.cur.execute('SELECT name FROM users WHERE id=1')
        user = self.cur.fetchone()
        self.assertEqual(user[0], 'Alice')

    def tearDown(self):
        self.conn.close()

if __name__ == '__main__':
    unittest.main()

Common Confusions

Migration testing only checks if the database structure changes.

Migration testing only checks if the database structure changes. Migration testing also verifies data accuracy, application compatibility, and performance, not just structure.

Once migration scripts run without errors, testing is not needed.

Once migration scripts run without errors, testing is not needed. Scripts may run without errors but still cause data loss or slow performance, so thorough testing is essential.

Summary

Database migration testing ensures that changes to the database do not break applications or lose data.

It covers checking the database structure, data correctness, and performance after changes.

Using tools and a careful testing process helps make migrations safe and reliable.

Practice

1. What is the main goal of database migration testing?

easy

A. To redesign the database schema

B. To check the speed of the new database server

C. To verify that data is correctly and completely moved from the old database to the new one

D. To backup the database before migration

Solution

1. Step 1: Understand the purpose of migration testing

   Migration testing focuses on ensuring data integrity and completeness after moving data.

2. Step 2: Identify the main goal

   The main goal is to verify data correctness and completeness in the new database.

3. Final Answer:

   To verify that data is correctly and completely moved from the old database to the new one -> Option C

4. Quick Check:

   Migration testing = Data correctness and completeness [OK]

Hint: Focus on data correctness and completeness in migration [OK]

Common Mistakes:

• Confusing migration testing with performance testing
• Thinking migration testing is about database design
• Assuming backup is the main goal

2. Which of the following is the correct SQL query to count rows in a table named users for migration testing?

easy

A. SELECT COUNT(*) FROM users;

B. SELECT COUNT FROM users;

C. COUNT FROM users;

D. SELECT * FROM users COUNT;

Solution

1. Step 1: Recall correct SQL syntax for counting rows

   The correct syntax uses SELECT COUNT(*) FROM table_name;

2. Step 2: Match options with correct syntax

   SELECT COUNT(*) FROM users; matches the correct syntax exactly.

3. Final Answer:

   SELECT COUNT(*) FROM users; -> Option A

4. Quick Check:

   Count rows = SELECT COUNT(*) FROM table [OK]

Hint: Use SELECT COUNT(*) FROM table_name; for row counts [OK]

Common Mistakes:

• Using COUNT without SELECT
• Placing COUNT after * incorrectly
• Omitting parentheses in COUNT

3. Given the following data in the old database table products:

id | name    | price
1  | Pen     | 1.20
2  | Notebook| 2.50
3  | Eraser  | 0.80

After migration, the products table in the new database has:

id | name    | price
1  | Pen     | 1.20
2  | Notebook| 2.50

What is the best conclusion?

medium

A. Migration is successful; all data is present

B. Migration failed; table structure is incorrect

C. Migration partially succeeded; price values changed

D. Migration failed; some data is missing

Solution

1. Step 1: Compare row counts and data between old and new tables

   The old table has 3 rows; the new table has only 2 rows.

2. Step 2: Identify missing data

   The row with id 3 (Eraser) is missing in the new table, indicating incomplete migration.

3. Final Answer:

   Migration failed; some data is missing -> Option D

4. Quick Check:

   Missing rows = Migration failure [OK]

Hint: Check row counts and sample data for missing records [OK]

Common Mistakes:

• Ignoring missing rows
• Assuming price changes mean failure
• Confusing structure issues with data issues

4. You wrote a script to compare row counts between old and new databases but it always shows zero for the new database. What is the most likely error?

medium

A. The script is connecting to the wrong new database instance

B. The old database is empty

C. The SQL query syntax is incorrect for the old database

D. The script is not counting rows in the old database

Solution

1. Step 1: Analyze why new database row count is always zero

   If the new database count is always zero, the script might be querying the wrong database or empty table.

2. Step 2: Identify the most common cause

   Connecting to the wrong new database instance is a frequent mistake causing zero counts.

3. Final Answer:

   The script is connecting to the wrong new database instance -> Option A

4. Quick Check:

   Zero count new DB = Wrong connection [OK]

Hint: Verify database connection details carefully [OK]

Common Mistakes:

• Assuming old database is empty without checking
• Blaming SQL syntax for new DB count
• Ignoring connection strings

5. During migration testing, you notice that some records with NULL values in the old database are missing in the new database after migration. What is the best approach to handle this?

hard

A. Ignore NULL records as they are not important

B. Modify the migration script to correctly handle NULL values and rerun migration tests

C. Delete NULL records from the old database before migration

D. Change NULL values to empty strings in the new database manually

Solution

1. Step 1: Identify the issue with NULL values in migration

   Missing records with NULL values indicate the migration script does not handle NULLs properly.

2. Step 2: Choose the best corrective action

   Modifying the migration script to handle NULLs ensures data completeness and correctness.

3. Final Answer:

   Modify the migration script to correctly handle NULL values and rerun migration tests -> Option B

4. Quick Check:

   Handle NULLs in script = Complete migration [OK]

Hint: Always handle NULLs explicitly in migration scripts [OK]

Common Mistakes:

• Ignoring NULL records thinking they are unimportant
• Manually changing data after migration
• Deleting data from source database