Moving Airtable to SQL

Joe_Cool · 2016-08-17T08:10:15+00:00

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.

T

+14

Tuur
Inspiring
366 replies
8 years ago
August 17, 2016

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.

C

+14

Chester_McLaugh
Inspiring
140 replies
8 years ago
August 17, 2016

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.

T

J

+2

Joe_Cool
Author
Known Participant
16 replies
8 years ago
August 18, 2016

Chester_McLaugh wrote:

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.

@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.

J

+2

Joe_Cool
Author
Known Participant
16 replies
8 years ago
August 18, 2016

Chester_McLaugh wrote:

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.

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)

C

+14

Chester_McLaugh
Inspiring
140 replies
8 years ago
August 18, 2016

Joe_Cool wrote:

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)

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

R

J

+2

Joe_Cool
Author
Known Participant
16 replies
8 years ago
August 19, 2016

Chester_McLaugh wrote:

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

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.

M

Manon_Leger
New Participant
1 reply
8 years ago
September 7, 2016

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

B

C

+14

Chester_McLaugh
Inspiring
140 replies
8 years ago
September 7, 2016

Manon_Leger wrote:

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

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!!! :slightly_smiling_face:

V

D

S

stephen_keeney
New Participant
1 reply
8 years ago
November 5, 2016

Chester_McLaugh wrote:

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.

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

P

C

+14

Chester_McLaugh
Inspiring
140 replies
8 years ago
November 7, 2016

stephen_keeney wrote:

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

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

R

M

Mathias_Fritsch
New Participant
1 reply
7 years ago
September 20, 2017

Tuur wrote:

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.

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

T

+14

Tuur
Inspiring
366 replies
7 years ago
September 21, 2017

Mathias_Fritsch wrote:

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

PM sent.

[20 char filler]

A

G

GUILLAUME_RATEL
New Participant
1 reply
7 years ago
October 3, 2017

Tuur wrote:

PM sent.

[20 char filler]

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

B

Babs_Peterson
New Participant
1 reply
7 years ago
October 21, 2017

Tuur wrote:

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.

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

A

T

+14

Tuur
Inspiring
366 replies
7 years ago
October 23, 2017

GUILLAUME_RATEL wrote:

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

Hi there!

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

I’ll send you a message in a minute…

A

Angelo_Blot
New Participant
1 reply
7 years ago
October 31, 2017

Tuur wrote:

Hi there!

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

I’ll send you a message in a minute…

Hi Arthur !

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

H

Harvey_Mason
New Participant
3 replies
7 years ago
January 15, 2018

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

R

Rashmi_Naidu
New Participant
1 reply
6 years ago
February 21, 2019

Tuur wrote:

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.

Hi Can you help me to find solution in syncing up data from airtable

R

Ryan_Johnson1
New Participant
1 reply
5 years ago
July 14, 2019

Check out Actiondesk. They’re new but they have a solution,

A

Abhishek11
New Participant
1 reply
4 years ago
February 11, 2021

Did anyone found a solution to this?

A

+4

acco
Participating Frequently
23 replies
4 years ago
February 12, 2021

Abhishek11 wrote:

Did anyone found a solution to this?

Hey @Abhishek –

If you’re looking to do this in the simplest possible way in just a one-off fashion, you can:

Export your tables as a CSV
Use a tool like TablePlus to import those CSVs into your Postgres database.

Note that this strategy will mean you lose types – everything will just be imported as type text.

We wanted to go further and sync Airtable to a Postgres db in real-time. So we built a service that does just that – Sync Inc. In just a couple clicks, we’ll setup a hosted Postgres database containing all your Airtable data, kept in sync in real-time. (Here’s the post in this community about it.)

Cheers,
Anthony

S

+3

stollerd
New Participant
4 replies
4 years ago
February 12, 2021

Abhishek11 wrote:

Did anyone found a solution to this?

We are using CData’s JSON ODBC Driver for SQL Server and it works perfectly. Complete ETL automation including scheduling within SQL Server. I would recommend downloading the trial to see if you can get it working prior to purchasing.

A

AntonScatton
New Participant
2 replies
3 years ago
September 21, 2021

Hi there,

I did a migration from Airtable to https://supabase.io/ using https://uibakery.io/move-airtable-to-postgresql . It works perfectly and it’s free.

S

Sophie_Mona_Pag
Participating Frequently
7 replies
3 years ago
March 11, 2022

AntonScatton wrote:

Hi there,

I did a migration from Airtable to https://supabase.io/ using https://uibakery.io/move-airtable-to-postgresql . It works perfectly and it’s free.

Is there a way to send the data from Supabase to Airtable in real-time? Like each time a new row is created in Supabase, it creates a record on Airtable.

E

+4

Engjell_Sela
Known Participant
12 replies
3 years ago
March 12, 2022

Sophie_Mona_Pag wrote:

Is there a way to send the data from Supabase to Airtable in real-time? Like each time a new row is created in Supabase, it creates a record on Airtable.

I’m building a similar solution with https://nocodebase.xyz/, you can clone your airtable database to a new SQL databaes and it automatically creates an API for you, which lets you do SQL queries on the API url ( no coding skill needed )

It currently does not automatically update whenever your airtable database is updated, but will do soon.

H

29 replies

Reply

Most helpful members this week

Reply

Related topics

How to determine the "other" side of a linked fieldicon

Most helpful members this week

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded