Copy tables, views, routines, triggers and events to another server, or to another database on the same one.
Where it is: Workspace › Tools › Data Migration
Open Data Migration from the workspace sidebar. It copies tables, views, routines, triggers and events from this connection to another server, or to another database on the same one.
Choose what to copy in the tree on the left. Its header has a filter by name: type part of a database or object name to narrow the list without expanding one database at a time. Objects are grouped by kind (Tables, Views, Routines, Triggers and Events); clicking an object ticks it, and each database checkbox selects or clears everything under it, with the count of selected objects out of the total beside it. Clear empties the selection. Calíope fixes the order — tables, views, routines, triggers and events — so that every object finds whatever it depends on already created.
Choose where it goes. Next to Destination:, open Select target server, pick one of the connections saved in the Connection Manager and click Connect. In Into database, type the database the objects will land in; left empty, each object keeps the database it has in the source. Calíope creates that database if it does not exist, and rewrites the references of a view so it reads from there.
Options: Replace at destination drops everything selected before creating anything —first what depends on the tables, and child tables before their parents—, so running the same migration again is safe; Export data copies the rows of every table; Skip FK checks turns off foreign key checks while each object loads, so tables can arrive in any order.
Click Migrate. The log gives one line per object, with the database it went to and, for a table, the rows copied. Stop halts between one object and the next.
The foreign keys of the copied tables are created at the end, once they are all there with their rows: the order in which they arrive no longer matters.
Between different engine families a notice next to Migrate says so before anything runs: one engine's DDL isn't understood by the other, so nothing is created. Only rows are copied, into tables with the same name that already exist in the target, matching columns by name; a table the target doesn't have, or an object that isn't a table, is skipped and the log says so.
SQL Server
An IDENTITY column keeps its values (Calíope turns IDENTITY_INSERT on while copying), but not its counter: in the target the next row continues from the highest value copied. Each object lands in its own schema, which is created if it doesn't exist. With only a database name in Into database, each object keeps its schema: demo.dbo goes to copia.dbo, and demo.rrhh to copia.rrhh.
PostgreSQL
Here a database is a schema of the connected database: Into database names the schema the objects go to, and it's created if it doesn't exist. The types the copied tables use —an ENUM, a domain— are created first; one that already exists in the target is kept as it is, because rebuilding it would mean dropping every column that uses it. A copied function's search_path points to the target schema, so its triggers write there and not in the source.
ClickHouse
Each table is created from the DDL the server keeps —engine, PARTITION BY, ORDER BY, TTL and SETTINGS included—, and there are no foreign keys and no Skip FK checks. Rows go in INSERT statements of a hundred at a time, and an Array, Map or Tuple column arrives with its type. A materialized view is created after the tables and starts empty: it only receives what is inserted from then on. If it writes into another table with TO, tick that table too to bring its rows.
If the destination is the same server as the source and an object would land in its own database, Migrate stays disabled and a warning says why: with Replace at destination, the copy would drop the original before reading its rows.
If the server you need does not appear in the selector, add it first in the Connection Manager and reopen Data Migration.
Oracle
With Oracle as the destination there is no Skip FK checks: Oracle does not turn foreign keys off per session. Between two Oracle schemas, a destination schema that does not exist is created as a user without a password (NO AUTHENTICATION) and with quota on its tablespace; tables arrive with Oracle's DDL, their rows in multi-row INSERTs committed as they run, dates as TIMESTAMP literals and foreign keys last. Oracle stores names in uppercase: across families, a table and its columns are also matched with those that differ only in case (CLIENTES with clientes).
With an environment tag: the target is protected by its profile’s tag. If it confirms writes, the migration asks once before starting, counting objects, and the preview doesn’t ask; if it is read-only, nothing is written to it. With a read-only tag on this tab’s connection, the tool isn’t offered.
Keywords: migrate, migration, copy, move, target server, target connection, selector
Review the SQL that would be executed before migrating.
Where it is: Data Migration › Preview
Tick Preview and the button becomes Generate SQL: instead of running anything, Calíope writes in the output panel the SQL the migration would execute, so you can review or copy it. It is the same script the migration runs: CREATE DATABASE IF NOT EXISTS, the switch to that database, foreign key checks off when Skip FK checks is on, with Replace at destination, the DROP of everything selected first —what depends on the tables, then child tables before their parents—, then each object, and the INSERT statements with Export data. Routines and triggers come between DELIMITER // and DELIMITER ;, so the script also runs as is in the SQL Editor with Run Script. In Preview mode no destination connection is needed.
The foreign keys go at the end, after all the tables and their rows.
SQL Server
In SQL Server the script creates the database and the schema if they're missing (IF DB_ID(…) IS NULL), has no DELIMITER —each routine goes in its own EXEC— and rows with an identity value go between SET IDENTITY_INSERT … ON and OFF.
PostgreSQL
In PostgreSQL the script creates the schema if it's missing (CREATE SCHEMA IF NOT EXISTS), turns off foreign key checks with SET session_replication_role, creates the types its tables use before them, and has no DELIMITER: a function's body goes between $function$ quotes.
ClickHouse
In ClickHouse the script doesn't switch database —each object carries its own in front—, doesn't turn off foreign keys, because there are none, and every DROP carries SYNC. An Array, Map or Tuple column goes into the INSERT as JSONExtract('…', 'type').
Oracle
In Oracle the script has no USE: each object carries its schema in front, and it starts with a PL/SQL block that creates the destination schema if it does not exist. It does not turn foreign keys off: it leaves them for the end.
Keywords: preview, SQL preview, review before executing
Compare two databases on the same server — tables, views, routines, triggers and events — and generate the migration script.
Where it is: Workspace › Tools › Schema Diff
The Schema Diff tool analyzes two databases on the same server and shows exactly what changed across tables, views, procedures, functions and triggers.
MySQLMariaDBAurora
On MySQL and MariaDB it also compares events.
SQL Server
On SQL Server each side is a schema of a database (demo.dbo), and the two may live in different databases of the same server.
PostgreSQL
On PostgreSQL each side is a schema of the connected database (demo, ventas).
Oracle
In Oracle each side is a schema —a user, such as DEMO or DEMO_R1— of the database you connect to, and it also compares packages, types and sequences.
Opening Schema Diff:
Click Schema Diff (opposing arrows icon) in the sidebar. It opens in a new tab.
Basic use:
1. Pick the Source and the Target on the top bar. The script changes the source until it matches the target: what only the target has is created in the source, and what only the source has is dropped.
2. Click Compare. Calíope reads each object's definition on both sides, as the server itself writes it.
3. Turn on the Changes only toggle to hide what matches.
4. The report is grouped by object type. Expand any row: a modified table shows, change by change, the definition it has and the one it will get; a modified view or routine shows both full definitions side by side; a new or dropped object shows its DDL.
5. Click Copy SQL for the whole script, or a row's copy button for that object alone.
6. Run it in the SQL Editor with Run Script. With Safe Mode on, it stops the script before it reaches the server. Compare again afterwards: the two sides should come out identical.
MySQLMariaDBAurora
The definitions are each object's SHOW CREATE …, and the editor understands the DELIMITER blocks that routines and triggers come in.
SQL Server
The definitions come from the catalog (sys.sql_modules for views, routines and triggers). Each view, routine and trigger goes in its own EXEC … sp_executesql, because T-SQL wants it alone in its batch, so the script runs without GO lines.
PostgreSQL
The definitions are the ones the server writes with pg_get_viewdef, pg_get_functiondef and pg_get_triggerdef.
Oracle
The definitions are the ones the server writes with DBMS_METADATA.GET_DDL, without storage or tablespace. Before comparing, factory values are removed: the ENABLE of each constraint and the default options of an identity or a sequence.
What it generates for tables:
MySQLMariaDBAurora
- A full CREATE TABLE IF NOT EXISTS for those missing from the source, with columns, indexes and options.
- DROP TABLE IF EXISTS for the ones left over.
- ALTER TABLE with ADD/DROP/MODIFY COLUMN, preserving type, nullability, default value, comment, character set and generated-column expression.
- Added and dropped primary keys, indexes and CHECK constraints, plus table option changes (ENGINE, DEFAULT CHARSET, COLLATE, ROW_FORMAT, COMMENT).
SQL Server
- IF OBJECT_ID(…) IS NULL CREATE TABLE for those missing from the source, with columns, keys and constraints, and a CREATE INDEX for each index.
- DROP TABLE IF EXISTS for the ones left over.
- ALTER TABLE with ADD, ALTER COLUMN and DROP COLUMN; before dropping a column, the script drops the default constraint that hangs from it.
- Added and dropped primary keys, indexes and CHECK constraints.
PostgreSQL
- CREATE TABLE IF NOT EXISTS for those missing from the source, with columns and constraints, and a CREATE INDEX for each index.
- DROP TABLE IF EXISTS for the ones left over.
- ALTER TABLE with ADD COLUMN, DROP COLUMN and ALTER COLUMN … TYPE, SET/DROP NOT NULL and SET/DROP DEFAULT.
- Added and dropped primary keys, indexes and CHECK constraints, and the table comment (COMMENT ON TABLE).
Oracle
- CREATE TABLE IF NOT EXISTS for those missing from the source, with columns, primary key, UNIQUE and CHECK constraints and their partitioning, and one CREATE INDEX per index. A spatial index is preceded by its metadata (USER_SDO_GEOM_METADATA), without which the server will not create it.
- DROP TABLE IF EXISTS … CASCADE CONSTRAINTS PURGE for the extra ones.
- ALTER TABLE with ADD, DROP COLUMN and MODIFY, which writes only what changes: the type, the default value or the nullability.
- Adding and dropping the primary key, indexes and constraints, and the comments of the table and its columns (COMMENT ON).
What it generates for everything else:
A view or a routine cannot be patched piecemeal: if its definition differs, the script rebuilds it from the target, rewriting the references to the source schema so it points at the right place.
MySQLMariaDBAurora
It rebuilds with DROP + CREATE.
SQL Server
It rebuilds with CREATE OR ALTER, which views, procedures, functions and triggers all accept.
PostgreSQL
It rebuilds with CREATE OR REPLACE; a trigger or a materialized view, which do not have it, are dropped and created again.
Oracle
It rebuilds it with the CREATE OR REPLACE its DDL already carries; a sequence or a materialized view, which have none, are dropped and created again. A trigger is created on the table of the schema being repaired.
Script order:
Tables first; then foreign keys in their own block, because when several tables are created alphabetical order does not respect dependencies; then the remaining objects by type, each type ordered by dependencies — a view built on another is emitted after it.
MySQLMariaDBAuroraSQL Server
Before the first object that is not a table, the script sets the active database with a USE.
PostgreSQL
Before the tables come the schema's own types, which their columns are declared with.
Oracle
Oracle has no USE: every statement is qualified with the schema being repaired. Before the tables come the object types (CREATE TYPE) their columns are declared with.
Ignored on purpose:
MySQLMariaDBAurora
The AUTO_INCREMENT counter, which advances with every insert and never matches between two copies.
SQL ServerPostgreSQLOracle
The current value of an identity column, which advances with every insert and never matches between two copies.
Oracle
Nor the current value of a sequence (START WITH).
What it does not cover:
Columns of an existing table are not reordered, and a renamed column reads as a drop plus an add. Views, routines and triggers are compared textually, so a mere formatting change counts as a difference.
MySQLMariaDBAurora
A partitioning change is detected and noted as a warning but never turned into SQL: repartitioning rewrites the whole table. A DEFINER change also counts as a difference, and on MariaDB a trigger's DDL is the literal text it was written with. An event with a different start time also differs, because that time is part of its definition.
PostgreSQL
Types of their own — ENUM, domains, composite types — are compared like a view, in their own Types group. The script creates the missing ones before the tables that use them and drops the ones only the source has at the very end. An ENUM that only gained labels at the end is updated with ALTER TYPE … ADD VALUE; any other change to a type stays in the script as a note to make by hand, because rebuilding a type in use means rewriting every column that uses it. The types an extension creates, such as PostGIS's, are left out: its CREATE EXTENSION brings them.
Oracle
An object type in use that differs and a partitioning change stay in the script as a note to do by hand, and so does adding an identity to an existing column, which the server does not allow. A constraint with a system name (SYS_C…) is matched by its definition.
Always review the script before running it in production.
Typical use case:
Before a deployment, compare production (source) against the development schema (target): the script brings production up to what development already has, without writing it by hand.
Available on macOS, iPad, iPhone and Apple Vision Pro.
ClickHouse
In ClickHouse it compares tables and views from their SHOW CREATE. Columns, skip indexes, projections, constraints, TTL, SETTINGS and the comment are synced with ALTER TABLE; a change of engine, ORDER BY, PARTITION BY, PRIMARY KEY or SAMPLE BY doesn't fit in an ALTER, so it comes out in the script as a comment saying the table has to be recreated. Materialized views and dictionaries aren't compared, and the engine has no procedures or triggers.
Keywords: schema diff, compare, schema, alter table, migration, differences, ADD COLUMN, DROP COLUMN, MODIFY COLUMN, SHOW CREATE TABLE, CREATE TABLE, indexes, foreign keys, partitioning, changes only