`dclient update` - update a single record in an existing table

[simonw]

The CLI command for the API method I am building here:

• https://github.com/simonw/datasette/issues/1863

[simonw]

(I'm going to try building these in parallel with the API implementations.)

[simonw]

Initial API endpoint design:

POST /db/table/1/-/update
Authorization: Bearer xxx
Content-Type: application/json

{
  "update": {
    "name": "New name"
  }
}

Which returns:

 {
  "ok": true
}

(Unless you pass "return": true in which case it returns a copy of the full object - not needed here.)

Errors will look something like this:

{
    "ok": false,
    "type": "https://example.net/validation-error",
    "title": "Your request is not valid.",
    "errors": [
        {
            "detail": "must be a positive integer",
            "pointer": "#/age"
        },
        {
            "detail": "must be 'green', 'red' or 'blue'",
            "pointer": "#/profile/color"
        }
    ]
}

[simonw]

Command design:

dclient update dbalias tablename pk1 pk2 pk3 -p colname colvalue -p colname2 colvalue2

Or alternatively (this example has only one primary key column):

dclient update dbalias tablename pk --json '{
  "colname": colvalue
}'

Design challenge: with the --json version the values have types. But with the -p version they don't - does that mean that the API should know how to convert strings into the correct types? Or should dclient be smart enough to first fetch the table column types and convert into the correct JSON types before POSTing to the API?

[simonw]

I was inclined to say that the API should convert types automatically, but there's an edge-case: what if I add the ability to run alters as part of updating a record, such that any new columns that are missing get created?

dclient update dbalias tablename pk --alter -p weight 3.5

Need a way for the tool to understand that the new weight column should be floating point, not a string.

[simonw]

Decision: I'm going to have the API know how to convert types, and I'm going to tell people that if they want to create a new floating point or integer field using dbclient update ... --alter then they'll need to use the --json option to do it.

[simonw]

Changed my mind. I don't like the -p option - it's short and weird. I'm going to switch to having type-specific option names instead:

• -t name value - for text
• -i name value - for integers
• -f name value for floats
• -b name value for binary

These are based on the language used in sqlite-utils: https://sqlite-utils.datasette.io/en/stable/cli.html#cli-create-table