Modifying source code of database objects

PhpStorm tracks changes that you make to objects that store their source code in the database. These objects might be triggers, procedures, functions, views, or other objects. Every modification that you make to these objects in the editor is saved as a local version of the object source code.

The Database Changes tool window displays a summary of all your changes.

Updating source code

See all the changes in source code

Select View | Tool Windows | Database Changes from the main menu.
The Database Changes tool window becomes available only after you change source code of an object.

Edit source code of an object

Right-click an object and select Go to DDL. Alternatively, press Ctrl+B.
Make changes to source code.
(Optional) In the Database Changes tool window (View | Tool Windows | Database Changes), double-click the modified object to open a difference viewer and verify your changes.
Click Submit ().
When you press the Submit button () in the Database changes tool window, you see the Migration dialog. The Migration dialog displays a migration script for the object.
In the Object Migration dialog, verify that the migration script is correct and press OK.
A migration script is code that changes all database or a part of it. You can use migration scripts to add or remove a column, upgrade the database version, or change column properties.
PhpStorm can automatically generate a migration script, but you must check it before running.

See the difference between modified and stored versions

In the Database tool window, double-click the modified database object.
When you edit source code of any object, PhpStorm tracks changes and highlights them in the gutter. For example, add a comment line to a routine or a trigger function. The added line becomes highlighted. If you click the highlighted line in the gutter, a small toolbar is displayed with the Show Diff button. You can click the Show Diff button () to see the difference between the code that you added and the one from source code.
In the Database Changes tool window (View | Tool Windows | Database Changes), double-click the modified object to open a difference viewer and verify your changes.

Load source code for a data source

In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon .
Select one or more data sources for which you want to download source code.
Right-click the selection and navigate to Load Sources. You can select between the following options:
- None: do not download source code.
- Except System Schemas: download source code for all the objects except for system schemas.
- All Schemas: download all the available source code.

Migrating changes from one schema to another

Migration is a process of moving data from one object to another (for example, from one schema to another). In PhpStorm, these objects are called Origin and Target. The migration script is generated to make Target equal to Origin. But you can modify the script to satisfy your requirements.

In the Database tool window (View | Tool Windows | Database), 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 (View | Tool Windows | Database), 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.

Outdated cached objects

PhpStorm caches the source code of all the objects from the database to provide fast code assistance and navigation. If an object that you opened was updated from a third-party location, you will see a notification that the cached object differs from the source code of the same object in the database.

If you see this warning in the IDE, you can select between the following actions:

Synchronize: fetch changes from the database and update the cached local object.
Disable check: disable this notification.

Also, there might be a conflict between your version of the object source code and the one in the database. For example, when you has modified the same source code as someone else and pressed Submit (Ctrl+K).

You can forcefully replace source code of the object in the database (Force Refactoring) or synchronize the object state and then proceed with changes (Abort Refactoring and Synchronize).

If you selected Abort Refactoring and Synchronize, PhpStorm aborts the submit operation and fetches changes from the database (just like if you pressed Synchronize Ctrl+F5). If the conflict still exists, you will see the following notification.

In this notification, you can select between the following options:

Revert local changes: roll back all your changes and replace them with the version from the database.
Keep local changes: use your changes and overwrite changes in the database.
Merge: display the diff dialog to merge two versions of the object source code.

Colors of the object state

By default, all the objects are displayed in black. It means that the current state of an object source code does not differ from source code of the same object on a server.

If source code of the object has been changed but there are no conflicts with the object source code on a server, the object is displayed in blue.

If there are conflicts in source code of a local object and the same object on a server, the object is displayed in red. If you open such object in the editor, you will see a notification about conflicts.

For example, on the following screenshot, the Do_Something procedure is modified but contains no conflicts, the V1_trg_i trigger was modified and conflicts with source of the same trigger on a server, other objects are not modified.

Controls of the Database Changes tool window

Item	Shortcut	Description
Submit	`Ctrl+Enter`	Submit local changes to the database server. See Submit changes to a database.
Roll back		Roll back changes. This button is available only for the manual transaction mode.
Show Diff	`Ctrl+D`	Compare two versions of source code.
Group by	`Ctrl+D`	Group objects according to their type. For example, routines, rules, or views.
	`Ctrl+NumPad +`	Expand all nodes in all trees.
	`Ctrl+NumPad -`	Collapse all nodes in all trees.

Last modified: 19 March 2022