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 export data in TXT, CSV, JSON, XML, Markdown, Excel, and other formats. You can select a predefined data extractor or create your own.
In GoLand, you export object structures 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 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.
Export object structures
Data definition language (DDL) defines the structure of a database, including rows, columns, tables, indexes, and other elements. In GoLand, 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…CtrlAlt0G.
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.
note
The DEFINER clause specifies the security context (access privileges) for the routine execution. In MySQL and MariaDB, select Skip DEFINER clause to skip this clause when you generate DDL for a procedure or a function.
Change output settings of the SQL Generator
In the Database tool window ( View | Tool Windows | Database) , right-click a database object (for example, a table) and select SQL Scripts | SQL GeneratorCtrlAlt0G.
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 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.
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
GoLand 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.
You can use a built-in data extractor, configure a custom extractor that is 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.
For more information about data extractors, refer to the corresponding page.
tip
If the table data that you need to extract contains commas within the cells, GoLand will honor the contents of such cells.
Export data from the Database tool window
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.
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.
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 the Export Data icon () on the toolbar. Configure the export settings and click Export to File.
To export full data to the clipboard, open a table or a result set and click 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 CopyCtrl0C action only copies the selection of rows on the current page. To copy all the rows on the current page, click a cell, press Ctrl0A and then Ctrl0C. To configure a number of rows on a page, see 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 Ctrl0C.
To copy the whole result or the whole table to the clipboard, do one of the following:
Click a cell, press Ctrl0A and then Ctrl0C.
Click the Select All button.
On the data editor toolbar:
Click the Export Data icon ().
Select the export format from the Extractor list and configure the export settings.
Click Copy to Clipboard.
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.
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 GoLand. You can read about them at dev.mysql.com and postgresql.org.
Export data with mysqldump or pg_dump
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.
note
To use mysqldump and pg_dump in GoLand, you must install GoLand on the same workstation.
In the Export with <dump_tool> dialog, specify the path to the dump tool executable in the Path to <dump_tool> field.
note
Specify the full explicit path to the dump tool executable.
GoLand supports using WSL Path to <dump_tool> 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.