DataGrip 2024.2 Help

Import SQL dump files

In this tutorial, we will learn how to run SQL dump files in DataGrip.

For MySQL and PostgreSQL, DataGrip supports dump tools. For more information about using them, refer to Restore a full data dump for MySQL and PostgreSQL

Before you start

Make sure that you have the following:

  1. A running database instance ready to use.

  2. In DataGrip, a data source that can connect to your database instance.

  3. SQL dump files.

For illustration purposes, in this tutorial we will use SQLite database, corresponding data source, and Sakila dump files.

Where do I get those materials?
  1. In SQLite, a database is a single file. DataGrip will create the file automatically when you create the data source.

  2. To learn how to create SQLite data source in the IDE, refer to the SQLite topic. For more information about data sources and instructions on creating data sources for other database vendors, refer to the Create a data source chapter.

  3. You can get the Sakila files by cloning the dumps repository.

    git clone https://github.com/DataGrip/dumps.git

    Note that you need to install Git to clone the repository. For more information about installing plugins, refer to the Plugins topic. For more information about cloning repositories, refer to the Step 2. Clone a GitHub repository step of a GitHub tutorial.

So, the initial state includes locally stored SQL dump files and a DataGrip project with a data source.

Start

In DataGrip, there are several ways of running SQL files. We will take a detailed look at two of them:

  • Run files in Database Explorer (⌘ 1).

    In this case, you will use a context menu of a target schema to navigate to and select your dump files in your file browser. You can run any dump files that are accessible to you, no special association to DataGrip project is required.

    This case is preferable if your dump files aren't attached to your DataGrip project. For example, if you only need to run your files once.

  • Run attached files in Files tool window (Alt+2).

    In this case, you will use a context menu of a dump file to create a run configuration and select your target schema. You can run the dump files that are located in the directories attached to your project.

    This case might be preferable if the directory with your dump files is already attached to your DataGrip project, and the files are available in the Files tool window .

For more information about ways of running SQL files in DataGrip, refer to the Run an SQL file topic.

Step 1. Prepare files in IDE

For the Database Explorer scenario, you can skip this step.

To use the files that are stored locally on your machine in the Files tool window , you have to attach directories with them to your DataGrip project.

Attach the directory with dump files

  1. You can open the Files tool window by doing one of the following:

    • In the main menu, go to View | Tool Windows | Files.

    • On the tool window bar, click Files tool window icon Files.

    • Press Alt+2.

  2. In the Files tool window , click the Attach Directory to Project button (Attach Directory to Project) in the toolbar.

    Alternatively, right-click in the area of the Files tool window and select Attach Directory to Project.

  3. In the file browser, navigate to the directory that you want to attach. In our case, it is the dumps directory.

  4. Click Open.

Directory with dump files is attached to the DataGrip project

Step 2. Import structure dump

To run the dump files that contain structure-generating scripts, you will have to define the schema that you want to run the files and their scripts against. You can do this in a dedicated dialog.

Let's run the sqlite-sakila-schema.sql structure dump file that generates objects in the main schema.

  1. In the Database Explorer ( View | Tool Windows | Database Explorer) , right-click a data source, or a schema and select SQL Scripts | Run SQL Script.

  2. In the file browser window that opens, navigate to the sqlite-sakila-schema.sql file.

  3. Click Open.

    You can view the output in Run tool window. For more information about tool window controls, refer to Run tool window.

  1. In the Files tool window ( View | Tool Windows | Files) , navigate to the sqlite-sakila-db tree node.

  2. Expand sqlite-sakila-db tree node.

  3. Right-click the sqlite-sakila-schema.sql file and select Run 'sqlite-sakila-schema...'. Alternatively, press Ctrl+Shift+F10.

  4. In the Target data source / schema table of Edit Configuration dialog that opens, click the Add button (The Add button) and select SQLite | main.

  5. Click Run.

    You can view the output in Run tool window. For more information about tool window controls, refer to Run tool window.

Once the execution of the dump file with structure-generating scripts is done, your SQLite database has database objects in its main schema. You can work with the structure, explore it in the Database Explorer, and run queries in the query console.

Step 3. Import data dump

To add data to those database objects, run the sqlite-sakila-insert-data.sql file with corresponding scripts against your main schema.

  1. In the Database Explorer ( View | Tool Windows | Database Explorer) , right-click a data source, or a schema and select SQL Scripts | Run SQL Script.

  2. In the file browser window that opens, navigate to the sqlite-sakila-insert-data.sql file.

  3. Click Open.

    You can view the output in Run tool window. For more information about tool window controls, refer to Run tool window.

  1. In the Files tool window ( View | Tool Windows | Files) , navigate to the sqlite-sakila-db tree node.

  2. Expand sqlite-sakila-db tree node.

  3. Right-click the sqlite-sakila-insert-data.sql file and select Run 'sqlite-sakila-insert...'. Alternatively, press Ctrl+Shift+F10.

  4. In the Target data source / schema table of Edit Configuration dialog that opens, click the Add button (The Add button) and select SQLite | main.

  5. Click Run.

    You can view the output in Run tool window. For more information about tool window controls, refer to Run tool window.

After running all the dump files, you can proceed with working with your database.

  • For more information about working with database objects in DataGrip, refer to Database objects.

  • To write and run queries, open the default query console by clicking the data source and pressing F4.

  • To view and edit data of a database object, open Data editor and viewer by double-clicking the object.

Last modified: 30 July 2024