MOLT Schema Conversion Tool

The Migrations page on the CockroachDB Cloud Console features the MOLT Schema Conversion Tool. This tool helps you:

• Convert a schema from a PostgreSQL, MySQL, Oracle, or Microsoft SQL Server database for use with CockroachDB.
• Migrate directly to a CockroachDB Cloud database that uses the converted schema. You specify the target database and database owner when migrating the schema.

• Export the converted schema. To migrate to a CockroachDB Self-Hosted database, you can execute the statements in cockroach sql, or use a third-party schema migration tool such as Alembic, Flyway, or Liquibase.

  Note:

  The Migrations page is used to convert a schema for use with CockroachDB and to create a new database that uses the schema. It does not include moving data to the new database. For details on all steps required to complete a database migration, see the Migration Overview.

To view this page, select a cluster from the Clusters page, and click Migration in the Data section of the left side navigation.

Required privileges

To access the Migrations page, as user must have either of the following roles:

• Cluster Administrator
• Cluster Operator

Convert a schema

The steps to convert your schema depend on your source dialect.

Note:

This feature is in preview. This feature is subject to change. To share feedback and/or issues, contact Support.

1. With the Schemas tab open, click the Add Schema button. This opens the Add SQL Schema dialog.
2. In step 1 of the Add SQL Schema dialog, select the appropriate Dialect from the pulldown menu.
3. Configure the following defaults for schema conversion:
   INT type conversion: On CockroachDB, INT is an alias for INT8, which creates 64-bit signed integers. On PostgreSQL, INT defaults to INT4. For details, see Schema design best practices.
   Casing of Identifiers: Select Keep case sensitivity to enclose identifiers in double quotes, and Make case insensitive to convert identifiers to lowercase. For details on how CockroachDB handles identifiers, see Identifiers.
   AUTO_INCREMENT Conversion Option: We do not recommend using a sequence to define a primary key column. For details, see Schema design best practices. To understand the differences between the UUID and unique_rowid() options, see the SQL FAQs.
   Enum Preferences: On CockroachDB, ENUMS are a standalone type. On MySQL, they are part of column definitions. You can select to either deduplicate the ENUM definitions or create a separate type for each column.
   GENERATED AS IDENTITY Conversion Option: We do not recommend using a sequence to define a primary key column. For details, see Schema design best practices. To understand the differences between the UUID and unique_rowid() options, see the SQL FAQs.
   IDENTITY Conversion Option: We do not recommend using a sequence to define a primary key column. For details, see Schema design best practices. To understand the differences between the UUID and unique_rowid() options, see the SQL FAQs.
4. Click Next.

Use either the Upload File or Use Credentials option to add your schema.

Upload File

The Schema Conversion Tool expects to analyze a SQL dump file containing only data definition statements.

To generate an appropriate PostgreSQL schema file, run the pg_dump utility and specify the -s or --schema-only options to extract only the schema of a PostgreSQL database to a .sql file.

To generate an appropriate MySQL schema file, run the mysqldump utility and specify the -d or --no-data options to extract only the schema of the MySQL database to a .sql file.

To generate an appropriate Oracle schema file, run the expdp utility to extract only the schema of the Oracle database to a .sql file.

To generate an appropriate Microsoft SQL Server schema file, use either SQL Server Management Studio or the equivalent mssql-scripter utility to extract only the schema of the SQL Server database to a .sql file.

The dump file must be smaller than 4 MB. INSERT and COPY statements will be ignored in schema conversion. To add a schema file:

1. In step 2 of the Add SQL Schema dialog, click Upload File. Click the upload box and select a .sql file, or drop a .sql file directly into the box.
2. Click Convert and wait for the schema to be analyzed. A loading screen is displayed. Depending on the size and complexity of the SQL dump, analyzing the schema can require up to several minutes.
3. When analysis is complete, review the Summary Report and edit, add, or remove SQL statements in the Statements list.

Use Credentials

The Schema Conversion Tool can connect directly to a PostgreSQL or MySQL database to obtain the schema. To add a schema using credentials:

1. In step 2 of the Add SQL Schema dialog, click Use Credential. Select the credentials to use. If the list is empty, this is because no credentials have been created for the selected database type. You can add credentials directly from the pulldown menu.

2. Click Convert and wait for the schema to be analyzed. In the background, the Schema Conversion Tool runs the pg_dump or mysqldump utility to obtain the schema.

   A loading screen is displayed. Depending on the size and complexity of the SQL dump, analyzing the schema can require up to several minutes.

3. When analysis is complete, review errors and suggestions in the Summary Report. Apply bulk actions in the Summary Report and edit, add, or remove SQL statements in the Statements list.

Add database credentials

Note:

Credentials can be added for PostgreSQL and MySQL databases.

