Firebase PostgreSQL Integration: 2 Easy Methods

on BI Tool, Data Integration, Tutorials • April 22nd, 2021 • Write for Hevo

Firebase PostgreSQL Integration

Many companies are facing complexities when trying to connect data between Firebase and PostgreSQL. These companies have various reasons for connecting these systems. For example, when a company acquires another firm or plans to upgrade its technical infrastructure, data transfer occurs. The company might need to move data from PostgreSQL to Firebase. Or, it might require shifting the acquired firm’s data to its current storage for offline processing.

This process involves selecting, extracting, prepping, and then converting the data so that it is compatible with the target data store. It may also involve syncing the data between both systems either in real-time or at specified intervals. Data verification is also conducted to confirm the authenticity of the data.

In this post, you will learn why Firebase and PostgreSQL are important, give you a step-by-step breakdown of how you can share data between these systems using native tools, and provide an easier alternative for connecting both systems and implementing your Firebase PostgreSQL Integration.

Table of Contents

Introduction to Firebase

Firebase Logo - Firebase PostgreSQL

Firebase is a Backend-as-a-Service (Baas) platform that eliminates a lot of the DevOps work that is involved when hosting applications such as managing backend databases along with the corresponding hardware. Firebase provides APIs that cover the full spectrum of backend technology. 

Official documentation regarding Firebase can be found here.

Understanding the Importance of Firebase

  • Database: Two choices to choose from between Realtime DB or Firestore. The real-time database stores and synchronizes data in real-time and it also avails your app data even when you’re offline.
  • Hosting: Firebase Hosting allows developers to host static websites with an automated SSL certificate for each site deployed.
  • Storage: Cloud Storage for Firebase offers object storage in the cloud (similar to AWS S3).
  • Functions: Cloud Functions for Firebase allow you to run serverless functions in the cloud (similar to AWS Lambda).
  • Authentication: Really makes auth easy by providing backend services, easy-to-use SDKs, and ready-made UI libraries to authenticate users to your app.

And there are many more products within the Firebase ecosystem that enable developers to quickly develop high-quality apps, grow their user base, and earn profit.

Firebase is compatible with JavaScript, iOS and Android. 

Despite its advanced technology, users have to build their indexes manually, making it a bit more difficult to query larger datasets — something that you can easily achieve using PostgreSQL.

Introduction to PostgreSQL

PostgreSQL Logo - Firebase PostgreSQL

PostgreSQL, also known as Postgres, is the world’s most popular open-source database. It’s an object-relational database that applies the SQL language combined with many features that allow you to set up, maintain, manage, and administer your relational databases. You can use it to safely store and scale the most complicated data workloads.

Official documentation regarding PostgreSQL can be found here.

Understanding the Importance of PostgreSQL

PostgreSQL is highly extensible and it has many advanced features that outperform most relational databases such as upsert, geospatial support, high availability, streaming replication, bidirectional replication, logical replication, horizontal partitioning, full-text search, key-value-storage (JSON / JSONB column type), and a foreign-data-wrapper extension that lets you query external sources directly from within PostgreSQL.

Thanks to the broad support of many field types, it can function in situations where you might choose a document-based (NoSQL) database. It has superior indexing capabilities that can benefit high-performance applications with low thresholds for latency. PostgreSQL support for clustering makes it well-suited for data warehousing environments. Many companies have been built around PostgreSQL like AWS Redshift, CitusDB, EnterpriseDB, Timescale, Red Hat, Skype, PostGIS, and others.

2 Methods to Set Up Firebase PostgreSQL Integration

You can use any of the following methods to implement your Firebase PostgreSQL Integration according to your need:

Method 1: Firebase PostgreSQL Integration using JSON Files

Using this method, the user will be manual required to extract JSON files from Firebase and add it to the PostgreSQL Server at the local machine.

Method 2: Firebase PostgreSQL Integration using Hevo

Hevo is a No-code Data Pipeline. It will automatically load your Firebase data into PostgreSQL Server for free without writing any line of code. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code.

Get Started with Hevo for Free

Pre-Requisites for Implementation of Firebase PostgreSQL Integration

  • Set up of a Firebase project.
  • PostgreSQL Server set up on your local machine.
  • PSQL client installation on your PostgreSQL server.

Procedure to Implement Firebase PostgreSQL Integration

Download the Ultimate Guide on Database Replication
Download the Ultimate Guide on Database Replication
Download the Ultimate Guide on Database Replication
Learn the 3 ways to replicate databases & which one you should prefer.

Method 1: Firebase PostgreSQL Integration using JSON Files

Connecting Firebase to PostgreSQL is not quite straightforward because Firebase is completely different from PostgreSQL. PostgreSQL is a table database, where data is stored in rows and columns while on other hand, Firebase is a JSON database where the database structure is not tabular, rather it has a tree structure.

You are going to use the managed import and export service to move data from and into Firebase. You will also use the PSQL command-line client to do the same in PostgreSQL.

Exporting Firebase Data to PostgreSQL Database

