Ability to bundle metadata and templates inside the SQLite file

[simonw]

One of the nicest qualities of SQLite as a data format is that you get a single file which you can then backup or share with other people.

Datasette breaks this a little once you start including custom metadata.json or template files and CSS.

It would be cool if there was an optional mechanism for baking that extra configuration into the SQLite file itself. That way entire datasette mini-applications (including canned queries and custom HTML and CSS) could be constructed as single .db files.

Since datasette configuration is all file-based, one way to achieve that would be to support a "datasette_files" table which, if present is used to search for file contents by path.

This is inline with the philosophy described by https://www.sqlite.org/appfileformat.html

[simonw]

One point of complexity: datasette can be used to bundle multiple .db files into a single "app".

I think that's OK. We could require that the datasette_files table is present in the first database file passed on the command-line. Or we could even construct a search path and consult multiple versions of the table spread across multiple files.

That said... any configuration that corresponds to a specific table should live in the same database file as that table.

Ditto for general metadata: if we have license/source information for a specific table or database that information should be able to live in the same .db file as the data.

[bsilverm]

This would be a great feature to have!

[simonw]

Potential problem: the existing metadata.json format looks like this:

{
    "title": "Custom title for your index page",
    "description": "Some description text can go here",
    "license": "ODbL",
    "license_url": "https://opendatacommons.org/licenses/odbl/",
    "databases": {
        "database1": {
            "source": "Alternative source",
            "source_url": "http://example.com/",
            "tables": {
                "example_table": {
                    "description_html": "Custom <em>table</em> description",
                    "license": "CC BY 3.0 US",
                    "license_url": "https://creativecommons.org/licenses/by/3.0/us/"
                }
            }
        }
    }
}

This doesn't make sense for metadata that is bundled with a specific database - there's no point in having the databases key, instead the content of database1 in the above example should be at the top level. This also means that if you rename the *.db file you won't have to edit its metadata at the same time.

Calling such an embedded file metadata.json when the shape is different could be confusing. Maybe call it database-metadata.json instead.

[simonw]

This is likely to be covered by plugin hooks: #860 for the metadata and after investigating in #1042 it looks like the existing prepare_jinja2_environment hook may already be enough to load templates from the database.