Introduction
Inter-communication between databases when developing applications is not an entirely new challenge to developers or the software engineering space. I recently found myself in a situation where I had to access data from an external PostgreSQL database while developing a Java API locally. The external database was hosted on Fly.io and my local development setup had to interact with it seamlessly. After some research and consultation, I discovered that I could harness the power of PostgreSQL Foreign Data Wrapper (FDW) and Fly Proxy to make it work. In this article, I’ll walk you through my journey, the steps I took, and how you can apply this solution to your projects.
The Challenge
I was working on a Java API(with SpringBoot) that needed to fetch data from a table that was being managed by another application's Postgres database. The external application and its database were all hosted on Fly.io. While developing locally, I could not directly connect to the Fly Postgres database due to network restrictions.
I had two main issues to contend with
- Establishing a connection or communication channel with Fly.io from my local setup
- Accessing the data from the external database on Fly.io
Finding solutions for the above issues led me to discover Fly Proxy and PostgreSQL Foreign Data Wrapper.
What Is PostgreSQL Foreign Data Wrapper?
PostgreSQL Foreign Data Wrapper (FDW) is a powerful module that allows you to query external data sources as if they were local. It acts as a bridge between the local database and the external one.
I used the postgres_fdw
extension to connect to the external Fly.io PostgreSQL database.
Setting Up The Proxy
You should install flyctl
before proceeding to carry out these procedures.
Since Fly.io restricts direct access to its databases, I needed a way to securely connect to it from my local machine. I used Fly.io’s flyctl
proxy to create a secure tunnel. The general command is given by:
fly proxy <local:remote> [remote_host] [flags]
In setting up my proxy I needed to know the name of the remote_host which was the Fly Postgres database application I wanted to interact with. I then set up the proxy with a similar command as below:
fly proxy 5433:5432 -a db_app_name
This created a local tunnel to the Fly.io database. With my proxy set up, I proceeded to my FDW to interact with the external database.
Setting Up PostgreSQL FDW
Step 1: Install the postgres_fdw Extension
First, I installed the postgres_fdw extension in my local PostgreSQL database:
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
Step 2: Create a Foreign Server Object
Next, I defined the connection to the external database by creating a foreign server object to represent the remote database I wanted to connect to.
CREATE SERVER fly_server FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
host '127.0.0.1',
port '5433',
dbname 'db_name'
);
fly_server
can be replaced with any preferred name.
Step 3: Map a Local User to the Remote User
I created a user mapping for each database user I wanted to allow to access each foreign server. In my case, I did for only one user.
CREATE USER MAPPING FOR local_user SERVER fly_server
OPTIONS (
user 'remote_user',
password 'remote_password'
);
Step 4: Link the External Table
Next, I had to create a foreign table for each remote table I wanted to access.
I used the IMPORT FOREIGN SCHEMA approach and limited it to the table I was interested in.
IMPORT FOREIGN SCHEMA foreign_schema_name
LIMIT TO (remote_table)
FROM SERVER fly_server
INTO local_schema_name;
In my case as well, there was an ENUM TYPE in the foreign database that the table depended on so I had to create that TYPE as well in my local database.
Step 5: Create Materialized View (Optional)
This is an optional step that I found to be useful, especially in situations where data is seldom updated and the only operations being performed are READ
operations.
Integrating with Your Java API
With the FDW and proxy in place, I could now query the external data directly from my SpringBoot application using either my materialized view or the imported foreign table directly by establishing a connection with my local database.
Lessons Learned
- FDW is powerful but requires careful setup to get things working
- flyctl proxies make it easy to create secure tunnels to interact with remote databases
- This approach also enabled me to treat my app as a separate service with its own db decoupled from the remote application even though it depended on some data from it.
Conclusion
Using PostgreSQL Foreign Data Wrappers and a flyctl proxy, I was able to seamlessly integrate external data into my Java web application. This approach not only solved my immediate problem but also opened up new possibilities for working with distributed data.
If you’re facing a similar challenge, I highly recommend giving FDW a try!
Feel free to connect with me on LinkedIn.
System.out.println("Happy Coding!🚀")
Top comments (0)