Recently we were working on the angular app using firebase. Our client wanted to sync data between firebase to MYSQL. After some R&D we decided to use firebase events to capture changes done in firebase database and then save into the MYSQL. In this article, we have demonstrated how we can data sync firebase to MySQL?
Firebase Overview: Firebase is a real-time NoSQL database. Firebase gives you functionality like analytics, databases, messaging and crash reporting so you can move quickly and focus on your users. Firebase is built on Google infrastructure and scales automatically, for even the largest apps.
Data sync Firebase to MYSQL:
Let’s sync Users table from Firebase to MYSQL database. Follow the steps to sync Firebase database to MYSQL database.
Step 1: Create Users table according to Firebase table structure into MYSQL. For Example
Create the MySQL table Users with the age, firstName, lastName, and phone field using below command:
create table User ( id INT NOT NULL AUTO_INCREMENT, age INT(10) NULL, firstName VARCHAR(250) NULL, lastName VARCHAR(250) NULL, phone VARCHAR(250) NULL PRIMARY KEY ( id ) );
Step 2: Listen to Firebase Event and Insert data into MYSQL table
We need to write a nodejs function which listens to firebase event and according to insert data into MYSQL table. Firebase automatically fires events if any changes ( add/delete/update ) occur on firebase database.
For example: If new user “120I1aJIkeHPOdG4V896xnmvjrqaws” added to the firebase Users tree then firebase fire an event with the newly created objectData ( snapshot ). Our nodejs function listens to the event and inserts the data snapshot into the MYSQL data. Please see the sample for nodejs:
//sync Users
exports.syncUsers = functions.database.ref('/Users/{userId}')
.onWrite(event => {
// Grab the current value of what was written to the Realtime Database.
var userId = event.params.userId;
var eventSnapshot = event.data;
// Exit when the data is deleted.
if (!event.data.exists()) {
console.log("DELETE User by Id:" + userId);
var DELETE_USER_SQL = "DELETE FROM `Users` where `userId` = ?";
var params = [
userId
];
var connection;
return mysql.createConnection(dbconfig).then(function(conn){
connection = conn;
return connection.query(DELETE_USER_SQL, params);
})
}
console.log("INSERT/UPDATE User by Id:" + userId);
var INSERT_USER_SQL = "INSERT INTO `Users` (`userId`, `age`, `firstName`, `lastName`, `phone` ) VALUES (?, ?, ?, ?, ?)";
var params = [
userId,
eventSnapshot.child("age") ? eventSnapshot.child("age").val() : null,
eventSnapshot.child("firstName") ? eventSnapshot.child("firstName").val() : null,
eventSnapshot.child("lastName") ? eventSnapshot.child("lastName").val() : null,
eventSnapshot.child("phone") ? eventSnapshot.child("phone").val() : null
];
return mysql.createConnection(dbconfig).then(function(conn){
connection = conn;
return connection.query(INSERT_USER_SQL, params);
});
});
