Database migration testing in Testing Fundamentals - Test Execution Trace

Test Overview

This test verifies that data is correctly migrated from the old database schema to the new schema without loss or corruption. It checks that all expected tables and records exist after migration.

Test Code - unittest

Testing Fundamentals

import unittest
import sqlite3

class TestDatabaseMigration(unittest.TestCase):
    def setUp(self):
        # Connect to old and new databases
        self.old_db = sqlite3.connect(':memory:')
        self.new_db = sqlite3.connect(':memory:')
        self.create_old_schema()
        self.create_new_schema()
        self.insert_old_data()
        self.perform_migration()

    def create_old_schema(self):
        cursor = self.old_db.cursor()
        cursor.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
        self.old_db.commit()

    def create_new_schema(self):
        cursor = self.new_db.cursor()
        cursor.execute('CREATE TABLE users (user_id INTEGER PRIMARY KEY, full_name TEXT, age INTEGER)')
        self.new_db.commit()

    def insert_old_data(self):
        cursor = self.old_db.cursor()
        cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 30))
        cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Bob', 25))
        self.old_db.commit()

    def perform_migration(self):
        old_cursor = self.old_db.cursor()
        new_cursor = self.new_db.cursor()
        old_cursor.execute('SELECT id, name, age FROM users')
        rows = old_cursor.fetchall()
        for row in rows:
            new_cursor.execute('INSERT INTO users (user_id, full_name, age) VALUES (?, ?, ?)', row)
        self.new_db.commit()

    def test_data_migrated_correctly(self):
        new_cursor = self.new_db.cursor()
        new_cursor.execute('SELECT user_id, full_name, age FROM users ORDER BY user_id')
        migrated_rows = new_cursor.fetchall()
        expected = [(1, 'Alice', 30), (2, 'Bob', 25)]
        self.assertEqual(migrated_rows, expected)

    def tearDown(self):
        self.old_db.close()
        self.new_db.close()

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

Execution Trace - 5 Steps

Step	Action	System State	Assertion	Result
1	Test starts and sets up in-memory old and new databases	Two empty in-memory SQLite databases are created with old and new schemas	—	PASS
2	Insert sample data into old database's users table	Old database users table contains two records: Alice (30), Bob (25)	—	PASS
3	Perform migration by copying data from old to new database with new schema	New database users table populated with migrated records matching old data	—	PASS
4	Query new database users table and compare data to expected records	New database users table has records: (1, 'Alice', 30), (2, 'Bob', 25)	Assert that migrated data equals expected data [(1, 'Alice', 30), (2, 'Bob', 25)]	PASS
5	Close database connections and end test	Databases closed, test ends cleanly	—	PASS

Failure Scenario

Failing Condition: Data is missing or incorrectly migrated in the new database

Execution Trace Quiz - 3 Questions

What does the test verify after migration?

A That all user records are copied correctly to the new database

B That the old database schema is deleted

C That the new database is empty

D That the database connection is closed

Key Result

Always verify that data migrated to the new database matches exactly the expected records to catch any loss or corruption during migration.

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