能否借助Git等VCS工具追踪数据库记录变更？含测试与业务场景需求

Great question! Since your database is small (only 6 tables with hundreds of thousands of records total), you've got some lightweight, effective options to track data changes and visualize them like you would with Git. Let's break down the best approaches tailored to your use case (especially verifying app updates in testing):

This is the simplest approach if you don't want to add new tools to your workflow. It leverages Git's built-in diff capabilities to compare full database snapshots.

• Take snapshots at key points: Before and after running your app's update logic, export your database to a SQL file. For MySQL:

  mysqldump -u your_username -p your_db_name --compact > db_snapshot_pre_update.sql
  

  For PostgreSQL:

  pg_dump -U your_username your_db_name --format=plain --no-owner --no-acl > db_snapshot_pre_update.sql
  

  The --compact (MySQL) or --no-owner/--no-acl (PostgreSQL) flags strip out redundant metadata, making diffs cleaner.

• Commit snapshots to Git: Add the SQL file to your Git repo and commit with a clear message:

  git add db_snapshot_pre_update.sql
  git commit -m "Snapshot before running app v1.2.0 update test"
  

• Visualize changes: After running your app's update, take another snapshot and commit it. Then use Git's diff command to see changes:

  git diff db_snapshot_pre_update.sql db_snapshot_post_update.sql
  

  For a more user-friendly view, use Git GUI tools like GitKraken or SourceTree—they'll highlight added rows, deleted rows, and modified field values in a readable format.

Dolt is a MySQL-compatible database with built-in Git-like version control. It lets you commit database changes directly, without exporting files, and provides granular diffs at the row and field level.

• Set up Dolt: Import your existing data into Dolt (you can use a SQL dump just like with MySQL). Once imported, you can interact with it using standard SQL commands.

• Track changes: Before running your app's update, commit the current state:

  CALL DOLT_COMMIT('-m', 'Before app update test');
  

  Or use the CLI:

  dolt commit -m "Before app update test"
  

• View diffs: After the app update, run:

  dolt diff
  

  This will show you exactly which rows were added, deleted, or modified—including specific field value changes (e.g., status from pending to completed). Dolt also has a web UI (run dolt sql-server and visit http://localhost:3306 in your browser) for visualizing changes.

• Bonus: You can create branches for different test scenarios, roll back to previous states, and even collaborate on database changes just like with Git.

If you only care about specific tables (not the entire database), export those tables to CSV files and track them in Git. CSVs produce cleaner diffs than SQL for field-level changes.

• Write a simple export script: Use Python with pandas and sqlalchemy to pull data from your tables and save as CSVs:

  import pandas as pd
  from sqlalchemy import create_engine
  
  # Connect to your database (adjust the URL for your DB type)
  engine = create_engine('mysql+pymysql://username:password@localhost/your_db_name')
  
  # List of tables you want to track
  target_tables = ['users', 'orders', 'inventory']
  
  for table in target_tables:
      # Export table to CSV
      df = pd.read_sql_table(table, engine)
      df.to_csv(f'{table}_snapshot.csv', index=False)
  

• Commit CSVs to Git: Add the CSV files to your repo and commit before/after your app update.

• Compare changes: Use Git diff to see field-level updates in the CSV files—this is especially useful for verifying that specific fields (like order_total or user_role) changed as expected.

Here's a step-by-step workflow tailored to your test scenario:

1. Take a baseline snapshot: Before running your app's update logic, commit the current database state (via SQL dump, Dolt, or CSV export).
2. Run the app update: Execute the code or API calls that modify the database (e.g., a batch update, user action, or migration).
3. Take a post-update snapshot: Commit the new state with a clear message (e.g., "After running user profile update feature").
4. Review diffs: Use your chosen tool to check for expected changes:
   • Did rows get added/deleted as planned?
   • Did field values update to the correct values?
   • Are there any unexpected changes (e.g., accidental deletions or incorrect updates)?

This workflow ensures you can quickly verify that your app's database changes behave as intended, without manually checking each row.

内容的提问来源于stack exchange，提问作者M2014