Source: https://fireloom.io/firestore-to-postgres

> How to sync Firebase Firestore to PostgreSQL: the options (exports, Cloud Functions, ETL, change streaming), the table you get, the SQL you can run, and a 5-minute setup.

# Sync Firestore to PostgreSQL, in real time.

Firestore is great for building apps and awkward for questions like “revenue by plan last month”. This guide covers how to get Firebase data into PostgreSQL and keep it current: the options, the table you end up with, and the SQL you can run on it.

## Why sync Firebase to PostgreSQL

Firestore queries are fast and simple by design: no joins, no aggregations beyond counts and sums, and indexes you plan up front. Once a product has customers, teams want SQL:

- **Reporting and BI.** Metabase, Looker Studio, Grafana, Hex and most BI tools speak Postgres, not Firestore.
- **Joins.** Combine app data with billing, CRM or support tables in one query.
- **Migration.** Moving off Firebase is far safer when Postgres already holds a live, verified copy.
- **Ad-hoc questions.** Engineers and analysts can answer them without writing scripts against the Admin SDK.

## Four ways to get Firestore data into Postgres

1. **Export and import.** `gcloud firestore export` writes LevelDB files to Cloud Storage; a script converts and loads them. Fine for a one-off copy. It's a snapshot, and every new export reads every document again.
2. **Cloud Functions triggers.** An `onDocumentWritten` function writes each change to Postgres. Real time, but it's code you own: you also need a backfill, retries, ordering, deletes, and a trigger per collection path, including nested ones.
3. **Batch ETL tools.** Scheduled syncs that usually re-read collections on each run because Firestore has no change cursor, so cost grows with data size, and deletes and subcollections need checking.
4. **Change streaming (Fireloom).** Listens to Firestore's change stream, applies inserts, updates and deletes to Postgres within seconds, and resumes from checkpoints after restarts. Nothing to deploy.

A side-by-side table is on the [comparison page](https://fireloom.io/compare).

## The table you get

Fireloom writes every document, from every collection at every depth, into one table. Collection names never create tables, so any naming scheme works and nothing breaks when the app adds a field.

PostgreSQL · firestore.documents (schema)

```
CREATE TABLE firestore.documents (
  path        text PRIMARY KEY,  -- customers/ada/orders/1042
  collection  text,              -- orders
  id          text,              -- 1042
  parent_path text,              -- customers/ada
  data        jsonb,             -- the document's fields
  create_time timestamptz,
  update_time timestamptz,
  synced_at   timestamptz
);  -- indexed on (collection) and (parent_path)
```

Firestore types map to JSON without losing information: timestamps become RFC 3339 strings, GeoPoints become `{lat, lng}`, references become document paths, and bytes become base64.

## Querying it

Filter by `collection`, read fields with `->>`, and join a subcollection to its parent with `parent_path`:

psql (example)

```
-- Order items with their order and customer, three levels deep
SELECT c.data->>'name'  AS customer,
       o.id                AS order_id,
       i.data->>'sku'   AS sku,
       (i.data->>'qty')::int AS qty
FROM   firestore.documents i
JOIN   firestore.documents o ON o.path = i.parent_path
JOIN   firestore.documents c ON c.path = o.parent_path
WHERE  i.collection = 'items'
AND    o.data->>'status' = 'shipped';
```

For heavier use, add a view per collection and expression indexes on the fields you filter by, for example `CREATE INDEX ON firestore.documents ((data->>'status')) WHERE collection = 'orders'`.

## Set it up in about five minutes

1. **Connect Firestore.** Run one command in your Firebase project's folder. It creates a service account that can read Firestore (and export it for the first load) but never write to it, and hands its key to Fireloom.
2. **Connect Postgres.** Paste a connection string for Supabase, Neon, RDS, Cloud SQL or any reachable Postgres. Fireloom creates the `documents` table in a schema you choose.
3. **Go live.** Pick collections or take everything. Fireloom loads every document once, then streams every change.

## What it costs in Firestore reads

Firestore bills one read per document returned. The first load reads each document once (an export costs the same). Afterwards Fireloom reads only documents that change, resumes from checkpoints after restarts instead of re-reading, and runs a daily consistency check that costs about one read per 1,000 documents. Details are in [the sync engine](https://fireloom.io/sync-engine) page.

## Limits worth knowing

- A subcollection with a brand-new name is picked up at the next daily consistency check, or right away with Resync. New documents in known collections, however deep, stream immediately.
- Fields live in `jsonb`. Typed columns are on the roadmap; views cover most needs today.
- Sync is one way, Firestore to Postgres. Fireloom never writes to Firestore.

## Try it on your own data

Free up to 100,000 documents per Firebase project. No credit card.

[Start syncing free](https://fireloom.io/app?signup=1)