This page describes Calíope 1.5, the version I am building right now. 1.4 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.
Find text across every text column of a database and replace it in bulk, with a preview of the result.
Where it is: Workspace › Tools › Find
Global search scans the tables you select and tells you which columns contain a piece of text, how many times, and what it looks like.
How to use it
1. Pick the database in the header.
2. Select tables in the tree on the left.
3. Type the text and press Find.
4. Expand a table to see which columns contain it, with sample rows.
SQL Server
In SQL Server the list shows each database with its schema (demo.dbo), and the search covers that schema.
PostgreSQL
In PostgreSQL the list shows the schemas of the database you connected to, and the search covers one of them.
Every match can be opened in the SQL editor with its SELECT already written, in case you want to see it in context or refine it by hand.
One database per search. This is deliberate: searching a whole server multiplies the scan and turns the results into a list nobody can review. If you need several databases, repeat the search in each one.
It can be stopped. While running, the header shows progress by table and a Stop button; the search also appears in the Activity panel, so you can switch to another tab and come back without losing it.
Keywords: find, search, replace, text, global, all tables, bulk
Searching inside a column cannot use indexes: every row is read, and Calíope tells you how much before starting.
Searching for text inside a column cannot take advantage of any index. This is not a Calíope limitation: it is how relational databases work. An index can order by the start of a value, not by what sits in the middle of it.
That means every search reads every row of every selected table. This is why you see a notice with how many tables will be scanned and how much they take up before you start.
How to make it faster
- Select only the tables where searching makes sense, instead of all of them.
- Start with small tables to confirm the text is right, then widen.
- If the server sits behind an SSH tunnel, expect the latency to show.
The size shown comes from the server catalog, so it is an estimate: on busy tables it can fall short or run long.
Keywords: cost, slow, indexes, performance, size, scan, large tables
The search follows the server rules and ignores letter case; the replace does not. Calíope shows you the exact difference.
This is the most important trap in this tool, which is why Calíope shows it instead of hiding it.
Finding and replacing do not compare the same way. The search usually ignores letter case: searching for foo also finds Foo and FOO. A replace, however, always distinguishes: it would only change foo.
MySQLMariaDBAurora
What decides the search is the column collation, which in most databases ignores letter case; the REPLACE() that writes compares byte by byte.
SQL Server
The search follows the column collation, which usually ignores letter case. REPLACE() would follow it too, so Calíope makes it compare with the variant of that same collation that tells case and accents apart: what the warning counts is what that write would change.
PostgreSQL
The search uses ILIKE, which ignores letter case whatever the collation; the replace() that writes compares character by character.
If nobody points this out, the result is a replace that announces 300 rows and changes 100, with nothing failing and no error message. The data ends up half migrated.
What Calíope does
- It counts both figures separately in the same query and shows you the warning with each one.
- Next to each column total you see how many rows are genuinely replaceable.
- The preview only shows rows that will actually change.
What you can do
- Turn on Match case when searching: what you find is then exactly what will be replaced, and the warning disappears.
- Or make several passes, one per written form (foo, Foo, FOO).
Every text column is searched, but some cannot be written; Calíope says which and why.
Searching covers every text column: short and long text, fixed lists and JSON documents.
Replacing is not offered in four cases, and in each one the column is marked with a padlock and its reason:
Reason
Why
The server computes the column
Its value comes from other columns; writing it has no effect
Only accepts values from a fixed list
A replace can leave a value outside the list, and some servers turn it into empty text without warning
Part of the primary key
Changing it can break relationships with other tables
Has a unique index
The new value can collide with one that already exists
SQL Server
SQL Server has no columns limited to a fixed list, so here the cases are three. xml columns are replaced and must still be valid XML afterwards: the server rejects a write that breaks it.
PostgreSQL
In PostgreSQL the fixed lists are ENUM types. json and jsonb columns are replaced and must still be valid JSON afterwards: the server rejects a write that breaks it.
Tables without a primary key are not replaced, and this is stated in the confirmation window. Without a key there is no way to identify each row, and a write that matches too broadly would touch records nobody has seen.
Views are left out. Some of them accept writes, but working out which ones requires analysing their definition, and the risk is not worth it.
A replace always goes through a server-computed preview, and you can take the SQL to the editor instead of running it.
A global replace is a bulk write over data you have not seen one row at a time. That is why it cannot be launched without going through the review window.
What you see before anything is written
1. Which tables and columns will be touched, and how many rows in each.
2. The before → after preview, row by row.
3. Any warnings that apply: the gap between what was found and what is replaceable, the blocked columns, and the foreign keys that depend on what you are about to change.
The preview is computed by the server, using the same operation that will perform the write. It is not a Calíope simulation: what you see is literally what will be stored, including truncation by maximum length that you would otherwise discover far too late.
View script gives you the complete SQL to copy and run yourself, or to keep. That button writes nothing.
Two more brakes
- Above a thousand rows you must type the number to confirm.
- Each table is written inside its own transaction, and afterwards the rows changed are checked against the rows announced. If they do not match, you are told: when it finishes, a line under the search field gives the rows replaced and, if the count falls short, the tables where it did.
There is no undo. If you are about to touch data that matters, take a backup first from the Backup tool.