Add new `"sql_file"` key to Canned Queries in metadata?

[asg017]

Currently for canned queries, you have to inline SQL in your metadata.yaml like so:

databases:
  fixtures:
    queries:
      neighborhood_search:
        sql: |-
          select neighborhood, facet_cities.name, state
          from facetable
            join facet_cities on facetable.city_id = facet_cities.id
          where neighborhood like '%' || :text || '%'
          order by neighborhood
        title: Search neighborhoods

This works fine, but for a few reasons, I usually have my canned queries already written in separate .sql files. I'd like to instead re-use those instead of re-writing it.

So, I'd like to see a new "sql_file" key that works like so:

metadata.yaml:

databases:
  fixtures:
    queries:
      neighborhood_search:
        sql_file: neighborhood_search.sql
        title: Search neighborhoods

neighborhood_search.sql:

select neighborhood, facet_cities.name, state
from facetable
join facet_cities on facetable.city_id = facet_cities.id
where neighborhood like '%' || :text || '%'
order by neighborhood

Both of these would work in the exact same way, where Datasette would instead open + include neighborhood_search.sql on startup.

A few reasons why I'd like to keep my canned queries SQL separate from metadata.yaml:

• Keeping SQL in standalone SQL files means syntax highlighting and other text editor integrations in my code
• Multiline strings in yaml, while functional, are a tad cumbersome and are hard to edit
• Works well with other tools (can pipe .sql files into the sqlite3 CLI, or use with other SQLite clients easier)
• Typically my canned queries are quite long compared to everything else in my metadata.yaml, so I'd love to separate it where possible

Let me know if this is a feature you'd like to see, I can try to send up a PR if this sounds right!

[asg017]

Oh, another thing to consider: I believe this would be the first "_file" key in datasette's metadata, compared to other "_url" keys like "license_url" or "about_url". Not too sure what considerations to include with this (ex should missing files cause Datasette to stop before starting, should build scripts bundle these sql files somewhere during datasette package, etc.)

[20after4]

FWIW I implemented something similar with a bit of plugin code:

@hookimpl
def canned_queries(datasette: Datasette, database: str) -> Mapping[str, str]:
    # load "canned queries" from the filesystem under
    #  www/sql/db/query_name.sql
    queries = {}

    sqldir = Path(__file__).parent.parent / "sql"
    if database:
        sqldir = sqldir / database

    if not sqldir.is_dir():
        return queries

    for f in sqldir.glob('*.sql'):
        try:
            sql = f.read_text('utf8').strip()
            if not len(sql):
                log(f"Skipping empty canned query file: {f}")
                continue
            queries[f.stem] = { "sql": sql }
        except OSError as err:
            log(err)

    return queries

[eyeseast]

I just put together a version of this in a plugin: https://github.com/eyeseast/datasette-query-files. Happy to have any feedback.