issues: 1870672704

This data as json

id node_id number title user state locked assignee milestone comments created_at updated_at closed_at author_association pull_request body repo type active_lock_reason performed_via_github_app reactions draft state_reason
1870672704 PR_kwDOBm6k_c5Y-7Em 2162 Add new `--internal internal.db` option, deprecate legacy `_internal` database 15178711 closed 0     4 2023-08-29T00:05:07Z 2023-08-29T03:24:23Z 2023-08-29T03:24:23Z CONTRIBUTOR simonw/datasette/pulls/2162

refs #2157

This PR adds a new --internal option to datasette serve. If provided, it is the path to a persistent internal database that Datasette core and Datasette plugins can use to store data, as discussed in the proposal issue.

This PR also removes and deprecates the previous in-memory _internal database. Those tables now appear in the internal database, with core_ prefixes (ex tables in _internal is now core_tables in internal).

A note on the new core_ tables

However, one important notes about those new core_ tables: If a --internal DB is passed in, that means those core_ tables will persist across multiple Datasette instances. This wasn't the case before, since _internal was always an in-memory database created from scratch.

I tried to put those core_ tables as TEMP tables - after all, there's always one 1 internal DB connection at a time, so I figured it would work. But, since we use the Database() wrapper for the internal DB, it has two separate connections: a default read-only connection and a write connection that is created when a write operation occurs. Which meant the TEMP tables would be created by the write connection, but not available in the read-only connection.

So I had a brillant idea: Attach an in-memory named database with cache=shared, and create those tables there!

sql ATTACH DATABASE 'file:datasette_internal_core?mode=memory&cache=shared' AS core;

We'd run this on both the read-only connection and the write-only connection. That way, those tables would stay in memory, they'd communicate with the cache=shared feature, and we'd be good to go.

However, I couldn't find an easy way to run a ATTACH DATABASE command on the read-only query.

Using Database() as a wrapper for the internal DB is pretty limiting - it's meant for Datasette "data" databases, where we want multiple readers and possibly 1 write connection at a time. But the internal database doesn't really require that kind of support - I think we could get away with a single read/write connection, but it seemed like too big of a rabbithole to go through now.

:books: Documentation preview :books:: https://datasette--2162.org.readthedocs.build/en/2162/

 107914493 pull     
{
    "url": "https://api.github.com/repos/simonw/datasette/issues/2162/reactions",
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
 0  

Links from other tables