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.
⚠️ 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.
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.
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.
Log in
