Databases in the Version Control System

DDL data source is a virtual view of a database structure based on SQL files that contain data definition language statements (DDL statements). You can reference all tables, columns and other objects defined in such files in the editor. Diagrams are also supported.

Storing these SQL files in the Version Control System (VCS) is a way to keep your database under the VCS.

The general workflow with VCS is the following:

Configure the VCS integration.
First of all, you need to enable and configure the VCS integration in your IDE. It will allow you to share and apply changes right from the IDE.
As an example, we will be working with Git and GitHub.
Prepare a directory.
With the VCS integration enabled, prepare a directory that you will share as a repository. You can do it by cloning an existing repository to a certain directory. This directory will also be a root folder for your DDL data source.
Prepare a DDL data source.
Then you need to create your DDL data source by dumping a regular data source (for example, SQLite) to the prepared directory.
In this tutorial, we will dump a regular data source to an empty directory that is shared as a repository.
Map regular data source to DDL data source.
Then you need to create a mapping between a regular data source and a DDL data source. You need the mapping to set what you want to share. For example, you hardly need to share all system or template schemas. So, you can select those schemas that you will share with your colleagues.
Share your DDL data source.
For the database of your regular data source to be available under the VCS, you need to commit and push the SQL files of DDL data source to the remote repository.

Depending on what you want to do, you can dump your changes and share them through a VCS, or you can pull changes and apply it to your data source. The synchronization in both directions is made with the help of a DDL data source.

I am making changes and want to share them	I want to get changes and apply them
Make changes to your data source. Apply changes to a DDL data source. Commit and push your changes to VCS.	Pull changes from VCS to update your DDL data source folder. Migrate changes from a DDL data source to a data source.

Configuring your Version Control System

Enable the Git plugin

This functionality relies on the Git plugin, which is bundled and enabled in DataGrip by default. If the relevant features aren't available, make sure that you didn't disable the plugin.

Press Ctrl+Alt+S to open settings and then select Plugins.
Open the Installed tab, find the Git plugin, and select the checkbox next to the plugin name.

Install the GitHub plugin

This functionality relies on the GitHub plugin, which you need to install and enable.

Press Ctrl+Alt+S to open settings and then select Plugins.
Open the Marketplace tab, find the GitHub plugin, and click Install (restart the IDE if prompted).

We will use Git as a version control system and GitHub as a place to store our repository. To create a repository at GitHub, follow the steps in the Create a repo tutorial at docs.github.com.

As a first step, we need to enable the VCS plugins and sign into GitHub.

Step 1. Enable the VCS plugins by JetBrains

Press Ctrl+Alt+S to open settings and then select Plugins.
Open the Installed tab.
In the search field, type Git, and press Enter.
Select the checkbox near the Git plugin name.
Open the Marketplace tab.
Install and enable the GitHub plugin.
Restart the IDE.

Then you need to authorize at GitHub with a user account that has access to the repository with a DDL data source.

Step 2. Sign in to GitHub

Press Ctrl+Alt+S to open settings and then select Version Control | GitHub.
Click .
Select Log In via GitHub.
Enter your GitHub credentials in the browser window that opens. If you have two-factor authentication enabled, you will be asked to enter a code that will be sent to you by SMS or through the mobile application.

After all the necessary plugins are installed, you need to enable the VCS integration in the IDE.

Step 3. Enable version control integration

Click Git | Enable Version Control Integration from the main menu.
In the Enable Version Control Integration dialog, ensure that Git is selected in the list and click OK.

Now clone the repository from GitHub to your local machine.

Prepare a directory

Click Git | Clone.
In the URL field, type the URL for your repository. For example, https://github.com/JetBrainsUser/mySQLiteDDL.git.
In the Directory field, click the icon and specify a directory for the repository files. For example, /Users/jetbrains/DataGripProjects/mySQLiteDDL.
Click Clone
In the Open Project dialog, click Attach.
The repository folder will appear in the Files tool window .

Prepare a DDL data source

When all the configuration with VCS is done, you can start preparing your DDL data source.

Step 1. Dump an existing data source to a DDL data source

