Import & Export Database

A database that cannot leave is a trap. EXPORT DB writes one out as a file in your namespace; IMPORT DB reads one back in. Both work on a whole database or a single table, in the formats every other tool already speaks.

Exporting

EXPORT DB takes the database name, the format, and where the file should land. It returns the path it wrote, so the file is immediately yours to share, download or move.

EXPORT DB "shopdb" AS "sql" INTO "/root/backups/shop.sql" SET ?path
AFTER EMIT ?path

The path is an ordinary namespace path, so everything you already do with files applies. Share the dump and you have a download link:

EXPORT DB "shopdb" AS "sql" INTO "/root/shop.sql" SET ?path
AFTER FILE SHARE ?path SET ?url
AFTER EMIT ?url

One table

Add TABLE and only that table is written. CSV and TSV are single-table formats and require it — a spreadsheet has no way to hold several tables at once.

EXPORT DB "shopdb" TABLE "people" AS "csv" INTO "/root/people.csv" SET ?path
AFTER EMIT ?path

The formats

Structure or data

By default an export carries both the schema and the rows. STRUCTURE ONLY writes the shape and no data — useful for standing up an empty copy elsewhere. DATA ONLY writes the rows into a schema that already exists.

EXPORT DB "shopdb" AS "sql" INTO "/root/schema.sql" STRUCTURE ONLY SET ?path
AFTER EMIT ?path

Importing

IMPORT DB reads a file already in your namespace and writes what it finds into the named database. It returns what it did, so nothing has to be assumed.

IMPORT DB "shopdb" FROM "/root/backups/shop.sql" SET ?result
AFTER EMIT ?result("tables") & " tables, " & ?result("rows") & " rows"

A CSV goes into one named table. The header row names the columns, and a column the table does not have is skipped rather than failing the whole import.

IMPORT DB "shopdb" TABLE "people" FROM "/root/people.csv" SET ?result
AFTER EMIT ?result("rows") & " rows added"

A workbook comes back the same way. Every sheet is a table, so a whole database exported to Excel imports as a whole database - name a table and only that one sheet is read.

IMPORT DB "shopdb" FROM "/root/shop.xlsx" SET ?result
AFTER EMIT ?result("tables") & " tables, " & ?result("rows") & " rows"

IMPORT DB "shopdb" TABLE "people" FROM "/root/people.xlsx" SET ?result
AFTER EMIT ?result

JSON carries the schema alongside the rows, so it is the format that can rebuild a table from nothing.

IMPORT DB "shopdb" FROM "/root/shop.json" SET ?result
AFTER EMIT ?result

Tab-separated files read exactly like comma-separated ones - the extension decides which.

EXPORT DB "shopdb" TABLE "people" AS "tsv" INTO "/root/people.tsv" SET ?out
AFTER IMPORT DB "shopdb" TABLE "people" FROM "/root/people.tsv" REPLACE SET ?in
AFTER EMIT ?in("rows") & " rows came back"

The formats, both directions

What happens to what is already there

An import adds to a table by default. REPLACE empties it first, so what lands is exactly what was in the file. CREATING makes the database or table if it is not there yet, rather than refusing.

IMPORT DB "shopdb" TABLE "people" FROM "/root/people.csv" REPLACE SET ?result
AFTER EMIT ?result

IMPORT DB "freshdb" FROM "/root/backups/shop.sql" CREATING SET ?result
AFTER EMIT ?result

A file with no header row

A CSV without a header is read positionally against the table's columns, in the order DESCRIBE TABLE reports them. The same modifier on an export leaves the header out.

IMPORT DB "shopdb" TABLE "people" FROM "/root/raw.csv" NO HEADERS SET ?in
AFTER EXPORT DB "shopdb" TABLE "people" AS "csv" INTO "/root/out.csv" NO HEADERS SET ?out
AFTER EMIT ?out

Headings and exact values

An import loses nothing. Each column's heading in the file is kept as the column's heading, word for word, and every value is stored exactly as the file holds it.

• Into an existing table, each header is matched to a column by name, then by heading. A header with no matching column stops the import before any row is written, naming the headers it could not place.
• With CREATING, in every format, a missing table is made from the file. For CSV, TSV and workbooks, each header becomes a heading and a name is formed from it: Date of Birth becomes date_of_birth, and a repeated header gets a numbered name so no column overwrites another. A column is typed NUMBER only when every value in it reads back exactly as written; otherwise it is STRING. From an SQL dump, each CREATE TABLE is read in full: the declared type is kept as the column's source_type, a COMMENT becomes its heading, and keys, defaults and timestamps become the matching modifiers.
• Values stay exact. 007, 1.50 and long identifiers are kept as written rather than turned into numbers, and spaces inside a cell are kept.
• Exports write headings back. A file imported and exported again in the same format comes back with the same headings and values; an SQL export reproduces the original types and comments.

IMPORT DB "shopdb" TABLE "staff" FROM "/root/staff.xlsx" CREATING SET ?result
AFTER EMIT ?result("columns")
(* {"date_of_birth": "Date of Birth", "employee_no": "Employee No.", ...} *)

ALL TEXT stores every value as text, exactly as written, whatever it looks like. HEADER ROWS n reads the first n rows together as the headings, for files whose headings span two rows.

IMPORT DB "shopdb" TABLE "ledger" FROM "/root/ledger.csv" CREATING ALL TEXT HEADER ROWS 2 SET ?result
AFTER EMIT ?result("rows")

What comes back

EXPORT DB returns the namespace path it wrote - a plain string. IMPORT DB returns an object:

Reference

What's next

Back to Databases & Tables for schemas, or Columns & Rows for working with data directly.