DataGrip 2021.3 Help

Databases in the Version Control System

DDL data source is a virtual data source with a schema based on a number of SQL scripts. Storing these 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:

db_storing_db_in_vcs

First of all, you need to enable the VCS integration in your IDE. It will allow you to share and apply changes right from the IDE. With the VCS integration enabled, prepare a folder that you will share as a repository. This folder will also be a root folder for your DDL data source.

Then you need to create your DDL data source by dumping a regular data source (for example, SQLite) to a root/repository folder. Then you need to create a mapping between a 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 exchange with your college.

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. All synchronization in both directions is made with the help of a DDL data source.

Configuring your Version Control System

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.

By default, DataGrip has the VCS integration disabled. As a first step, we need to enable it and log into GitHub. To do that, you need to verify that the following plugins are installed and enabled: Git and GitHub.

Enable the Git plugin by JetBrains

  1. Press Ctrl+Alt+S to open the IDE settings and select Plugins.

  2. Click Installed.

  3. In the search field, type Git, and press Enter.

  4. Select the checkbox near the Git plugin name. To add integration with GitHub, you can also enable the GitHub plugin.

  5. Restart the IDE.

Install the Git plugin by JetBrains

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

Register an existing account

  1. Press Ctrl+Alt+S to open the IDE settings and select Version Control | GitHub.

  2. Click the Add button.

  3. In the dialog that opens, specify your GitHub server URL (either github.com, or an enterprise instance).

  4. Do one of the following:

    • If you already have a token, click the Use Token link and paste it there.

    • If you want to obtain a new token, enter your login and password. 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. See Creating a personal access token for more details on GitHub tokens.

    The token must have the repo, the gist and the read:org scopes enabled in your account permissions (see Understanding scopes).

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

Enable version control integration

  1. Click VCS | Enable Version Control Integration on the menu bar.

  2. In the Enable Version Control Integration dialog, ensure that Git is selected in the drop-down list and click OK.

    Enable version control integration

Now clone the repository from GitHub to your local machine.

Clone repositories

  1. Click Git | Clone.

  2. In the URL field, type the URL for your repository (for example, https://github.com/JetBrainsUser/mySQLiteDDL.git)

  3. In the Directory field, click the folder icon (The folder icon) and specify a directory for the repository files (for example, /Users/jetbrains/DataGripProjects/mySQLiteDDL).

  4. Click Clone

  5. In the Open Project dialog, click Attach.

    The repository folder will appear in the Files tool window.

    Clone repositories

Prepare a DDL data source

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

Dump an existing data source to a DDL data source

  1. 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 SQL Scripts | Dump to DDL Data Source.

    To configure code settings for the DDL data source, see Configuring DDL generation settings.

  2. In the Data Sources and Drivers dialog, select the DDL data source, and click Add directories or DDL files.

  3. In the file browser, navigate to the directory that will store DDL files of a data source and click Open.

  4. Click OK.

    When you dump files to a DDL data source, the folder with these SQL files is automatically attached in the Files tool window.

    Dump an existing data source to a DDL data source

Add a DDL mapping

  1. 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 (View | Tool Windows | Database Explorer), click the Data Source Properties icon The Data Source Properties icon.

  2. Click the DDL Mappings tab.

    Alternatively, you can get to this tab by clicking a data source in the Database Explorer (View | Tool Windows | Database Explorer) and selecting Create DDL Mapping.

  3. Click the <Choose Data Source> for Data Source.

  4. Select SQLite.

  5. Click the <Choose Data Source> for DDL Data Source.

  6. Select SQLite (DDL).

  7. In the Scope tree, select schemas or databases that will be used in the mapping (for example, main).

Add a DDL mapping

Share your changes from a data source

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.

new table (actor_1) to a data source

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.

    Apply changes from a data source to a DDL data source

Commit and push your changes to VCS

  1. Open the vertical Commit tool window Alt+0 located on the left.

  2. 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.

  3. Enter the commit message. You can click Commit message history button to choose from the list of recent commit messages.

  4. To push changes from the current branch, press Ctrl+Shift+K or choose Git | Push from the main menu.

  5. Click Push.

    Commit and push your changes to VCS

Get changes from VCS and apply them to your 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.

  • In the Branches popup or in the Branches pane of the Version Control tool window, select a branch and choose Update from the context menu.

DataGrip will pull changes from the remote branch and will rebase or merge them into the local branch depending on which update method is selected in Settings/Preferences | Version Control | Git.

Migrate changes to a data source

  1. In the Database Explorer (View | Tool Windows | Database Explorer), right-click a data source and select SQL Scripts | Apply from SQLite (DDL).

  2. In the Migration dialog, modify scripts generated in the Script Preview tab. Leave only those scripts that you plan to execute.

  3. Click Execute.

    Migrate changes to a data source

Productivity tips

  • 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.

    File-related actions

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.

    refresh a DDL data source when you change the corresponding files

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.

    Default schemas/databases
Last modified: 16 November 2021