Database object and fully-qualified object names migration path

Database objects and fully-qualified names (FQN) are supported in SqlDBM, allowing users to maintain a complete Database > Schema > Object hierarchy. SqlDBM allows users to choose between two-level (Schema.Object) naming or three-level FQNs when working with project objects. Both approaches have their advantages, and projects are designed to work flexibly with both and allow users to transition between them. 

In either approach, Database > Schema > Object forms the basis of the object's unique name (i.e., customer != rep.customer != prod.rep.customer).

• One-level naming - unqualified object names (e.g., customer) allow for quick prototyping and review. 
• Two-level naming - allow users to specify the schema but maintain a single object version (e.g., rep.customer) that can be updated from any database (i.e., dev or prod) using the options described in this article.
• Three-level naming - if wishing to maintain multiple versions of the same object in a single project (prod.rep.customer and dev.rep.customer), three-level FQNs should be used.

Database Object

The Database object serves as the root of the project hierarchy when using three-level naming. While referred to as "Database" generally, the terminology adapts based on the specific project type:

• Database: Snowflake, Azure, AlloyDB, Redshift, Oracle, SQL Server, PostgreSQL, and Logical projects.

• Catalog: Databricks.

• Project: Google BigQuery.

• Instance: MySQL.

Note that Database objects are included in Logical projects to ensure compatibility during import and export. 

Managing Databases

• Create: Use the "Database Explorer" to create new databases via the context menu or the bottom bar.

• Properties: In the Database Properties Editor (PE), you can specify physical/logical names, descriptions, and quoting options (for Snowflake).

• Uniqueness: Database names must be unique within the project.

• Deletion: Deleting a database will remove that database from any schemas where it is assigned

Fully Qualified Names (FQN) in Projects

To support complex environments, SqlDBM uses FQNs to identify objects:

• Schema FQN: Represented as <database>.<schema>.

• Object FQN: Represented as <database>.<schema>.<object> for most DB types (e.g., Snowflake, BigQuery, SQL Server).

  • Exceptions: Oracle and MySQL use <schema>.<object> for FQNs.

Assigning a Database to Schema Objects

In SqlDBM, a database can be assigned to a schema, and a schema can be assigned to an object. This allows users to go from two-level to three-level naming just by modifying a single (schema) object. To avoid mass alters on database (un)assignment or rename, individual tables are not affected.

Once a database has been created, it can be selected and assigned to a schema through the schema properties. 

Visualization on Canvas

You can control how object names appear on the diagram through the Diagram Properties Editor:

• Object only: Displays only the table or view name.

• Object and Schema: Displays the schema name with the object.

• Object Fully-Qualified Name: Displays the full <db>.<schema>.<object> path.

Reverse Engineering

During Reverse Engineering, users can choose to import either two-level or three-level objects. This is especially relevant when using direct connect, where FQNs are always returned. 

In direct connect, FQNs are currently supported for Snowflake, Google BigQuery, and Databricks. But if you upload the script using FQNs manually, three-level objects are also supported for Azure Synapse Analytics, AlloyDB, Amazon Redshift, Microsoft SQL Server, and PostgreSQL.

Please use the "Database names" option to choose how the database will behave on import. 

Keeping in mind the object uniqueness described earlier in this article, use these options to indicate how the update should behave. 

• Discard names on upload - if the DDL uses FQN, the database name will be stripped from the final output and two-level names will be returned.
• Keep names on upload - keeps the FQN intact and uses three-level FQN. 

If you keep the database name, new databases will automatically be created in your project if they do not already exist (same behavior as schemas). 

Please note that CREATE DATABASE statements are not supported in Reverse Engineering. To use fully-qualified names (3-level naming), you must manually create the Database object in the Database Explorer and assign it to the relevant Schema before performing the Reverse Engineering.

Important for MySQL and Oracle Users: 

The Database/Instance object for MySQL and Oracle is supported only via Excel import. If you have assigned a Database to your schemas, please be aware that Reverse Engineering via SQL script will not update the objects associated with those schemas. For these project types, always verify if you need to use Database object.

Switching Between Two-Level and Three-Level FQN Naming

Projects can easily switch between two-level and three-level names (and back) by following these steps. 

Two-Level to FQN

1. Create a database object (e.g., DEV)
2. Assign a database object to a schema (e.g., DEV.RAW)
3. Reverse Engineer (keep database object)
   • DEV.RAW.CUSTOMER would update an existing DEV.RAW.CUSTOMER object
   • PROD.RAW.CUSTOMER would create a new object if only DEV.RAW.CUSTOMER previously existed

Three-Level FQN to Two-Level

1. Unassign a database object from a schema (e.g., DEV.RAW to RAW)
2. Reverse Engineer (Discard database object)
   • DEV.RAW.CUSTOMER would update an existing RAW.CUSTOMER object

Update Two-Level Objects from Multiple Databases

1. Assume that RAW schema exists and contains RAW.CUSTOMER
2. Reverse Engineer (Discard database object)
   1. DEV.RAW.CUSTOMER would update an existing RAW.CUSTOMER object
   2. PROD.RAW.CUSTOMER would update an existing RAW.CUSTOMER object
      • Even if PROD.RAW.CUSTOMER (three-level) also exists in the project

Updating Three-Level FQN Objects from a Different Database

1. Assume that DEV.RAW.CUSTOMER exists but needs to be updated using a script that points to  PROD.RAW.CUSTOMER.
2. Temporarily remove the database from the DEV.RAW schema.
3. Follow the instructions described in the "Three-Level FQN to Two-Level" section.
4. Restore the (DEV) database to the (RAW) schema.
    

Database Object and FQN Impact on DDL and API

As outlined in this article, both two-level and three-level naming is supported in SqlDBM. When using two-level naming, there is no impact on any existing formatting or functionality regarding DDL, compare, or API.

When using FQN three-level naming, the database will appear as specified in DDL and structure. This includes:

• Forward Engineering
• Compare Revisions
• Merge in Concurrent Working
• Excel export
• API

See also

• Reverse Engineering
• Object Naming