Help

Re: How to use REST API to delete up to ten records at a time using Node.js library axios?

Solved
Jump to Solution
3170 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Dev_Local
6 - Interface Innovator
6 - Interface Innovator

How do I use the REST API to delete ten records at a time using axios?

I can successfully delete one record at a time, but due to the API design, I am having a problem constructing the URL to delete ten at a time.


Here is what works with one record at a time:

I am using a library in Node.js called axios to do HTTP method calls, and here is the call that works:

await axios.delete(url, {params: params, headers: headers})
	.then(async function(response) {
        console.log('DELETED:',
            response.status,
            response.statusText);
});

I define params this way (I have a method call that returns the params to send to axios)

params = await getParams(rec);  // rec is the Airtable record identifier like: `rec
async function getDelParams(rec) {
    const params = {
        'records[]': rec		// rec is like: rec5GWL8xCpofFKFC
    };
    return params
}

In this key/value pair, I can only have one entry of : records[] , no more, I need ten.

The REST API requires multiple records like:

records[]=rec5GWL8xCpofFKFC&records[]=rec5GWL8xCpofFLSD&records[]=rec5GWL8xCpofXMAS

and so on, up to ten max.

but axios accepts params as a list of key/value pairs, and there can be only one key: records[] in the list.

I need key/value pairs because I have other keys/values that I am sending, and this makes it convenient.

How can I use axios/key value pairs to make this work so that it sends 10 at a time, instead of one at a time?


Follow up:

What could be done here (or what is typically done here) to design a better API to accept a list of values with one key?

1 Solution

Accepted Solutions

I’m certain I cannot lend any assistance concerning PHP, but this is how it works well in javascript, specifically Google Apps Script. Note that there is no URL encoding required because record IDs are strings that need no such encoding to be properly executed in a URL command line.

Ergo, a properly formatted batch request URL for three records looks like this:

https://api.airtable.com/v0/app9cal9JNalRhtkx/Test%20Table?records[]=rec6LzaDnlc2zcoYO&records[]=recCCdy6JKlSxyGMC&records[]=recGxog35B0kG2KSt

Given this function:

//
// delete airtable record batch
//
function atDeleteRecords_(apiKey, baseID, tableName, deleteRecordList)
{
  apiKey = (apiKey == undefined) ? airtableAPIKey : apiKey;
  var url = airtableAPIEndpoint + baseID + "/" + encodeURIComponent(tableName) + deleteRecordList;
  
  const options = {
    method: 'DELETE',
    headers: {
      'Authorization' : 'Bearer ' + apiKey,
      'Content-type': 'application/json'
    },
    muteHttpExceptions : true
  };
  
  try {
    var response = UrlFetchApp.fetch(url, options).getContentText();
  } catch (e) {
    Logger.log("DELETE: " + e.message);
  }
  return(response);
}

This harness will delete all records in a table 10 at a time and also accommodate the deletion of any remainders. This test harness reads all of the records in the table and then performs the deletion process. I assume you have no trouble reading the table to be deleted.

 var results = atGetTable_(apiKey, baseID, tableName);
  // Logger.log(results);
  
  var aRecords = JSON.parse(results).records;
  Logger.log(aRecords.length);
  
  var aDeleteRecordList = [];
  var count = 0;
  for (var i in aRecords)
  {
    aDeleteRecordList.push(aRecords[i].id);
    count += 1;      
    if ( (aDeleteRecordList.length == 10) || ( (parseInt(i) + 1 == aRecords.length) && (aDeleteRecordList.length > 0) ) )
    {
      Logger.log("Delete Batch of " + aDeleteRecordList.length);
      var deleteRecordList = "?records[]=" + aDeleteRecordList.toString().replace(/,/ig, "&records[]=");
      var response = atDeleteRecords_(apiKey, baseID, tableName, deleteRecordList);
      aDeleteRecordList = [];
    }
  }
  
  Logger.log("Total records deleted: " + count);

See Solution in Thread

11 Replies 11

I’m not familiar with the axios library but I was wondering if it’s a simple matter of including the ten record id’s as items in the records[] array.

records['rec5GWL8xCpofFKFC','rec5GWL8xCpofFLSD','rec5GWL8xCpofXMAS', ...]

The javascript API seems to intimate this exact approach.

image

