SQL to Firebase – A Metamorphosis

Abstract– Firebase is being extensively used in cross-platform apps for various functionalities, one of them being Realtime Databases. This paper highlights how an RDBMS based database such as MySQL can be converted to a JSON based Firebase Realtime Database for easier readability, multi-device data access and manipulation, and cloud storage for a centralized database. Although MySQL follows a schema for the data and stores information in the form of tables and relations, Firebase stores information in a JSON tree. Conversion of data from the former to the latter depends on how the application queries the database. The paper covers the three basic queries – Insertion, Retrieval and Deletion of data.

Keywords-Firebase; JSON Objects; MySQL; Realtime Database;

With the app market booming and proving to be lucrative, many products and platforms are being launched to make the process of developing and maintaining these apps simple. Firebase, which is one such example, is a cloud based platform for mobile and web application development. It provides “backend as a service” [1] that includes analytics, real time database, remote configuration for apps, push notifications, multiple authentication types, hosting platform and Admob-which allows developers to monetize their apps by displaying advertisements to name a few. Notably, Firebase is expanding to become a unified app platform for android and iOS. Firebase allows developers to build high quality apps and mix and match the features available to meet their requirements. It is owned by Google and a host of new features were introduced recently in May 2016. [2]

The real-time database (a NoSQL database) provided by Firebase is what forms the basis of this paper. Data is stored as JSON (Java Script Object Notation) objects and synchronized in real time to every connected client and remains available even when the app goes offline. [3] It is designed in such a way that only operations that can be executed quickly are allowed which enables a great real time experience. The firebase storage is built for app developers who need to store and serve user generated content, and is backed up by google cloud storage, a powerful, simple and cost effective object storage service.

We now turn to relational databases, arguably the most popular kind of database, even today. Data is stored in tables (combination of rows and columns). The concept of schemas is implemented in relational databases. [4] Schema is the blueprint that defines how data is organized. SQL databases are strict to ensure integrity but lack flexibility. It can be difficult when initially developing because one might be unaware of the schema. Once the schema is decided, the developer needs to code accordingly. This can be tedious as designers are strictly bound by the schema. A NoSQL database, like the firebase database is schema-less which in turn provides a lot of flexibility. In summary, relational databases are inflexible but they provide data integrity whereas NoSQL databases are flexible but the data integrity is left to the developer.

The Firebase database stores data in the form of a tree. The root node can be compared to the database in case of RDBMS. The root node can have multiple child nodes as shown in figure 1.

Figure 1: Structure of a Firebase Database

The child nodes in turn can have multiple child nodes and so on. Data, as mentioned before is stored as a key value pair. To access a value, this tree needs to be traversed. [5]

Neelima Kuderu in her paper proposed Schema-Migration and Mapping Framework to support developers for automatically migrating relational databases to NoSQL while preserving the semantics of the original database [6]
A B M Moniruzzaman in his paper provides classification, characteristics and evaluation of NoSQL databases in Big Data Analytics. The study also provides an independent understanding of the strengths and weaknesses of various NoSQL database approaches to supporting applications that process huge volumes of data; as well as to provide a global overview of this non-relational NoSQL databases. [7]
Siim Plangi describes what Backend as a Service is, its advantages, disadvantages. Analysis of one such platform was performed with respect to an Android application and it is concluded that using such platforms is a great way to reduce development time and resources. [8]
Ryan Thompson in his paper attempts to determine the sustainability of cloud storage We also get an overview of evolution of cloud storage moving into the future, and attempt to determine whether integrations into cloud storage can be relied upon to transport vital information.  [9]
Navdeep Singh in his paper introduces everyone with Google firebase API and its features. [10]
Mital Potey in his paper discusses the study revealing that database management becomes considerably scalable, flexible and efficient when traditional relational databases are complimented by a specifically designed set of alternative databases such as NoSQL, NewSQL based systems. [11]
Keith W. HareKeith W. Hare in his paper reviews the features common to the NoSQL databases and compares those features to the features and capabilities of SQL databases. [12]

