Database Integration

Overview

This article explains the setup for real-time integration of Clappia Submissions data with your own Database. In this article we have taken a sample use case of maintaining an Employee Master App on Clappia. The app has basic fields like Employee ID, name and other employee details. All submissions made into this App on Clappia will be synchronised to a MySql Database. Other databases that are supported via this integration are Azure SQL and Postgres.

Summary

Following topics are covered in this article:

1. Designing the Clappia App
2. Enabling data sync from Clappia to the MySql Database
3. Verifying data sync for new submissions and edited submissions

‍

1. Designing the Clappia App

An app with the same fields - Employee ID, name etc is created on Clappia using the simple data input blocks. 

‍

2. Enabling data sync from Clappia to the MySql Database

A. Go to the App in Edit Mode -> Configuration -> Database Integration.

‍

B. Switch on the Enable data push to your Database option and enter the database connection details. Please note that integrations are supported only with MySql, Postgres and Azure SQL databases as of now.

C. Once all the connection details are entered, click on Authenticate. If all the details are correct and the database is accessible, the Authentication will succeed and you will find a list of tables available in the database.

D. Select the table that we created in the first step. You will see a list of all the columns of the table. You can map the fields from the Clappia App to the Table columns in this step. Once you are done, save the details. 

3. Verifying data sync for new submissions and edited submissions

Once the integration setup is done, create some new submissions in the Clappia App. You will be  able to see the same employee records in the MySql database as well.

‍

‍

‍

Now try editing some fields of both these submissions (In the example, we have changed the Address field value). The same changes will reflect in the MySql table also.

‍

‍

Configuring MySql Database Table Schema

‍

A. Login to your MySql Database with the following command:

> mysql -h clappia.mysql.database.azure.com -u clappia  -p

NOTE: The database should be publicly accessible and should not be a localhost address.

‍

B. Create a table named EMPLOYEE_MASTER using the following query. EMPLOYEE_ID is marked as the primary key in this table.

‍

mysql> CREATE TABLE EMPLOYEE_MASTER (EMPLOYEE_ID varchar(255), EMPLOYEE_NAME varchar(255), EMAIL varchar(255), ADDRESS varchar(255), PRIMARY KEY (EMPLOYEE_ID));

‍

In this article, we saw how to sync submissions in a Clappia App to a MySql database in real-time. Steps are similar for other databases like Postgres and Azure SQL.

Going forward, we will be supporting other databases like Oracle,, SQL Server etc as well.