Add rails style updates for ORM Struct

[sharkos]

Describe the feature

When writing SQL 'UPDATE' statements using V ORM, there is not a built-in record update feature similar to how Sequel or ActiveRecord in Ruby allow passing the object using Models. Instead, you must write out the "set = " syntax. This creates a maintainability issue as the struct changes or grows, as you have to goto the queries and also change them. It would be optimal if instead, you could pass a populated struct variable that updates the record.

Use Case

This feature reduces code sprawl and provides a more maintainable way to manage database tables mapped to structs as the schema changes or grows.

Proposed Solution

@[table: 'users']
pub struct User {
pub mut:
id int @[primary; sql: serial]
created_at time.Time @[default: 'now()']
updated_at time.Time @[sql_type: 'TIMESTAMP']
name ?string @[unique]
}

// Query the user with id 1 into 'thisuser' var
mut thisuser := sql db { select User where id == 1 }

// Change the user's name
thisuser.name = 'Changing The Name'

// Now save the record
// Something this this effect would work, pass the Struct/Database Table and the record struct with the data
sql db.update_record(User, thisuser)

Other Information

Reference Sequel Active Record section on Accessing Model values, note the ability to modify one or more fields and then run a .save method to update it.

https://sequel.jeremyevans.net/rdoc/files/README_rdoc.html#label-Accessing+record+values

Acknowledgements

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

Version used

0.4.8

Environment details (OS name and version, etc.)

Ubuntu Linux 24.04

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.

[jorgeluismireles]

Right now, to update you can do this https://play.vlang.io/p/051db61d57

import db.sqlite
import time

@[table: 'users']
pub struct User {
pub mut:
	id         int       @[primary; sql: serial]
	created_at time.Time @[default: 'now'] // now() not accepted by sqlite
	updated_at time.Time @[sql_type: 'TIMESTAMP']
	name       ?string   @[unique]
}

fn main() {
	mut db := sqlite.connect(':memory:')!
	sql db { create table User }!
	user := User{ name:'Original' }
	sql db { insert user into User }!
	// current update option
	db.exec('UPDATE users SET name = "Modified" where id = 1;')!
	record := sql db {
		select from User where id == 1
	}!
	println('${record[0].name?}') // prints "Modified"
}

You propose something like this:

...
record := sql db { select from User where id == 1 }
record[0].name = 'Second change'
// New feature!
db.update(record[0])!

I think the new db.update()! feature should return Result since the operation could fail for repeated keys already in other records. New feature could be smart enough to know what table and field is what is meant to be updated, I don't know...

[jorgeluismireles]

My bad (still learning), already is possible to use ORM for updates:

	sql db {
		update User set name = 'Modified' where id == 1
	}!

https://play.vlang.io/p/5fca858793

[sharkos]

Right, but you have to manually issue 'set' statements for each field that is modified. The RFE here is specifically for being able to make changes on an instance of the struct, and simply issue a save method call without having to write out the entire list of set statements in SQL. See the link to the Sequel ruby GEM that I posted in the original issue form. It demonstrates how that ORM performs a save without having to write any SQL.

[jorgeluismireles]

Seems using a comma, we can add more than one field SET field_1 = value_1 , field_2 = value_2

	sql db {
		update User set name = 'Modified', updated_at = time.now() where id == 1
	}!

https://play.vlang.io/p/2565999c44