Moving Airtable to SQL


#1

Has anyone figured out how to move an AirTable table onto an SQL database. I use Air-table for everything, but I’m starting to run into issues with the 100 record limit that Airtable imposes. Unfortuntely, it is a proprietary plugin making the API requests and it generates an error when the user retrieves over 100 records at a time (leaving many records blank).

Does anyone have codebase or ideas on how to ‘sync’ Airtable records into an SQL database, which can then be queried by a web app that displays dynamic content. I don’t want to move away from Airtable as a CMS because its so easy for other project collaborators to use for data entry.


#2

Hi Joe!

It’s not an option to make the web app fetch the data directly from Airtable I suppose?

If not, I’m working on a tool that will let you export data in XML from Airtable. Drop me a line in case you’re interested. We can probably work something out.

Best, Arthur.


#3

Out of curiosity, what language/platform/framework does this proprietary plugin run under?

The problem with “syncing” to a SQL database is that while the API exposes the RECORD_ID() and CREATED_TIME() it doesn’t currently have a way of retrieving MODIFIED_TIME() or otherwise filter by changed/updated records. This means you’ll have to “resync” everything at some interval.

And until the Airtable metadata API is released, you’ll have to create the SQL tables/fields manually in order to match and maintain the number of fields and the types of fields. So while syncing to SQL is fairly straightforward, the benefits of doing so are actually quite limited.

What I have done on several Wordpress websites is cache Airtable requests locally to ensure a speedy response. This way I can use the filterByFormula parameter in the API to make quite complex requests and then cache the entirety of the JSON result using a hash of the original request. The end result is that Wordpress can serve data quickly from it’s own local SQL database, but I get all the goodness of Airtable’s interface for managing data. The only “drawback” is the learning curve of switching from SQL queries to Airtable filterByFormula—but it’s not so bad with their great documentation.

Long story short, get the developer to fix the plugin or get a better plugin. And if you can’t serve data directly from the Airtable API, don’t “sync” it—cache it so that you can take full advantage of the Airtable GUI and API while getting local database read speeds.


#4

@Chester_McLaughlin Wow, this was excellent advice! I was able to to significantly limit the records being requested by creating both views in Air-table and using filterByFormula via the API. The filterByFormula request used RECORD_ID() to pull only records from an “item details” table that matched records in the “order submissions” table (i.e. rather than requesting the detailed info of all items, we only requested the detailed info for items that customers actually ordered).

Thanks a ton for the help.


#5

Another quick question: one the rare occasion (it has happened several times) that over 100 orders come in at once. Is there a conditional parameter to pass into the API call (similar to Maxrequest,etc.) that would return 100 records --> ‘wait five seconds’ --> and then re-query any remaining records?

(I’m guessing this would have to be done server-side)


JSON string only contains 100 rows when connected to Power BI Desktop
#6

From the API Docs:

The server returns one page of records at a time. Each page will contain pageSize records, which is 100 by default. If there are more records, the response will contain an offset. To fetch the next page of records, include offset in the next request’s parameters.

So in my code (which is server side PHP), I do a loop at the bottom of which I check if the returned offset is null or not. If it is NOT null, then I let the loop continue with the new offset. Eventually (depending on just how many records match your filter or are in your view/table) the offset isn’t returned, or is NULL, thus breaking the loop.

So no waiting is required, each result contains the information required to determine if an additional request is needed or if all the result have been returned.

