upsert multi

[feluelle]

Is your feature request related to a problem? Please describe.

If I currently want to upsert multiple records I would have to call upsert for each model which means a lot of overhead and unnecessary db calls. A Multi-upsert can also be done in just one query.

Describe the solution you'd like

I would like to have an upsert function that allows inserting or updating multiple models. It doesn't have to be in one query necessarily. We can also continue with the current strategy using get and create or update.

Describe alternatives you've considered

The current alternative (postgres specific) I am using is:

from sqlalchemy.dialects import postgresql

stmt = postgresql.insert(...).on_conflict_do_update(...)
await session.execute(statement=stmt, params=...)

[JakNowy]

Postgresql solution looks best, but it makes it fail if other dbms is used.

Honestly I would opt for focusing on postgresql as the most popular dbms and making it as efficient as possible (I don't quite like the get and create approach as it costs additional call). That way we could efficiently cover bulk operations of all crud types. Igor mentioned that we might redesign some parts of the library and relase a breaking 1.x.x version, so that might be a good moment to get that transition.

For now I think we should stick to the get and create/update approach for consistency. If you need that done soon, feel more than welcome to open a PR. Otherwise I might cover that but I'm having a few other issues to be finished first.

@igorbenav your thoughts?

[igorbenav]

I think what we should aim for eventually is a db "engine" approach, falling back to this get and create when engine is not specified, identified or covered, I think that would justify a breaking version. Until we do something like this, I believe creating the fallback would be the way to go

[igorbenav]

Btw there's a lot that still needs to be done before we (as in me and @JakNowy) actually get to creating the db engine approach, so if you feel like you could do it (and need it done), @feluelle, we'd be more than happy.

[feluelle]

Cool, will give it a try.