This section highlights the characteristics of Firebase that are not existent in MySQL.

1. Real-time Multi-user Access

The application is designed such that multiple people are able to access a centralized database simultaneously. The challenges that arise with maintaining a consistent state are taken care of. There are protocols in place in the event of a conflict. Rollback functions are implicitly called in the event of connection loss or drop to the database. [13]

B. Cloud Access

Data in all of its forms such as text and images are capable of being stored on the cloud. Since the cloud is the point of access, the database is readily available everywhere, provided the user has the right permissions. In the event that the application is offline, the last synchronized copy of the database is used by the application, hence aiding responsiveness. Once access is regained, the database synchronizes again with the main online database.

3. Cross-Platform API

Firebase can be integrated into various platforms that the application is built upon. Hence if one user records in the database, the updates are conveyed to all other uses, be those users are on an iOS, Website or Android platform. The APIs are bundled onto a single SDK hence the enabling the developer to focus less on the infrastructure and more on the customers’ needs. [14]

4. Online Storage

Firebase takes care of storage hence the data is hosted by them. This saves the developer from high investments on hardware for storage and the physical space occupied by it. In some scenarios, this is a vulnerability since the user does not have real custody over the data stored online. In most data-insensitive cases, this fact is overlooked by the user.

Firebase, like any other form of service has its fair share of shortcomings. Various application developers have spoken out elucidating the specific issues they have faced while incorporating it. Here are a few common complications:

A. High pricing with scalability

Firebase charges a fee for the services it provides based on the number of simultaneous connections, size of the data stored, bandwidth per month and automatic backups [15]. Free of charge services are limited up to fifty connections and a hundred megabytes of storage.

B. You don’t own your data

The data that is stored on the database is not owned by you. The bane being that actions such as exporting of the complete database cannot be performed by the user alone. Although this is possible by contacting the Firebase team to do it for you. [16] The boon is highlighted in section III (D).

C. Relations are proven to be tedious

Due to the structure of Firebase Realtime-Database, formation of relations has proven to be a nightmare to most developers. This is evident and problematic only when the amount of data is scaled multi-folds.

Despite all these limitations, Firebase continues to be used extensively to build Minimum Viable Products (MVPs) and first working prototypes at Hackathons and large scale integration by major application enterprises.

The section provides an insight as to the conversion of SQL queries to Firebase code. Since Firebase is a multi-platform supported tool, the code is expressed in terms of the logic and algorithm for easy transitioning into the respective programming languages.

A. Data Insertion

In MySQL, an entry into the database¹ takes place with a query such as:

-> INSERT into EMPLOYEES

-> (UID, Name, Department, Salary)

-> VALUES

-> (25412, Ajay Patel, Marketing, 45000);

Figure 2: A single entry into a SQL Database

This exact tuple can be represented in Firebase by administering the following algorithm:

• An instance of the database is retrieved by first initializing an object of the Database reference. The instance stores a location (usually in the form of a URL pointing to the online reference of the Database) returned by methods of a Firebase object.
• Now an instance is required to reference the immediate child of the database. Another object of the Database Reference is initialized to the location of the child.
  • The child is uniquely identified by the name (key)given to it.
  • In this case, the first immediate child is the table we are querying, i.e.- Employees.
  • The location is returned by the method of the instance created to reference the database earlier.
• Once an instance of the table is created and initialized, multiple instances are required to query the attributes of this table.
  • One reference each is initialized using the name to identify each attribute of the child.
  • The location of each of them is returned by the method of the instance created in step 2 for the table we are querying.
• Finally, each of these references are used to set the desired value required by the user.
  • A methodunder each of these references are called and the desired value is passed as a parameter to the method.

