Customers often need to integrate Play SQL Spreadsheets with their own business database. In the following document, we describe how to set up this situation:
- Users can create spreadsheets in their PostgreSQL database as usual,
- The tables of their other database are visible in read-only mode.
It leverages a feature of PostgreSQL named "foreign tables" and "foreign data wrappers".
Installing the Foreign Data Wrapper (FDW)
There are a dozen FDWs documented in the wiki. We'll set up the one for MySQL, which is provided by EnterpriseDB, the company who provides enterprise support for PostgreSQL. The following snippet is extracted from their installation guide.
Creating the connection
PostgreSQL uses "User Mappings" to provide access to those servers to some users. In the following snippet, we define a user mapping for PUBLIC. To make it read-only, the MySQL user must only have read privileges.
Documentation: CREATE USER MAPPING
Creating the table
There are two ways to create the table. The first way (mentioned by this blog post) is to make a hard copy of the data in PostgreSQL. It improves performance, but it requires triggering the command every time the synchronization must happen. The other way is to create a foreign table, which dynamically displays the remote one:
Documentation: CREATE FOREIGN TABLE
Every table of the foreign database must be imported. Foreign tables act like local tables, they belong to a schema and they are submitted to the privilege rules.
If the table is not visible for the Play SQL user, check the privileges.
- The official Foreign Data Wrapper of EnterpriseDB for MySQL
- Playing with Foreign Data Wrappers in PostgreSQL by Admire Nyakudya
- List of Foreign Data Wrappers in the Postgresql.org wiki