Update source code of database objects

In DataGrip, you can update the source code of a database object directly in its DDL CREATE script. The IDE will generate a migration script for you to review and submit to the database.

Update a single database object

Step 1. Load source code

DataGrip retrieves the source code of database objects during the introspection of a data source. The retrieved source code can be modified and submitted back to the database.

Open data source properties. You can open data source properties by using one of the following options:
- Navigate to File | Data Sources....
- Press Ctrl+Alt+Shift+S.
- In the Database Explorer ⌘ 1 ( View | Tool Windows | Database Explorer), click the Data Source Properties icon ().
Right-click the data source for which you want to download source code and select Load Sources | Except System Schemas.
Click OK.

Step 2. Make the changes

To make the required changes in source code of an object, open and edit its DDL CREATE script directly in the DDL editor.

In the Database Explorer, right-click the object and select Navigation | Go to DDL. Alternatively, press Ctrl+B.
In the DDL CREATE script that opens in DDL editor, make the required changes.
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.
Click the change marker in the gutter to see the previous contents of the modified line.
For more information about seeing the differences between modified and stored versions, refer to Modify source code.
The object can be updated from a third-party location, in this case the cached object would differ from the source code of the same object in the database. For more information about it, refer to Outdated cached objects.

Step 3. Submit the changes

Submit the changes that you made in source code of database object to the database.

By default, the migration script preview for a valid script is not shown in the Object Migration dialog upon submitting. Make sure that it appears at this step. Otherwise, the valid migration script will be executed silently.
Open IDE settings by pressing Ctrl+Alt+S, navigate to Database | Other | Refactoring, and ensure that the Show preview of valid script when updating source text checkbox is selected.
In the DDL editor, click the Submit icon () on the toolbar.
In the Object Migration dialog that opens, verify that the migration script is correct.
DataGrip can automatically generate a migration script, but you must check it before running.
Click OK to execute the script.

In case you need to apply more complex changes, DataGrip will generate a corresponding migration script with, for example, DROP and CREATE included.

Migration script with changes for a single database object

Update multiple database objects

Step 1. Load source code

DataGrip retrieves the source code of database objects during the introspection of a data source. The retrieved source code can be modified and submitted back to the database.

Open data source properties. You can open data source properties by using one of the following options:
- Navigate to File | Data Sources....
- Press Ctrl+Alt+Shift+S.
- In the Database Explorer ⌘ 1 ( View | Tool Windows | Database Explorer), click the Data Source Properties icon ().
Right-click the data source for which you want to download source code and select Load Sources | Except System Schemas.
Click OK.

Step 2. Make the changes in DDL scripts of multiple objects

To make the required changes in source code of database objects, open and edit their DDL CREATE scripts directly in the DDL editor.

In the Database Explorer, select the objects, right-click them and navigate to Navigation | Go to DDL. Alternatively, press Ctrl+B.
In the DDL CREATE scripts that open in DDL editor for each object, make the required changes.
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.
Click the change marker in the gutter to see the previous contents of the modified line.
For more information about seeing the differences between modified and stored versions, refer to Modify source code.
The object can be updated from a third-party location, in this case the cached object would differ from the source code of the same object in the database. For more information about it, refer to Outdated cached objects.

Step 3. Observe pending changes

To verify the changes, in the Database Changes tool window (View | Tool Windows | Database Changes), double-click the modified object. Alternatively, select the object and click the Show Diff icon ().
In the Diff Viewer that opens, verify your changes for the selected object.

Step 4. Submit the changes

Submit the changes that you made in source code of database object to the database.

By default, the migration script preview for a valid script is not shown in the Object Migration dialog upon submitting. Make sure that it appears at this step. Otherwise, the valid migration script will be executed silently.
Open IDE settings by pressing Ctrl+Alt+S, navigate to Database | Other | Refactoring, and ensure that the Show preview of valid script when updating source text checkbox is selected.
To submit the changes for all the modified database objects at once, in the Database Changes tool window, select the objects and click the Submit icon () on the toolbar.
To submit the changes for the modified database objects one by one, select the object and click the Submit icon () on the toolbar.
In the Object Migration dialog that opens, verify that the migration script is correct for all the included database objects.
DataGrip can automatically generate a migration script, but you must check it before running.
Click OK to execute the script.

Migration script with changes for multiple database objects

Last modified: 31 May 2024