This page describes Calíope 1.7, the version I am building right now. 1.6 is on the App Store for the Mac, iPad, iPhone, Apple Watch, Apple TV and Apple Vision Pro. The changelog says which version each feature landed in.
Adds an “Open in Calíope” button to every topic. It only works with the app installed.
Create or modify tables with a visual interface without writing DDL manually.
Where it is: Catalogs › right-click › New table / Design table
The Table Builder opens from Catalogs: click New table in the toolbar, or right-click an existing table and choose Design table. Catalogs opens in read-only mode, so turn that off first. Available on macOS, iPad, iPhone and Apple Vision Pro, and on all four it opens as a persistent Workspace tab: you can switch to another tab (for example Catalogs to check another table) and come back without losing your work. Each table opens in a tab of its own.
The editor has its tabs: Columns, Indexes, Foreign keys —where the engine has them— and Options. Below them, the SQL Preview shows the CREATE TABLE —or, for an existing table, the ALTER TABLE— that your changes produce, and follows every edit; Copy puts it on the clipboard. When you are done, click Create table (or Apply changes for an existing table) to run it on the server. Reset discards what you changed: back to the defaults for a new table, or to the server's definition for an existing one.
In the Columns tab, click + to add a column; the buttons beside it duplicate the selected column, move it up or down, and delete it. Click a column in the list to edit it underneath: the name, the data type —the list is the one of the server's engine—, the length when the type takes one (e.g. 500 for VARCHAR), and the constraints Allow NULL, Primary key (PRIMARY KEY) and Unique (UNIQUE), plus the ones only some types take. Only the ones the chosen type supports appear, and marking a column as primary key turns NULL off.
MySQLMariaDBAurora
On integer types, Auto increment (AUTO_INCREMENT) numbers the rows by itself and Unsigned (UNSIGNED) leaves out negative values.
SQL ServerPostgreSQL
On integer types, Auto increment (IDENTITY) numbers the rows by itself. There is no Unsigned.
The default value can be none, a literal value, NULL, CURRENT_TIMESTAMP or an expression. Each column can also carry a comment.
MySQLMariaDBAurora
Text columns can have their own character set and collation; left as they are, they inherit the table's.
SQL ServerPostgreSQL
The builder offers no character set or collation per column.
SQL Server
The comment is written as the MS_Description extended property.
PostgreSQL
The comment is written with COMMENT ON COLUMN.
A column that already existed keeps its original name, so renaming it becomes a rename in the ALTER TABLE instead of dropping the column and adding a new one.
SQL ServerPostgreSQL
A new column always goes at the end of the table: the engine has no AFTER. Moving a new column up or down changes nothing on the server, and the SQL Preview says so in a comment.
Oracle
On NUMBER, Identity (IDENTITY) numbers the rows by itself (GENERATED BY DEFAULT AS IDENTITY). There is no Unsigned. The length of VARCHAR2 and CHAR counts characters: 50 is VARCHAR2(50 CHAR); to count bytes, type 50 BYTE. NUMBER takes its precision and scale in the same field (10,2), and TIMESTAMP the precision of its fractional seconds (6). The builder offers no character set or collation per column, and the comment is written with COMMENT ON COLUMN. A new column always goes at the end of the table. On an existing table, the ALTER TABLE states only what you changed in each column; an identity can be removed but not added to a column that already exists, and the SQL Preview says so in a comment.
Keywords: column, data type, INT, VARCHAR, PRIMARY KEY, AUTO_INCREMENT, nullable
Manage the table's indexes: INDEX and UNIQUE, and FULLTEXT and SPATIAL where the engine has them.
Where it is: Table Builder › Indexes Tab
In the Indexes tab, click + to add an index. Give it a name and choose the type; the list offers only the types the server's engine has: INDEX (standard) and UNIQUE (no duplicates).
MySQLMariaDBAurora
Also FULLTEXT (full-text search on CHAR/VARCHAR/TEXT columns) and SPATIAL (geospatial data).
SQL ServerPostgreSQL
There is no FULLTEXT or SPATIAL in the builder.
Tick the columns it covers: the order in which you tick them is the order of the index, and the number beside each one shows it. An index can span several columns (composite index).
Right-click an index in the list (on iPad, touch and hold it) to edit or delete it. Editing one that already existed drops it and creates it again.
The primary key is defined per column in the Columns tab (Primary key (PRIMARY KEY)); it does not appear here as a separate index.
ClickHouse
With ClickHouse the only type is Skip index: you choose its type (minmax, set(100) or bloom_filter) and its granularity, and it skips blocks of data instead of looking up a row. There is no UNIQUE. It can also be added and removed on an existing table, with ALTER TABLE … ADD INDEX and DROP INDEX.
Oracle
The builder has no FULLTEXT or SPATIAL. A UNIQUE index is created as a UNIQUE constraint, and deleting it drops the constraint together with its index.
Apple Vision Pro: the context menu opens by looking at the row and pinching and holding.
Define referential integrity relationships between tables.
Where it is: Table Builder › Foreign Keys Tab
In the Foreign Keys tab, click + to add a FK. Give it a name, choose the local column, the database —the same one unless you pick another— and the referenced table, and type the referenced column.
Choose the ON DELETE and ON UPDATE actions: RESTRICT (error if integrity is violated), CASCADE (propagates the change), SET NULL (sets the FK to NULL) or NO ACTION.
MySQLMariaDBAurora
NO ACTION is equivalent to RESTRICT. Both tables must use the InnoDB storage engine.
SQL Server
There is no RESTRICT: the builder writes NO ACTION, which in this engine does the same. A foreign key cannot point at another database, and the SQL Preview says so in a comment.
Right-click a key in the list (on iPad, touch and hold it) to edit or delete it; editing one that already existed drops it and creates it again. The builder generates the CONSTRAINT with an explicit name, so it can be dropped later by that name.
ClickHouse
With ClickHouse there is no Foreign Keys tab: the engine doesn't have them.
Oracle
Oracle only has ON DELETE CASCADE and ON DELETE SET NULL: with RESTRICT or NO ACTION the builder writes no clause, which is what the engine does by default. ON UPDATE does not exist, and the tab does not offer it. A foreign key can point at a table in another schema if you hold the REFERENCES privilege on it.
Apple Vision Pro: the context menu opens by looking at the row and pinching and holding.
What belongs to the whole table: its comment and, where the engine has them, storage engine, row format, character set and collation.
Where it is: Table Builder › Options Tab
The Options tab holds what belongs to the table as a whole.
MySQLMariaDBAurora
The storage Engine (ENGINE), the Row format (ROW_FORMAT), the table's Character set and Collation —changing the character set selects the first collation of that set— and the Table comment. Text columns left as “(inherits table)” take the table's character set.
MySQLMariaDBAurora
The row format is chosen when the table is created: for an existing table the tab does not offer it, because the builder does not change it. The lists of character sets and collations come from the server; the storage engine list is fixed, with InnoDB first.
SQL ServerPostgreSQL
The engine has no storage engine, row format or character set per table, so the tab holds only the Table comment.
SQL Server
The comment is written as the MS_Description extended property.
PostgreSQL
The comment is written with COMMENT ON TABLE.
ClickHouse
With ClickHouse, when you create the table, the tab has the Keys section with ORDER BY, PARTITION BY, PRIMARY KEY, TTL and SETTINGS, depending on the chosen engine: the MergeTree family has them, and Memory or Log don't. With ORDER BY empty, the columns marked as primary key are used; with neither, Create table turns off and the footer says why. When you edit an existing table the section doesn't appear, because the engine and the keys can't change afterwards. A note at the end says what the engine doesn't have: foreign keys, auto-increment, unique indexes and character sets.
Oracle
The engine has no storage engine, row format or character set per table, so the tab holds only the Table comment, written with COMMENT ON TABLE.