1. With the Credentials tab open, click the Add Credential button.
2. Provide the following information:
   • A Credential Name to associate with the credentials.
   • The Dialect of the database you are connecting to. Currently, PostgreSQL and MySQL are supported.
   • The Host (i.e., hostname or IP address) for accessing the database, excluding the protocol (e.g., tcp://). For example, migrations.cockroachlabs.com. The following are not allowed:
     • Local hosts such as localhost and 127.0.0.1.
     • Private IPs, including 127.0.0.0/8, 10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16, 169.254.0.0/16, ::1/128, fe80::/10, and fc00::/7.
   • The Port for accessing the database.
   • A valid Username and Password for accessing the database.
   • The Database Name to access. The Schema Conversion Tool will obtain the schema for this database.
   • The SSL Mode for accessing the database:
     • None: Do not force a secure connection.
     • Verify CA: Force a secure connection and verify that the server certificate is signed by a known CA.
     • Verify Full: Force a secure connection, verify that the server certificate is signed by a known CA, and verify that the server address matches that specified in the certificate.

If the credentials are valid, they will be added to the Credentials table with a VERIFIED badge.

Review the schema

Use the Summary Report and Statements list to update the schema and finalize it for migration.

The banner at the top of the page displays:

• The number of Statements Total in the uploaded .sql file that were analyzed.
• The number of Errors in SQL statements that are blocking schema migration. Errors are further categorized and counted in the Summary Report.
• The number of Incidental Errors in SQL statements that are caused by errors in other SQL statements.

The number of Incompatible Statements that could not be converted because they have no equivalent syntax on CockroachDB.

The number of Compatibility Notes on differences in SQL syntax. Although these statements do not block schema migration, you should update them before migrating the schema.
• The number of Suggestions regarding CockroachDB best practices.

Summary Report

The Summary Report displays the results of the schema analysis and provides bulk actions you can apply to update the schema.

To apply bulk actions to statements, refer to the tables in the Summary Report:

• Required Fixes
• Compatibility Notes
• Suggestions

To edit, add, or delete individual statements, click the Statements tab to open the Statements list. Errors and suggestions are displayed for each statement.

After updating the schema, click Retry Migration. If the schema has zero errors, click Migrate Schema to migrate the schema to a new CockroachDB Cloud database.

Required Fixes

Required Fixes indicate errors that must be resolved before you can migrate the schema.

Column	Description
Description	A summary of the error type.
Category	The category of error: Unimplemented Feature: A statement that uses an unimplemented feature. Uncreated User: A statement that references a nonexistent user. Incompatibility: (Non-PostgreSQL schemas) A statement that could not be converted because it has no equivalent syntax on CockroachDB. Uncategorized: A statement that the tool did not or could not execute. Incidental: A statement that failed because another SQL statement encountered one of the preceding error types.
Complexity	The estimated difficulty of addressing the error.
Remaining Instances	The number of times the error still occurs on the provided schema. This number will change as you update the schema to fix errors. Click the + icon on the row to view up to 20 individual statements where this occurs.
Actions	The option to Add User to add a missing SQL user, or Delete all statements that contain the error type. This cannot be undone after you retry the migration.

Compatibility Notes

Compatibility Notes indicate compatibility issues that do not block schema migration. These are only displayed for non-PostgreSQL schemas.

Column	Description
Description	A summary of the SQL compatibility issue.
Complexity	The estimated difficulty of addressing the suggestion.
Instances	The number of times the compatibility note occurs on the provided schema. Click the + icon on the row to view up to 20 individual statements where this occurs.
Actions	The option to Acknowledge all instances of the compatibility note. This is not required for schema migration.

Suggestions

Suggestions relate to schema design best practices. They do not block schema migration.

Column	Description
Description	One of the following suggestion types: Sequences: A statement that uses a sequence to define a primary key column. Using a sequence for a primary key column is not recommended. Missing Primary Key: A statement that does not define an explicit primary key for a table. Defining an explicit primary key on every table is recommended. Index Set On Timestamp Related Column: A statement that creates an index on a TIMESTAMP/TIMESTAMPTZ column. Indexing on sequential keys can negatively affect performance.
Complexity	The estimated difficulty of addressing the suggestion.
Instances	The number of times the suggestion occurs on the provided schema. Click the + icon on the row to view up to 20 individual statements where this occurs.
Actions	The option to Acknowledge all instances of the suggestion. This is not required for schema migration.

Statements list

The Statements list displays the result of analyzing each statement in the .sql file that you provided. The numbers from the Summary Report are displayed above the list of statements.

• To migrate the schema and create a new database for migration, click Migrate Schema. The schema must have zero errors.
• If the Migrate Schema button is disabled, use the Statements list to update the schema. Navigate the list by scrolling or by clicking the arrows and Scroll to Top button on the bottom-right.

Statements are displayed as follows:

• A statement that succeeded is displayed without further detail.
• A statement that failed is displayed with [error] and a message with error details. If the failure was due to an incidental error, the message also states: This error may automatically resolve once an earlier statement no longer errors.
A statement that failed due to incompatible syntax is displayed with [incompat], a message with syntax details, and an Acknowledge checkbox.

A statement that has a SQL compatibility issue is displayed with [compat note], a message with syntax details, and an Acknowledge checkbox.

Note:

Some statements with compatibility issues are automatically removed during conversion. If a statement was removed from the schema, this is stated in the statement's [incompat] or [compat note] message.

• A statement that has a suggestion is displayed with [suggestion], a message with suggestion details, and an Acknowledge checkbox.

To edit a statement, click the Edit button or the statement itself and enter your changes. Your changes are saved when you click outside the statement, or when you click the Save button. Click Cancel to discard your changes.

To edit multiple statements, click Find & Replace above the list.

• Enter a search term. Click Prev and Next to display each matching result.

• Click > to expand the menu. Enter a replace term and click Replace, Replace & Find Next, or Replace All to replace the matching results.

  This will update the corresponding statements. You cannot undo the replace operation.

  Note:

  Replace operations are not currently supported for regex matches.

To remove or add a statement, click the ellipsis above the statement and then click Delete statement, Add statement above, or Add statement below.

Filter the Statements list

To filter the statements that are displayed in the Statements list, click the dropdown menu above the list.

The available filters match the results displayed in the Summary Report:

• Required Fixes
• Suggestions
• Compatibility Notes (non-PostgreSQL schemas)

Click Apply to filter the Statements list using the selected filters.

To remove a filter, click the x next to the active filter. To remove all active filters, click Clear filters above the Statements list.

Export the schema

To export the current schema, click Download File at the top of the Statements list.

Update the schema

To update the schema:

• Apply bulk actions in the Summary Report.
• Edit, add, or remove statements in the Statements list.

Category	Solution	Bulk Actions	Required for schema migration?
Unimplemented feature	The feature does not yet exist on CockroachDB. Implement a workaround by editing the statement and adding statements. Otherwise, remove the statement from the schema. If a link to a tracking issue is included, click the link for further context. For more information about unimplemented features, see the Migration Overview.	Delete	Yes
Uncreated user	Click the Add User button next to the error message. You must be a member of the admin role. This adds the missing user to the cluster.	Add User, Delete	Yes
Incidental	Resolve the error in the earlier failed statement that caused the incidental error.	Delete	Yes
Incompatibility (non-PostgreSQL schemas)	There is no equivalent syntax on CockroachDB. Implement a workaround by replacing the statement. Otherwise, remove the statement from the schema. Then check Acknowledge.	Delete	Yes
Uncategorized	Edit the statement to fix the error. Otherwise, remove the statement from the schema.	Delete	Yes
Compatibility note (non-PostgreSQL schemas)	Edit the statement to match the CockroachDB syntax. Then optionally check Acknowledge.	Acknowledge	No
Suggestion	Review and take any relevant actions indicated by the message. Then optionally check Acknowledge.	Acknowledge	No

After updating the schema, click Retry Migration. If the schema has zero errors, click Migrate Schema to migrate the schema to a new CockroachDB Cloud database.

Retry the migration

To analyze a schema that you have updated, click Retry Migration at the top of the Summary Report or Statements list.

This will verify that the schema has zero errors and can be migrated.

Migrate the schema

You can migrate the schema directly to a new CockroachDB Cloud database when the number of errors is zero. This value is displayed on the Schemas table, Summary Report, and Statements list.

To migrate the schema, click Migrate Schema when viewing the Summary Report or Statements list. A modal will open:

1. Name the new database and select a SQL user to own the database.
2. Click Migrate.

After migrating the schema and creating the new database, you can load some test data and validate your queries.

Schemas table

If you have added a schema to convert, the following details are displayed when the Schemas tab is open:

Column	Description
Schema Name	The filename of the .sql file that you added.
Status	The status of the schema migration: READY FOR REVIEW, READY TO MIGRATE, or MIGRATED. You can migrate schemas with READY TO MIGRATE status.
Date Imported	The timestamp when the SQL dump was uploaded.
Last Updated	The timestamp when the SQL statements were updated.
Errors	The number of SQL errors preventing a schema from attaining READY TO MIGRATE status.

To view the Summary Report or Statements list for a migration, click the migration name.

Credentials table

If you have added any external database credentials (PostgreSQL or MySQL only), the following details are displayed when the Credentials tab is open:

Column	Description
Credential Name	The name associated with the access credentials. A VERIFIED badge will display if the credentials are verified.
Dialect	The type of database being accessed.
Host / Port	The host and port used to access the database.
Database Name	The name of the database being accessed.
Created At	The timestamp when the credentials were successfully created.

To delete or verify a set of credentials, select the appropriate option in the Actions column.

See also

• Migration Overview