PhpStorm 2025.1 Help

Export

Enable the Database Tools and SQL plugin

This functionality relies on the Database Tools and SQL plugin, which is bundled and enabled in PhpStorm by default. If the relevant features are not available, make sure that you did not disable the plugin.

  1. Press Ctrl+Alt+S to open settings and then select Plugins.

  2. Open the Installed tab, find the Database Tools and SQL plugin, and select the checkbox next to the plugin name.

You can use a variety of methods to export data and object structure from your databases. These methods include usage of various generators, data extractors, and shortcuts. Also, you can export data in TXT, CSV, JSON, XML, Markdown, Excel, and other formats. You can select a predefined data extractor or create your own.

In PhpStorm, you export object structure and data separately. It means that you can export a structure of a table or a view and then export data from these objects.

The full data dump is available for PostgreSQL and MySQL with the help of mysqldump and pg_dump. The full data dump includes the structure of all database objects and data of these objects in a single file. For more information, refer to Create a full data dump for MySQL and PostgreSQL.

Export object structure

Data definition language (DDL) defines the structure of a database, including rows, columns, tables, indexes, and other elements. In PhpStorm, you can generate data definition structure 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 (for example, a table) and select SQL Scripts | SQL Generator Ctrl+Alt+G.

  2. In the SQL Generator tool window, click the File Output Options icon (The File Output Options icon).

  3. From the Layout list, select a method that you want to use:

    • File per object by schema: generates a set of SQL files sorted in folders by schemas.

    • File per object by schema and database: generates a set of SQL files sorted in folders by schemas and databases.

    • File per object: generates a set of SQL files.

    • File per object with order: generates a numbered set of SQL files.

    • File per object by schema and type: generates a set of SQL files sorted in folders by schemas and types.

    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 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.

Export data

PhpStorm uses data extractors to export data in various formats to a file or the clipboard. Each time you export or copy data, the copied data format is defined by the selected data extractor.

Data extractor list on the data editor toolbar

You can use a built-in data extractor, configure a custom extractor based on CSV or DSV format, and create a custom data extractor using a provided API.

The last selected extractor becomes the default one for newly opened editor tabs. For MongoDB, the default extractor is always JSON.

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 Import/Export | Export Data to File.

    To export data from multiple database objects to files, select and right-click the objects, then navigate to Import/Export | Export Data to Files.

  2. 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 column header: adds a row with column names at the beginning of the CSV list.

    • Add row header: adds a column with enumeration of rows.

    • Output file: select a file that will store the data.

  3. To copy the generated script to the clipboard, click Copy to Clipboard. To save the script to a file, click Export to File.

Export data from the data editor and query results tab

You can open tables, views, and virtual views data in the data editor. Query result sets appear in the result tabs of Services tool window and in the In-Editor Results of query console.

  • To export full data to a file, open a table or a result set and click Export Data the Export Data icon on the toolbar. Configure export settings and click Export to File.

    Export data from the editor
  • To export full data to the clipboard, open a table or a result set and click Export Data the Export Data icon on the toolbar. Configure the export settings and click Export Table to Clipboard.

    Alternatively, right-click a cell and select Export Table to Clipboard. The data will be exported using currently selected data extractor.

    In contrast to the Export Table to Clipboard action, the Copy Ctrl+C action only copies the selection of rows on the current page. To copy all the rows on the current page, click a cell, press Ctrl+A and then Ctrl+C. To configure a number of rows on a page, refer to Set a number of rows in the result set.

Export to the clipboard

  • To copy the selection of rows from the result set or the data editor, press Ctrl+C.

  • To copy the whole result or the whole table to the clipboard, do one of the following:

    1. Click a cell, press Ctrl+A and then Ctrl+C.

    2. Click the Select All button.

      The Select All button in the data editor
    3. On the data editor toolbar:

      1. Click the Export Data icon (The Export Data icon).

      2. Select the export format from the Extractor list and configure the export settings.

      3. Click Copy to Clipboard.

    4. To export the whole result or the whole table to the clipboard, open a table or a result set, right-click a cell, and select Export Table to Clipboard.

    To configure a number of rows on a page, refer to Set a number of rows in the result set.

Export data from a MongoDB collection

  1. Right-click the collection that you want to export and select Export Data to File.

  2. In the Export Data dialog, click the Extractor list and select JSON.

    The output of this operation is MongoDB Extended JSON. Read about MongoDB Extended JSON in MongoDB Extended JSON (v2) at docs.mongodb.com.

    MongoDB Extended JSON

Export data from a Microsoft SQL Server table

You can export a Microsoft SQL Server table data by using the bcp utility. The tool is not integrated into PhpStorm. You can read about it at learn.microsoft.com.

  1. In the Database tool window (View | Tool Windows | Database) , right-click the table that you want to export your data from and select Import/Export | Export with 'bcp'.

  2. In the Export with bcp (<data_source_name>) dialog, specify the path to the bcp executable in the Path to bcp field.

  3. In the Target file, specify the path to the file that you want to export the data to.

  4. Click Run.

Export data from a Microsoft SQL Server table using bcp

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. mysqldump and pg_dump are native MySQL and PostgreSQL tools. They are not integrated into PhpStorm. You can read about them at dev.mysql.com and postgresql.org.

