Schema Design and Release

Use Schema Design and Release to move database changes through development, test, and production environments in a controlled workflow. NineData collects baseline changes, generates ordered SQL scripts, and helps teams validate and approve database changes before production release.

How it works

Enterprise database changes usually move through multiple environments before production. To protect production data, many teams require SQL scripts to run and pass validation in a lower environment before release. During development, the same change set may go through several rounds of edits. If those changes are not applied to production in the correct order, teams may miss changes or cause application release failures.

Schema Design and Release consolidates change tasks executed against the baseline data source in the development or test environment into one SQL script. NineData preserves the execution sequence and dependencies. When users submit changes to non-baseline data sources, administrators can require changes to be executed only from the generated SQL script, reducing errors caused by missed or out-of-order changes.

Project administrators can monitor workflow progress and execution status. Developers and collaborators can view task status, identify issues earlier, and keep the release process moving.

Before you begin

In the commercial versions (DevOps Pro, DevOps Enterprise), make sure your monthly or yearly subscription has not expired. An expired subscription may prevent you from using the Database DevOps service. You can check the remaining quota and expiration date in the NineData Console header.

Procedure

Follow these steps to configure a process template, create a Schema Design and Release workflow, submit change tasks, and move the workflow through its nodes.

Configure a Schema Design and Release process

NineData includes a default process configuration with two nodes: Publish to Dev and Publish to Prod. These nodes allow development team members to skip the current node, edit SQL executed in the previous node, and roll back to the previous node.

You can edit the default process or create a new process from it. This procedure uses a new Schema Design and Release process as the example.

1. Sign in to the NineData Console.

2. Go to DevOps > Policy & Process.

3. Click the Schema R&D Configuration tab, then click Create Process in the toolbar to open the Create Process window. Configure the process, then click OK.

   You can also click Duplicate in the Actions column of an existing process.

   Parameter	Description
   Duplicate Schema Design & Release Flow	Select an existing Schema Design and Release process to create a new one based on that process. If you use Duplicate, NineData fills this field for you.
   Name	Enter the name of the Schema Design and Release process, up to 32 characters.
   Description	Enter a description of this Schema Design and Release process.

4. Click the newly created Name, or click Details in the Actions column of the process to open the Schema R&D Details page.

5. Because the current process is copied from another process, the data source for the first node must be globally unique. Before editing other settings, modify the data source for the node whose No. is 1. Click Edit for this node, modify the Datasource parameter, and click OK.

6. Click Add Node in the toolbar to add nodes to the process. For example, add a Test Environment node between the default Publish to Dev and Publish to Prod. Configure the node in the table, then click OK.

   Parameter	Description
   Node Name	Enter the name of the node to be added. For example: Test Environment.
   Datasource	Select the data sources available for changes in this node. Add data sources by By Envir. or By Datasource. You cannot add the same data source or environment as the first node. By Envir.: All data sources in this environment are available for changes in this node. By Datasource: Select the target database or schema for the target data source to control which data sources are available for changes in this node. Click Add Datasource to add multiple data sources.
   Node Config	Allow Rollback: Whether to allow rollback from the current node to the previous node. Modify Alter SQL: Whether to allow users to edit SQL scripts when making changes to non-baseline databases in the standard process. Allow Skip: Whether to allow skipping the current node and directly advancing to the next node without executing any changes.

7. After creating the nodes, you can sort, edit, and delete nodes on the page.

   • Sort: Hover over the target node row, then click the icon next to No. to move the node up.
   • Edit: Click Edit in the Actions column of the target node to edit its details.
   • Delete: Click Delete in the Actions column of the target node to delete it.
   Shortcut switches

   On the Schema R&D Details page, use shortcut switches to edit node settings (Allow Rollback, Modify Alter SQL, and Allow Skip) without opening each node's edit page.

8. Return to the Schema R&D Configuration page, then turn on the switch in the Status column of the target process to activate it.

Create a Schema Design and Release workflow

1. Sign in to the NineData Console.

2. Go to DevOps > Schema Design & Release.

3. Click Create Schema Design & Release in the toolbar.

