How do I query multiple databases and environments in the same SQL statement?

Joining rows from two databases, or looking at a Production table while the tab is on Staging, usually takes you out of the editor. In many SQL clients that means exporting CSV, running a side script, or setting up a DB Link on the server.

A DB Link needs extra setup and privileges on the server, and it often fails when the databases are different engines, such as PostgreSQL and MySQL.

Kerry Studio lets you query the other connection’s table in the same statement. You define a SQL alias on the project and reference the remote table as @project.table, for example @billing.orders.

When the query runs, Kerry fetches the rows through the matching saved connection and places them in a temporary table on the tab’s connection. The JOIN runs there, without a DB Link and without installing anything on the server. The same syntax works between PostgreSQL and MySQL.

You join the data in the editor, without leaving the query.

How to define the alias

The alias lives on the project, in the SQL alias (@alias) field. Without that name, the editor has nothing to resolve in the token.

You need an open workspace, a project with the field filled in, and a SQL tab.

1. In the New project or Edit project dialog, enter the alias, for example billing. Leave the @ out of the field. It belongs only in the SQL.
2. Connect the tab to the project and environment where the query should run.
3. In the editor, write @project.table in place of the remote table, for example @billing.public.orders.
4. Run it. Kerry fetches the rows and builds the temporary table on the tab’s connection.

The palette (⌘K / CtrlK) → New Connection opens the same project dialog.

How to write the alias in SQL

There are two ways to write the alias.

@project.table, for example @billing.orders, uses the environment the tab is on. With a schema, the path is the engine’s path: @billing.public.orders.

@project:environment, for example @billing:production, uses that environment even if the tab is on another one. The : sits only between the alias and the environment. The rest still uses .: @billing:production.public.orders.

Schema, table, and column use ., never :. Right: @billing.public.orders, @billing:production.public.orders. Wrong: @billing:production:public.orders.

@name alone, with no .table and no :environment, is a MySQL user variable (SET @s). It is not an alias.

You define @… on the project. The alias must be unique among saved projects. After @project: the editor lists that project’s environments. After @project:environment. it lists schemas and tables on that connection.

The alias does not change the project selector on the tab. It only chooses where those rows come from. If the name in the SQL is not a saved project, the editor flags the token and the run fails with the name the query asked for.

Two projects in the same query

A common case is a tab on an orders database and customers in another saved project.

-- Tab on the shop project, Staging environment.
-- @shop.public.orders follows the tab.
-- @crm.public.customers fetches the other project, on the tab’s environment.

SELECT
  o.id,
  o.status,
  c.email
FROM @shop.public.orders o
JOIN @crm.public.customers c
  ON c.id = o.customer_id
WHERE o.created_at > current_date - 7;

If crm is MySQL and the tab is on PostgreSQL, the pattern is the same. Kerry fetches the rows and the JOIN still runs on the tab’s connection.

Staging and Production in the same query

When the tab is on Staging and you want the same table in Production, lock the environment in the token.

-- Tab on the shop project, Staging environment.

SELECT
  h.id,
  h.status AS status_staging,
  p.status AS status_production
FROM @shop.public.orders h
JOIN @shop:production.public.orders p
  ON p.id = h.id
WHERE h.status <> p.status;

If you only change the tab’s environment, the statement in the editor stays the same. What changes is the token without :environment, because it follows the tab. The token with :production still points at Production.