Firebase / no firebase or how can we use the firestore as a relational database?

[HaGuesto]

Firebase has many advantages. It is taking care of the authentication and is a great hosting service. We will spend much less time on maintaining a backend and firebase could be a great tool when we want to add offline support (#28), too.

However, our data model is relational and the firestore is a noSQL-database. Currently, we are implementing queries in a very inefficient and complicated way. We are nesting queries (c.f. #227) or BoxListContainer

<FirestoreCollection
        path="boxes"
        filter={filters}
        sort="humanID:asc"
        render={boxresult => {
          return (
            <ProductsCollection
              organizationRef={organization.ref}
              render={productresult => {
                return (
                  <BoxList
                    isLoading={boxresult.isLoading || productresult.isLoading}
                    boxes={boxresult.data}
                    products={productresult.data}
                    selectedProductFilter={selectedProductFilter}

Such queries multiply the number of reads.

Example:
Our first tester has about 20 products and registered about 20 boxes in total. If our user wants to see an overview over all boxes with each of them showing their product name, we would need in the order of 40 reads if we use a relational database. As it is implemented above we need about 400 reads.

It did not matter until now because we do not have much data and only queries with one JOIN.
However, the next features (#178 or #190) already require more advanced queries including aggregation, more JOINS, ... We are also thinking to offer something like labels (like in github) to tag different boxes. Such tags would require to model even a N-to-N relationship.
So, in the long run it does matter. The pricing of firebase is based on the reads and writes and it will slow down each query if we keep on nesting our queries. Most important it will be annoying to implement the queries as nested queries.

==> We need to decide (now) how to do queries in general.

possible solutions:

• There is a package to run SQL commands on json-like data which runs the queries locally and just magically solves all our problems. Do you know of such a package?
• We have to implement basic SQL commands ourselves (JOINS) on json-like data.
• We switch to a similar service as firebase with a relational database. Have you heard of such a service?
• We set up our own backend.

PS. We had this topic partly already before in #73.

[barto74]

I think one very important reason to have chosen this platform is the offline capabilities. Just as to make the discussion as wide as possible: what if we let this feature go and help users finding hardware based solutions (mobile wifi points, extenders) or even let them work on a local box.

[rgrnwd]

Not sure if this is the solution you're looking for.
In a previous project, we used a noSQL data store (couchbase), and used PouchDB to query from it. It was super simple. The syntax of the queries is not SQL but was very straight forward, and integrated especially well in our React application.
Essentially all the data is stored as JSON files in Couchbase (or firebase for this instance), then when the app is loaded it gets synced into the local PouchDB instance (which wraps the browser's IndexDB). We stored some of the information in the React store (the static stuff), and queried the rest from PouchDB. All information is thus available offline as long as the data is synced once in online mode.
You can see how these queries look in this link

[rubenspgcavalcante]

Even if we solve the problem with the query syntax using some abstraction layer over it, we'll still have problems with the number of reads/writes on it. Firebase don't have a join syntax integrated to the get() method yet, and they don't plan to have it (I've can't find now the thread where the firebase team responded this).
Well in someway it makes a lot of sense, as I was talking with @HaGuesto , noSQL basically is made to work with aggregation rather than relations. Because of this it just don't care about data history and is matches nicely application which needs heavy writing speed, which is not our case.
Maybe we can try out Heroku or so, with some Node framework+ORM to automatically create a RESTful API. Heroku provides for free 1k rows in postgres which I think is enough for now.

With a setup like this we'll be free ourselves from a vendor lock, something that Google is an expert doing, and being able to move to any service with the node+postgres 👍
Although CouchDB can be a good solution, still a noSQL solution and we may end up with the same problem while doing joins, because we'll try to adapt it to work like a SQL database.

For offline mode, for now, we should go KISS by just precaching data on the app state and dump this information on something simple as localstorage or so, and only when the necessity appears, try to move to a indexedDB wrapper or any more robust solution.

UPDATE:
Looking here, you're able to create "views" to do the join job done. So, maybe PouchDB is a go 🤔
The thing is, can we use a relational db for it or only works with couchbase?

[rubenspgcavalcante]

For not having to maintain complex backend we can try out something as:
https://github.com/PostgREST/postgrest

It seem to have even a way to deploy directly to heroku 🤔

[HaGuesto]

I just had an interesting read on medium.
https://medium.com/one-tap-software/why-im-leaving-firebase-for-pouchdb-or-am-i-73a12390bfef

He describes a similar solution to ours and proposes to use firebase and pouchdb parallel. What do you think of that?

[rubenspgcavalcante]

Even if we use both, we'll need at least a minimum backend to communicate with both services to deal with the authentication problem. 🤔
Anyway, we can try out doing a small NodeJS service to get this job done and deploy it to a free micro aws instance

[HaGuesto]

We had a discussion about this issue on skype yesterday (c.f. meeting minutes).
The result is that we going to put more effort to make firebase work. The plan is to download the database to local storage and query it locally. Firebase is offering already most of the tools for downloading and updating data and one of our requirements is anyway that boxwise works partly offline.

Some guidelines how this will work (#290) and a first example (#289) will be produced.