Firestore Queries for SQL Minds

Create an app fast with Firebase, even if you’re intimidated by NoSQL databases—the translation of queries to SQL is easier than you think.

SQL databases are quite different than NoSQL databases, so you must think a little differently when modeling your data. However, most translations from SQL code to NoSQL are identical or easier. Here, we use the Firebase Modular API, but the firebase-admin API will be the same.

TL;DR

Firestore is a document database that doesn’t allow joins. You can simulate them by duplicating your data. Most regular queries, however, work as expected. The lack of a schema allows you to get up and going extremely quickly. You need to learn the data modeling patterns.

Introduction

Firestore is a NoSQL document database.

Tables

• A table is a collection or subcollection. A row is a document.

Schema

• You must declare a schema in SQL; you don’t need one in NoSQL.
• Each document can have different kinds of data, although this would not be useful.

Create

You create a table when you create a document. A table does not exist without a document.

Alter

There is nothing to alter.

Drop

To drop a table, you must delete all documents in a collection. This can also be done in the Firebase Console.

Constraints

There is no schema, but you can declare constraints using Firebase Rules.

Naming Conventions

You will usually see camel case field names instead of snake case.

Primary Key

Each document has a unique path. The documentId() of the document is the primary key. In the collection posts/2xelskel, the 2xelskel is the document ID. You can use any alphanumeric string, as well as _ characters. Otherwise, it will be automatically generated.

Insert

You can insert data in SQL by declaring each field and value.

SQL

INSERT INTO posts (title, content, created_by, created_at)
VALUES (
    'My First Post', 
    'This is the content of my first post.', 
    1, 
    NOW()
);

Firestore

addDoc(
	collection(db, 'posts'),
	{
		title: 'My First Post',
    content: 'This is the content of my first post.',
    createdBy: 1,
    createdAt: serverTimestamp()
  }
});

📝 Notice the serverTimestamp() function, which will use the date on the server. Since Firestore can be safely run on the client, this would be secured in Firestore Rules.

Update

Updating is basically the same.

SQL

UPDATE posts
SET title = 'Updated Post Title',
    content = 'This is the updated content of the post.',
    updated_at = NOW()
WHERE post_id = 1;

Firestore

updateDoc(
	collection(db, 'posts', '1'),
	{
		title = 'Updated Post Title',
    content = 'This is the updated content of the post.',
    updatedAt: serverTimestamp()
  }
});

📝 Notice the document ID is 1 in this case. The ID will probably be a uniquely generated string in a real application.

Upsert

You can upsert a document with setDoc.

SQL

INSERT INTO posts (id, title, content, created_by, created_at, updated_at)
VALUES (1, 'Upserted Post', 'This is the upserted content.', 1, NOW(), NOW())
ON CONFLICT (id)
DO UPDATE SET 
    title = EXCLUDED.title,
    content = EXCLUDED.content,
    author_id = EXCLUDED.created_by,
    updated_at = NOW();

📝 This will vary between SQL languages.

setDoc(
	doc(db, 'posts', '1'),
	{
		title: 'Upserted Post',
		content: 'This is the upserted content.',
		createdBy: 1,
		createdAt: serverTimestamp(),
		updatedAt: serverTimestamp()
	}, { merge: true }
});

📝 setDoc behaves like addDoc without the { merge: true } parameter.

Select

In Firestore, you can only select all fields from a document unless you use the REST API with your own code.

SQL

SELECT * FROM posts;

Select all posts.

Firestore

query(
	collection(db, 'posts')
);

Exists

In SQL, you SELECT first.

SQL

SELECT EXISTS (
    SELECT 1
    FROM posts
    WHERE id = 1
);

Firestore

const postSnap = await getDoc(doc(db, 'posts', '1'));
const exists = postSnap.exists();  

In Firestore, you must read the document to check its existence.

Limit

Of course, we limit our selection to one or more in actual use cases. We don’t want all documents in a collection or all rows in a table.

SQL

SELECT * FROM posts LIMIT 1;

Firestore

query(
	collection(db, 'posts'),
	limit(1)
);

There is nothing surprising here.

Where

Firestore queries use AND by default, but you can also add OR queries.

SQL

SELECT * FROM posts
WHERE
    (a = 1 AND c = 3) OR
    (a = 1 AND d = 4) OR
    (b = 2 AND c = 3) OR
    (b = 2 AND d = 4);

Firestore

query(
  collection(db, 'posts'),
  or(
    and(where('a', '==', 1), where('c', '==', 3)),
    and(where('a', '==', 1), where('d', '==', 4)),
    and(where('b', '==', 2), where('c', '==', 3)),
    and(where('b', '==', 2), where('d', '==', 4))
  )
);

