Add an API for upserting

[dantownsend]

Based on this discussion: #248

We don't currently have an API for upserting.

This would be based around the ON CONFLICT clause you can add to INSERT queries.

https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

There will probably be two APIs needed. Something for insert queries, and a convenience method for objects queries.

For example:

# For insert
Band.insert(Band(name='Pythonistas')).on_conflict(...).run_sync()

# For objects
Band.objects().upsert(Band.name == 'Pythonistas', {Band.popularity: 5000}).run_sync()

[AbhijithGanesh]

I would like to work on this if It is open and unassigned.

[dantownsend]

@AbhijithGanesh Great, thanks. This might be a tricky issue though, just so you're aware!

[AbhijithGanesh]

Thanks for the heads up , I'll read upon docs and get this reviewed before it gets merged

[AbhijithGanesh]

Clarification: The upserting method/process is like HTTP PUT right?

[AbhijithGanesh]

This requires more interaction with the maintainer than I thought, where can I reach out to you? @dantownsend

[AbhijithGanesh]

Based on this discussion: #248

We don't currently have an API for upserting.

This would be based around the ON CONFLICT clause you can add to INSERT queries.

https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

There will probably be two APIs needed. Something for insert queries, and a convenience method for objects queries.

For example:

# For insert
Band.insert(Band(name='Pythonistas')).on_conflict(...).run_sync()

# For objects
Band.objects().upsert(Band.name == 'Pythonistas', {Band.popularity: 5000}).run_sync()

So I am implementing the insert part at query>methods>insert.py and upsert as a new file.
Would that be okay?

[dantownsend]

You're best reaching me here - I don't use much else besides email.. You can start a separate discussion thread if you prefer.

That's right - an UPSERT is a bit like a PUT. When an insert fails due to a unique constraint, it will update the existing row instead of raising an error.

Here are the docs:

https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
https://sqlite.org/lang_conflict.html

It's a tricky one - I need to think through the API a bit, as the SQLite and Postgres implementations are a bit different.

Feel free to experiment with it while I give it some more thought, or feel free to pick up another issue in the meantime.

[AbhijithGanesh]

On a high level, the insert method and upsert method would ideally be sharing similar database querystrings right(Exclude the query, just the structure)

[dantownsend]

That's right, it will most likely be an on_conflict argument to Insert. If you look at the codebase, there are many 'delegates'. For example:

piccolo/piccolo/query/mixins.py

Lines 38 to 53 in 8ec9d10

	@dataclass
	class Offset:
	__slots__ = ("number",)
	number: int
	def __post_init__(self):
	if type(self.number) != int:
	raise TypeError("Limit must be an integer")
	@property
	def querystring(self) -> QueryString:
	return QueryString(f" OFFSET {self.number}")
	def __str__(self) -> str:
	return self.querystring.__str__()

We would have a new one called OnConflict.

You can set the on conflict value using the constructor:

Band.insert(Band(name='Pythonistas'), on_conflict=Conflict.do_nothing).run_sync()

Or you can use a method, which sets the same underlying value on the OnConflict delegate:

Band.insert(Band(name='Pythonistas')).on_conflict(Conflict.do_nothing).run_sync()

And Conflict would be an Enum.

[AbhijithGanesh]

Thanks for this vital information, Can you please assign me to this issue

[AbhijithGanesh]

Can you quickly tell me about delegates quickly?
I know they're a part of the mixins, can you please give me a description(I searched the code base for onconflict and couldn't find relevant references)

[dantownsend]

There is no code related to on conflict yet. A delegate is just a class which stores data - if you look at mixins.py there's a bunch of them, and they're all pretty similar. The reason we use them instead of inheritance is because it's better for type annotations.

[AbhijithGanesh]

got it, I shall use them