Firebase Firestore is an excellent NoSQL document real-time database built for automatic scaling & high performance. But when you would like to build any reports on top of your Firebase Firestore, needed a lot of code!
This article will compare Firestore vs PostgreSQL and how you can quickly Sync your data from Firestore to PostgreSQL to get SQL-relation database benefits.
Postgresql vs Firestore or Firebase vs SQL databases
Let's consider the pros and cons of Firestore vs PostgreSQL:
SQL vs NoSQL
– Firestore. NoSQL (Non-Relational Database). App development platform from Google, pay as you go (free quote per day 50k reads, additional per 100,000 documents – $0.06)
– PostgreSQL. Free and Open-Source SQL Database Relational database.
– Firestore. Yes, only available on GCP (Google Cloud Platform), owned by Google.
– PostgreSQL. Host everywhere (AWS, Azure, or host on your own). Several clouds provide support-managed versions of it.
Data structure & Data Types
– Firestore. Based on the concept of collections and documents. There’s no schema for the database, no tables, and no columns, it’s just a combination of key/value pairs. No need for a detailed database model.
– PostgreSQL. Based on the concept of tables and relations. A detailed database model is needed before the creation.
– Firestore. Stores and syncs data in real-time (a real-time document store).
– PostgreSQL. No real-time, PostgreSQL is based on the domain-specific language SQL.
Custom queries & reports
– Firestore. Lacks standard query language,
– PostgreSQL. SQL queries allow you to create any reports.
– Firestore. Firebase scales horizontally.
– PostgreSQL. Scales vertically. Postgresql database designed with scalability in-core.
How to Sync Firestore to PostgreSQL database
Jet allows you to sync data from a Firebase Firestore database to a Jet PostgreSQL database. The process copies the entire contents of a single Firestore collection to a single PostgreSQL table. Any changes you did in Firestore, update the Jet PostgreSQL database immediately.
Firstly, create a new project and connect Firestore database. Allow Jet to Sync Firebase data to Jet Tables by choosing operation mode.
Next, you can set up a visualization for the Collection: parse your JSON, specify relations, change the field type, update the data, etc. Click to More icon right after the Firestore icon to change the Sync configuration.
Query and Blend your Firestore data using SQL
Create your first SQL query on the Data page by clicking Create with SQL query. On the right side panel, you can find prompts – the list of Firestore. Press Send Request to run your SQL query.
Jet allows you to join data from different data sources. For example, you can join data from Firestore and Hubspot. There are two options:
- Relations and Roll-up/Look-ups fields without code
- SQL Queries
Connect one more data source you want to join with Firestore data (for example, Stripe, Hubspot, Intercom).
Using Relations and Roll-up/Look-ups fields to Blend Data
Go to your Collection that has the Stripe Collection ID and specify Relation. For example, the Firestore collection has Stripe ID field -> Customer ID Stripe: Click to Column and change Type to Link to Record. Specify Data Source and Collection (Stripe -> Customers).
Add a new Custom Column and choose Lookups/Rolloup based on what operations you would like to do. Learn more about Rolloup/Lookups.
SQL Queries to Blend Data
Click Create with SQL query in Data page. On the right side panel you can see the list of Data Sources that you can blend, join.
Extend Firestore API using Jet API
The flexible Jet API enables you to use Jet as a backend, extend your apps, and benefit from inter-operability. Jet generates List, Retrieve, Create, Update, and Delete endpoints for each synced Firestore.
Build an App on top of your data
Jet allows you to build Internal tools, Customer portals, Dashboards, Automations&Workflows on top of your Data.
Learn how to build an app on top of Firestore database
Learn how to build Automations & Workflows on top of Firestore data