# Import & Export of Databases

> Export a table, a database or a whole instance of MongoDB, PostgreSQL, MySQL, MariaDB, SQL Server or Oracle from the API Maker admin panel with the database's own programs (mongodump, pg_dump, mysqldump, mariadb-dump, SqlPackage, Data Pump), download the file, and import it, or a file those programs wrote, into any instance of the same kind. The programs are installed from their publishers when needed, with the versions of each admin chosen on Sandbox Settings.

Source: https://docs.apimaker.dev/v4/docs/features/import-export.html

Copy a table, a database or a whole instance into one file, and that file back into any instance of the same kind : another server, another database name, or a table under a new name. The files are made and read by the database's **own programs** — mongodump, pg_dump, mysqldump, SqlPackage, Data Pump — so what goes out is what the database itself would write, and a file can be restored by hand without API Maker.

| | |
|---|---|
| Where | The **⋮** menu of the cards : **Instances** (a whole instance), **Databases** (a database), **Collections / Tables** (the tables you selected). Each opens the Import & Export dialog : **Export**, **Import** and **Jobs**. |
| Databases | MongoDB, PostgreSQL, MySQL, MariaDB, SQL Server, Oracle. |
| Programs | mongodump, mongorestore, mongoexport, mongoimport · pg_dump, pg_restore, pg_dumpall, psql · mysqldump, mysql · mariadb-dump, mariadb · SqlPackage · Oracle Data Pump, run by the database itself. |
| Files | A `.zip` with the files of the program, `manifest.json` (what it holds) and `README.txt` (how to restore it by hand). An import also takes the program's own files : an archive of mongodump, a dump of pg_dump, a script of SQL, a `.bacpac`, a file of Data Pump, JSON or CSV. |
| Jobs | Run in the background on a server, two at a time on each server ; the dialog follows them, with their log. The files stay 24 hours by default. |
| Tools | Downloaded from their publishers the first time a job needs them, into `__import_export_tools` on each server. Each admin chooses the versions on [Sandbox Settings](https://docs.apimaker.dev/v4/docs/features/import-export.html#the-tools-on-sandbox-settings). |

## Export

1. Open the **⋮** menu of the card and choose **Export Instance**, **Export Database** or **Export Tables** (**Collections** for MongoDB).
2. **What to export** : the whole instance, one database (a schema for Oracle), or some tables of it.
3. **Form of the file** : the forms of the database, below.
4. **Options** : what the program of the database offers, each with its help behind the **?**.
5. **Export**. The job runs on the server : its progress, its log, then **Download** and **Import it somewhere**.

The download is a link the server makes for this file, good for 15 minutes : the browser downloads the file itself, a file of gigabytes included.

| Database | Forms of the file |
|---|---|
| MongoDB | **Archive of mongodump** (documents, indexes, options, views) ; for collections also **JSON** or **CSV**, one file per collection (mongoexport) |
| PostgreSQL | **Dump of pg_dump** (custom format, restored in parallel) or **Script of SQL** ; a whole instance adds the roles (`pg_dumpall --globals-only`) |
| MySQL, MariaDB | **Script of mysqldump** / **mariadb-dump**, compressed, with views, routines, triggers and, for a whole instance, the users and their grants |
| SQL Server | **.bacpac of SqlPackage** (schema and data), or a **Script of T-SQL** written by the server for a database or some tables : the tables, their rows and their foreign keys, which goes into any database |
| Oracle | **File of Data Pump**, written by the database itself (`DBMS_DATAPUMP`) : a schema, some tables, or the whole database |

> **Tip : No program on the server for SQL Server on ARM**
>
> Microsoft publishes SqlPackage for x86-64 only. On an ARM server, export a database of SQL Server as a **Script of T-SQL**.

### Options of an export

| Database | Options |
|---|---|
| MongoDB | a filter of JSON for one collection, the fields of JSON or CSV, one array of JSON or a document per line, canonical extended JSON, the users and roles of the database, a point in time (oplog) for a whole replica set, views as collections, where to read from (a secondary keeps the load off the primary), collections at once |
| PostgreSQL | schema only, data only, without owners, without grants, without comments, INSERT statements (with column names) instead of COPY, `DROP … IF EXISTS` first, compression, only some schemas, without some tables, without the rows of some tables (logs, caches), the roles with or without their passwords |
| MySQL, MariaDB | schema only, data only, routines, triggers, events, `DROP TABLE` first, `INSERT IGNORE` or `REPLACE`, many rows or one row per INSERT, column names in each INSERT, a condition for the rows of one table, the users and their grants |
| SQL Server | check the schema before the data (SqlPackage), the time a statement may take ; for the script : schema only, data only, replace the tables, rows per INSERT |
| Oracle | objects and rows, rows only or objects only, a condition for the rows of one table, every table at one moment (`FLASHBACK_SCN`), statistics, the whole database (`FULL`), a dump an older Oracle reads, workers, the directory object |

## Import

1. Open the **⋮** menu and choose **Import into Instance**, **Import into Database** or **Import Tables**.
2. **The file** : drop a file of your computer — it is sent in pieces of 4 MB, a piece which fails on the way is sent again, up to 64 GB — or choose **An export of this account** : the file of an export which is done, read where it is, nothing downloaded or sent.
3. **What it holds** : the kind of the file, the instance and the version of the database it comes from, the program which made it, its size and SHA-256, its databases and their tables.
4. **Where it goes** : every database of the file under its own name, one database of the file into the database you name (made when it is not there), or some tables of the file. One table can take another name (MongoDB, MySQL, MariaDB, Oracle).
5. **Options**, then **Import**.

An option which removes or replaces what is there — replace the database, replace the collections, remove the objects first, empty or replace the tables, delete the documents of the file — asks you to type the name of the database before the import starts.

| Database | Options of an import |
|---|---|
| MongoDB | replace the collections (`--drop`), without the indexes, in the order of the file, stop at the first error, without the validators, the users and roles of the file, keep the UUIDs of the collections ; for JSON and CSV : insert, upsert, merge or delete by the keys you name, without empty fields of CSV ; writers per collection |
| PostgreSQL | create or replace the database, remove the objects first, without owners, without grants, schema only, data only, without triggers while the rows go in, all or nothing (one transaction), stop at the first error, connections in parallel, the roles of the file |
| MySQL, MariaDB | create or replace the database, go on past errors (`--force`), the character set of a new database, the users and their grants |
| SQL Server | create or replace the database, stop at the first error of a script, the time a statement may take |
| Oracle | tables there already (skip, append the rows, empty them first, replace them), objects and rows, rows only or objects only, without creating the user, the directory object |

> **What each database takes**
>
> - **PostgreSQL** : pg_restore reads a dump of its own version or older ; the job runs a pg_restore at least as new as the dump and the server. Some tables of a dump come with their keys, indexes, sequences, defaults, triggers and grants.
> - **SQL Server** : SqlPackage imports a `.bacpac` into a new or empty database only — choose **Replace the database** to import over one which holds tables. A script and a `.bacpac` go in whole.
> - **Oracle** : the dump goes through a directory object of the database (`DATA_PUMP_DIR` by default), which the user of the instance needs to read and write. A schema can go into another schema, a table under another name.
> - **MySQL and MariaDB** read each other's scripts.

## The file of an export

```text
shop-pg-shop-20261031-012233.zip
├── manifest.json     what the file holds : the kind of database, its version, the instance, the scope,
│                     the program, the databases and tables, the size and SHA-256 of each entry
├── README.txt        how to restore it by hand, with the commands of the program
└── shop.dump         the file of the program : .archive.gz, .dump, .sql.gz, .bacpac, .dmp, .json, .csv
```

The SHA-256 of each entry is checked when the file is imported. The name says the instance, the database and the time of the export, in UTC.

## Jobs

The **Jobs** tab lists the exports and imports of the account, newest first : what each covers, its state, the size of its file, when and where it ran, and how long it took. From there : **Download**, **Import this file into the instance**, the **log**, **Stop** a job which runs, **Remove** a job and its files.

- An export runs on the server which started it, an import of an export where its file is ; the files stay on that server, and the other servers fetch them from it when they serve a download.
- Two jobs run at a time on each server ; the others wait in the queue.
- The files of a job are removed from the server **24 hours** after it ended : change it on Sandbox Settings, from 1 to 720 hours.
- A job which was running when its server stopped is marked failed when the server starts again.

## The tools on Sandbox Settings

`Operations → Sandbox Settings → Import & Export Tools` : one row per tool.

| Tool | Publisher | Downloaded from |
|---|---|---|
| MongoDB Database Tools | MongoDB, Inc. | downloads.mongodb.org : macOS, Windows, and the build for the Linux of the server (Ubuntu, Debian, Red Hat and its kin, Amazon Linux, SUSE) |
| PostgreSQL client | The PostgreSQL Global Development Group | the PostgreSQL apt repository (Debian, Ubuntu), EnterpriseDB (macOS, Windows) ; on other Linux, the packages of the system |
| MySQL client | Oracle Corporation | cdn.mysql.com — 8.4 LTS is recommended : the clients of MySQL 9 and later no longer log in with `mysql_native_password` |
| MariaDB client | MariaDB Foundation | downloads.mariadb.org (Linux x86-64, Windows) |
| SqlPackage | Microsoft | the releases of SqlPackage (x86-64) |

- **Your versions** : the versions every server installs for your account, checked against the checksum the publisher gives. A chip says where the servers stand : ready, installing (with its share), or failed (with the error, and a button to try again). Up to 6 versions of each tool.
- **The jobs run** : **Automatic** takes the newest version on the server which suits the database (a pg_dump of its version or newer), else the program the PATH of the server finds, else the newest one of the publisher, installed then. Or choose one version.
- **This server** : the program its PATH finds, and the versions it has besides yours — installed by a job, or listed by another admin. Each admin has their own versions ; a version stays on a server while one admin lists it or one job runs on it.
- **Files of the jobs stay** : how many hours the files of an export or an import stay after the job ended.

Where a publisher has no build for a server — MariaDB on macOS and on Linux ARM, SqlPackage on Linux ARM, MySQL and MariaDB on Windows ARM — the row says so, and the jobs use the programs the PATH of the server finds.

The tools are in `__import_export_tools` next to the other folders of the installation, the files of the jobs in `__import_export_files`. A server without internet uses the programs on its PATH : install them with the package manager of the server.

> **Warning : The files hold your data**
>
> An export holds every row of what it covers. The download link opens without signing in for 15 minutes : share it only with who may read the data. The passwords of the instances never go on a command line — the programs read them from files only the server's user can read, removed when the job ends — and they are hidden in the logs.

## Related

- [Table designer](https://docs.apimaker.dev/v4/docs/schema/table-designer.html) : design tables, run a SQL script, drop a table.
- [Database migration scripts](https://docs.apimaker.dev/v4/docs/features/database-migration.html) : change the structure of the databases in code, once per environment.
- [Sandbox Settings](https://docs.apimaker.dev/v4/docs/settings/sandboxSettings.html) : the page of the tools.