⚠️ Unlike in SQL, each query has a strict limit of 30 disjunctions.

In

You can search for IN as well in Firestore.

SQL

SELECT *
FROM posts
WHERE created_by IN (1, 2, 3);

Firestore

query(
	collection(db, 'posts'),
	where('created_by', 'in', [1, 2, 3])
)

Keep in mind this is a simplification of:

query(
	collection(db, 'posts'),
	or(
		where('created_by', '==', 1),
		where('created_by', '==', 2),
		where('created_by', '==', 3)
	)
);

The same goes for SQL. IN is just several OR clauses on the same field, condensed.

Order By

You can order your data similar to SQL.

SQL

SELECT * FROM posts
ORDER BY created_by ASC, created_at DESC;

Firestore

query(
	collection(db, 'posts'),
	orderBy('createdBy'),
	orderBy('createdAt', 'desc')
);

📝 The default sort order is asc.

📝 An orderBy also sorts data, so the field must exist. Otherwise, it is filtered out.

⚠️ Changing orderBy fields requires a composite index for each query. If you change the field, you need another index.

Inequalities

You can have inequalities on multiple fields.

SQL

SELECT * FROM cities
WHERE population > 1000000
  AND density < 10000;

Firestore

query(
  collection(db, 'cities'),
  where('population', '>', 1000000),
  where('density', '<', 10000),
);

📝 Order index fields by equalities followed by most selective range or inequality field.

⚠️ Order fields in decreasing order of query constraint selectivity.

• See the best practices in the docs.

⚠️ Queries with range or inequality filters on document fields and only equality constraints on the document key (__name__) aren’t supported.

⚠️ Cloud Firestore limits the number of range or inequality fields to 10. This prevents queries from becoming too expensive to run.

NOT Equal

You can only have a != on one field in Firestore per query.

SQL

SELECT * FROM cities
WHERE capital != false;

Firestore

query(
	collection(db, 'cities'),
	where("capital", "!=", false)
);

⚠️ A != query clause might match many documents in a collection. To control the number of results returned, use a limit clause.

📝 Not-equal queries exclude documents where the given field does not exist.

📝 For multiple not equals on the same field, use not-in.

Incrementing

You don’t need to increment columns in SQL until your database gets big. Either way, the code is simple.

SQL

UPDATE posts
SET views = views + 1
WHERE id = 1;

Firestore

updateDoc(
	doc(db, 'posts', '1'),
	{
		views: increment(1)
	}
});

📝 This is more useful in NoSQL databases. You can also use increment(-1) for decrementing.

Count

Counting can get complex in Firestore, but generally, you want to count on the server.

SQL

SELECT COUNT(*) AS post_count
FROM posts
WHERE created_by = 1;

Firestore

const countSnap = await getCountFromServer(
  query(
    collection(db, 'posts')
  )
);
const postCount = countSnap.data().count;

📝 You need two separate calls to count a query and return the query results.

Other Count Aggregations

You can also do sum and average aggregations.

📝 min and max are not supported.

See Aggregation Queries.

Sorting by Count

You can’t sort by a count directly, so you must first count in Firestore.

SQL

Let’s say we want to get all users and them by post_count, or the number of posts they have.

SELECT users.*, COUNT(posts.id) AS post_count
FROM users
JOIN posts ON users.id = posts.author_id
GROUP BY users.id
ORDER BY post_count DESC;

Firestore

In Firestore, we must create the post_count with each post created in the user’s document. We can do this in a batch transaction.

  const userRef = doc(db, 'users/1');
  const postRef = doc(collection(db, 'posts'));

  const batch = writeBatch(db);

  batch.set(userRef, {
      postCount: increment(1)
  }, { merge: true });

  batch.set(postRef, {
      createdBy: auth.currentUser,
      createdAt: serverTimestamp(),
      title: 'Some post',
      content: 'post content here'
  });

  batch.commit();
}

And finally, we can sort the documents later.

query(
	collection(db, 'users'),
	orderBy('postCount', 'desc')
);

You can secure this in Firestore Rules or create a Firebase Trigger Function on onCreate that automatically increments and decrements the user document.

Counters get extremely complicated in Firestore. See Four Ways to Count in Firestore for niche cases.

Unique and Distinct

The only way to keep something unique is to set it as the document ID in Firestore. If you want a unique field, you must put it in two places in a transaction.

Unique Usernames

For uniqueness, we can save a username in the users collection on the user document and in a usernames collection in a single transaction. We can use Firestore Rules to secure this.

SQL

SELECT DISTINCT username
FROM users
ORDER BY username ASC;

Firestore

const batch = writeBatch();

batch.set(
    doc(`usernames/${username}`),
    {
        uid,
        username
    }
);

