Database-style Queries with Firebase

Question

Is there a fast way to perform database-style queries with Firebase?

For example:

Given a firebase reference users with fields user_id, name, and age, what would be the best way to do a query similar to this:

SELECT name FROM users WHERE `user_id`=147;

and

SELECT COUNT(*) FROM users WHERE age=21;

score 70 · Accepted Answer · answered Jul 20 '12 at 23:21

In general, no. Firebase is essentially a "realtime database", constantly streaming updates to you as data changes, so it's more difficult to do general-purpose querying. For now, there are a couple of (admittedly limited) querying primitives that are provided. See the Queries/Limits page in the docs.

You can often work around these limitations through a variety of approaches:

Use location names and priorities intelligently. If you structure your data as /users/[userid]/name, you can accomplish your first "query" by just retrieving /users/147/name. If you know you'll want to query by age, you can use age as the priority for user nodes and then do "usersRef.startAt(21).endAt(21).on('child_added', ...)" to get all users age 21. You still have to count them manually.
Do client-side querying. If the entire data set is smallish, you may be able to retrieve the entire data set and then filter / process it manually on the client.
Run a separate server. It can connect to Firebase, sync data and then answer "queries" for clients. It can still communicate to clients through Firebase, and Firebase can still be the primary data store, but your separate server can do the work to perform queries quickly.

We intend to improve on this over time, as we realize it's a weak spot compared to the flexible querying provided by traditional relational database systems.

Michael, has your team made any new developments with querying? — David Robbins, Apr 21 '13 at 16:40
No notable new features. You might be interested in this blog post we recently put out though, describing best practices for structuring your data for Firebase, given that we don't support SQL-like querying: https://www.firebase.com/blog/2013-04-12-denormalizing-is-normal.html — Michael Lehenbauer, Apr 21 '13 at 20:30
There is now a blog post and a new lib for the "Run as a separate server" approach that shows how crazy-easy it is to integrate ElasticSearch: https://www.firebase.com/blog/2014-01-02-queries-part-two.html — Kato, Jan 15 '14 at 23:22

score 24 · Answer 2 · edited May 23 '17 at 10:31

Mr. Lehenbauer is of course the master of all things Firebase, so listen to him. ;) However, this particular topic is one I've been laboring over for a couple of weeks now.

Here are a few of my thoughts, to enhance the "Run a separate server" and "Client-side querying" responses:

ElasticSearch (a node.js script)

With a node.js script on the server, you can have ElasticSearch integrated and providing some solid content searches in under an hour. Here's a blog post and a lib that makes it even easier: https://www.firebase.com/blog/2014-01-02-queries-part-two.html

cacheable/common queries

These can be handled by a server/cron process which reads the table and duplicates the data. For instance, assume I want to show "unavailable/available" for a user's login name during registration, but store the user records by a different unique ID for some complex reason.

My cron/server could read all the records from the users table, then insert them into another table that is stored by email address, with a value of the user's record ID (or any other data I might want to know).

This duplicated data approach is sort a manual caching technique and is a common practice in No-SQL environs; we're trading storage space (which is presumed to be cheap and available) for speed and simplified processes.

customized queries (using a queue)

Custom queries could be sent via XHR (ajax) directly to a server, which could do the hard labor and return better results. Alternately, you could utilize Firebase to connect with a server back-end by utilizing a queue.

The client places the query request as a JSON into a special Firebase table called queue and awaits a response.

The server listens for queue.on('child_added', ...) and serves the data back using `queue_record.child('response', ...data here...)

This has some nice advantages. For one, any number of servers could listen and serve responses, making load balancing a breeze. The code for this is very simplistic to set up and covered in another thread here in SO.

Hope this is helpful!

There is also a lib now for integrating ElasticSearch, which is crazy easy and makes searches easy as pie, at the cost of a node.js script: https://www.firebase.com/blog/2014-01-02-queries-part-two.html — Kato, Jan 15 '14 at 23:21

score 2 · Answer 3 · answered Jul 12 '14 at 19:35

I created my own CMS for firebase so when creating a table of the firebase data i filtered it with this

var child = ref.child();
var compare;
switch(filter){
          case "First_Name":
            compare = child.First_Name;
          break;
          case "Last_Name":
            compare = child.Last_Name;
          break;
          case "Phone_Number":
            compare = child.Phone_Number;
          break;
          case "Department_Number":
            compare = child.Department_Number;
          break;
          case "Position":
            compare = child.Position;
          break;
          case "Status":
            compare = child.Status;
          break;
          case "Tier":
            compare = child.Tier;
          break;
      }


      if(compare.match("^" + string)){
        //display items

This is what Lehenbauer means by “client-side querying.” And I’d recommend using this equivalent but more readable form: `if (child[filter].match(‘^’ + string)) {` (replaces everything from the second line onwards) — Jonathan, Oct 24 '15 at 10:55

Database-style Queries with Firebase

3 Answers3

Linked