Templating Identifers into SQL

[nackjicholson]

honza/anosql#45 and honza/anosql#47

Are both asking for support for Identifiers in order to template other values into the SQL, like table names. I've never had need for this, and actually wasn't aware of it as a feature in psycopg2, but it's worth some thought to see if it's something we can do in this project.

One potential problem in the way of doing this is that the other driver libraries like sqlite or asyncpg may not have safe features for doing this. I just really don't know since I've never had to do this kind of table name formatting.

[danmurphy1217]

Hmmm, neat idea. Let me think about how we could approach this. Any initial thoughts @nackjicholson?

Thoughts on requiring another identifier, such as --type: <> in the .sql file that defines whether a query is static or dynamic? Then, if --type is 'static' or not specified, the path continues as things currently operate. If the --type is 'dynamic', we can branch off and handle the query differently (still don't have a clear roadmap for this, but maybe we could add another parameter to the QueryDatum class that accepts the dynamic values?)

[jkr78]

It would be neat to have a possibility to dynamically change the behavior of the query depending on supplied columns.
The simplest example would be a PATCH command for REST API, e.g., update only some columns.

The way I would do this is to use Jinja2 to process SQL and provide access to parameters. If dependencies or performance is an issue, use an operator or smth like this to specify normal or Jinja2 style of SQL.

[frafra]

Using string templates or jinja to do this could be dangerous. psycopg2 has sql identities to handle this case correctly.
It would be nice just to use ::colname to specify that the variable should be escaped as identity instead of a value.

[jkr78]

@frafra I didn't mean to use Jinja2 for variable substitution in SQL. Contrary, I think variable substitution must be handled by the driver.

But let's define what problem I would solve with Jinja2. Imagine you have to implement a query to update users table. Table contains id, full_name, email and is_admin. All or some fields may be updated (except id).
Currently, I cannot see how to write it using aiosql without defining every possible update as a separate query.

With help of Jinja2 (or another template) it would look like this:

update "users"
   set {% if 'full_name' in aiosql.fields -%}
       "full_name" = :full_name
       {% endif -%}
       {% if 'email' in aiosql.fields -%}
       "email" = :email
       {% endif -%}
       {% if 'is_admin' in aiosql.fields -%}
       "is_admin" = :is_admin
       {% endif -%}
 where "id" = :id

Here I used aiosql as a variable containing a dict of fields/values passed. SQL arguments/fields left as is. Information about fields passed is used to select what fields should be updated.

[midir99]

There is a project called JinjaSQL that allows you to write SQL queries dynamically depending on the values you pass, maybe we could rely on this dependency, but make it optional, only for those users who want templated SQL queries.

Or maybe we can dive into psycopg, extract all the functions that enable one to generate SQL queries and then copy those on aiosql modules: https://github.com/psycopg/psycopg/blob/master/psycopg/psycopg/sql.py

I think that using JinjaSQL would fit better the things aiosql users want to achieve.

[zx80]

Hmmm. I agree that it could be helpful in some cases, but ISTM that aiosqlshould not depend (too much) on driver features, but stay as much as possible at the DB API level.