Reconsider default temporary database, use on-disk database instead

[simonw]

Based on the revelations from this Twitter thread, regular humans don't really think in terms of files any more - they expect data to mostly live inside the application that was used to create it.

On that basis, I'm rethinking the way Datasette Desktop starts out with a "temporary" in-memory database which is used for opening CSV files but which resets when the application is restarted.

[simonw]

I still like opening CSV files in a temporary in-memory database, since that avoids the import step and means that if you use the app to explore hundreds of CSV files you don't end up with a database with hundreds of duplicate tables.

But... I think the app should open with a default on-disk database which you can then start importing data into. This will avoid confusion when people use tools like datasette-configure-fts but lose their changes after an application restart.

I can maybe have a default.db which lives in the user's Documents folder - and which they can then double-click to open.

If a user detaches that database it doesn't need to come back.

[simonw]

Creating a default.db in the Documents folder without asking the user first feels rude.

I think what I'll do instead is add a very prominent "Create your first database" CTA to the index page which, when clicked, prompts the user to save a default.db file using the save-file dialog.

It looks like https://www.electronjs.org/docs/api/dialog#dialogshowsavedialogbrowserwindow-options lets me set a defaultPath - in fact I already use that for the "New Empty Database…" menu option here:

datasette-app/main.js

Lines 875 to 881 in a0fe7ad

	label: "New Empty Database…",
	accelerator: "CommandOrControl+Shift+N",
	click: async () => {
	const filepath = dialog.showSaveDialogSync({
	defaultPath: "database.db",
	title: "Create Empty Database",
	});

It would be good if I could come up with a mechanism where by the temporary database either doesn't let users edit stuff (configure FTS, edit schema etc) and prompts them to instead import the CSV data into an on-disk database so they can start making changes to it.

That's going to be a bit tricky to design from a UI perspective though, since the mental model it needs to communicate is quite complex.

[simonw]

Maybe I need some core principles to help guide this?

• Users should not have to think at all hard about where their data is stored
• If a user does anything more than open a CSV file to preview it, any changes they made should be persistent without them having to do anything else
• Users shouldn't have to think about file system hierarchies at all
• The software should be usable by and useful to people who think in terms of apps, not in terms of files and folders

[tf13]

FWIW, I like the idea of a default in-memory database for datasette — a kind of scratchpad. But I take your point that users could be put off by its disappearance. Maybe the same thing can be achieved by creating a default on-disk database, and giving people tools to split tables off from it when they want to stop exploring and make a particular set of tables (database) more permanent and distinct.

[simonw]

Yeah I agree - I think the key thing to figure out here is how to help users who are looking at an in-memory table "promote" that to on-disk status.

[simonw]

An API method for copying a table from memory to disk might help here, maybe something like this:

POST /-/copy-table
{"source": "temporary", "destination": "mydb", "table": "tablename"}

[tf13]

Sorry — you meant to essentially "export" tables you want to move from memory to disk. That works. I'm not sure it solves the question of what to do with the in-memory tables/database. Persist to next launch? That's saving to disk. Delete on exit? That gets back to the value of autosave and the risk of surprising users.

My original comment (not sure of the protocol here): I mean, there's an ancient mechanism for this ... a save button. 😱 But given where you started here (users not understanding files/folders), that seems dangerous. And let's face it, autosave is a good thing.