Custom SQL support

[grigi]

To allow odd requirements, or using special SQL syntax that is not yet supported (or may never be) by Tortoise ORM we should provide a simple, clean, custom SQL interface.

How do we identify the connection?

Is Tortoise.get_connection("models") adequate?

Currently the connection used execute() function accepts RAW SQL, and returns data in a RAW format (dependant on the DB driver)

These functions are used internally by the ORM itself. We should privatise these, and provide standard public functions that have a well-defined interface.

How do we format parameters?

Since parametrized queries are all handled very differently for each DB we support, we should do an intermediate representation and support that. Possibly just adapt the same {field} format that SQLAlchemy ORM has and map those to the right parameter format for the local db?

How do we handle parameters?

Named parameters only. For single, should it be kwarg based: execute("<SQL>", ...) or dict based: execute("<SQL>", {...}) ? For bulk operations we can only accept a list of dicts each containing I would make it ALWAYS just accept a dict

[grigi]

@abondar @shaloba Please provide feeback/recomendations/changes you want for this feature so we can get a good spec & plan for this?

[gdmoore]

Will you be able to mix and match custom SQL with a regular ORM query? For example if I wanted to do Team.filter(name__icontains="CON") but then additionally enrich the query with some SQL that tortoise does not support, without writing the whole thing from scratch.

For example pony-orm provides raw_sql ( https://docs.ponyorm.org/api_reference.html#raw_sql ) which lets you mix and match ORM queries with raw SQL when you need that escape hatch. It feels like this could be implementable in the context of filter fairly sanely, eg allow raw="snippet" and just pass-through that snippet verbatim; it's up to the user to make sure it's valid SQL for their DB.

[grigi]

:thinking: That's an idea. It would have to be very restricted. We would have to support it in specific areas, e.g.:

• As F: function/aggregate (part of select)
• As Q: filter (part of where)

And we won't be able to provide much guarantees, or automatically know that this filter is part of a pre or post aggregation step if those exist. So basically just a way of extending with basic syntax.

It would be of limited use, but not too much work. @lntuition What are your thoughts? Could we consider this as part of the F/Q refactor that you are talking about?

[hqsz]

Yeah. I agree that it have to be limited use. I think expand and refactor current F and Q to support custom SQL is good choice.

[gdmoore]

It's definitely a "last resort" feature. For example Pony-orm doesn't support Python enums. You can write a converter to create the model initially, but every query you write will be broken since it doesn't know how to serialize an enum. In a previous project we worked around it by using raw_sql for only the portion of the query involving the enum.

None of that applies to tortoise but I'm sure there will be the occasional weird use case someone has.

Supporting it in both F and Q objects would probably be great.

[marcoaaguiar]

For single, should it be kwarg based: execute("", ...) or dict based: execute("", {...}) ?

You can easily use a dict in a kwarg signature: execute("<SQL>", **dict), the converse is a bit bit more inconvenient