Add upsert sql method in orm

[mid1221213]

Describe the feature

upsert is roughly an INSERT OR UPDATE SQL method. It should insert a row, and if unique fields would be duplicated, the method would update the existing row instead.

This is more or less easily manageable in the V orm DB available backends, e.g. INSERT INTO … ON CONFLICT DO UPDATE SET … for Postgres and SQlite, or INSERT INTO … ON DUPLICATE KEY UPDATE … if using MySQL.

Use Case

I need to make an upsert command and I have to fallback to using SQL expressions instead of the multi-backend orm elegant method.

Proposed Solution

Example use, something like:

module model

@[table: 'config']
pub struct Config {
pub:
	key   string @[primary]
	value string
}

module main

import model

new_config := model.Config{
	key:   'hello'
	value: 'world'
}

sql my_db {
	upsert new_config into model.Config
}

Other Information

I've set the checkbox below about my ability to implement the feature, I even started to work on it, but I don't know enough V's internals and workflow to list all places to modify. And a quick search showed more than I first thought (including generators).

The list of files I started to modify is:

vlib/db/mysql/orm.c.v
vlib/db/pg/orm.v
vlib/db/sqlite/orm.v
vlib/orm/orm.v
vlib/v/ast/ast.v
vlib/v/parser/orm.v

Acknowledgements

• I may be able to implement this feature request
• This feature might incur a breaking change

Version used

V 0.4.9 21874f9

Environment details (OS name and version, etc.)

All environments should be supported along with the currently available orm backends.

FWIW I'm using Debian GNU/Linux trixie/sid.

Note

You can use the 👍 reaction to increase the issue's priority for developers.

Please note that only the 👍 reaction to the issue itself counts as a vote.
Other reactions and those to comments will not be taken into account.

[huly-for-github]

Connected to Huly®: V_0.6-22352

[mid1221213]

I forgot to say, I ended up using a construct like:

sql my_db {
	insert new_config into model.Config
} or {
	sql my_db {
		update model.Config set value = new_config.value where key == new_config.key
	} or { panic(err) }
}

…which seems ± the same, but possibly with some caveats? And is less elegant 😉

[Avey777]

I like this proposal

[Avey777]

@mid1221213

#23921

Can you help me? I understand that you should also be writing about Veb. Thank you.