SQLWays (Beta) Web: Data Type Mapping (Local & Global)

SQLWays Beta (web-based) supports flexible data type mapping, allowing column data types to be modified either globally across the entire database—including tables, variables, and parameters—or locally for selected tables only. To accommodate different migration strategies and levels of control, the tool provides two distinct data type mapping approaches. The following sections describe global data type mapping and local data type mapping, along with the scenarios in which each approach is most appropriate.

It is designed to enforce consistent data type transformations across the entire database. When enabled, a defined source data type is systematically replaced with the corresponding target data type in all applicable database objects, including tables, variables, parameters, and expressions. This approach is particularly useful when a data type change must be applied uniformly to avoid type inconsistencies and implicit conversions.

This approach is particularly effective when a data type change must be applied uniformly to eliminate type incompatibilities, avoid implicit conversions, and ensure predictable behavior across the migrated schema. Global mapping simplifies maintenance by allowing data type transformation rules to be defined once and applied consistently throughout the project.

For example, in PostgreSQL the smallint data type (a two-byte signed integer) often requires explicit casting when working with numeric literals or when assigning values from other integer types. This can introduce unnecessary complexity and reduce code readability. Replacing smallint columns with integer eliminates the need for such casts while preserving full type compatibility and avoiding functional side effects.

Another common use case involves increasing the capacity of counter or identifier columns. For instance, a column may initially be designed to store a 10-digit value, but over time it becomes necessary to extend this limit to 12 digits or more. Although this change can be implemented directly in the source database, it is often impractical in environments with large data volumes or systems that require continuous availability and cannot tolerate prolonged downtime. In such cases, migration provides an excellent opportunity to adjust data types in a controlled manner and align them with current and future requirements.

Additionally, data type harmonization can significantly improve query performance, especially in scenarios where tables are joined on columns with different, albeit compatible, data types.

Example:

 SELECT ... 
     FROM table1 t1 
 JOIN table2 t2 on t1.numeric_field = t2.bigint_field ...

The field can be used in various queries and compared with fields of other types. In this case, you need to choose a higher priority option for the column type. This change requires a thorough analysis of the entire code, but can be very useful for optimizing mission-critical queries.

As you can see SQLWays Beta (web-based) can be very useful for making such changes to the database.

It allows data type transformations to be applied selectively at the level of individual tables or columns. This approach provides fine-grained control over the migration process and is well-suited for scenarios where only specific database objects require adjustment.

Local mapping is typically used when global changes are unnecessary or undesirable, or when exceptions must be made for certain tables due to business rules, performance considerations, or backward compatibility requirements.

Once mapping is specified, it is saved in sqlways.xml project file, so the mapping can be further used in conversion using the batch mode.

Database Migration Guide

More Tips and Tricks

If you have any questions or face any difficulties, please contact our support team at support@ispirer.com