With Play SQL, you can display data from external systems. It’s an exciting feature of PostgreSQL named Foreign Tables. It applies when organizations have a central database where they want to import data from all their systems: CRM, accounting, production instances…

This article may be useful both for users of Play SQL Base and Play SQL Spreadsheets.

Importing data

Postgres has a few Foreign Data Wrappers for NoSQL, CSV, LDAP and of course MySQL. After setting up the synchronization, Postgres will display data in the form of tables. They will look like local tables in your schema, but their content is actually downloaded from whatever remote datasource you’ve chosen.

There is a guide in the documentation about setting up foreign tables in Postgres.

Augmenting external data with comments and columns

Of course the foreign data is read-only, so you’re not at risk of modifying it. The next step is to join this data with a local table, so your users can add comments.

One of the use-cases is to import a catalog of mobile phones, and augment this catalog with local information about prices, margins and ROI.

In Play SQL 2.10, we’ve introduced Joint Tables. It makes it easy to select a read-only table to join with a read-write one. Here’s how it looks once it is set up:

The setup itself is quite easy. On the first page there are two tables to select:

On the second page, there are a few options to select the primary key and foreign key between the read-only and read-write tables.

The result is as expected:

We’re expecting a lot from this capability. Foreign tables in Postgres are pretty powerful, and augmenting external sources within your intranet opens up a world of possibilities.