Export data with mysqldump or pg_dump

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

    • Export with 'mysqldump': for MySQL data sources. The mysqldump tool is located in the root/bin directory of the MySQL installation directory.

    • Export with 'pg_dump': for PostgreSQL data sources. pg_dump, pg_dump_all, pg_restore tools are all located in the bin folder of the PostgreSQL.

  2. In the Export with <dump_tool> dialog, specify the path to the dump tool executable in the Path to executable field.

    PhpStorm supports using WSL Path to executable paths with these tools. For example, //wsl$/Ubuntu-22.04/usr/bin/mysqldump.

    (Optional) Edit the command-line options in the lower part of the dialog.

  3. Click Run.

    Create a full data dump with pg_dump

'Export with' dialogs reference

the Export with mysqldump dialog

Item

Description

Where to run

Sets where to run the tool. You can either run it locally or in a Docker container.

  1. Locally

    • Path to executable: Defines the path to the mysqldump executable on your machine.

    • Output result to: Defines the path to the output result on your local machine.

  2. In a Docker container

    • Server: Sets the server used to run the container.

    • Container: Sets the container to run the mysqldump executable in.

    • Path to executable: Defines the path to the mysqldump executable inside the container.

    • Output result to (in container): Defines the path to the output result inside the container.

Options

Databases to dump

Name of the database to connect to.

Tables to dump

Database tables to dump from the specified databases.

Add DROP TABLE before CREATE TABLE

--add-drop-table

mysqldump documentation

Add DISABLE KEYS before each INSERT

--disable-keys, -K

mysqldump documentation

Add LOCK TABLES before each table dump

--lock-tables, -l

mysqldump documentation

Add DROP TRIGGER before CREATE TRIGGER

--add-drop-trigger

mysqldump documentation

Export schema without data

--no-data, -d

mysqldump documentation

Export schema without tablespaces

--no-tablespaces, -y

mysqldump documentation

Export without table creation

--no-create-info, -t

mysqldump documentation

Include column names in each INSERT

--complete-insert, -c

mysqldump documentation

Include all table options in CREATE TABLE

--create-options

mysqldump documentation

Include stored routines in the dump

--routines, -R

mysqldump documentation

Lock all tables for the duration of export

--add-locks

mysqldump documentation

Use INSERT DELAYED (up to MySQL 5.5)

--delayed-insert

Use single INSERT for multiple rows

--extended-insert

mysqldump documentation

For more information about export options, refer to the mysqldump documentation.

the Export with pg_dump dialog

Item

Description

Where to run

Sets where to run the tool. You can either run it locally or in a Docker container.

  1. Locally

    • Path to executable: Defines the path to the pg_dump executable on your machine.

    • Output result to: Defines the path to the output result on your local machine.

  2. In a Docker container

    • Server: Sets the server used to run the container.

    • Container: Sets the container to run the pg_dump executable in.

    • Path to executable: Defines the path to the pg_dump executable inside the container.

    • Output result to (in container): Defines the path to the output result inside the container.

Options

Statements

  • Copy: Dumps data as COPY statements.

  • Insert: --inserts. Dumps data as INSERT statements. Makes restoration slow.

  • Insert with columns: --column-inserts. Dumps data as INSERT statements with explicit column names. Makes restoration significantly slower.

Database

--dbname

Name of the database to connect to.

Schemas

--schema

Dump objects that are in the specified schema.

Tables to dump

--table

Database tables to dump from the specified schemas.

Format

--format

Format of the output:

  • File: --format=p. SQL script file.

  • Directory: --format=d. Directory-format archive suitable for input into pg_restore.

  • Custom-format archive: --format=c. Custom-format archive suitable for input into pg_restore.

  • Tar archive: --format=t. Tar-format archive suitable for input into pg_restore.

Path to dump

Defines the path to the dump file on your machine.

Clean database

--clean, -c

Drops all the dumped database objects before creating them.

Add "IF EXISTS"

--if-exists

Uses DROP ... IF EXISTS to drop objects when Clean database is enabled.

Create database

--create, -C

Creates a new database first and then reconnects to it. If Clean database is enabled, drops and recreates the target database before reconnecting to it.

Data only

--data-only, -a

Dumps only the data, not the schema.

For more information about export options, refer to the pg_dump documentation.

Reference

Export Data dialog

The settings in Export Data dialog depend on the selected data extractor.

the Export Data dialog

Item

Description

Source

Displays the source of extracted data.

Extractor

Sets the data extractor that will be used for extracting the data.

Add column

  • Computed: adds the code for columns with computed values to the output .sql file.

  • Generated: adds the code for columns with auto-generated values to the output .sql file.

Transpose

Changes the exported data view. In this view, the rows and columns are interchanged.

Add table definition (DDL)

Adds the DDL CREATE script of the table to the output .sql file.

Add column header

Adds a row with column names at the beginning of the CSV list.

Add row header

Adds a column with enumeration of rows.

Output query to a separate sheet

Creates a separate Query sheet in the output Excel (xlsx) file. The sheet contains the query used to generate the extracted data.

Output file

Path to the output file with extracted data.

Last modified: 03 April 2025