Confusing error if metadata.json contains nested data

[simonw]

Create an invalid metadata.json file like this:

{"settings": {"trace_debug": 1}}

And start Datasette like so:

datasette -m metadata.json

Datasette appears to start correctly, but when you attempt to load any page you get this error:

Error binding parameter 1 - probably unsupported type

I ran datasette -m metadata.json --get / --pdb and found that the problem was here:

126  	    async def execute_write(self, sql, params=None, block=True):
127  	        def _inner(conn):
128  ->	            return conn.execute(sql, params or [])
129  	
130  	        with trace("sql", database=self.name, sql=sql.strip(), params=params):
131  	            results = await self.execute_write_fn(_inner, block=block)
132  	        return results
133  	
(Pdb) print(sql)
              INSERT INTO metadata_instance(key, value)
                VALUES(?, ?)
                ON CONFLICT(key) DO UPDATE SET value = excluded.value;
(Pdb) params
['settings', {'trace_debug': 1}]

[simonw]

That's happening here:

datasette/datasette/app.py

Lines 737 to 746 in 1f3fb5f

	async def set_instance_metadata(self, key: str, value: str):
	# TODO upsert only supported on SQLite 3.24.0 (2018-06-04)
	await self.get_internal_database().execute_write(
	"""
	INSERT INTO metadata_instance(key, value)
	VALUES(?, ?)
	ON CONFLICT(key) DO UPDATE SET value = excluded.value;
	""",
	[key, value],
	)

Two options:

1. Add metadata validation, such that problems like this are spotted and Datasette refuses to start until the metadata.json is fixed
2. Teach that feature to convert complex values to JSON and write those to the table

I'm tempted by option 2 - I think it will be easier and supporting nested JSON in metadata feels like it could be useful.

[simonw]

With that fix in place, the above example can do this:

datasette -m bad.json --get '/.json' | jq

{
  "databases": {
    "_memory": {
      "name": "_memory",
      "hash": null,
      "color": "a6c7b9",
      "path": "/_memory",
      "tables_and_views_truncated": [],
      "tables_and_views_more": false,
      "tables_count": 0,
      "table_rows_sum": 0,
      "show_table_row_counts": false,
      "hidden_table_rows_sum": 0,
      "hidden_tables_count": 0,
      "views_count": 0,
      "private": false
    }
  },
  "metadata": {
    "settings": "{\"trace_debug\": 1}"
  }
}