Here’s how I do it in PHP

    <?php
    // Initialize the offset.
    $offset = 0;

    // Initialize the result set.		
    $records = array();

    // Make calls to Airtable, until all of the data has been retrieved...
    while (!is_null($offset)):
    	
    	// We're using an offset to get specific batches of records.
    	if ($offset > 0){
    		$http_params["offset"] = $offset;	
    	}
    	
    	if (isset($params["view"])){
    		// And we're specifying a view. The API will honor any filters 
    		// that have been applied to the view, as well as any sort
    		// order that has been applied to it.
    		$http_params["view"] = $params["view"];
    	}
    	
    	if (isset($params["fields"])){
    		// By default, the APi will return every column/field
    		// You can specify smaller specific fields only
    		// using this query parameter, as we are here.
    		$http_params["fields"] = $params["fields"];
    	}

    	if (isset($params["limit"])){
    		// By default, the APi will return 100 records per request.
    		// You can specify smaller batch sizes using the "limit"
    		// query parameter, as we are here.
    		$http_params["limit"] = $params["limit"];
    	}
    	
    	if (isset($params["sort"])){
    		// We're also specifying a sort order for the request,
    		// which will override any sort order that has been 
    		// applied on the view.				
    		if (is_array($params["sort"])){
    			$http_params["sort"] = $params["sort"];
    		} else {
    			$http_params["sort"] = array($params["sort"],"asc");
    		}
    	}

    	if (isset($params["filterByFormula"])){
    		$http_params["filterByFormula"] = $params["filterByFormula"];
    	}

    	// Specify the URL to call.
    	$curlopt_url =   $this->AIRTABLE_API_URL
    		           . $this->APP_ID
    		           . '/' . rawurlencode($endpoint)
    		           . "?" . http_build_query($http_params);

    	$args = array(
    	    'timeout'     => 30,
    	    'redirection' => 5,
    	    'blocking'    => $blocking,
    	    'headers'     => $http_headers,
    	    'cookies'     => array(),
    	    'body'        => null,
    	    'compress'    => false,
    	    'decompress'  => true,
    	    'sslverify'   => true
    	);

    	$response = wp_remote_get( $curlopt_url, $args );

    	// When getting a table, we'll build an array of records,
    	// when getting a record, we'll just return the record.
    	if (isset($body["records"])){
    		$records = array_merge($records,$body["records"]);	
    	} else {
    		$records = $body;
    	}

    	// Adjust the offset.
	// Airtable returns NULL when the final batch of records has been returned.
	$offset = (isset($body["offset"]))? $body["offset"] : null;

endwhile;
?>

#7

Excellent, problem solved. Thank you!

I like the use of offset like @Chester_McLaughlin just posted. It’s also worth checking out @Alexander_Sorokin 's post on pagination. He mentions that as an alternative to using an offset, one might try using “fetchNextPage” when a user clicks on a “next” or “load more” button.


#8

Hi everyone,

Quitely moving away from the subject, but only quitely :
if you ever want to stop using AirTable, and to move from AirTable to an SQL database, is it fully possible ?
Or can you only export .CSV files and make the migration “manually”, as for importation ?

Cheers !
Manon


#9

Completely possible.

Aside from using the API to automate the whole transition, you can use formula fields to expose each record’s RECORD_ID() and use lookup fields to expose the RECORD_ID() of any related records. Then it’s a matter of rebuilding your data structure in SQL (plus any required “join” tables) and creating queries from each CSV record using the exposed RECORD_ID() as the primary key for each record.

More importantly though—why would you ever want to leave Airtable!!! :grin:


#10

hello, would you be interested in creating a web app to sync airtable with shopify? we want to enter all the shopify data including photos, title, description, dimensions, location, sku, etc. and have them sync with the corresponding shopify meta data fields. stephen


#11

Sorry… let’s move this discussion to a private message.


#12

Hi Arthur,

I still look for a way to use the airtable API for Excel or Word. An export to XML would be great. Could you please let me know more about your solution? Thank you.

Best,
Mathias


#13

PM sent.

[20 char filler]


#14

Hi Arthur, we are interested as well in an export to xml!


#15

Hi Arthur - have you found a solution? :grinning: Best, Babs


#16

Hi there!

I’m not following this thread, so make sure to send me a private message if you have a request. :slight_smile:

I’ll send you a message in a minute…


#17

Hi Arthur !

I’m looking for a solution as well ! If you can help me :wink:


#18

Hi there,

I just created this topic in the Work Offered thread, but wondering if anyone here could help?

In basic terms, I’m looking to generate an XML feed from a table…

Many thanks