batch.set(
    doc(`users/${uid}`),
    {
        username
    },
    { merge: true }
);

await batch.commit();

If you update the username, also consider removing the old username in the usernames collection first.

query(
	collection(db, 'usernames'),
	orderBy('usernames')
);

Distinct Countries

If you want to search for all countries users belong to, you must also store a unique countries collection.

SQL

SELECT country, COUNT(*) as user_count
FROM users
GROUP BY country
ORDER BY user_count DESC;

Firestore

const batch = writeBatch();

batch.set(
    doc(`countries/${country}`),
    {
        uid,
        country
        userCount: increment(1)
    }
);

batch.set(
    doc(`users/${uid}`),
    {
        country
    },
    { merge: true }
);

await batch.commit();

You could also create the countries using a Firebase Trigger Function. You should also consider keeping a count of the number of users in each country. This could come in handy if you want to search for the most popular country your users belong to.

query(
	collection(db, 'countries'),
	orderBy('userCount', 'desc')
);

🖋️ The common theme you start to see is that Firestore requires you to create your database for the queries instead of vice versa.

Like

There is no LIKE equivalent in Firestore.

SQL

SELECT country
FROM users
WHERE country LIKE 'Uni%';

However, there is a startsWith hack you can use.

Firestore

export const startsWith = (
    fieldName: string,
    term: string
) => {
    return [
        orderBy(fieldName),
        startAt(term),
        endAt(term + '~')
    ];
};

This function can be used to find a string that starts with a term. It only works in parent objects and does not work with arrays or objects.

query(
	collection(db, 'users'),
	...startsWith('country', 'Uni')
);

Use an external database or store your search index inside Firestore if you want Fuzzy Searching capabilities. You can do this with Vectors or a custom function like Soundex.

Inner Joins

You can easily do an inner join in Firestore by querying two different collections, but this will cost you an extra document read and could potentially slow down your app.

Many-to-One

The class example is adding user information to a post.

SQL

SELECT 
  p.id AS post_id,
  p.title AS post_title,
  p.content AS post_content,
  jsonb_build_object(
    'id', u.id,
    'name', u.name,
    'email', u.email
  ) AS created_by
FROM posts p
JOIN users u ON p.created_by = u.id;

You could easily do this without JSON, but I wanted an equivalent object.

Firestore

addDoc(
	doc(db, 'posts', 'post-id'),
	{
		title: 'some title',
		content: 'some content',
		createdBy: {
			uid: auth.currentUser.uid,
			displayName: 'auth.currentUser.displayName,
			username: 'get username from user document or custom claim'
		}
	}
});

This could be done in many ways, but the query is easy.

getDoc(
	doc(db, 'posts', 'post-id-here')
);	

Again, you need to consider when a user updates their username, displayName or other profile information. You should create a Firebase Trigger Function to handle it.

Many-to-Many Tags

Here, we have posts and tags. Each post can have many tags, but you won’t have an infinite number of tags on a post. Firestore allows you to store data as arrays and search based on the array. An array works perfectly in this case.

SQL

In SQL, we may have a tags table with the user_id.

SELECT p.id AS post_id, p.title, p.content, t.tag
FROM posts p
LEFT JOIN tags t ON p.id = t.post_id
ORDER BY p.id;

Or, we may have a junction table post_tag.

SELECT p.id AS post_id, p.title, p.content, t.name AS tag_name
FROM posts p
JOIN post_tag pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
ORDER BY p.id;

Firestore

In Firestore, we must create the tags in two different places, similar to the unique index.

const batch = writeBatch(db);

const tags = ['tag1', 'tag2'];

// add tags array when adding post
batch.set(
	doc(db, 'posts', 'some-post-id'), {
  tags,
  title: 'some title',
  content: 'some content',
  createdBy: auth.currentUser.uid,
  createdAt: serverTimestamp()
});

// create a tag document
tags.forEach(tag => {
	batch.set(doc(db, 'tags', tag), {
	  postCount: increment(1)
	}, { merge: true });
});

await batch.commit();

🖋️ We must also remember to decrement the postCount on tags when a post is modified or deleted. This should be done with Firebase Functions, as Firestore Rules would get too complex for multiple tags. Firestore Rules also do not have loops.

query(
	collection(db, 'posts')
);

The tags will already be attached to the post document.

Complex Queries

Firestore does not handle complex queries, but there are workarounds for most use cases. This post only scratches the surface.

• See my Firebase Tutorial for more cases.

Documents vs. Tables

A NoSQL database is quite different from an SQL database. You must think about your queries before you even create a document. Once you learn the patterns, you can create any structure.

🌐 I am still a firm believer that all data is relational!

But, Firebase allows you to create an app faster than any database platform I have used. Firestore is amazing in this aspect.