Has anyone actually gotten batch delete working? I’ve been banging my head against it for a few days. Managing data in PHP with curl requests. I’ve tried building, encoding and formatting the array in every way I can think. Including, but not limited to:

  • Building anonymous arrays in PHP
  • Building names arrays in PHP
  • Leveraging mixtures of http_build_query() and urlencode()
  • Even manually trying to build base string representations of arrays, along with various querystring representations of arrays.
    Does anyone have any working code samples? I’m resorting to looping and one off deletes, but that just irks me!

Welcome to the Airtable community!

Have you looked at the api documentation? The documentation gives examples specific to your base.

curl -v -X DELETE https://api.airtable.com/v0/appXXXXXXXXXXXXXX/TableName \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -G \
  --data-urlencode 'records[]=recXXXXXXXXXXXXX1' \
  --data-urlencode 'records[]=recXXXXXXXXXXXXX2'
Sam_Spicer
4 - Data Explorer
4 - Data Explorer

Sadly, yes I have. And I’ve tried to get to that but just can’t. What’s odd is that I’m able to build a PATCH request, via PHP array to json_encode(), and successfully update multiple records, via batch. However, that is a JSON request. The DELETE is clearly a urlencode so I think something is getting lost in translation.
I’ll try to post some of my attempts later but just wanted to see if anyone had successful code in hand.
And thank you, appreciate any eyes on this because mine aren’t cutting it!

Wanted to document my attempts, a bit more methodically, so went through this exercise. The various permutations I’ve attempted to try and get this thing to work. Mostly out of sheer stubbornness…
I know the original post was Node.js, and I’m doing PHP, but regardless of the language I’m not sure what the endpoint is expecting here. At this point, I’d just love to hear of someone getting this working, in any language, for some clues.

  //records from a basic GET to my table
  $records = json_decode($get,true);
  //creating an array to loop through and collect the record IDs to be deleted.
  $delete = ["records"=>[]];
  foreach($records["records"] as $item){
    array_push($delete["records"],$item["id"]);
  }

  // passed to http_build_query()
  $deleteString = http_build_query($delete);
  // which effectively output (record IDs replaced for legibility):
  $deleteString = urlencode("records[0]=recID1&records[1]=ID2&records[2]=ID3");
  // which I modded to remove index numbers in the array to match the Airtable suggestion and my best understanding of urlencode query string passing.
  $deleteString = urlencode("records[]=recID1&records[]=ID2&records[]=ID3");

  //After which, I just started playing with variations:
  $deleteString = urlencode("records[]=recID1,ID2,ID3");
  $deleteString = urlencode("records[]='recID1'&records[]='ID2'&records[]='ID3'");
  $deleteString = urlencode("records[recID1,ID2,ID3]");
  $deleteString = urlencode("records['recID1','ID2','ID3']");
  $deleteString = urlencode("['recID1','ID2','ID3']");
  $deleteString = urlencode("[recID1,ID2,ID3]");
  $deleteString = urlencode("recID1,ID2,ID3");
  $deleteString = urlencode("'recID1','ID2','ID3'");

  // Also, tried building a nested named array, nope.
  $deleteString = urlencode("records[0][id]=recID1&records[1][id]=ID2&records[2][id]=ID3");
  $deleteString = urlencode("records[][id]=recID1&records[][id]=ID2&records[][id]=ID3");

  $a_url = 'https://api.airtable.com/v0/app################/Test';
  $a_headers = array( "Authorization: Bearer key################" );
  // callAPI is a separate curl API handler function.
  $deleteresponse = callAPI('DELETE',$a_url,$deleteString,$a_headers);
Ludom_Initiativ
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,
I wonder if this was resolved at some point as I have been confronted with the same problem using the REST API with Google Apps Script to delete then push data to Airtable and have also tried many different ways - just like @Sam_Spicer mentioned above.
I’ve spent a lot of time on this and am still stuck with the exact same error message - details in this post.
Could anyone help here?

Best,
Ludo

I’m certain I cannot lend any assistance concerning PHP, but this is how it works well in javascript, specifically Google Apps Script. Note that there is no URL encoding required because record IDs are strings that need no such encoding to be properly executed in a URL command line.

Ergo, a properly formatted batch request URL for three records looks like this:

https://api.airtable.com/v0/app9cal9JNalRhtkx/Test%20Table?records[]=rec6LzaDnlc2zcoYO&records[]=recCCdy6JKlSxyGMC&records[]=recGxog35B0kG2KSt

