FireSQL is a library built on top of the official Firebase SDK that allows you to query Cloud Firestore using SQL syntax. It’s smart enough to issue the minimum amount of queries necessary to the Firestore servers in order to get the data that you request.
On top of that, it offers some of the handy utilities that you’re used to when using SQL, so that it can provide a better querying experience beyond what’s offered by the native querying methods.
Just add firesql
and firebase
to your project:
npm install firesql firebase
# or
yarn add firesql firebase
If you want to receive real-time updates when querying, then you will also need to install rxjs
and rxfire
:
npm install firesql firebase rxjs rxfire
# or
yarn add firesql firebase rxjs rxfire
// You can either query the collections at the root of the database...
const dbRef = firebase.firestore();
// ... or the subcollections of some document
const docRef = firebase.firestore().doc('someDoc');
// And then just pass that reference to FireSQL
const fireSQL = new FireSQL(dbRef);
// Use `.query()` to get a one-time result
fireSQL.query('SELECT * FROM myCollection').then(documents => {
documents.forEach(doc => {
/* Do something with the document */
});
});
// Use `.rxQuery()` to get an observable for realtime results.
// Don't forget to import "firesql/rx" first (see example below).
fireSQL.rxQuery('SELECT * FROM myCollection').subscribe(documents => {
/* Got an update with the documents! */
});
One-time result (Promise)
import { FireSQL } from 'firesql';
import firebase from 'firebase/app';
import 'firebase/firestore';
firebase.initializeApp({ /* ... */ });
const fireSQL = new FireSQL(firebase.firestore());
const citiesPromise = fireSQL.query(`
SELECT name AS city, country, population AS people
FROM cities
WHERE country = 'USA' AND population > 700000
ORDER BY country, population DESC
LIMIT 10
`);
citiesPromise.then(cities => {
for (const city of cities) {
console.log(
`${city.city} in ${city.country} has ${city.people} people`
);
}
});
Realtime updates (Observable)
import { FireSQL } from 'firesql';
import firebase from 'firebase/app';
import 'firesql/rx'; // <-- Important! Don't forget
import 'firebase/firestore';
firebase.initializeApp({ /* ... */ });
const fireSQL = new FireSQL(firebase.firestore());
const cities$ = fireSQL.rxQuery(`
SELECT city, category, AVG(price) AS avgPrice
FROM restaurants
WHERE category IN ("Mexican", "Indian", "Brunch")
GROUP BY city, category
`);
cities$.subscribe(results => {
/* REALTIME AGGREGATED DATA! */
});
SELECT
queries for now. Support for INSERT
, UPDATE
, and DELETE
might come in the future.JOIN
s.LIMIT
doesn’t accept an OFFSET
, only a single number.COUNT
.GROUP BY
, it cannot be combined with ORDER BY
nor LIMIT
.NOT
.LIKE
. Allows for searches in the form of WHERE field LIKE 'value%'
, to look for fields that begin with the given value; and WHERE field LIKE 'value'
, which is functionally equivalent to WHERE field = 'value'
.FireSQL transforms your SQL query into one or more queries to Firestore. Once all the necessary data has been retrieved, it does some internal processing in order to give you exactly what you asked for.
For example, take the following SQL:
SELECT *
FROM cities
WHERE country = 'USA' AND population > 50000
This would get transformed into this single Firestore query:
db.collection('cities')
.where('country', '==', 'USA')
.where('population', '>', 50000);
That’s pretty straightforward. But what about this one?
SELECT *
FROM cities
WHERE country = 'USA' OR population > 50000
There’s no direct way to perform an OR
query on Firestore so FireSQL splits that into 2 separate queries:
db.collection('cities').where('country', '==', 'USA');
db.collection('cities').where('population', '>', 50000);
The results are then merged and any possible duplicates are eliminated.
The same principle applies to any other query. Sometimes your SQL will result in a single Firestore query and some other times it might result in several.