mySQL authoritative database – combined with Firebase

All we need is an easy explanation of the problem, so here it is.

We have built a LAMP-stack API application via PHP Laravel. This currently uses a local mySQL instance. We have mostly implemented views in AngularJS.

In order to use Firebase, we need to sync data between the authoritative store in mySQL with anything relevant that exists on Firebase, as close to real-time as possible. This means that other parts of the app which are not real-time and don’t use Firebase can also serve up fresh content that’s very recently been entered into the system.

I know that Firebase is essentially a noSQL database in the cloud. My question is – how do I write a wrapper or a means to sync the canonical version of my Firebase into my database of record – mySQL?

Update to answer – our final decision – ditching Firebase as an option

We have decided against this, as we can easily have a socket.io instance on the same server with an extremely low latency connection to mySQL, so that the two can remain in sync. There’s no need to go across the web when resources and endpoints can exist on localhost. It also gives us the option to run our app without any internet connection, which is important if we sell an on-premise appliance to large companies.

A noSQL sync platform like Firebase is really just a temporary store that makes reads/writes faster in semi-real-time. If they attempt to get into the “we also persist everything for you” business – that’s a whole different ask with much more commitment required.

The guarantee on eventual consistency between mySQL and Firebase is more important to get right first – to prevent problems down the line. Also, an RDMS is essential to our app – it’s the only way to attack a lot of data-heavy problems in our analytics/data mappings – there’s very strong reasons most of the world still uses a RDMS like mySQL, etc. You can make those very reliable too – through Amazon RDS and Google Cloud SQL.

There’s no specific problem beyond scaling real-time sync that Firebase actually solves for us, which other open source frameworks don’t already solve. If their JS lib actually handled offline scenarios (when you START offline) elegantly, I might have considered it, but it doesn’t do that yet.

So, YMMV – but in our specific case, we’re not considering Firebase for the reasons given above.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The entire topic is incredibly broad, definitely too broad to provide a simple answer to.

I’ll stick to the use-case you provided in the comments:

Imagine that you have a checklist stored in mySQL, comprised of some attributes and a set of steps. The steps are stored in another table. When someone updates this checklist on Firebase – how would I sync mySQL as well?

If you insist on combining Firebase and mySQL for this use-case, I would:

  1. Set up your Firebase as a work queue: var ref = new Firebase('https://my.firebaseio.com/workqueue')

  2. have the client push a work item into Firebase: ref.push({ task: 'id-of-state', newState: 'newstate'})

  3. set up a (nodejs) server that:

    1. monitors the work queue (ref.on('child_added')
    2. updates the item in the mySQL database
    3. removes the task from the queue

See this github project for an example of a work queue on top of Firebase: https://github.com/firebase/firebase-work-queue

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply