ORM needs to handle failed statements (sql 'or { panic(err) }' block)

[xy3]

What:

• catch failed SQL statements (e.g. update, insert) with an or { } block similar to array indexing

Why:

• currently there is no way to catch a failed update or insert statement, and if such an SQL statement fails, the server will crash
• in order to perform updates/inserts, you have to perform (sometimes multiple) SQL queries, e.g. to make sure a row with the same unique field doesn't already exist. This really slows down performance

I was discussing with @spytheman to try to work on this change myself, but unfortunately I think it's too difficult for me to complete.

I understand that V is still very much a new language and I like it a lot, but this makes it almost unusable for me for developing APIs.

Discord quote from @spytheman :

imho, it would be best, if sql statements can handle options, like x := a[idx] ? or x := m[key] ? can handle them - then the programmer will have full control over what to do in case of errors/failures
and it will look the same as other similar situations are processed
You would have to start by modifying vlib/v/ast/ast.v to have a place where to store the or blocks in the statements, then vlib/v/parser/sql.v to parse the optional or{} blocks and store them in the AST nodes of the statements, then in vlib/v/checker/orm.v and potentially vlib/v/checker/checker.v to check the new or blocks for static errors, and finally in vlib/v/gen/c/sql.v to generate the code for the new or blocks.
vlib/orm/orm.v may also need to be modified, not sure - afaik it is the runtime part of the ORM
after the feature works, vlib/v/fmt/fmt.v will need to be modified too, so that it formats the code for the new or {} blocks (otherwise they will be silently eaten, when doing v fmt -w file_with_sql_orm.v)
and of course it would need tests for the above, to prevent regressions

[spytheman]

After 8452644, the return type is ![]orm.Row, so you can handle errors in the usual V way, with or {} blocks.

[jacksonmowry]

Is there syntax in the ORM to return just one row? It seems that I can only get the ORM to return a slice of rows even with a limit 1 statement

[JalonSolov]

Sure, it could be done via a sumtype, but then you have to add the code to figure out whether you only got one row or an array of rows.

Much simpler for you to just use rows[0] when you used limit 1.

[medvednikov]

@jacksonmowry with this new approach everything is handled the same way. What if your limit 1 returned 0 rows? You'd have to handle an Option, and also handle the db error (Result).

Always returning ![]Row simplifies things.

[jacksonmowry]

@medvednikov @JalonSolov Great! Thank you. I was just wanting to make sure I wasn't missing a piece of syntax in the ORM update.