search

simonw / datasette

An open source multi-tool for exploring and publishing data
https://datasette.io
Apache License 2.0
9.59k stars 691 forks source link

/db/table/-/rename API (also allows atomic replace) #1867

Open simonw opened 2 years ago

simonw commented 2 years ago

There's one catch with batched inserts: if your CLI tool fails half way through you could end up with a partially populated table - since a bunch of batches will have succeeded first.

...

If people care about that kind of thing they could always push all of their inserts to a table called _tablename and then atomically rename that once they've uploaded all of the data (assuming I provide an atomic-rename-this-table mechanism).

Originally posted by @simonw in https://github.com/simonw/datasette/issues/1866#issuecomment-1293893789

simonw commented 1 year ago

Basic version of this allows you to rename a table:

POST /db/table/-/rename
{
    "name": "new_table_name"
}

If a table with that new name already exists you will get an error - unless you pass "replace": true in which case that table will be dropped and replaced by the new one.

POST /db/table/-/rename
{
    "name": "new_table_name",
    "replace": true
}

This is useful because it allows for that atomic replacement operation: upload brand new data into a _tmp_name table, then atomic rename and replace after the upload has completed.