Last updated September 26, 2024

Heroku Data Links helps you connect a Heroku Postgres database to another Heroku Postgres database. After connecting, you can use SQL semantics to read and write data from them, regardless of where the data lives.

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. For the examples in this article, HEROKU_POSTGRESQL_BLUE_URL is the config var designating the URL of the remote database and postgres-colorful-33594 is the add-on name for the remote database.
• Local: The Heroku Postgres database that’s accepting the connection. This term corresponds to the foreign server in the Foreign Data Wrapper context. For the examples in this article, DATABASE_URL is the config var designating the URL of the local database and postgres-animate-91581 is the add-on name for the local database.
• Data Link: The name of the connection between the remote and local databases. For the examples in this article, DATA_LINK_NAME is a placeholder name and datalink_test is the name for the data link.

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 mini databases

The command lists all the databases attached to an application and existing Data Links within each database. In the previous 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 [REMOTE] [LOCAL] --as [DATA_LINK_NAME] command:

$ heroku pg:links:create HEROKU_POSTGRESQL_BLUE_URL DATABASE_URL --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).

Setting up a Data Link creates a new foreign schema in your local database, named as the Data Link. All the tables in the public schema of your remote database are created as foreign tables within this schema.

Use heroku pg:psql to inspect all the schemas of your database with the \dn command and find the new schema:

$ 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)

You can list all the foreign tables created by the Data Link by running \det [DATA_LINK_NAME].*:

example-app::DATABASE=> \det datalink_test.*
             List of foreign tables
   Schema      |    Table    |          Server
---------------+---------+------------------------------
 datalink_test |    people   | postgresql_colorful_33594
(1 row)

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;

Removing a Data Link

Remove a Data Link with the heroku pg:links:destroy [LOCAL] [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

Caveats and Considerations

• Data Link creation fails 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 isn’t supported on Shield Heroku Postgres plans, even if these exist in the same Shield Space, due to ingress and egress network traffic restrictions.