Heroku Data Links
Last updated July 15, 2022
Table of Contents
Heroku Data Links helps you connect a Heroku Postgres database to another Heroku Postgres database. Once connected, you can use SQL semantics to read and write data from them, regardless of where the data lives.
Heroku Data Links isn’t available on hobby tier databases.
Overview
Heroku Data Links uses a native feature of Postgres called Foreign Data Wrappers. A Foreign Data Wrapper maps the logical representation of foreign tables on a foreign server to tables in the local database.
When you write a query against data in a remote database, Postgres transparently queries the remote data. The details of where the data lives and how it’s abstracted in Postgres are pushed down into the Postgres extension that connects the two data stores.
Heroku Data Links takes care of the difficult parts of managing Foreign Data Wrappers, including creating the server specification within the local database and creating tables to query against in the remote system.
Terminology
The documentation on Heroku Data Links uses a strict set of terms to define all of the interactions.
- Remote - The data store that is being connected to a Heroku Postgres database.
- Local - The Heroku Postgres database that’s accepting the connection.
- Data Link - The name of connection between the remote and local databases.
Listing Data Links
List all the existing Data Links for an application with the heroku pg:links CLI command.
$ heroku pg:links -a example-app
=== postgresql-animate-91581
* datalink_test
created_at: 2021-10-11T14:46:22.721+00:00
remote: HEROKU_POSTGRESQL_BLUE_URL (postgresql-colorful-33594)
remote_name: postgresql_colorful_33594
=== postgresql-colorful-33594
No data sources are linked into this database.
=== postgresql-round-06985
This operation is not supported by hobby-dev databases
The command lists all the databases attached to an application and existing Data Links within each database. In the above example, postgresql-animate-91581 is the local database for the datalink_test Data Link.
The Remote: line references the config var and the resource name of the database that’s connected to the local database. Remote Name is the foreign server reference inside of the local database.
Linking Heroku Postgres to Heroku Postgres
Create a Data Link between two Heroku Postgres databases with the heroku pg:links:create [LOCAL] [REMOTE] --as [DATA_LINK_NAME] command.
$ heroku pg:links:create DATABASE_URL HEROKU_POSTGRESQL_BLUE --as datalink_test -a example-app
When Heroku Postgres instances are linked via Data Links, a new schema is added to Postgres based on the link name (in this example, the schema is named datalink_test).
$ heroku pg:psql postgresql-animate-91581 -a example-app
psql (13.2, server 13.5 (Ubuntu 13.5-2.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
example-app::DATABASE_URL=> \dn
List of schemas
Name | Owner
---------------+----------------
datalink_test | u12344l89vh890
public | u12344l89vh890
(2 rows)
If you have the people table in the remote data store HEROKU_POSTGRESQL_BLUE, you can query that table from the local database like this:
SELECT * FROM datalink_test.people;
Only the tables that exist at the time of the link creation are available. If you create tables after you created the link, they won’t appear in the local database. If you want to include newly created tables, you must remove the link and create it again.
When you delete the remote data store, or the remote date store moves to another host (for example, Postgres failover), the data link won’t be updated. Also, when you reset your local database with the command like heroku pg:reset, the Data Link and the internal Foreign Data Wrapper configuration are also reset. You must recreate the Data Link for these cases.
Linking Heroku Data for Redis to Heroku Postgres
Creation of Heroku Data Links from Heroku Data for Redis to Heroku Postgres has been deprecated as of January 30, 2018.
Naming a Data Link
A good practice when creating Data Links is to give them a meaningful name. Use the --as flag to specify a link name:
$ heroku pg:links:create DATABASE_URL HEROKU_POSTGRESQL_BLUE --as datalink_test -a example-app
Names must consist of lower case alphanumeric characters and underscores and be at most 63 characters.
The name of the schema where the remote tables for the Data Link are created is based on the Data Link name. Query the remote Postgres table using the same name in the --as flag:
SELECT id, name, description FROM datalink_test.remote_table_name;
Removing a Data Link
Remove a Data Link with the heroku pg:links:destroy [REMOTE_DATABASE] [DATA_LINK_TEST] CLI command.
$ heroku pg:links:destroy DATABASE_URL datalink_test -a example-app
! WARNING: Destructive Action
! This command will affect the database: DATABASE
! This will delete datalink_test along with the tables and views created within it.
! This may have adverse effects for software written against the datalink_test schema.
! To proceed, type "example-app" or re-run this command with --confirm example-app
> example-app
Deleting link datalink_test in DATABASE... done
If any views or code have been written against a Postgres table that comes from a Data Link, no checks are made before the Data Link is deleted.
Caveats and Considerations
- Data Link creation will fail if the tables in your remote database have columns with custom types that don’t exist in the local database. Create any required custom types in your local database in advance before creating the Data Link.
- Data Links is not supported on Shield Heroku Postgres plans, even if these exist in the same Shield Space, due to ingress and egress network traffic restrictions.
