Export
You can use a variety of methods to export data and object structures from your databases. These methods include usage of various generators, data extractors, and shortcuts. Also, you can also export data in a variety of data formats. For example, TXT, CSV, JSON, XML, Markdown, Excel, and other formats. You can select a predefined extractor or create your own.
In DataGrip, you export object structures and data separately. It means that you can export a structure of a table and then export data from the table. The full data dump is available only for PostgreSQL and MySQL with the help of mysqldump and pg_dump. The full data dump includes structures of all database objects and data of these objects in a single file. For more information, see Create a full data dump for MySQL and PostgreSQL.
Exporting object structures
Data definition language (DDL) defines the structure of a database, including rows, columns, tables, indexes, and other elements. In DataGrip, you can generate data definition structures by using shortcuts with predefined settings or by using the SQL Generator and customize the export settings.
Generate DDL definitions for database objects
In the Database tool window (), right-click a database object and select Ctrl+Alt+G.
On the right toolbar, you can find the following controls:
: copy output to the clipboard.
: save output to a file.
: open output in a query console.
Change output settings of the SQL Generator
In the Database tool window (), right-click a database object (for example, a table) and select Ctrl+Alt+G.
In the SQL Generator tool window, click the File Output Options icon (
).From the Layout list, select a method that you want to use:
File per object: generates a set of SQL files.
File per object with order: generates a numbered set of SQL files.
Generate a DDL definition to the query console
In the Database tool window (), right-click a database object and select .
Generate a DDL definition to the clipboard
In the Database tool window (), right-click a database object and select .
If your database stores DDL of the object, you can retrieve DDL from the database by selecting the Request and Copy Original DDL.
Export data
You can export database data as SQL INSERT and UPDATE statements, TSV and CSV, Excel, Markdown, HTML tables and JSON format. When you export to a file, a separate file is created for each individual table or view.
To configure CSV extractors, see Configure an extractor for delimiter-separated values. In CSV settings, you can set separators for rows and headers, define text for NULL values, specify quotation, create new extractors for formats with delimiter-separated values.
To export data in binary formats (for example, XLSX), use the Export Data dialog.
Before DataGrip 2020.1, if you select the default extractor from the list, you set this extractor as default for the whole IDE. Beginning from DataGrip 2020.1, you set the extractor for a single table. If you open a different table, the extractor defaults to CSV.
Export data from the Database tool window
In the Database tool window (), right-click a database object and navigate to Export Data to File(s).
In the Export Data dialog, customize the following settings:
Extractor: select the export format (for example, Excel (xlsx)).
Transpose: select to export data in the transposed view. In this view, the rows and columns are interchanged.
Add table definition (DDL): add a table generation code (
CREATE TABLEorALTER TABLE).Computed: include virtual columns that are not physically stored in the table (for example, the IDENTITY column).
Generated: include auto-increment fields for INSERT and UPDATE statements.
File name: type a filename. This option is available only if you export one table.
Output directory: select a storage path.
To copy the generated script to the clipboard, click Copy to Clipboard. To save the script to a file, click Dump to File.
Export data from the editor
To invoke the Export Data dialog for a table in the editor, click the Export Data icon (
).
Create a full data dump for MySQL and PostgreSQL
You can create backups for database objects (for example a schema, a table, or a view) by running mysqldump for MySQL or pg_dump for PostgreSQL.
Export data with mysqldump or pg_dump
In the Database tool window (), right-click a database object and navigate to:
Export with "mysqldump": for MySQL data sources.
Export with "pg_dump": for PostgreSQL data sources.
In the Export with <dump_tool> dialog, specify the path to the dump tool executable in the Path to <dump_tool> field.
(Optional) Edit the command-line options in the lower part of the dialog.
Click Run.
Productivity tips
Copy a table to another schema
Right-click a table and select Copy Table to. Alternatively, press F5.
Enter a schema name and click OK.
(Optional) In the Import dialog, modify table settings.
