Skip to content

Query naming conventions

One of the problems that I have encountered when using SQL loading libraries such as yesql and aiosql is that the queries defined in SQL files need to be given unique names. Often, one ends up writing a group of queries that are mostly similar to each other and differ only slightly. Giving unique and consistent names to each query can become tricky.

A tool like tapestry cannot automatically give a name to a query. However, since the queries are listed in the manifest file, we can partly address the problem with the use of naming conventions.

These naming conventions involve clever use of special characters such as @, +, & and *. Let's look at some examples from examples/chinook dir.

[[queries]]
id = "artists_long_songs@genre*limit"
template = "artists_long_songs.sql.j2"
conds = [ "genre", "limit" ]

[[queries]]
id = "songs_formats@artist&file_format+album"
template = "songs_formats.sql.j2"
conds = [ "artist", "album_name", "file_format" ]

In the above queries, the id is defined using an alphanumeric prefix (artists_long_songs and songs_formats) followed by suffix that's an encoding of the conditional Jinja variables relevant to the query.

The convention is as follows,

  • @ precedes "cond" vars used for conditionally including a filter i.e. a WHERE clause.
  • + precedes "cond" vars used for conditionally returning a column
  • * precedes "cond" vars used for conditionally including any other part of the query e.g. LIMIT, ORDER BY etc.
  • & is used as a delimiter between two "cond" vars of same type e.g. @artist&file_format.

The name of the output file for the SQL query will be generated by slugifying the id i.e. by replacing the above special characters with hyphen (-). In case of the above two queries, the output file names will be artists_long_songs-genre-limit.sql and songs_formats-artist-file_format-album.sql respectively.

Note that these naming conventions are only recommended by tapestry and are not mandatory.