This tutorial will guide your first steps in Jetspike and teach the basic functions
What will you learn
First look around
After opening Jetspike and signing in, you will see something like this:
1) Query editor is where you write your SQL.
2) In the code generator window, node.js code for your query is generated in real time. All changes in the SQL show immediately as you write.
3) After running the query, the results show in the lower part of the screen. In order to run a query, we first need to connect to Firestore, which we will do in a short while.
4) The project menu shows all the collections containing documents in your Firestore project and all your stored views.
5) The project selector in the upper part of the screen displays all the Firestore projects added to your account.
Connecting to Firestore
To connect to your cloud database, you need to import a service account private key. See this tutorial on how to obtain a Firebase private key an how to manage access permissions.
After connecting to Firestore, new project will be created in your user account. The left side menu will be populated with collections in your database, if there are any.
Clicking on the collection name will pre-generate a query to select all documents in the collection.
Running a query
Our first pre-generated query looks like this:
SELECT _documentId, *
If this doesn’t make much sense to you, don’t worry, this tutorial explains the basic anatomy of a SQL query. For now, we will just click the Execute button in the middle of the screen. After finishing the execution, the lower part of the screen is populated with results.
Congratulations! You have just run your first SQL query on a cloud database!
Query with conditions
Some collections can contain thousands or millions of records and we don’t always want to return all of them. In such cases we constrain the query with conditions. The following query only returns the coins worth more than one dollar per coin:
SELECT _documentId, *
WHERE price >= 1
After clicking Execute, new filtered set of results will be returned. We recommend this article for more information on how to use conditions.
You can edit documents by clicking the row number. This will open the document editing popup window.
You can add, modify and remove fields of the document here. It is also possible to created nested structures with lists and maps.
Clicking the two icons next to the document ID will duplicate or delete the currently selected document.
You can also insert new documents manually by clicking the plus icon that appears when you hover with your mouse cursor over the collection name in the left side menu.
While manual editing and creating documents can be very convenient in certain situations, sometimes we want to insert, modify or delete large number of documents in batch. In the next section, you will see how to do it programmatically with SQL.
Modifying data by SQL
Jetspike allows you to insert, update and delete documents programmatically based on defined conditions. When you hover with your mouse over the collection name in the left side menu, three icons will apper that help you to pre-generate SQL queries for INSERT, UPDATE and DELETE.
Here are some examples to modify data in your cloud database:
INSERT INTO currencies (code, name, price)
VALUES ('XX', 'myCoin', 1.256)
Insert a new document into the collection currencies with defined values. You can even insert multiple documents in batch by providing multiple sets of values.
UPDATE currencies SET price = 21380.1 WHERE name = 'Bitcoin'
Set the price of Bitcoin to 21 380.1. This modifies the value of an field in an existing document.
DELETE FROM currencies WHERE price < 1
Delete all coins with price less than 1.
For more information on modifying values in your Firestore, we recommend this article.
Firestore is a NoSQL database, unlike traditional relational databases like SQL. NoSQL databases have excellent performance and scalability, but this comes at the costs of limited features. Jetspike provides this functionality on top of Firestore to give you the powerful tools you are used to with traditional SQL databases.
Nevertheless, NoSQL is a different paradigm and requires different ways of thinking. We recommend this tutorial summarizing the main differences and strategies for designing advanced queries.
We also recommend following tutorials on standard SQL functionality, that is normally not available in Firestore:
– GROUP BY
Firebase enables you to substructure your documents using Lists and Maps. This is normally not possible with SQL. Read this tutorial to find out how to utilize them with Jetspike.
You can get inspiration from our advanced example demonstrating the use of Jetspike to solve real-world business problem.