Given this function:

//
// delete airtable record batch
//
function atDeleteRecords_(apiKey, baseID, tableName, deleteRecordList)
{
  apiKey = (apiKey == undefined) ? airtableAPIKey : apiKey;
  var url = airtableAPIEndpoint + baseID + "/" + encodeURIComponent(tableName) + deleteRecordList;
  
  const options = {
    method: 'DELETE',
    headers: {
      'Authorization' : 'Bearer ' + apiKey,
      'Content-type': 'application/json'
    },
    muteHttpExceptions : true
  };
  
  try {
    var response = UrlFetchApp.fetch(url, options).getContentText();
  } catch (e) {
    Logger.log("DELETE: " + e.message);
  }
  return(response);
}

This harness will delete all records in a table 10 at a time and also accommodate the deletion of any remainders. This test harness reads all of the records in the table and then performs the deletion process. I assume you have no trouble reading the table to be deleted.

 var results = atGetTable_(apiKey, baseID, tableName);
  // Logger.log(results);
  
  var aRecords = JSON.parse(results).records;
  Logger.log(aRecords.length);
  
  var aDeleteRecordList = [];
  var count = 0;
  for (var i in aRecords)
  {
    aDeleteRecordList.push(aRecords[i].id);
    count += 1;      
    if ( (aDeleteRecordList.length == 10) || ( (parseInt(i) + 1 == aRecords.length) && (aDeleteRecordList.length > 0) ) )
    {
      Logger.log("Delete Batch of " + aDeleteRecordList.length);
      var deleteRecordList = "?records[]=" + aDeleteRecordList.toString().replace(/,/ig, "&records[]=");
      var response = atDeleteRecords_(apiKey, baseID, tableName, deleteRecordList);
      aDeleteRecordList = [];
    }
  }
  
  Logger.log("Total records deleted: " + count);
Jono_Prest
6 - Interface Innovator
6 - Interface Innovator

Hi @Dev_Local, @Sam_Spicer and @Ludom_Initiatives-Th,

I know this is a late for your question but I thought I’d add my two cents for the given issue.

The reason it does not work with your params object is because you obviously cannot use the same key multiple times in a javascript object. Ultimately you want your query string to look something like this:

?records[]=recM3SHTW2mtTgGZ4&records[]=recUgV8NdXuFQBSvs"

My suggestion would either be to simply build this string yourself and append it to the URL you are passing to axios. Or to use a serializer for your params.

For example:

let queryParams = "?";

function appendRecordToParams(recordId) {
  const recordString = `${queryParams === "?" ? "" : "&"}records[]=${recordId}`;
  queryParams = queryParams + recordString;
}

for (recordId of recordIdsArray) {
  appendRecordToParams(recordId);
}

await axios.delete(url + queryParams, { headers: headers });

OR

Use the qs serializer package (qs - npm) and pass in your record values with this syntax:

axios
  .delete(url, {
    params: {
      records: ["rec8gTJwJUZ3sUemT", "recSrJNSHOCpUDbWX"],
    },
    paramsSerializer: (params) => {
      return qs.stringify(params, { encode: false, arrayFormat: "brackets" });
    },
    headers: {
      Authorization: "Bearer $API_KEY",
    },
  })

*A problem that can be encountered is the encoding of the [ ] in query parameters. And encoded square bracket is not the same as a literal square bracket.

Jono_Prest
6 - Interface Innovator
6 - Interface Innovator

In reply to this point. It is perfectly acceptable to pass in array values using query params in the way that Airtable’s API handles it. The developers of the API could allow you to pass the array in the body of the request instead of the query params, which would probably be more sensible for using a library like axios. However, they are also supplying a javascript module that handles it all for you.

Ludom_Initiativ
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you very much for your help @Bill.French ! It was very easy to make it work based on your feedback on the expected syntax.

@Jono_Prest,
The JS object was not the issue here (we were not using the same key multiple times here), this weird syntax was (I’ve never seen such syntax for a delete API call for the 15 years I’ve been working with APIs).
Maybe there’s something to do in the documentation here to make that more obvious?

Best,
Ludo

There’s an axiom in product development -

Do not surprise users unless it’s a delightful surprise.

This… ?records[]=rec6LzaDnlc2zcoYO&records[]=recCCdy6JKlSxyGMC is a surprise but not delightfully so. :winking_face: