Firebase Firestore is an excellent NoSQL document, real-time database built for automatic scaling & high performance. But if you would like to build reports or things like that on top of your Firebase Firestore, you'll need 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-relational 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 quota per day – 50k reads, additional per 100,000 documents – $0.06)
– PostgreSQL. Free and Open-Source SQL Database Relational database.
Hosting
– 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 creating it.
Real-time
– 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.
Scalability
– Firestore. Firebase scales horizontally.
– PostgreSQL. Scales vertically. PostgreSQL databases are designed with scalability at their 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 make in Firestore update the Jet PostgreSQL database immediately.
Firstly, create a new project and connect your Firestore database. Allow Jet to Sync Firebase data to Jet Tables by choosing the 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 on the More icon that is just to the right of the Firestore icon to change the Sync configuration.
Query and Blend your Firestore data using SQL
Create your first SQL query on the Data Editor page by clicking Create with SQL query. On the right side panel, you can find prompts – they list the collections in your 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
To use this feature, first connect another data source that 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 on the Column and change the type to Link to Record. Specify Data Source and Collection (Stripe -> Customers).
Add a new Custom Column and choose Lookups/Rollup based on what operations you would like to do. Learn more about Rollup/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