Creating and editing SQL files
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:
- 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
tocol
, press Tab. Replacecol
withmember_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.