Design URLs for the write API

[simonw]

My original design for this issue:

• API to insert a single record into an existing table #1851

Was POST /db/table with JSON of {"insert": {...}}.

[simonw]

The problem with the above design is that I want to support a bunch of different actions that can be taken against a table:

• insert a single row
• insert multiple rows
• bulk update rows
• rename table
• alter table
• drop table

I could have ALL of those be a POST /db/table with different JSON root keys ({"drop": true} for example, but this raises two problems:

1. Server logs that only show POST /db/table will be less useful, they won't reveal what action was performed
2. What happens if you send {"insert": {"title": "New record"}, "drop": true}? Does that return an error, or does it perform both of those actions?

This is already slightly confusing in that POST /db/name-of-query is the existing API for executing a writable canned query: https://docs.datasette.io/en/stable/sql_queries.html#json-api-for-writable-canned-queries

So I'm ready to consider other design options.

Initial thoughts on possible designs (for the single row insert case, but could be expanded to cover other verbs):

• POST /db/table?action=insert
• POST /db/table?nsert
• POST /db/table/-/insert

I quite like that third one: it feels consistent with the existing /-/actor etc pages that Datasette serves already.

There's one slight confusion here in that it overlaps with the URL for a row with a primary key of "-" - which is currently at /db/table/- - but that might be OK.

Especially if I say that child pages of rows must theselves use the /-/ pattern. So to update or delet a row you would use:

• POST /db/table/row/-/update
• POST /db/table/row/-/delete

So a row with primary key - would end up as /db/table/row/-/-/update - which I think is OK.

[simonw]

The other option here would be to lean into custom HTTP verbs like DELETE and PATCH. I'm not sold on those: they've never given me any convincing wins over just using POST for the many times I've encountered them in my career to date.

[simonw]

So given this scheme, the URL design would look like this:

• POST /db/table/-/insert - insert a single row
• POST /db/table/-/insert-many - insert multiple rows (might just keep that on /-/insert with a JSON array rather than object though)
• POST /db/table/-/drop - drop a table
• POST /db/table/-/alter - alter a table
• POST /db/table/-/upsert - upsert, https://sqlite-utils.datasette.io/en/stable/python-api.html#upserting-data
• POST /db/table/-/create - could be an endpoint for explicitly creating a table, or should that live at /db/-/create instead?

And for rows (pks here since compound primary keys are supported):

• POST /db/table/pks/-/update - update row
• POST /db/table/pks/-/delete - delete row

[simonw]

I'm going to stick with one /-/insert endpoint which handles both single row inserts AND multiple row inserts I think - partly because I don't want to build both /-/upsert and /-/upsert-many, I'd rather just have /-/upsert.

[simonw]

I'm happy with this /db/table/-/action design for the moment. Will review it once I've built it to see if I still like it!