SQLx - Update (almost) arbitrary field in postgres table

help
1

Hi!

I hope this is not all too SQL specific and non-rust question :slight_smile:

I am experimenting with porting a REST backend written in PHP to rust actix-web with sqlx. Coming from PHP it really is mindblowing having the compiler even check sql syntax and types at compile time!

However I have bumped into one question. Say I have a Postgres table like:

CREATE TABLE foo (
    id SERIAL PRIMARY KEY,
    a VARCHAR(64) NOT NULL,
    b VARCHAR(128) NOT NULL,
    c INT NULL,
    d TIMESTAMP NOT NULL,
    e TIMESTAMP NULL,
    f BOOLEAN NOT NULL
)

with some fields of different types and some nullable but most of them not. Now I would like to make a REST-like api for updating this table.

In PHP I would just do something like the following(hack? probably bad but beside the point):

// What field to change
$field = $_POST['field'];

$value = $_POST['value'];
$id = $_POST['id'];

$changeable_fields = ['a', 'b', 'c', 'd', 'e', 'f'];
if(!in_array($field, $changeable_fields)) {
    die();
}

$con // We have validated $name so no SQL injection should be possible, probably...
    ->prepare("UPDATE foo SET $name = :value WHERE id = :id")
    ->bindValue(':value', $value)
    ->bindValue(':id', $id)
    ->execute();

How do I best achieve something similar with rust and sqlx, preferably without having to write one sql query per table field and still keeping the syntax and type checking? The api does not need to be compatible with the PHP version.

There happens to be a lot of endpoints in my app with similar situations and it would be nice to have some sort of good (perhaps even reusable) solution to this :slight_smile:

Solutions found when searching the internet

I found an answer to a similar question on stack overflow(cant remember where) recommending a query like:

UPDATE foo SET 
    a = COALESCE($1, a) -- Set `a` to the provided value if a is not null, else set it to its current value `a`
    b = COALESCE($2, b) -- Same for b and so on...
    ...
WHERE id = $5

where every value except the one to change would be null. However I do not believe that would work with nullable values, since there would be no way to differentiate between wanting to set a value to NULL and not providing a value.

I believe there also were some recommendations to simply always send all values, even the ones not changed. However I believe that would risk overwriting recent changes to the other fields which I would like to avoid.

2

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.