In the Database Explorer ( View | Tool Windows | Database Explorer) , right-click a data source that you want to dump to a DDL data source and select Import/Export | Dump to DDL Data Source.
To configure code settings for the DDL data source, refer to Configuring DDL generation settings.
In the Data Sources and Drivers dialog, select the DDL data source, and click Add directories or DDL files.
In the file browser, navigate to the directory that will store DDL files of a data source and click Open.
Click OK.
In the Add Files to Git dialog, click Add to add the SQL files of your DDL data source to Git.

Step 2. Add a DDL mapping

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 ().
Click the DDL Mappings tab.
Alternatively, you can get to this tab by right-clicking a data source in the Database Explorer ( View | Tool Windows | Database Explorer) and selecting DDL Mapping | Create DDL Mapping.
Click the <Choose Data Source> for Data Source.
Select SQLite.
Click the <Choose Data Source> for DDL Data Source.
Select SQLite (DDL).
In the Scope tree, select schemas or databases that will be used in the mapping (for example, main).
If the Dump DDL dialog apperas, click Later.

Share your DDL data source

For the DDL files of your DDL data source to appear in the remote repository, commit and push them.

Commit and push your DDL files

Open the Commit tool window by clicking the More tool windows icon in the main toolbar and selecting Commit.
In the Commit tool window, select the checkbox near Changes to select all the changes.
If you press Ctrl+K, the entire active changelist will be selected.
Enter the commit message.
To push changes from the current branch, press Ctrl+Shift+K or choose Git | Push from the main menu.
Click Push.

This workflow might help you if you made changes to your data source and want to share it in the VCS. For example, let's add a new table (actor_1) to our SQLite data source.

Add new table (actor_1) to a data source

Step 1. Update a DDL data source from a data source

In the Database Explorer ( View | Tool Windows | Database Explorer) , right-click a DDL data source and select DDL Mapping | Update from SQLite.

Step 2. Commit and push your changes to VCS

Open the vertical Commit tool window Alt+0 located on the left.
As your changes are ready to be committed, select the corresponding files or an entire changelist.
If you press Ctrl+K, the entire active changelist will be selected.
Enter the commit message. You can click to choose from the list of recent commit messages.
To push changes from the current branch, press Ctrl+Shift+K or choose Git | Push from the main menu.
Click Push.

Get changes from VCS and apply them to a project data source

If you have not yet cloned the repository and the necessary changes are there, clone the repository, create a DDL data source, and add a DDL mapping. Then proceed with the migration procedure.

Otherwise, update a local copy of the repository and migrate changes.

Step 1. Update a project from the VCS

Select the update type (this strategy will be applied to all roots that are under Git version control):
- Merge the incoming changes into the current branch: select this option to perform merge during the update. This is equivalent to running git fetch and then git merge, or git pull --no-rebase.
- Rebase the current branch on top of the incoming changes: select this option to perform rebase during the update. This is equivalent to running git fetch and then git rebase, or git pull --rebase (all local commits will be put on top of the updated upstream head).

If you choose not to show the Update Project dialog in the future, and then want to modify the default update strategy later, go to the Version Control | Confirmation page of settings Ctrl+Alt+S, select Update under Display options dialog when these commands are invoked, and modify the update strategy the next time you perform an update.

When the update operation is completed, the Update Info tab is added to the Git tool window Alt+9. It lists all commits that were made since the last sync with the remote, and lets you review the changes the same way as in the Log tab.

Step 2. Migrate changes to a data source

In the Database Explorer ( View | Tool Windows | Database Explorer) , right-click a data source and select SQL Scripts | Apply from SQLite (DDL).
In the Migration dialog, modify scripts generated in the Script Preview tab. Leave only those scripts that you plan to execute.
Click Execute.

Productivity tips

File-related actions

All actions for files are available on DDL data source elements as well. For example, you can delete, copy, or commit files related to the schema elements from the Database Explorer.

Automatically refresh a DDL data source when you change the corresponding files

If the Auto sync option is turned on, the DDL data source will be automatically refreshed with changes to the corresponding files. This was already the default behavior, but now you have the option to disable it.

Initial search path

On the New File Layout tab, you can define names for your database and schemas, which will be displayed in the DDL data source. DDL scripts do not usually contain names, and in these cases, there will be dummy names for databases and schemas by default.

Last modified: 11 February 2024