Can I delete all rows via code, as quickly as I can through using an AirTable webpage?

[KevinScottGoff]

We know the API can be used to delete a range of record IDs at once (i.e. 10 rows, 50 rows).  That works fine for fairly small tables, but we have some tables in excess of 10K rows where we'd like to be able to delete the rows quickly.

To add to the question, I know that AirTable allows a user (with rights) to delete all rows very quickly, by doing the following:

- Go to the AirTable page.

- Click on the checkbox in the upper left part of the column headings to mark all rows with a checkbox

- Right-click and take the option to "Delete all selected positions".

Again, even if the table has more than 10K rows, AirTable removes them very quickly (maybe a second or two). 

We'd like to be able to do that programmatically.  Unfortunately, we haven't yet found a way to reproduce that using the API.   The best we've been able to come up with is the approaches we've found in the API documentation, which execute more slowly (and sometimes timeout) 

Maybe a more general question - when someone takes a specific set of steps in the UI, is it possible to record those steps as a macro?   There are other scenarios as well when someone can perform a task in the interface, where we try to figure out how to replicate that behavior through code).  So we wish we could "peek under the hood" to see what AirTable is doing when someone checks the top checkbox and is able to delete thousands of rows instantly.        

At one point I wondered if maybe the table was being copied without rows to a temp table, then physically removed, and renamed back, without the table ID itself being changed. 

Thanks,

Kevin

[kuovonne]

It is possible to delete records very, very quickly via scripting. I have never tried to manually delete 10,000 records manually using the technique you described. But I have done so using a script, and it does take several seconds. The fastest that scripting can delete records is 750 records per second.

On the other hand, if you are using the web API the fastest you could delete records is 50 records per second. If you need to start the process from the web API but want to delete records faster, you could trigger an Airtable scripting automation that does the deleting.

There currently is no way to “record a macro” in Airtable.

Care to share more about your use case that requires code to delete so many records?

[KevinScottGoff]

Thanks for responding.  A little more background: we have a third party desktop vertical market app (using SQL Server) that handles much of our order/production entry.   Our company needed some significant enhancements that went beyond anything the vendor would be expected to implement.    For specific reasons (including lack of developer resources), the company decided to use AirTable as a means of extending the application.    We have jobs that push data between the app and airtable in such a way that users can still perform the "bread and butter" tasks in the regular application, but can still use the "extended" functionality implemented in AirTable.      For the most part, the solution works pretty well - we just have to realize that AirTable doesn't scale in the same way SQL Server does, so we have to be careful with our synch jobs.

We have one situation where we need to do a daily refresh of inventory of about 13K rows.  A pretty high % of them can change (even subtle changes), and we decided for several reasons that it was better to do a "truncate and re-load" of the 13K rows every morning.    I realize truncate and load is often a primitive approach and I only want to revert to it when necessary, but this was necessary.

So again, we have an interest in chasing out the 13K rows every morning.  (Note, we maintain a daily snapshot in SQL Server).  So think of those 13K rows in AirTable as a "quasi-staging table".  

You mentioned scripts that could delete X number of rows per second.  Admittedly, every approach I've tried to use runs slower than that - by chance do you have a link to a script example that shows 250 rows deleted per second?   That would be great.

Thanks very much,
Kevin

[ScottWorld]

@KevinScottGoff 

1. It’s too bad that they decided on the wrong tool to extend their development. The perfect tool would have been FileMaker, which has no limitations (for example, you can have over 100 million records per table), and its “expunge” functionality is designed to delete all records INSTANTLY. I don’t do FileMaker development anymore, but if you need a referral to an expert FileMaker developer, please feel free to contact me through my website.

2. Also, for those reading this thread in the future who might be looking for a low-code/no-code way of deleting all records in Airtable (that doesn’t require JavaScript scripting), I personally use Make for these purposes.

[kuovonne]

I do not have a link to a script that deletes an entire table that quickly, and do not want to publish the ones I have written. On the other hand, an experienced script writer should be able to come up with the script from the documentation. 

The numbers that I cite for how fast you can delete records are based on the documentation.

I made a mistake in the 250 number. It should be 50. The web API can delete in batches of ten records. The web API has a rate limit of 5 requests per second. Thus 50 records per second. 

Scripting can delete records in batches of 50, with a rate limit of 15 requests per second.

You could run a scheduled scripting automation that deletes records at a set time every night.