CartoDB - Deleting datasets via API

Question

We're running our own CartoDB stack and all is working well, but in writing an API testing script, that creates and subsequently deletes a dataset, we've discovered we're not sure how to programmatically delete a dataset completely.

Existing questions I've found on the net about this all lead to using the SQL API to simply DROP the dataset's table, but that leaves references to that table in place in the primary Carto database, and that means the dataset is still listed in the UI but errors out when trying to view it -- basically the Carto UI application still thinks the dataset exists, even without its table.

That primary database is also outside the range of the SQL API so a DELETE query isn't plausible.

Has anyone found a more "correct" way of doing this? It seems like an incredibly huge gaping hole in API functionality not to be able to delete something you've created.

Addendum: I see that the SQL API has the "cdb_cartodbfytable" function that registers a table (created with the SQL API) so that it's visible to the editor, but doesn't seem to have an equivalent function to deregister upon dropping.

Edit: After a few days of no response I've also added it as an issue in the software's repo. https://github.com/CartoDB/CartoDB-SQL-API/issues/401

Answer 1

See my answer on the issue. You need SSH connection to the machine running Carto. Seems that currently it can't be done from frontend. This is really a "hack", but works for me.

---

I have encountered the same problem. I've solved connecting with the machine that is running Carto through SSH and executing queries directly with the PostgreSQL database. First I'm executing a query thorugh SQL API:

DROP TABLE IF EXISTS <dataset_name> CASCADE;COMMIT;

And next, I'm executing two queries though SSH:

psql -U postgres -d carto_db_development -c "DELETE FROM user_tables WHERE (name='<dataset_name>' AND user_id='<carto_user_id>');"
psql -U postgres -d carto_db_development -c "DELETE FROM visualizations WHERE (name='<dataset_name>' AND user_id='<carto_user_id>');"

You can get the carto_user_id executing through SQL API SELECT current_database(); and getting the HEX string separated by - characters.

I'm not sure if is the correct way to do this or if there are more metadata that needs to be deleted in order to avoid other conflicts, but it works, datasets are deleted from Carto Builder datasets administration interface. I hope this can help someone.