SQLx optionally update values

help
1

I'm building a CRUD API with axum and SQLx, and now want to write the "update" part.

The comments table in my database has fields name, email, content.

Now for the "update" API call it should be possible to update any combination of those values. So, for example, you can only update content, but you can also update all three values, or two of the values.
For this, the handler receives an instance of:

struct UpdateCommentInput {
    name: Option<String>,
    email: Option<String>,
    content: Option<String>,
}

Now I want to turn this into an SQLx Query which updates the respective values.

Does anybody have an idea how to do this elegantly? (perhaps somehow with QueryBuilder?)

1 Like
2

QueryBuilder looks right. I've not used sqlx in anger, or tested this but this sort of thing ought to work: playground

I haven't tested it I'm afraid!

It's quite a bit of boilerplate, might be a good reason to make a proc macro. Might not seem very elegant if you're looking for a nice one liner :smiley:

I opted for a ...Fields enum because it seems likely you'll want to insert something where the fields aren't all the same one day.

I am curious what experienced sqlx users do for this kind of thing!

1 Like
3

Such dynamic query building does not really fit the designed use case of sqlx, as it mostly prioritize statically known queries. You might want to use a real query builder for such dynamic constructs.
As diesel maintainer I can suggest diesel here, as it has built-in primitives for this case. Otherwise other libraries like sea-orm will likely work as well.

2 Likes
4

Thanks for the suggestion, and I will try diesel when I need an ORM, but right now I don't want/need an ORM. (For example, some queries are not just selecting some data from a table by an easy condition, but I need recursive common table expressions.)

5

Just as a headsup: Diesel allows you to write queries as SQL strings as well. So if you ever hit something that's not supported by the DSL you can write your query via for example diesel::sql_query (or extend the DSL for that).