jet
jet copied to clipboard
go-jet
Pointer versions of expression factories
Is your feature request related to a problem? Please describe.
Currently, there are expression factories like String() to create literals for values, but they don't support pointers. This can be tedious, especially when copying data from one table to another, because model fields are by default pointers. Would it make sense to have pointer versions of the expression factories?
Describe the solution you'd like
For example,
func StringOrNULL(val *string) StringExpression {
if val == nil {
return StringExp(NULL)
}
return String(*val)
}
var friend := computeBestFriend()
Cat.UPDATE().
SET(
Cat.BestFriendName.SET(StringOrNULL(friend.Name))
)
Hi @anuraaga. When dealing with data retrieval, insert or update, recommended way is to use model types. Model types will add type and pointer safety to the query. And literal expressions(String, Float, ...) to use mainly for query conditions.
The same query using model type:
Cat.UPDATE(Cat.BestFriendName).
MODEL(
model.Cat{
BestFriendName: friend.Name
}
)
Oh actually I realized at least in my case I wouldn't be able to use MODEL. AFAIU, models can only be used with Go values, not expressions, so setting both that string field friend_name and e.g. created_at to CURRENT_TIMESTAMP() isn't possible. Perhaps this is too minor for adding pointer expression factories but what do you think?
Yeah, it is a valid point. For created_at there are other alternatives. It is possible to set created_at to time.Now() in model type, or define a column to be DEFAULT CURRENT_TIMESTAMP or use trigger.
There is a scenario in which update statement has to set some pointer value, plus some complex expression value (using different columns or value from select statement). It is not likely scenario, but if that's the case, developer can still write StringOrNULL, as you did.
It is something to think about and maybe add in the future.
It's impossible to use MODEL with ON_CONFLICT().DO_UPDATE(), so it's necessary to write some TypeOrNull helpers. While it's not that hard, it would be nice to have them in jet "out of box".
@houten11 I couldn't find any documentation on how to use the EXCLUDED.ColumnName. I can see from the doc there's a reference
Link.ID.SET(Link.EXCLUDED.ID),
https://github.com/go-jet/jet/wiki/INSERT#postgresql-sqlite-insert-with-on-conflict-update
I'm not sure how does it handle the pointer case, like the field can have value or can be null
Interestingly if I try to set nil, I find a runtime crash. @go-jet
Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description).
VALUES(100, "http://www.postgresqltutorial.com", "PostgreSQL Tutorial", DEFAULT).
ON_CONFLICT(Link.ID).DO_UPDATE(
SET(
Link.URL.SET(nil),
),
)
EXCLUDED is a standard postgres keyword. More info here - https://www.postgresql.org/docs/15/sql-insert.html.
Assuming we have this query:
Link.INSERT(Link.ID, Link.URL, Link.Name, Link.Description).
VALUES(100, "http://www.postgresqltutorial.com", "PostgreSQL Tutorial", DEFAULT).
VALUES(100, NULL, "PostgreSQL Tutorial", DEFAULT). // first conflict
VALUES(100, "http://www.pg2.com", "PostgreSQL Tutorial", DEFAULT). // second conflict
ON_CONFLICT(Link.ID).DO_UPDATE(
SET(
Link.URL.SET(Link.EXCLUDED.URL),
),
)
For the first conflict Link.EXCLUDED.URL will be NULL and for the second conflict it will be "http://www.pg2.com". This means db will store "http://www.pg2.com" as the URL for ID 100.
If you still want to set Link.URL to NULL, you can use: Link.URL.SET(StringExp(NULL)), or if you want to set some non-NULL value: Link.URL.SET(String("www.someurl.com"))
Wow, I wonder why I didn't Google that first. Thanks a lot for explaining in detail.
🙏