Local database, global access

This is a tutorial to offer a small yet valuable tip for aspiring software developers who want to showcase their hobby projects while staying within a budget.

If you’re looking to connect your software backend to a paid database system, which is typically more reliable and versatile, you might find that the cost is prohibitive. I encountered this issue myself and sought a way to connect my Spring Boot backend to a local database, while still being able to deploy the project and showcase it to potential employers or reviewers. After some research, I found a solution that may already be familiar to many, but I hope it will help those who, like me, are new to this and struggling with the same challenge.

If you’re using Docker and a PostgreSQL database on your local machine, and you’ve connected it to your Spring Boot backend and deployed your backend project to Render or a similar hosting service, the next step is simple. You just need to direct Render to your public IP address and open the necessary port to grant access to your database.

This guide will walk you through the steps to seamlessly connect your local PostgreSQL database to a deployed Spring Boot application on Render, ensuring your project is accessible and impressive.

Step By Step Guide To Connect Render to Your Local PostgreSQL Database

Step 1: Create a Docker Image for PostgreSQL

1. Pull the PostgreSQL Docker Image: Begin by pulling the latest PostgreSQL image from Docker Hub:
   
   docker pull postgres:latest
   
   
2. Run the PostgreSQL Container: Start a new PostgreSQL container with the necessary environment variables for your user, password, and database name:
   
   run -d --name your_docker_name
-e POSTGRES_USER=your_username
-e POSTGRES_PASSWORD=your_password
-e POSTGRES_DB=database_name
-p 5432:5432 postgres:latest
   
   
   
3. Verify the Database Container: Ensure your container is running smoothly:
   
   docker ps

Step 2: Connect to the PostgreSQL Database

Use the following command to connect to your PostgreSQL database:

psql -h localhost -p 5432 -U your_username -d database_name

You will be prompted to enter your password.

Step 3: Update Your Spring Boot Application to Use the New Database

1. Update application.properties: Configure your Spring Boot application to point to the new PostgreSQL database by updating the application.properties file
   
   spring.datasource.url=jdbc:postgresql://${DB_HOST:localhost}:${DB_PORT:5432}/${DB_NAME:database_name} spring.datasource.username=${DB_USER:your_username} spring.datasource.password=${DB_PASSWORD:your_password} spring.datasource.driver-class-name=org.postgresql.Driver spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect



2. Restart Your Spring Boot Application: Apply the new configuration by restarting your Spring Boot application:
   
   mvn spring-boot:run
   

Step 4: Push Your Project to GitHub

Push your backend project to GitHub to ensure your code is up-to-date and ready for deployment:

git push origin main

Step 5: Set Up Port Forwarding

To allow external access to your PostgreSQL database, follow these steps to configure port forwarding:

1. Access Your Router’s Web Interface:
   • Open a web browser and enter your router’s IP address (e.g., 192.168.0.1).
   • Log in with your router’s admin credentials.
2. Find the Port Forwarding Section:
   • Navigate to the port forwarding section, often found under Advanced Settings or Firewall Settings.
3. Create a New Port Forwarding Rule:
   • Service Port: 5432
   • Internal Port: 5432
   • Internal IP Address: Your local machine’s IP address (e.g., 192.168.0.108).
   • Protocol: TCP
4. Save and Apply the Changes:
   • Save the new rule and apply or restart the router if needed.
5. Verify Port Forwarding:
   • Use an online port checker (e.g., canyouseeme.org) to ensure that port 5432 is open and accessible.
6. Test the Connection:
   • Attempt to connect to your PostgreSQL database from a remote machine using your public IP address:
   
   psql -h your_public_ip -U your_username -d your_database_name
   
   
   

Step 6: Configure PostgreSQL for Remote Access

1. Update postgresql.conf: Allow PostgreSQL to listen on all IP addresses by modifying the listen_addresses parameter:
   
   
   listen_addresses = '*'
   
   
2. Update pg_hba.conf: Configure PostgreSQL to allow connections from any IP address with password authentication:
   
   
   host all all 0.0.0.0/0 md5
   
   
   
3. Restart PostgreSQL: Restart the PostgreSQL service to apply the changes.

Step 7: Configure Windows Firewall

1. Allow Incoming Traffic on Port 5432:
   
   • Open Windows Defender Firewall.
   • Navigate to Advanced Settings > Inbound Rules.
   • Create a rule to allow TCP traffic on port 5432.
     
2. Restrict Access to Specific IP Addresses:
   
   • In the Scope tab of your Inbound Rule, select “These IP addresses” and add each IP address provided by Render.
     
3. Save and Apply: Save the changes and ensure the rule is active.
   

Step 8: Set Render Environment Variables

Finally, configure Render’s environment variables to connect to your database:

• DB_HOST: Your public IP address
• DB_PORT: 5432
• DB_NAME: Your database name
• DB_USER: Your username
• DB_PASSWORD: Your password
  

Conclusion

With these steps, you can seamlessly connect your local PostgreSQL database to a deployed Spring Boot application, allowing you to showcase your work to potential employers or reviewers without the need for a costly database service. This approach is not only budget-friendly but also empowers you to take full control of your project’s deployment and accessibility. Good luck, and happy coding!