DataGrip 2023.3 Help

DDL data sources

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.

DDL data source lets you maintain database versioning. Keep the SQL files under a VCS system and regenerate them every time your database structure is updated.

Once created, DDL data sources are available in Database Explorer ( View | Tool Windows | Database Explorer) . You can create and manage the SQL files with statements in the Files tool window ( View | Tool Windows | Files) .

DDL data source in Database Explorer and SQL files with statements in Files tool window

Check the following video that shows how to work with DDL data sources.

In DataGrip, you can create a DDL data source by dumping an existing data source and by using existing SQL files:

Use file scopes to filter the files and folders that are used as sources for the DDL data source. When you dump DLL files to the existing DDL data source, the excluded ones will be ignored.

Create a 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 Import/Export | Dump to DDL Data Source.

    To configure code settings for the DDL data source, refer to 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 Database Explorer.

Create a DDL data source from existing files

  1. Open data source properties. You can open data source properties by using one of the following options:

    • Navigate to File | Data Sources....

    • Press Control+Alt+Shift+S.

    • In the Database Explorer Command 1 ( View | Tool Windows | Database Explorer), click the Data Source Properties icon (The Data Source Properties icon).

    Add new data source
  2. In the Data Sources tab of the Data Sources and Drivers dialog left pane, click the Add icon (The Add icon) and select DDL Data Source.

    The Add data source button
  3. In the Sources pane, click the Add button (the Add button).

  4. In the file browser, navigate to the SQL file or files with DDL statements and click Open.

  5. Click the Dialect link and select a dialect that you want to use for added SQL files.

  6. Apply settings and click OK.

    Create a DDL data source from existing SQL files

Work with DDL data sources

Filter files and folders using scopes

  1. Open data source properties. You can open data source properties by using one of the following options:

    • Navigate to File | Data Sources....

    • Press Control+Alt+Shift+S.

    • In the Database Explorer Command 1 ( View | Tool Windows | Database Explorer), click the Data Source Properties icon (The Data Source Properties icon).

    Add new data source
  2. In the Sources pane, click the Add button (the Add button).

  3. To add and filter files and folders using scopes, select one of the following options:

    • To use the existing scope, select it from the menu.

    • To create a group of files specifically for the current DDL data source, select Anonymous Scope. This scope will not be stored among others.

    • To edit the existing scope or create a new one, click Edit Scopes. For more information about scopes, refer to Scopes and file colors.

  4. Apply settings and click OK.

    Filter the files and folders using scopes

If the Auto sync is selected, the DDL data source is automatically refreshed with changes to the corresponding files. To disable this behavior, clear the checkbox.

Reference objects in a data source

You can create an index in the DDL data source that references a table in a usual data source. In your code, this table will be correctly referenced and the table with the index will appear in the database tree.

  1. Open data source properties. You can open data source properties by using one of the following options:

    • Navigate to File | Data Sources....

    • Press Control+Alt+Shift+S.

    • In the Database Explorer Command 1 ( View | Tool Windows | Database Explorer), click the Data Source Properties icon (The Data Source Properties icon).

    Add new data source
  2. Select a DDL data source that you want to extend.

  3. Click the Add icon (the Add icon), select Extend Data Source and select the data source that you want to reference.

    Reference objects in data source

Set a dialect for SQL files in the DDL data source

  1. Open data source properties. You can open data source properties by using one of the following options:

    • Navigate to File | Data Sources....

    • Press Control+Alt+Shift+S.

    • In the Database Explorer Command 1 ( View | Tool Windows | Database Explorer), click the Data Source Properties icon (The Data Source Properties icon).

    Add new data source
  2. Click a DDL data source that you want to modify.

  3. On the Sources tab, click the Configure SQL Dialect icon (the Configure SQL Dialect icon).

    Set a dialect for SQL files in the DDL data source

Creating objects in the DDL data source

  • In the Database Explorer ( View | Tool Windows | Database Explorer) , right-click an item under the DDL data source node and navigate to New. In the list of objects, select the object that you want to create.

    Creating objects in the DDL data source
  • Right-click a directory of the DDL data source in the Database Explorer (View | Tool Windows | Files) and select New | SQL File. Type a CREATE statement in the created SQL file.

    Creating objects in the DDL data source

Configuring DDL generation settings

You can configure code settings for files that are generated for the DDL data source.

  1. Open data source properties. You can open data source properties by using one of the following options:

    • Navigate to File | Data Sources....

    • Press Control+Alt+Shift+S.

    • In the Database Explorer Command 1 ( View | Tool Windows | Database Explorer), click the Data Source Properties icon (The Data Source Properties icon).

    Add new data source
  2. On the Data Sources tab, select the DDL data source.

  3. Click the New File Layout tab.

    Configuring DDL generation settings

Settings of the New File Layout tab

Option

Description

Layout

Defines the way how to store and generate DDL files.

  • File per object by schema: generate separate files for each object and place them in folders with schema names.

    File per object by schema
  • File per object by schema and database: generate separate files for each object and place them in folders with schema names. Places folders with schema names in folders with database names.

    File per object by schema and database
  • File per object: generate separate files for each object and place them in one directory.

    File per object
  • File per object with order: generate separate files for each object and add a numeric prefix to a filename.

    File per object with order
  • File per object by schema and type: generate separate files for each object and place them in folders with type names. Place folders with type names in folders with schema names.

    File per object by schema and type

Root path

Defines the location directory for DDL files.

Code style

Applies the code style to generated DDL files.

Qualify objects with schema names

Adds a schema name to the table name. You can qualify a table when you have two and more tables with identical names in different schemes. This option has the following parameters:

  • Auto: automatically qualifies table names if you have more than two identical table names in different schemes.

  • Never: never qualifies table names.

  • Always: always qualifies table names.

Place constraints

Defines where to place FOREIGN KEY, UNIQUE, CHECK, and other constraints.

  • Inside column: into the column definition when the constraint is based on one column. When the constraint is based on two or more columns, place constraints into the table definition after all the columns.

    Place constraints inside column
  • Inside table: into the table definition, after all the columns.

    Place constraints inside table
  • After table: after the table definition, using ALTER TABLE ADD CONSTRAINT.

    Place constraints inside table

Regenerate database-provided code

Regenerate property definitions that are provided as a part of source code by the database.

Reformat generated code

Reformat generated code with the current code style profile. This option affects generated code only and does not affect the code received directly from the server.

Default schemas/databases

Set a default schema or database for objects in the DDL data source. DDL data sources have unnamed databases and schemas. The following table allows you to set the default schema name.

Default schemas/databases
Last modified: 25 October 2023