Skip to main content

Database version management

NineData Database Version Management tracks MySQL database structure versions without creating version tables in the target database. Use it to archive DDL snapshots, compare schema changes, generate forward change SQL, and prepare rollback scripts.

How it works​

NineData Database Version Management is a non-intrusive solution for managing database structure changes (DDL). NineData collects metadata, generates global version numbers, and records table structure snapshots for change history, difference comparison, and change or rollback SQL generation.

Key capabilities include:

  • Non-intrusive collection: No version tables are created in the target database.
  • Automated collection: Capture metadata through scheduled tasks or event triggers, such as after SQL task execution. NineData also captures full metadata once a week.
  • Multiple change sources: Track changes from SQL Console, SQL tasks, structure design and publishing, and other sources. For changes made outside NineData, generate version information through scheduled or manual metadata collection.
  • Visual comparison: Compare version differences and generate SQL.

Collection sources​

NineData collects DDL changes through these methods.

Collection Source
Description
Metadata CollectionCollects metadata from MySQL information_schema, which is suitable for all change types. A weekly scheduled task or a user can trigger collection. System collection captures the full structure of all databases and tables in the current database instance. Manual collection captures incremental structure information for table objects that have changed since the last version in the user-specified database.
SQL ConsoleAfter users execute DDL statements, such as creating a table, adding a column, or modifying an index, in NineData SQL Console, NineData detects the change and triggers collection.
SQL TaskFor SQL tasks submitted in NineData, if the task contains DDL statements, NineData collects the changed structure after the task finishes.
Structure Design and PublishingAfter a structure change release process is submitted through the structure design and publishing module, NineData triggers collection when the task finishes.

Before you begin​

  • You have created or joined an organization, and this organization has subscribed to either DevOps Pro or DevOps Enterprise. Please ensure that your annual or monthly subscription is still active. For more information, please refer to Manage Organizations.
  • Your current account has switched to the target organization. For more information, please refer to Switching to an Organization.
  • Database type is MySQL.
  • The number of databases is less than or equal to 10, and the total number of tables is less than or equal to 2000.

Usage limits​

  • Database DevOps Professional Edition users can collect up to 20 database versions.
  • Database DevOps Enterprise Edition users can collect up to 200 database versions.

Procedure​

Configure database version management​

NineData only collects metadata and version records for data sources that have enabled version management.

  1. Sign in to the NineData Console.

  2. Go to DevOps > Database Version.
  3. Under the Database Version tab, turn on the switch under the Database Version column for the target data source.

View collected DDL changes​

For DDL changes that NineData has collected, view SQL details at any time. If multiple versions exist, compare SQL differences between two versions.

  1. Sign in to the NineData Console.

  2. Go to DevOps > Database Version.
  3. Under the Database Version tab, click Details under the Actions column for the target data source.
  4. On the Datasource Details page, click Details under the Actions column for the target database.
  5. Click Details under the Actions column for the target version number to view the specific SQL statements. These actions are also available:
    • Select Version comparison to compare the SQL statements with the previous version (if any).
    • Select List updated tables to view the DDL changes involved in the current version, if any. By default, the page opens a full mirror snapshot of the current database.
    • Click copy to copy the SQL statement to the clipboard and execute it elsewhere.
    • Click download to generate a download task and download it from Downloads. To download SQL files, see Download SQL files.

Generate change SQL​

Generate Change SQL compares two database structure versions and generates the DDL statements required to migrate from the old version to the new version. It compares table structures and outputs executable change scripts, reducing manual work and omissions during structure upgrades.

  1. Sign in to the NineData Console.

  2. Go to DevOps > Database Version.
  3. Under the Database Version tab, click Details under the Actions column for the target data source.
  4. On the Datasource Details page, click Details under the Actions column for the target database.
  5. Select the checkboxes on the left side of the two target versions and click Version Comparison in the toolbar. To find database versions faster, filter by Version Generation Time, Collection Source, Table Name, Version Number, Tag, and other fields.
  6. On the Details page, click Generate Modify SQL at the top of the page. If there are differences between the two versions, NineData generates SQL statements. Use these actions, or click download to generate a download task and download it from Downloads. To download SQL files, see Download SQL files.
    • Click Submit SQL Task under the Generate Modify SQL window to initiate a change for the target data source.
    • Click copy to copy the SQL statement to the clipboard and execute it elsewhere.
    • Click download to generate a download task and download it from Downloads. To download SQL files, see Download SQL files.

Generate rollback SQL​

Generate Rollback SQL restores the database structure from the current version to a specified historical version. NineData compares the differences between the two versions and generates reverse DDL scripts, helping teams recover the structure after a failed release or other issue.

  1. Sign in to the NineData Console.

  2. Go to DevOps > Database Version.
  3. Under the Database Version tab, click Details under the Actions column for the target data source.
  4. On the Datasource Details page, click Details under the Actions column for the target database.
  5. Select the checkboxes on the left side of the two target versions and click Version Comparison in the toolbar. To find database versions faster, filter by Version Generation Time, Collection Source, Table Name, Version Number, Tag, and other fields.
  6. On the Details page, click Download Rollback SQL at the top of the page. If there are differences between the two versions, NineData generates rollback SQL statements.
    • Click Generate Modify SQL under the Submit SQL Task window to initiate a rollback for the target data source.
    • Click copy to copy the SQL statement to the clipboard and execute it elsewhere.
    • Click download to generate a download task and download it from Downloads. To download SQL files, see Download SQL files.

Add tags to database versions​

Database version numbers are generated from timestamps or unique identifiers, and are often hard to recognize. Add tags to important versions so users can identify and manage them more easily.

  1. Sign in to the NineData Console.

  2. Go to DevOps > Database Version.
  3. Under the Database Version tab, click Details under the Actions column for the target data source.
  4. On the Datasource Details page, click Details under the Actions column for the target database.
  5. Click the edit icon under the Tag column for the target version, enter the tag name, and then click check.

Download SQL files​

Database Version Management provides a Download Center for SQL files related to version management, including change SQL, rollback SQL, and full table-creation scripts. Download the scripts for the required version for backup, audit, or offline execution.

Notes

  • After a download task is generated, it is retained for one month. After it expires, generate a new download task.
  • After the download count reaches 10, the link becomes invalid. To continue downloading, generate a new download task.

Procedure

  1. Sign in to the NineData Console.

  2. Go to DevOps > Database Version.
  3. Click the Downloads tab and click Download under the Actions column for the target download task.

Result​

Database version management records structure snapshots and related DDL changes for the enabled data source. Use the collected versions to compare changes, generate forward or rollback SQL, add semantic tags, and download scripts for review or offline execution.