Modify source code of database objects
View all modified objects: View | Tool Windows | Database Changes
The Database Changes tool window becomes available only after you change source code of an object.
The following YouTube video shows a basic workflow of updating routine's source code: Updating source code of a routine.
DataGrip 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.
DataGrip retrieves the information about a database during the introspection process. This information is used to show the objects in the Database Explorer, display their DDL, suggest them during completion, and in other features for coding assistance.
You can update the source code of database objects directly by editing their DDL and submitting your changes. The IDE will generate a migration script and execute it in the database.
The Database Changes tool window displays a summary of all your changes.
DataGrip retrieves the source code for a data source during the introspection. You can manage this process in the data source properties.
Open data source properties. You can open data source properties by using one of the following options:
Navigate to File | Data Sources....
Press CtrlAltShift0S.
In the Database Explorer (View | Tool Windows | Database Explorer), 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.
You can update the source code of database objects by directly editing their DDL CREATE
scripts and submitting your changes in the editor. The IDE will generate a migration script with your changes, prompt you to verify it, and then execute it in the database.
Right-click an object and select Navigation | Go to DDL. Alternatively, press Ctrl0B.
note
The object references in the generated script are not qualified (for example,
<view_name>
instead of<schema_name>.<view_name>
). You can view this script, edit it, and submit your changes to the database.For a runnable DDL script that includes
CREATE OR REPLACE
, qualified references, and so on, use the SQL Generator.Make changes to source code.
(Optional) In the Database Changes tool window (View | Tool Windows | Database Changes), double-click the modified object to open the Diff Viewer and verify your changes.
Click the Submit button ().
When you click 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 click OK.
A migration script is code that changes the entire 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.
DataGrip can automatically generate a migration script, but you have to check it before running.
Select View | Tool Windows | Database Changes from the main menu.
note
The Database Changes tool window becomes available only after you change the source code of a database object.
When you edit the source code of any object, DataGrip 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 for Lines button. You can click the Show Diff for Lines 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 the Diff Viewer and verify your changes.
DataGrip caches the source code of all the objects from the database to provide fast coding 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 have modified the same source code as someone else and pressed Submit (Ctrl0K).
You can forcefully replace the 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, DataGrip stops the submit operation and fetches changes from the database (similar to pressing Synchronize CtrlF5). 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.
By default, all the objects are displayed in black. It means that the current state of an object source code does not differ from the source code of the same object on a server.
If the 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 the source code of a local object and the same object on a server, the object is displayed in red. If you open such an object in the editor, you will see a notification about conflicts.
For example, in the following screenshot, the get_customer_balance
function is modified but contains no conflicts, the film_in_stock
function was modified and conflicts with the source of the same function on a server, other objects are not modified.
Thanks for your feedback!