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

Help Video

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.

Submissions made through the Clappia mobile app will also sync to the database in real-time, just like in the web version.

‍

‍

‍

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.

‍If a submission is edited in Clappia, the changes will reflect in the database only if the table has a primary key set. The primary key helps match the correct record and update only the modified fields. Without it, updates will not sync, and the original data in the database will remain unchanged.

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));

‍

Ensure that the data types of fields in Clappia match the corresponding fields in your database. For example, if a field is set as an integer in the database, it should not contain text in Clappia. Mismatched data types can cause submission failures, preventing records from syncing properly.

Disabling Database Integration

If you disable the Database Integration, any new submissions made in Clappia will not be pushed to the database. If you re-enable the integration, you must authenticate it again. However, any submissions made while the integration was disabled will not be retroactively synced to the database.

Bulk Uploading Data

If you need to bulk upload data into Clappia, you can use the bulk upload feature in the Submissions tab to upload data into clappia which will be pushed into the database automatically.

Archiving/Deleting Submissions

If a submission is archived in Clappia, it will also be removed from the database. Restoring an archived submission will add it back to the database. However, if a submission is permanently deleted, it will not sync again unless a new submission is created.

Whitelisting IPs for Database Integration

When enabling database integration from Clappia, users may encounter connection issues due to their database restricting external IPs or blocking certain IP addresses by default. To successfully connect to Clappia, you must whitelist the following IP addresses in your database firewall settings:

• 15.197.176.43
• 3.33.248.224
• 13.127.107.115

Reattempt the integration from Clappia after making these changes.

Note:

• If the IP addresses are not whitelisted, authentication attempts may fail, and you may see an error message.
• In case the issue persists despite whitelisting the IPs, you can raise a support request to Clappia Support for further assistance.

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.