Consider a default on-disk database instead of (or as well as) "temporary" in-memory

[simonw]

I designed the in-memory temporary database to support ad-hoc analysis of CSV data - something I do a lot of.

But on thinking about this further, one of my favourite features of several Mac apps - VS Code, Apple Notes and suchlike - is that if they quit or I crash or I restart my computer they never lose any of my data. I often have dozens of unsaved VS Code documents open for random notes and I would be very sad if I lost them due to a crash or restart.

Maybe Datasette Desktop should embrace that philosophy? It could have a single scratch.db database which is always there and is backed by a file on disk, such that you won't lose any data accidentally.

[simonw]

I just put quite a bit of work into ensuring the temporary in-memory database can be dumped to disk and then restored on a server restart, in #42. Maybe that work was misguided and this offers a better solution to that problem?

[simonw]

This would also dramatically simplify the need for tracking which CSV files have been previously opened in #54 - since CSV files wouldn't need to be tracked at all, only databases. The extra work would then need to go into a history/bookmark mechanism instead, see #85.

[simonw]

Quoting #77 (comment)

In #87 I had a bit of a crisis of confidence about the whole idea of opening CSVs in a temporary in-memory database. Should I instead be defaulting to an on-disk database so that people don't lose their work?

But this demo has me thinking in the other way: treating Datasette Desktop as an "open this CSV file" tool really does make a lot of sense, and this improvement dramatically increases its usability there.

I'm worried about what happens when people start modifying the imported CSV data, using plugins like datasette-edit-schema and datasette-configure-fts - but I think the solution to that will be along the lines of detecting that changes have been made and showing a prominent "save your changes" interface element which persists the SQLite version to disk somewhere.

[simonw]

I'm sticking with the new "recently opened" menu - I'm not going to change how "temporary" works, at least for the moment.