Differences viewer for database objects
In PhpStorm, you can compare database objects and the output of tables, views, and materialized views.
Compare database objects
The differences viewer shows you differences and similarities between two database objects: Origin and Target. The migration script is generated to make Target equal to Origin.
In PhpStorm, you can compare two database objects of the same type. For example, you can compare two schemas, two tables, or two routines. PhpStorm shows you the differences in the structures of these two objects.
![Compare table structures Compare table structures](https://resources.jetbrains.com/help/img/idea/2022.1/db_compareTableStructures.png)
For changes, PhpStorm uses the following color coding.
Color | Description |
---|---|
Objects added in Origin, which will be created in Target in case of a successful migration. | |
Modified objects, which will be altered in Target. | |
Objects deleted in Origin, which will be dropped in Target in case of a successful migration. |
See the description of the dialog controls in the reference section.
In the Database tool window ( ), select two objects of the same type. For example, two schemas.
Right-click the selection and navigate to Compare. Alternatively, press Ctrl+D.
A set of statements that you can use for writing migration scripts is generated in the Script Preview tab.
Migrate changes between objects
In the Database tool window ( ), select two objects of the same type. For example, two schemas.
Right-click the selection and navigate to Compare. Alternatively, press Ctrl+D.
In the Script Preview tab, verify that the migration scripts are correct.
Click Execute. Alternatively, click Open Query in Console to open all the scripts in a query console.
Show differences between changes in a table form
In the Migration dialog, click an item in Origin or Target fields.
Click the Object Properties Diff tab.
Show differences between changes in DDL
In the Migration dialog, click an item in Origin or Target fields.
Click the DDL Diff tab.
Select changes to migrate
To exclude a change, click the checkbox neat the change.
To exclude all the changes, click the checkbox near the Target label.
To include all the changes that were excluded, click the checkbox near the Target label.
Show identical items
To display all items that are identical in Origin and Target objects, select the Show identical checkbox.
Compare the contents of tables, materialized views, and views
The Compare Content action compares the output of tables, views, and materialized views. To find this action, select two objects in the Database tool window ( ) and right-click the selection. Also, you can double-click these objects and then compare them from the editor.
You can compare the contents of two objects that belong to different types. For example, the output of a table and a materialized view.
![Table contents Table contents](https://resources.jetbrains.com/help/img/idea/2022.1/db_compare_table_contents.png)
Description of Tolerance and Detect column insertion is available in Controls of the difference viewer for contents.
By default, when you issue a query, the number of returned rows is limited to 500. This limitation is introduced to avoid an overload (for example, when your SELECT statement returns one million rows). To increase the number of compared rows, configure the Limit page size to parameter.
Compare the contents of two tables from the Database tool window
By default, when you issue a query, the number of returned rows is limited to 500. This limitation is introduced to avoid an overload (for example, when your SELECT statement returns one million rows). To increase the number of compared rows, configure the Limit page size to parameter.
In the Database tool window ( ), select two tables.
Right-click the selection and navigate to Compare Content.
If needed, change the value of the Tolerance parameter in the comparison dialog. The Tolerance parameter defines a maximum number of differences that are allowed between two result sets. For example, if you want to consider two rows as equal if their data differs in a single column, enter
1
in the Tolerance field.
Compare the contents of two tables from the data editor
Open two tables that you want to compare. To open a table, double-click it in the Database tool window.
In the editor, click the Compare Content button (
) and select the second table.
Controls of the difference viewer for database objects
Icon | Tooltip and shortcut | Description |
---|---|---|
Expand All | Expands all the collapsed nodes. | |
Collapse All | Collapses all the expanded nodes. | |
Options | Select options according to which changes are differentiated. If the option is selected, properties that are mentioned in the option are ignored in the diff.
| |
Include change | Apply the selected change. | |
Show identical | Show all items that are identical. |
Controls of DDL Diff
Item | Tooltip and Shortcut | Description |
---|---|---|
| Previous Difference/Next Difference Shift+F7/F7 | Jump to the next or previous difference. |
Jump to Source F4 | Open a definition of the selected object in the active pane in the editor. The caret is placed in the same position as in the differences viewer. | |
Side-by-side viewer | Select a viewer mode: Side-by-side viewer or Unified viewer. The side-by-side mode has two panels, and the unified mode has one panel. | |
Whitespaces | Define how the differences viewer should treat whitespaces.
| |
Highlighting mode | Select the way differences are highlighted. The available options are:
| |
Settings | Select options that you want to ignore during the comparison.
![]() | |
Help F1 | Open a browser and show the corresponding help page. |
Controls of the difference viewer for contents
In the differences viewer for contents, you can use the same sorting functionality that is available in the data editor. For more information about sorting columns, see Sort data.
Detect column insertion
When the tables have a different number of columns, extra columns in the table with more columns are ignored. If the Detect column insertion option is on, the most different columns are ignored. On the following picture, the first column in the second table is the most different and so it is ignored. As a result, the second row is shown as containing the same data.
![Compare table data Compare table data](https://resources.jetbrains.com/help/img/idea/2022.1/dbe_compareTableData.png)
If the option is off, ignored are the last of the columns. On the following picture, the last column in the second table is ignored. So all the rows are shown as containing different data.
![Compare table data with Detect column insertion off Compare table data with Detect column insertion off](https://resources.jetbrains.com/help/img/idea/2022.1/dbe_compareTableData_a.png)
Tolerance
The Tolerance parameter defines how many columns might differ to consider two rows equal. For example, if you set Tolerance to one, rows that differ in one column are considered equal.
![Compare table data with tolerance equals to one Compare table data with tolerance equals to one](https://resources.jetbrains.com/help/img/idea/2022.1/dbe_compareTableData_b.png)
With tolerance set to zero, such rows are considered different.
![Compare table data tolerance equals to zero Compare table data tolerance equals to zero](https://resources.jetbrains.com/help/img/idea/2022.1/dbe_compareTableData.png)
With this setting, you can also check the columns that differ when data in rows is different. Such rows in those columns are highlighted. Increase the Tolerance option if you have different data more than in one row. For example, with Tolerance set to 1
, you can see that between two tables only the last_name
column differs.
![columns differ when rows contain different data columns differ when rows contain different data](https://resources.jetbrains.com/help/img/idea/2022.1/db_columns_differ_when_rows_contain_different_data.png)