Can datasette be configured to sort tables using multiple columns by default?

[zaneselvans]

It's easy to tell datasette to sort tables using a single column, as described in the docs:

databases:
  ferc1:
    tables:
      f1_edcfu_epda:
        sort: created_time

But is there some way to tell it to sort using a composite key, like you would in an ORDER BY clause instead? For example, the way it's being done in this query on our Datasette?

SELECT
  respondent_id,
  report_year,
  spplmnt_num,
  row_number,
  row_seq,
  row_prvlg,
  acct_num,
  depr_plnt_base,
  est_avg_srvce_lf,
  net_salvage,
  apply_depr_rate,
  mrtlty_crv_typ,
  avg_remaining_lf,
  report_prd
FROM
  f1_edcfu_epda
WHERE
  respondent_id = 210
  AND report_year = 2020
ORDER BY
  report_year, report_prd, respondent_id, spplmnt_num, row_number
LIMIT
  1000

The problem here is that by default it's using rowid (the SQLite assigned autoincrementing integer key) to order the records, but the table should have a natural composite primary key, but the original database that this data is being migrated from doesn't enforce unique primary keys, so there are dupes, and we don't want to drop those rows, and the records are somehow getting jumbled in the database (the rowid ordering isn't lined up with the expected ordering based on the composite primary key, though it's close) and this jumbling is confusing to users that expect to see the data ordered based on the natural primary key.

I've tried setting the sort metadata parameter to a list of column names, a tuple of column names, a quoted string of comma-separated column names, a quoted string of a tuple of column names...

databases:
  ferc1:
    tables:
      f1_edcfu_epda:
        sort: "(report_year, report_prd, respondent_id, spplmnt_num, row_number)"

and they all give me server errors like:

Cannot sort table by (report_year, report_prd, respondent_id, spplmnt_num, row_number)

[simonw]

No it can't, but that's a good feature request. Converting this into an issue!