Creating and editing SQL files - Help

Opening the Files tool window

The Files tool window acts as a file manager for files and folders associated with your project. To open this tool window, do one of the following:

Select View | Tool Windows | Files.
Press Alt+2.
In the lower-left corner of the workspace, point to and select Files.

Attaching a directory to the project

To get optimal coding assistance when editing your SQL files, the corresponding files should be stored in directories defined in DataGrip as your "working directories". To make a directory your working directory, you should "attach" that directory to your project:

Right-click the content pane of the Files tool window and select Attach Directory.
In the dialog that opens select an existing directory or create a new one. For example, to create a new directory in your home directory, click , then click and specify the directory name (e.g. SampleSQLFiles).

Creating an SQL file

In the Files tool window, select the directory in which you want to create a file, and do one of the following:
- Select New | SQL File from the context menu.
- Press Alt+Insert and select SQL File.
- Select New from the File menu, and select SQL File.
In the dialog that opens, specify the file name, e.g. family-ddl. Click OK or press Enter.

The new file is shown in the Files tool window.

At the same time, the file opens in the editor.

Specifying an SQL dialect

Each SQL file, normally, is assigned an SQL dialect for better coding assistance. (An SQL dialect is a DBMS-specific version of SQL.)

DataGrip lets you specify the SQL dialect for an individual file, all .sql and .ddl files in a directory, project, or on your computer. In the following example, we'll set the SQL dialect for all the .sql and .ddl in our working directory.

Click the Change dialect to link in the editor. (Alternatively, right-click the editing area, select Change Dialect (<CurrentDialect>), and then select SQL Dialects.)
In the dialog that opens, click and then select your SampleSQLFiles directory.
Click the SQL Dialect cell to the right of your working directory and select the necessary dialect.
Agree to apply the selected dialect recursively, i.e. to all the contents of your working folder.

The result may look something like this:
Click OK.

Defining a table in an SQL file

Now we are going to define the following table structure in our file:

CREATE TABLE family (
  member_id INT NOT NULL,
  name VARCHAR(50),
  relation VARCHAR(50)
);

For inserting the CREATE TABLE statement, there is a predefined pattern. Press Ctrl+J and select the pattern (tab - new table definition).

Here is the result:
Type the table name (family).
To switch from family to col, press Tab. Replace col with member_id.
Type the remaining part of the table definition.

Creating a DDL data source

In addition to databases, SQL files containing data definition language statements (DDL statements) can be used as data sources. As a result, the tables, columns and other objects defined in such files become available for referencing in the editor.

The file family-ddl.sql defines a table and therefore can act as a DDL data source. Let us create a data source using that file.

The quickest way to create a DDL data source is by using drag-and-drop:

Drag the file family-dll.sql from the Files to the Database tool window. As a result, the corresponding data source is created.

To create DDL data sources, you can also use the Data Sources and Drivers dialog: Ctrl+Shift+Alt+S, , DDL Data Source, etc.

Defining data in an SQL file

Now we are going to create an SQL file for adding data to the table family. Because family-ddl.sql is defined as a data source, the names of the table and of its columns as well as the info about corresponding data types are now available in the editor.

Create an SQL file (e.g. family-data.sql).

Let us add this simple INSERT statement into the file:

INSERT INTO family (member_id, name, relation) VALUES (1, 'Chloe', 'mother');
Add the INSERT statement by selecting the corresponding predefined pattern: Ctrl+J | ins - insert rows into a table.
Select the table name (family).
Select the list of column names (member_id, name, relation).
When specifying the values, press Ctrl+P (View | Parameter Info) to see the information about the value types.