You can export your data from Firebase any time you like in the Firebase console. You are going to use the managed export service in the Firebase database console to export the data which you will later import in PostgreSQL. You can choose to export all documents but for demo purposes, we will look at how to export one collection at a time.

To export data as JSON:

Step 1: Select the node you wish to export. 

Step 2: Click the overflow menu in the upper right and select Export JSON. This option lets you export your Firebase database data in JSON format.

Step 3: Your browser will download a .json file with your Firebase data.

Step 4: Create a table in PostgreSQL with the same schema as the .json file. For example, when exporting user records, you can create a table using the following statement:

create table users 
(
  id     integer primary key,
  name   text not null,
  city   text
);

Step 5: Reference the file in a PostgreSQL table as follows:

create unlogged table users_import (doc json);

Step 6: Next, upload the file into a single row of that table using the copy command in PSQL.

copy users_import from 'users.json'

Step 7: Finally use the statement below to populate the table:

insert into users (id, name, comment)
select p.*
from users_import l
  cross join lateral json_populate_recordset(null::users, doc) as p
on conflict (id) do update 
  set name = excluded.name, 
      comment = excluded.comment;

Exporting PostgreSQL Database Data to Firebase

You can also save data from the PostgreSQL database into the Firebase real-time database. To demonstrate this, you are going to export data from the users the table you created in the previous section to JSON format and import it into Firebase using the managed import service.

To do this,

Step 1: The first step is to dump data from the source PostgreSQL database and store it in JSON file format by running the following commands in your terminal:

$ t
$ a
$ o users.json
$ select array_to_json(array_agg(row_to_json(u))) from users u;

The first commands set the tuples mode on. The second command sets the unaligned output format and the third command sets the output file name. Finally, the select statement uses the row_to_json function which accepts a row as a value and returns a JSON string. array_agg is an aggregate function which aggregates the argument provided into a PostgreSQL array in a similar fashion to sum or count in MySQL. The array_to_json function takes a PostgreSQL array and returns a single JSON value. The exported data is saved to the current working directory in a file that we have aptly named users.json.

Step 2: Now you have some data exported as JSON which can be imported in Firebase. To save data into your Firebase real-time database, select the node you wish to import data to.

Step 3: In the Firebase data page, click the overflow menu in the upper right and select Import JSON.

Step 4: Browse to the JSON file that you generated from PostgreSQL and click Import.

Congratulations! Your Firebase PostgreSQL Integration has been set up. You’ve just covered how to shift data from Firebase to PostgreSQL and vice versa without any data loss, manipulation, or recreation. As you have seen, this manual process can become tedious when you want to transfer many records. You should consider using a data migration tool to automate this process and speed it up. A data migration tool can help you to extract data from the source, load it to the new system, and verify its contents.

Limitations of using Manual Method for Firebase PostgreSQL Integration

If you really want to sync data in real-time then you’ll need to:

  1. Have a server that listens to changes in both database systems.
  2. Have a script that executes a SQL query every x seconds to synchronize data.
  3. Develop custom code to achieve this functionality.

Note that synchronizing the Firebase and PostgreSQL is very hard to achieve. A change in the PostgreSQL server will trigger a change in Firebase, which will trigger the listeners that change the PostgreSQL server, resulting in a feedback loop, which you somehow need to avoid. To make your life easier, you should consider using a cloud-based data migration tool. 

Method 2: Firebase PostgreSQL Integration using Hevo

Hevo Feature Image - Firebase PostgreSQL

Hevo is a No-code Data Pipeline. It supports pre-built data integrations from 100+ data sources, including 30+ Free Data Sources like Firebase. Hevo offers a fully managed solution for your data migration process to PostgreSQL Server.

Sign up here for a 14-Day Free Trial!

It will automate your data flow in minutes without writing any line of code. Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides you with a truly efficient and fully automated solution to manage data in real-time and always have analysis-ready data at PostgreSQL Server.

The steps to load data from Firebase to PostgreSQL using Hevo Data are as follows:

  1. Authenticate and connect Firebase as your data source.
  2. Connect PostgreSQL Server as a destination to load your data. 

Let’s look at some salient features of Hevo:

  • Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
  • Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
  • Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
  • Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within pipelines.
  • Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Explore more about Hevo by signing up for a 14-day free trial today

Conclusion

In this article, you learned about Firebase, PostgreSQL and their importance, 2 methods to implement Firebase PostgreSQL integration, and the limitations of using the manual method for the PostgreSQL Firebase Integration.

Visit our Website to Explore Hevo

Integrating and analyzing data from a huge set of diverse sources can be challenging, this is where Hevo comes into the picture. Hevo Data, a No-code Data Pipeline helps you transfer data from a source of your choice in a fully automated and secure manner without having to write the code repeatedly. Hevo with its strong integration with 100+ sources & BI Tools (including 30+ free sources), allows you to not only export & load Data but also transform & enrich your Data & make it analysis-ready in a jiffy.

Get started with Hevo today! Sign Up here for a 14-day free trial!

Tell us about your experience of setting up Firebase PostgreSQL Integration. Share your thoughts in the comments section below!

No-code Data Pipeline for PostgreSQL