IntelliJ IDEA 2020.1 Help

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 IntelliJ IDEA, 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 IntelliJ IDEA, 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 (View | Tool Windows | Database), right-click a database object and select SQL Scripts | SQL Generator Ctrl+Alt+G.

    On the right toolbar, you can find the following controls:

    • The copy icon: copy output to the clipboard.

    • The Save to File icon: save output to a file.

    • Run in a console: open output in a query console.

    Generate DDL definitions for database objects

Change output settings of the SQL Generator

  1. In the Database tool window (View | Tool Windows | Database), right-click a database object and select SQL Scripts | SQL Generator Ctrl+Alt+G.

  2. On the left toolbar, click the File Output Options icon (The File Output Options icon).

    Change output settings of the SQL Generator

Generate a DDL definition to the query console

  • In the Database tool window (View | Tool Windows | Database), right-click a database object and select SQL Scripts | Generate DDL to Console.

    Generate a DDL definition to the query console

Generate a DDL definition to the clipboard

  • In the Database tool window (View | Tool Windows | Database), right-click a database object and select SQL Scripts | Generate DDL to Clipboard.

    If your database stores DDL of the object, you can retrieve DDL from the database by selecting the Request and Copy Original DDL.

    Generate a DDL definition to the clipboard

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 Dump Data dialog.

Export data from the Database tool window

  1. In the Database tool window (View | Tool Windows | Database), right-click a database object and navigate to Dump Data to File(s).

  2. In the Dump 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 TABLE or ALTER 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.

  3. 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 into a file

Export data from the editor

  • To invoke the Dump Data dialog for a table in the editor, click the Dump Data icon (the Dump Data icon).

Export data source settings

Export data source settings

  1. Click File | Export Settings.

  2. In the Export settings window, click Select None.

  3. In the Export settings window, select Database: drivers, Database: settings, and Database: global sources. You must have at least one global data source to have Database: global sources in the export list.

  4. In the Export settings to field, type a path to the folder where you want to store the settings.zip file.

Copy and share data sources

Copy a data source

You may copy a data source if you need to use the same data source in another project, but have different requirements to it, such as its accessibility level (project or global), the user, database connection and schema settings, and so on.

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties button (the Data Source Properties button).

  2. In the Data sources and drivers window, select the data source that you want to copy.

  3. Click the Duplicate icon, or press Ctrl+D.

    Copy a data source

Share data sources between projects

Data sources in IntelliJ IDEA are available at two levels: project and global. The project level means that you can access a data source only from the project where this data source was created initially. At the global level, a data source is available for other projects. By default, a data source is available at the project level.

To share a data source between different projects inside your IDE, you need to move the data source to the global level.

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties button (the Data Source Properties button).

  2. In the Data sources and drivers window, click the Make Global icon (the Make Global icon) to make the data source available at the global level.

    Share data sources between projects

Share data sources between different IDE instances

You can copy information about a data source to the clipboard in the XML format. The resulted XML does not include password information.

You can send the XML snippet to another IDE (in a messenger or by email) and import it.

  1. In the Database tool window (View | Tool Windows | Database), select data sources that you want to copy.

  2. Right-click the selection.

  3. Click Database tools and select Copy Data Sources to Clipboard.

  4. On another workstation, click View | Tool Windows | Database.

  5. In the Database tool window, click the New icon the New icon, select Import from Clipboard.

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.

Dump data with mysqldump or pg_dump

  1. In the Database tool window (View | Tool Windows | Database), right-click a database object and navigate to:

    • Dump with "mysqldump": for MySQL data sources.

    • Dump with "pg_dump": for PostgreSQL data sources.

  2. In the Dump 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.

  3. Click Run.

Last modified: 15 April 2020
Export and import dataImport