Query multiple databases at once

To query multiple databases at once is to run one question that reads more than one database. To join data across databases is to return those sources in one result. SQL (Structured Query Language) is what a single database engine usually executes. TugQL is DataTug's query language for sources, joins, filters and columns without binding the query to one engine. The command-line interface (CLI) is the datatug program in a terminal. PostgreSQL and MySQL are two relational databases that CLI opens, which is how you join PostgreSQL and MySQL when both are sources in one project.

Cross-source TugQL joins in the CLIAvailable

Cross-source TugQL joins in the browserIn development

The problem

What do you do when the rows you need are not in the same database?

A shop system and a warehouse system rarely share one engine. Exporting each side into a sheet drops types and the original spacing, and two SQL clients cannot see each other's rows. The supported databases catalogue lists PostgreSQL and MySQL as command-line sources for read-only SQL and TugQL, with schema scan implemented. A server read on those engines requires unrestricted access, which the CLI calls --no-policies. That catalogue does not verify browser or hosted connections to a customer database.

A clearer workflow

Open both sources in one project
Name each database, the environment that is safe to read, and the key the two sides share. Ways to use DataTug is the map of browser, terminal and agent surfaces.
Join in the CLI
Cross-source TugQL joins are available in the command-line interface. One TugQL query can name more than one source. The SQL CLI tool is that terminal database client. Join PostgreSQL and MySQL is the how-to for those two engines. The browser join has its own status pill on this page, and Work across sources is the investigation plan for when the answer is not one join yet.
Keep the query with the project
A saved query is a file you can review. How the demo works shows a project as files in Git, and the knowledge graph page describes the longer model being built around that project.

Worked example

See a cross-source join in the live demo

Starting question

Where can you watch one query read two sources?

Steps to follow

Open the live demo. It needs no account.
Read How the demo works. That TugQL query counts Chinook customers by country and joins them to population figures kept as an inGitDB database. The customer rows come from an OpenVaultDB (OVDB) server. Session storage is a different use of OVDB, described in OVDB (OpenVaultDB) session storage.
Treat that demo as the public cross-source example. It is not a recorded PostgreSQL-to-MySQL result. A separate recorded CLI run, described on DataTug.ai, joined Chinook invoices, a country-alias table and a population snapshot.

What you learn

The CLI cross-source join is available today. The browser join follows the second status on this page. This example does not invent a PostgreSQL-to-MySQL row count.

Open the live demo

What changes

You can join data across databases from the CLI without a spreadsheet in the middle. The compare hub says how that differs from a client attached to one connection. The DataGrip alternative is the named page for JetBrains DataGrip, and reconciling two exports is the same problem told from the weekly sheet.

The case of order 11045 · Act 2 · Follow

In the demo story

From the terminal, Sarah joins order 11045 in Northwind to its request in the Shipping database with one cross-source TugQL query.

The guided demo of this story is being built. The worked example above runs in today’s demo project.