Migrate a database

Migrate to another engine… copies a whole schema, structure and data, into another database. Open it from the schema's … menu in the sidebar.

• From: any SQL connection.
• To: a writable PostgreSQL, MySQL, SQLite or SQL Server connection, or MongoDB (see SQL and MongoDB).

How it runs

1. Pick the Target connection. The sheet counts tables and foreign keys and lists warnings.
2. Preview DDL shows every statement that creates the structure.
3. Migrate creates the tables, copies the rows parent-first in batches of 1,000, then adds foreign keys and indexes.

Replace existing tables on the target drops tables with the same names first. Tables are created in the target's default schema.

What carries over

How much survives depends on the source. PostgreSQL sources are read in detail:

• types, mapped to the target's equivalents;
• NOT NULL, primary keys (composite too) and UNIQUE constraints;
• foreign keys, including composite ones (SQLite cannot add them after creating a table, so they are skipped there, with a warning);
• secondary indexes on plain columns;
• enums: a CREATE TYPE on PostgreSQL, an inline enum on MySQL, text with a CHECK on SQL Server and SQLite;
• arrays, as arrays on PostgreSQL and text elsewhere;
• auto-increment and identity columns, with sequences reset after the copy;
• portable defaults (literals, booleans, now(), gen_random_uuid()); others are skipped with a warning.

Generated columns become plain columns. Views, functions and triggers are listed under Not migrated so you can port them by hand.

From MySQL, SQLite, SQL Server, ClickHouse and Trino, Lily carries columns, types, nullability, the primary key and single-column foreign keys. Defaults, auto-increment, unique constraints, indexes and enums are not copied, and views are not listed.

Types Lily cannot map become text, with a warning.

If it fails

Lily drops the tables it created in this run, children first, and tells you how many it rolled back. Tables that Replace had dropped are not restored, and enum types it created stay.

The rows are copied in batches, not one big transaction, and there is no extra confirmation for Production targets. Avoid writing to the source while it runs, especially to tables without a primary key, which are copied in no particular order.