A visual representation of the Firebase structure in figure 3 is a tree with ‘Employees’ as the parent that represent the table and the tuple as a child with the unique reference (in this case, the value ‘25412’). UID is a primary key constraint in the MySQL database. Along the same lines, Firebase uses a key value (in this case UID = 25412) to uniquely identify a child of the parent ‘Employees’. As depicted, MySQL stores the data in the form of rows and columns, whereas Firebase employs a JSON tree format.

Figure 3: Firebase representation of a single entry.

In the case of multiple entries, MySQL uses the query aforementioned, multiple times to enter the data. The resulting table is shown in figure 4.

Each call counts to a single tuple containing the information required to query it.

Figure 4: Multiple tuples in a MySQL Database

Similarly, multiple children are entered into the Firebase database one tuple at a time. Each tuple is encapsulated in the form of a single JSON (JavaScript Object Notation) object.  This is similar to the insertion query in MySQL except the tuple is a single object passed to the database reference.

Conversion of tuple data to a JSON object is done using a concept in OOPs (Object Oriented Programming System) called POJO (Plain Old Java Object) class [17]. The data is passed to the getter methods of the object created for that class, each of which is defined for the attributes.

Once the values of the object are set, the object is passed to the reference defined earlier and the database is updated.

Note that each object holds only the attributes of a single tuple.

Figure 5: Multiple children in Firebase Structure.

B. Data Retrieval

In MySQL, data can be retrieved using the ‘select’ statement. Constraints can be defined in the statement to show only a certain tuple(s). The following query displays an output as shown in Figure 6.

->SELECT * from EMPLOYEES WHERE UID=32121;

Figure 6: Retrieval of a tuple based on condition in MySQL.

Firebase provides the functionality of triggering a certain list of methods that notifies the application when data is modified. The method used for this feature takes the location of the child to be monitored and is termed as a ‘listener’. The sequence of data retrieval is as follows:

• The listener is defined from the Firebase reference created previously. The listener has various methods available that enable the developer to employ them for other functionalities.
• Under the method that is implicitly called whenever a child is added, one of the parameters is the data snapshot that is downloaded from the cloud.
• Using this data snapshot, the children are identified using their unique IDs and subsequently stored onto variables such as Strings and Integer values.

Figure 7: Retrieval of child node with UID = 32121.

C. Data Deletion

MySQL makes use of ‘DELETE FROM’ along with a ‘where’ clause to determine the entry to be deleted. The following query results in a database as depicted in Figure 8.

->DELETE FROM EMPLOYEES WHERE UID=63212;

Figure 8: Resultant database after the query is performed.

The equivalent of the function in Firebase is as follows.

• The Firebase reference of the child that needs to be deleted is determined beforehand by identity of the unique ID.
• Using this reference, a method used to delete the complete child and its reference is called.
• All the attributes and children under this reference are removed.

Figure 9: Firebase database after the entry is deleted.

Firebase continues to be the leading BaaS for cross-platform applications that require a real-time database hosted on the cloud. Features and updates are continuously being introduced by Google to overcome the few flaws exhibited by it. Applications built on relational databases that are looking to move to the cloud now have the ability to do so.

Firebase has a growing support on various mobile platforms such as iOS and JavaScript based web applications. According to the official Google Blog [18], a few of the future goals of the platform include- lower and simpler pricing, Android and iOS SDKs (Software Development Kits) receive constant offline support and a much-improved UI (User Interface). Overall, Firebase will hopefully continue to provide developers with the tools and support required to radically change how app development is done.

Acknowledgment (Heading 5)

We would firstly like to thank Prof. H D Phaneendra, Head of the Department of Computer Science and Engineering, NIE, Mysore for giving us support and opportunity to work on this paper. We would also like to thank Prof. M.J. Yogesh, Department of Computer Science and Engineering, NIE for his vital insights and guidance. We also extend our warm regards to all the faculty members of the department for their constant encouragement and invaluable advice.