issues: 1060631257

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
1060631257 I_kwDOBm6k_c4_N_LZ 1528 Add new `"sql_file"` key to Canned Queries in metadata? 15178711 open 0     3 2021-11-22T21:58:01Z 2022-06-10T03:23:08Z   CONTRIBUTOR  

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

yaml 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:

yaml databases: fixtures: queries: neighborhood_search: sql_file: neighborhood_search.sql title: Search neighborhoods neighborhood_search.sql: 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!

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

Links from other tables