Connecting to Airtable using Sync Inc on DronaHQ

You would have used the Airtable bases to manage your data. However, there are some challenges or limitations when using Airtable databases. Often it can be difficult to fetch data consistently when using Airtable API. So let us consider another method to get the Airtable data into a Postgres database that provides an easier method to carry out different operations on your data using Sync Inc.

Adding an Airtable base

Let us first consider a sample database namely Product Catalog from Airtable’s templates.

Let us add an Airtable Product Catalog template. Optionally you can also make use of your existing bases from your Airtable Workspace :

  1. Log on to your Airtable workspace and click Add base> Start with a Template to select the Template from the available Templates.
  2. Select the Product Catalog template. It is added to your workspace. Click to view data.

Setting up Sync Inc account

For improved performance, you want to turn the above Airtable Base into a Postgres database that works with DronaHQ Studio. So let us replicate the Airtable database using Sync Inc into a Postgres database.

  1. Go to https://app.syncinc.so and create an account.

  2. Click Add Sync to add any of the platforms to get your data.

  3. You would be able to view the supported platforms, Airtable, Stripe, Twilio, and so on.

  4. Select Airtable for this example.

  5. Enter your Airtable API key from the Airtable Account page.

  6. When you enter the API key, all your available Bases from Airtable would be listed out. Select the one that you want to connect.

  7. Select the Product Catalog base and the tables. You can Toggle switch ON to Sync all tables or else switch it OFF to select only a few of them.

  8. Select Destination. Choose an Existing location or Launch a new one.

  9. Click Create. The selected database and its tables are then synced to a Postgres database. The Database credentials are now ready for your use. The Connection string, the host, port, database, schema, and so on. Now when you want to connect from your DronaHQ Studio app, you can make use of these credentials to connect.

DronaHQ Studio DB Connector configuration

After you have your Airtable or any other database in Sync you can use the Postgres connector to use your data.

  1. Under Studio Console left menu bar, select Connectors and select the Postgres connector.

  2. Enter the Connector name and provide the Connection String or enter the Host, Port, Database name, Database username, and Password for your Sync Inc database. You can copy and paste these from Sync Inc. Test the Connection. If the configuration is successful, click Save.

Now the Connector is configured and you can make use of the data as required. With DronaHQ Studio’s Connectors, you can design your app as per your wish to best suit your needs using SQL querying effectively.

Now whenever you need to use the data in your App, you can access data properly as all the data is transformed by Sync Inc into a proper Postgres Database. You can view the database schema as well as the column types using the Query Builder.

Displaying Data

Let us take a simple example to display the Furniture data in a table grid control. Now that you have configured your connector, you can see the Postgres connector under the Connectors list. Let us now add a query that would help us to get the data from your Postgres Database. If for example, you want to display data for Furniture under the type as Lighting, you can define your query accordingly.

Under your Postgres connector, click Add Query, and specify your query using the query builder. Name the query and Save it.

Here you can see that the data is displayed for the Furniture table.

Add a Table grid control to your form and use the Bind Data to fetch data from the Connector using the connected account.

You can then select the key from the available list and display data in the table grid control. If you want to use conditional formatting, you can apply it to display data accordingly.

Here the data is coming live from Airtable and is easily searchable. With Sync Inc enabling ease of access to your Airtable data and the easy interface that DronaHQ Studio offers by means of Connectors, you now have your data at hand and can easily transform it for a proper presentation.

Writing data to Airtable from DronaHQ Studio

You have fetched data from Airtable using the Postgres database. Now let us add data to the respective table in Airtable. Sync Inc provides Postgres read-only database to read Airtable data. If you want to use if for writing data into an Airtable table, then you can do it using Sync Inc Proxy API. You would first have to add another REST API and make use of the Airtable API to Add or update data from the database.

Using the Sync Inc Proxy

Now that we are getting data from Airtable using Sync Inc, you would be aware that it promotes one-way data flow. So in this case we will make use of a Sync Inc Proxy to write data to Airtable. The proxy lies between your app/code and the Airtable API. This enables you to write data with Create, Update, or Deletes to both Airtable and Sync Inc databases simultaneously.

Thus if you want to use the Sync Inc Proxy you would have to make a small change to the hostname https://proxy.syncinc.so/api.airtable.com instead of https://api.airtable.com for all your Airtable API. This sends the request through the Sync Inc Proxy and they are applied to your Postgres database as well as Airtable database.

So let us first add a REST API from the Connectors to register the Sync Inc proxy API. Add a GET method for the API endpoint with the parameters as shown below. So to configure the Airtable API:

  • Select Authentication as API Key Authentication.
  • Under API Keys add the API key from the Airtable account as Authorization > Bearer to be added in Header.
  • For your Airtable Base you need to append the Base ID to the following URL: https://proxy.syncinc.so/api.airtable.com/v0/{YOUR_BASE_ID}

It will be as seen in the illustration below.

  • Test Connection and if it is successful, then click Save. You Sync Inc Proxy is now registered.

Once your proxy gets registered you do not need to add the authorization again for your other API endpoints and methods thereof.

Now let us add an API to input data to your Airtable database.

If you want to add the dynamic values, you need to define the variables to the Body Parameter for the POST method as seen in the example below. So during this configuration provide the test values and Test it to see if the API endpoint is successful.

If the test is successful click Save. Your REST API is configured for data addition.

Now add a Form to enter data. For the moment let us only add the Name, Notes, and Product type dynamically for the new record. We will also add an Action button. The Action flow would be triggered on the click of the Action button. In the action flow, add a new Task to use the REST API and add your configured endpoint according to your requirement.

You can see from the example that we are sending the product name and the notes that we accept from the form and pass it on to the API.

So now whenever you run the form and add the data you can see the record is added to your Airtable data.

Now if you check your Airtable table you can see the record is added there.

You can use the registered API for any other data actions as well. Utilize the power of SQL queries with Sync Inc Proxy, to build custom apps on your Airtable data in no time. Sync Inc supports Airtable, Stripe, Close, Twilio, and Shopify at the moment which can give you amazing capabilities to build your custom apps.