DEV Community

Cover image for Local Development, Remote Data: Accessing Fly.io PostgreSQL from Your Java API
Jonathan Ato Markin
Jonathan Ato Markin

Posted on

Local Development, Remote Data: Accessing Fly.io PostgreSQL from Your Java API

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.

Server down

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

  1. Establishing a connection or communication channel with Fly.io from my local setup
  2. 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.

Postgres Elephant Logo

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]
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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'
);
Enter fullscreen mode Exit fullscreen mode

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'
);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

  1. FDW is powerful but requires careful setup to get things working
  2. flyctl proxies make it easy to create secure tunnels to interact with remote databases
  3. 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!🚀")
Enter fullscreen mode Exit fullscreen mode

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay