Integrate External Databases with Clappia

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.

Understanding Primary Key Requirements for Different Databases

When your Clappia app is connected to an external database, the primary key in your database table plays an important role in how submissions are handled.

A primary key helps the database uniquely identify each record. 

For example, this could be an “ID” column where every record has a different value (such as 1, 2, 3, and so on). No two rows have the same value in the database table.

Depending on the database you are using, the requirement for a primary key differs slightly when you create, edit, delete, or resync submissions.

The sections below explain this behavior clearly for each supported database.

MySQL / PostgreSQL

Creating a New Submission

When a new submission is created in Clappia, a new record is created in your database table.

For both MySQL and PostgreSQL, a primary key is not required when creating a new submission. The record will be inserted successfully even if a primary key is not mapped for create operations.

Editing or Deleting a Submission

When you edit or delete a submission in Clappia, the database must know exactly which record to update or remove.

For this reason, a primary key is required for Edit and Delete operations.

Without a primary key in the table, Clappia cannot identify the correct row in the database, and the changes will not reflect properly.

Using Resync

When you use the Resync option, Clappia attempts to match existing submissions with existing database records.

To do this accurately, a primary key is required.

Without a primary key, the system cannot determine which database record corresponds to which submission, which may result in duplicate entries instead of updates.

MySQL - Duplicate Primary Key Value

If a new submission tries to insert a primary key value that already exists in the MySQL table, the database will reject it.

For example, if the table name is EMPLOYEE_MASTER and there is already an entry where the column EMPLOYEE_ID has the value 10, and a new submission is made in Clappia with EMPLOYEE_ID = 10, then the following error will appear in App logs:

Failure Reason: Duplicate entry '10' for key 'EMPLOYEE_MASTER.PRIMARY'

This means the primary key value already exists in the table, and MySQL does not allow duplicate primary key values.

To avoid this error, ensure that primary key values remain unique.

PostgreSQL - Missing or Invalid Primary Key During Create

If the table name is EMPLOYEE_MASTER and the primary key column is EMPLOYEE_ID, and a new submission is created in Clappia without providing a value for EMPLOYEE_ID, then the following error will appear in the logs:

Failure Reason: null value in column "EMPLOYEE_ID" of relation "EMPLOYEE_MASTER" violates not-null constraint

This means that the EMPLOYEE_ID column does not allow empty values, and a value was not provided when the submission was inserted.

To resolve this, ensure that a valid value is provided for the primary key column when creating the submission.

Azure SQL

Identity Column Setting

In Azure SQL, if your table contains an identity column and IDENTITY_INSERT is set to OFF, record creation will fail when Clappia attempts to insert data.

To allow records to be inserted successfully, you must run:

SET IDENTITY_INSERT <tableName> ON

After enabling IDENTITY_INSERT, the record will be created successfully in the database.

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.

Using the Sync Option

To push submissions that were made to Clappia while the Database integration was disabled to the database, use the Sync option available under the Advanced tab in Database Integration.

When Sync is clicked, Clappia will attempt to push all existing submissions in the app to the connected database table, including those created while the integration was disabled.

Important Note:

For the Sync process to work correctly, the database table must have a primary key. If a primary key is not defined, the system will not be able to correctly identify existing records, and duplicate entries may be created instead of updates.

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.