gitversion-controldatagripredgateintellij-datagrip

Auto update local repository upon schema change

With DataGrip, I know there's a way to generate SQL scripts based on the database schema (Right click on the table > SQL Scripts > SQL Generator). However, is there a way to get DataGrip to automatically update these generated SQL scripts whenever the schema changes via queries (DDL) or the UI (Modify Table option)?

Right now, if I want my SQL script files to be under version control and if I want these files to reflect the latest state/schema of my database, I have to:

  1. Manually modify the file(s).
  2. Copy the contents of the file (query) and paste it onto a console in DataGrip.
  3. Run the query from the console in order to update the database schema.
  4. If step 3 succeeds, commit the changes in my file to my local GIT repository. If step 3 fails, start over from step 1.

Instead, I want to:

  1. Make a schema change directly on DataGrip via a query or New > Create Table option or Right click on the table > Modify Table option.
  2. (If step 1 succeeds) Be shown a change set on DataGrip itself, and upon clicking something like Save / Commit, modify the corresponding SQL scripts in my local repository and create a new commit. This will then trigger my CI/CD pipeline to build, test and deploy my changed schema.

P.S: This is similar to what Red-Gate's SQL Source Control can do in SQL Server Management Studio. Unfortunately, they don't have a plugin for DataGrip.

Solution

  • Now DataGrip cannot automatically update SQL files according to schema changing. We have the idea to implement this in the future. I think the best ticket to upvote will be: https://youtrack.jetbrains.com/issue/DBE-3852

    Anyway, your current algorithm seems to be not the most effective. What I would offer:

    1. Open Files pane (Ctrl/Cmd+1) and attach the folder with your DDL scripts there.
    2. Associate these files with the needed data sources via Preferences | Database | SQL Resolution Scopes
    3. Optionally, install VCS plugin to commit and push files just from DataGrip. Learn more here: https://blog.jetbrains.com/datagrip/2018/06/01/tutorial-how-to-use-git-with-datagrip/
    4. When you modify your files just run these scripts against the data sources you need. The context menu can also be used for this: enter image description here