Move data between engines

Moving day. I made a list. Obviously.

Move to another engine… copies rows from a SQL connection into another open connection, of any engine.

From where

• A query result: the button in the results bar. It moves what the grid shows, filters and sort included, within your row cap.
• A whole table: right-click the table in the sidebar.
• An answer from Ask: the query is run again and its result moved.

To where

Any other open, writable connection:

• SQL: PostgreSQL, MySQL, SQLite, SQL Server, ClickHouse, Trino. Choose Create new table or Append to existing.
• MongoDB: a new or existing collection in the database selected in that connection.
• DynamoDB: an existing table. Every row needs the table's key attributes.

Types

For a query result, Lily looks at every value to pick a type per column: boolean, integer, decimal, timestamp (ISO dates) or text. When copying a whole table to a SQL engine, it uses the source's declared types instead, so a column of digits stored as text stays text.

New tables get columns only: no primary key, no constraints, every column nullable. ClickHouse tables are created with MergeTree. For a full copy with keys and indexes, migrate the database.

Preview DDL shows the CREATE TABLE before moving a query result.

Big tables

A whole table going to a SQL engine streams in batches of 1,000 rows, so size does not matter. Each batch is committed on its own: if the move fails halfway, the target keeps a partial copy; drop it before trying again.

A whole table going to MongoDB or DynamoDB is read into memory, up to one million rows.

Transactions

A query result moved into PostgreSQL, MySQL, SQLite or SQL Server is inserted in one transaction. Streamed tables, ClickHouse, Trino, MongoDB and DynamoDB are written in batches without one.

Moving does not ask for an extra confirmation on Production targets. Check the target before clicking Move.