Firebase Realtime Database to PostgreSQL: How to Move Your Data

Syncing Firebase to PostgreSQL moves application data out of a JSON tree built for mobile clients and into a relational database where it can be queried with SQL. Teams do this because Firebase Realtime Database is excellent at serving an app and poor at answering analytical questions. Airbyte handles the extraction with no code.

One thing about the output shape should be settled in your head before you build, because it determines how much work happens after the sync. The connector does not produce typed columns. It produces a key and a value, where the value is the JSON document as a string.

What does a record actually look like?

The Firebase Realtime Database source emits records with two fields. One is the key, which is the node's identifier in the tree. The other is the value, containing that node's contents serialised as JSON.

So if your database holds user records under a path and you point the connector at it, you get one row per user with the user's identifier in one column and everything else, address, age, and any other fields, as JSON text in the second. Firebase Realtime Database has no schema to read, so there is nothing for the connector to infer typed columns from.

Plan for this rather than discovering it. Treat the landed table as raw and write a view or dbt model that extracts the fields you query into real columns, using Postgres JSON operators. That model becomes the contract between Firebase's flexible structure and everything downstream, and it is the right place for the inevitable moment when the app starts writing a new field.

One practical consequence worth noting: because the value is a string rather than a native JSON column, you may need to cast before applying JSON operators. Check what arrives on a sample before writing the model.

Choosing the node path

The Node Path setting specifies which part of your database's tree to sync, and it defaults to the root node. That default is almost never what you want on a real application database.

Firebase trees frequently hold everything the app touches, including session state, caches, presence data, and denormalised copies of the same information stored several times for read performance. Syncing from the root pulls all of it. Point the connector at the specific subtree carrying the entity you want, and if you need several entities, configure several connections rather than one broad sync you then filter downstream.

There is also a Buffer Size setting controlling how many records are fetched at a time. On a large node this is the knob to reach for if syncs are struggling, since it governs how much the connector holds in memory per batch.

Setting up credentials

You need a Google Cloud service account holding the Firebase Realtime Database Viewer role, which grants read access. Airbyte recommends a service account exclusive to Airbyte for permissioning and auditing, though an existing one with the right permissions works.

Create the service account, note its ID, add it as a member in your Google Cloud project, and grant it the role. Then create a key, choosing JSON, since Airbyte supports JSON keys only. Download it immediately, because that is the only moment Google will show you its contents. You paste the file's contents into the connector configuration, and Airbyte's documentation sensibly advises deleting the key from your computer once the source is configured.

Landing the data in Postgres

The Postgres destination writes each stream to a table in your target schema alongside Airbyte's metadata columns. It is an Airbyte-supported connector with a high reported sync success rate, so the destination half of this pipeline is well-trodden and most of your attention belongs upstream.

For querying the value column, Postgres JSON functions and operators are more than capable, and casting to JSONB lets you index specific paths with a GIN index if particular keys are queried often. That indexing work is yours to do; Airbyte creates the table and writes the rows but adds no indexes beyond what it needs.

Because a Firebase tree is edited by the application rather than governed by a schema, expect the shape inside the value column to drift as the app evolves. A field that was a string becomes an object; a field appears on new records but not old ones. Your extraction model should tolerate both rather than assuming every record is identical.

Frequently asked questions

Why is all my data in one column?

That is the connector's output shape. Records carry a key and a value, where the value is the node's contents as JSON. Extract the fields you need into real columns in a downstream view or model.

How do I sync only part of my database?

Set the Node Path to the subtree you want. It defaults to the root node, which on most application databases pulls in far more than you need.

Which credentials does the connector need?

A Google Cloud service account with the Firebase Realtime Database Viewer role, and a key in JSON format. Other key formats are not supported.

My sync is slow or memory-hungry on a large node.

Adjust the Buffer Size setting, which controls how many records are fetched at a time, and narrow the node path so you are not reading subtrees you do not need.

Get your Firebase data into PostgreSQL

Scope the node path to the subtree you actually need, use a dedicated service account with a JSON key, and build an extraction model over the value column rather than querying JSON everywhere. For the same source into a different database, see our guide to Firebase Realtime Database to MySQL. For another document store into Postgres, see MongoDB to PostgreSQL.

Start syncing now →