Database functions fields support

[stas]

The issue is related to the way field nodes are parsed. At this moment there's no way you can dynamically embed a function in the field SelectQuery (although this is partially achievable through psycopg2 API for PostgreSQL, other database adapters are very unlikely to provide the same functionality).

A good example for this use-case could be the pgcrypto functions that can be used to encrypt/decrypt data at the query level. Below is an example SQL query I would like to be able to build through the Field API:

INSERT INTO mytable (mycolumn) VALUES (encrypt_iv('TEST_DATA', '_key', '_iv', 'aes'));
SELECT t1."id", decrypt_iv(t1."mycolumn", '_key', '_iv', 'aes') as mycolumn FROM mytable as t1;

[coleifer]

I think I'm going to pass on merging this, but thank you for your hard work!

[stas]

@coleifer It would be great if you could share your opinions on this.

Btw, this issue is not directly relevant to the PR #402 you just closed, which is fine tbh.

[coleifer]

It would be great if you could share your opinions on this.

For sure, sorry for being vague. I think this could lead to subtle bugs, especially if you wanted to work with the raw value stored in the column (without the function). I also think that there are limited use-cases -- encryption/decryption is really the only one that comes to mind.

In my opinion, it would be better to be explicit:

SecretModel.select(
    SecretModel.id,
    fn.decrypt_iv(SecretModel.data, 'my key', 'iv', 'aes').alias('data'))

SecretModel.insert(
    data=fn.encrypt_iv('shhhh', 'my key', 'iv', 'aes')).execute()

[stas]

Thanks, this is really helpful!