4. On the Create Schema Design & Release page, configure the workflow, then click Create Schema Design & Release.

   Parameter	Description
   Name	Enter a workflow name. Use a meaningful name so collaborators can identify the release. Up to 64 characters are supported.
   Process Template (Optional)	Select a process template. After you select Process Template, Base Datasource lists only the data sources bound to that template. If you leave this field blank and select Base Datasource first, NineData selects the process template bound to that data source.
   Base Datasource	Select the baseline data source for the Schema Design and Release workflow. Supports Datasource or Database Grouping. All changes are based on this data source, and NineData organizes all SQL executed in the baseline data source into SQL scripts in execution order. Based on the process configuration, administrators can require changes to non-baseline databases to use only this SQL script.
   Database	Select a database as the baseline database for the Schema Design and Release process.
   Executor	Select the user who executes SQL after the change is approved. Note: The options in the Executor list depend on whether the data source uses development specifications: If the current data source is configured with development specifications, the options are based on the SQL Task Executor Config rule in the current specification. This rule is located on the SQL Task & Console tab. For configuration details, see Edit Specifications. If the current data source is not configured with development specifications, the options are users who have the SQL Task - Exec permission for the current data source. For permission configuration, see Configure User Permissions.
   Collaborator (optional)	Select collaborators who can participate in the current Schema Design and Release workflow and help drive development work.
   Note (optional)	Add notes for unfinished items in the current Schema Design and Release workflow.
   Alter SQL	Enter the change SQL to execute, or upload a file that contains the change SQL. SQL Text: Enter the SQL statement directly in the text box. SQL File: Click Upload File, then select and upload the file that contains the SQL statements to execute. Note: After uploading, hover over the uploaded file name and select to preview the file or to delete it.

5. NineData opens the task details page for the current Schema Design and Release workflow and creates a SQL change task for the baseline database. In Task List, move this task from Submit Approval to Execute. After the task status changes to Success, continue to the next procedure.

Use the Schema Design and Release process

The Schema Design and Release process can have multiple nodes. Move through the nodes step by step based on the administrator's configuration. By default, the process contains Publish to Dev and Publish to Prod. Each node has a task list, and all Collaborator in the process can submit SQL tasks to make database changes.

Submit tasks

1. Sign in to the NineData Console.

2. Go to DevOps > Schema Design & Release, not Datasource > Policy & Process.

3. Click the target Pipeline ID, or click Details in the Actions column.

4. In Task List, click Submit. Configure the task in the dialog, then click OK.

   Parameter	Description
   Target	Select the data source for the change.
   Target DB	Select the database for the change.
   Executor	Select the user who executes the current task after approval. Note: The options in the Executor list depend on whether the data source uses development specifications: If the current data source is configured with development specifications, the options are based on the SQL Task Executor Config rule in the current specification. This rule is located on the SQL Task & Console tab. For configuration details, see Edit Specifications. If the current data source is not configured with development specifications, the options are users who have the SQL Task - Exec permission for the current data source. For permission configuration, see Configure User Permissions.
   Alter SQL Source	Optional when neither Target nor Target DB is the baseline data source. In this case, select the data source where the task has been executed. The executed SQL script appears in the code box below.
   Alter SQL	Optional when both Target and Target DB are the baseline data source. Enter the change SQL to execute, or upload a file that contains the change SQL. SQL Text: Enter the SQL statement directly in the text box. SQL File: Click Upload File, then select and upload the file that contains the SQL statements to execute. Note: After uploading, hover over the uploaded file name and select to preview the file or to delete it.

After you submit the task, its substeps, such as withdrawal, approval, execution, pause, and restart, follow the same process as a SQL task.

Advance the process

After all tasks in the current node reach the final state, Success, you can advance the development pipeline to the next node. If the administrator enabled Allow Skip in Configure a Schema Design and Release process, you can advance the process even when the current node has no tasks.

1. Sign in to the NineData Console.

2. Go to DevOps > Schema Design & Release.

   tip

   If you can't find Schema Design & Release, make sure your console is in organization mode. To switch from personal mode to organization mode, see Switch to an organization.

3. Click the target Pipeline ID, or click Details in the Actions column.

4. In Task List, click Push to Next. In the confirmation window, click Push to Next again to advance the process to the next node. If there are no more nodes ahead, the development pipeline ends.

Roll back the process

If the administrator enabled Allow Rollback in Configure a Schema Design and Release process, and the current non-initial node has no tasks or all tasks have reached a final state, you can roll back the process to the previous node.

1. Sign in to the NineData Console.

2. Go to DevOps > Schema Design & Release.
3. Click the target Pipeline ID, or click Details in the Actions column.
4. In Task List, click next to Push to Next > Rollback to Prev., then click Rollback to Prev. again in the confirmation window to roll back the process to the previous node.

Terminate a Schema Design and Release process

While a Schema Design and Release process is running, administrators cannot adjust node configuration, such as adding nodes, changing node order, renaming nodes, or deleting nodes. To change these settings, terminate the process first.

1. Sign in to the NineData Console.

2. Go to DevOps > Schema Design & Release.
3. Click the target Pipeline ID, or click Details in the Actions column.
4. In Task List, click Terminate in the toolbar, then click Terminate again in the confirmation window.

Result

The Schema Design and Release workflow moves database changes through the configured nodes. Administrators can monitor progress, and collaborators can submit, approve, execute, advance, roll back, or terminate tasks based on the process configuration.