INSERTS and UPDATES with variable fields

[sinder38]

I frequently encounter a problem where I need to INSERT or UPDATE table rows that have optional data (not necessary or filled by database itself).
There are a few solutions that I am aware of but none of them feel right.

Here is a postgersql table:

CREATE TABLE ingredient
(
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1000) PRIMARY KEY,
    version     BIGINT                                                    NOT NULL DEFAULT 1,
    
    -- Timestamps
    creator_id  BIGINT                   NOT NULL,
    created_at  timestamp with time zone NOT NULL DEFAULT now(),
    updater_id  BIGINT,
    updated_at  timestamp with time zone,

    name        varchar(256)             NOT NULL,
    comment     varchar(256), 
    min_amount  FLOAT                    NOT NULL DEFAULT 0,
    max_amount  FLOAT                    NOT NULL DEFAULT 0,

    deleted bool                         NOT NULL DEFAULT false
);
SELECT trigger_updated_at('ingredient');

Its form struct and insert function:

#[derive(Debug, Deserialize)]
pub struct IngredientCreateForm {
	pub name: Option<String>,
	pub min_amount: Option<f64>,
	pub max_amount: Option<f64>,
	pub comment: Option<String>,
}

pub async fn create<'c,E>(
	ctx: &Ctx,
	executor: E,
	entity_c: IngredientCreateForm,
) -> Result<i64>
where
	E: Executor<'c, Database = Postgres>,
{

	let query = r#"
        INSERT INTO ingredient (
        	creator_id,
            name,
            min_amount,
            max_amount,
            comment
        )
        VALUES ($1, $2, $3, $4, $5)
        RETURNING id;"#;
	Ok(sqlx::query_scalar::<_, i64>(query)
		.bind(ctx.user_id())
		.bind(entity_c.name)
		.bind(entity_c.min_amount)
		.bind(entity_c.max_amount)
		.bind(entity_c.comment)
		.fetch_one(executor)
		.await?)
}

The problem in this code is that entity_c.min_amount and entity_c.max_amount could be None. if we try to insert into ingredient without min and max then everything works since it is set by default.

Ideally I want a solution that inserts only values that are Some.

Solutions that I know of:

1. SeaQuery or something similar (using a separate crate just to build INSERTS is very sad).
2. Insert whole object always by requesting existing entry from the database in case of UPDATE and manually setting all defaults in case of INSERT (more queries is bad).
3. require form to include all needed data from the start (it is not possible sometimes).
4. Manually construct all queries with format (literal torture).
5. Use code that gets non none fields names and their values. This problem feels like it should have been already solved so writing something your own feels not right... examples and their problems below.

This one needs to be a macros to be complete:

use std::any::Any;

#[derive(Debug)]
struct TestStruct {
    field1: Option<i32>,
    field2: Option<String>,
    field3: Option<f64>,
}

impl TestStruct {
    fn get_non_none_values(&self) -> (Vec<String>, Vec<Box<dyn Any>>) {
        let mut field_names = Vec::new();
        let mut values = Vec::new();

        if let Some(value) = &self.field1 {
            field_names.push("field1".to_string());
            values.push(Box::new(*value) as Box<dyn Any>);
        }
        if let Some(value) = &self.field2 {
            field_names.push("field2".to_string());
            values.push(Box::new(value.clone()) as Box<dyn Any>);
        }
        if let Some(value) = &self.field3 {
            field_names.push("field3".to_string());
            values.push(Box::new(*value) as Box<dyn Any>);
        }

        (field_names, values)
    }
}

Haven't tested this one(Value probably won't work) and it uses additional crates but demonstrates the idea:

use serde::Serialize;
use serde_json::Value;
fn get_non_none_fields<T: Serialize>(s: &T) -> Vec<(String, Value)> {
    let serialized = serde_json::to_value(s).unwrap();
    serialized.as_object().unwrap()
        .iter()
        .filter_map(|(k, v)| {
            if !v.is_null() {
                Some((k.clone(), v.clone()))
            } else {
                None
            }
        })
        .collect()
}

[CommanderStorm]

Why not simply entity_c.min_amount.unwrap_or_default() in the bind?
The last solutions seem overly error-prown to me.

Alternaively, you have a public struct and a database struct with From ... implemented to convert between them.

[sinder38]

entity_c.min_amount.unwrap_or_default() wont work unfortunately, in case of UPDATE since None value means we don't want to change existing row value. In case of INSERT we would have to copy or schema defaults for it to work properly. Basically it is solution 2

Conversion using From has the same problems as I understand it.

[CommanderStorm]

Okay. More serious try:

Why not a simple COALESCE($1, creator_id)?

use sqlx::PgPool;

async fn print_ingredient(pool:&PgPool){
    let val = sqlx::query!("SELECT * FROM ingredient").fetch_all(pool).await.unwrap();
    println!("ingredients: {val:?}");
}

#[tokio::main]
async fn main() {
    let pool = sqlx::postgres::PgPool::connect("postgres://postgres:CHANGE_ME@localhost:3333/postgres").await.unwrap();
    sqlx::migrate!().run(&pool).await.unwrap();
    let _ = sqlx::query!("INSERT INTO ingredient(creator_id) VALUES (1)").execute(&pool).await;
    print_ingredient(&pool).await;
    
    sqlx::query!(r#"UPDATE ingredient SET
                    creator_id = COALESCE($1, creator_id)
                    WHERE creator_id = 1"#r, Some(2)).execute(&pool).await.unwrap();
    print_ingredient(&pool).await;
    
    let no_id: Option<i64> =None;
    sqlx::query!(r#"UPDATE ingredient SET
                    creator_id = COALESCE($1, creator_id)
                    WHERE creator_id = 2"#r, no_id).execute(&pool).await.unwrap();
    print_ingredient(&pool).await;
}

[sinder38]

 let no_id: Option<i64> =None;
    sqlx::query!(r#"UPDATE ingredient SET
                    creator_id = COALESCE($1, creator_id)
                    WHERE creator_id = 2"#r, no_id).execute(&pool).await.unwrap();
    print_ingredient(&pool).await;

Seems very promising. I think I have seen something similar in https://github.com/launchbadge/realworld-axum-sqlx , but somehow discarded it. If this works in all my testcases it would be nice to add to the docs (they lack in insert and update examples)

[sinder38]

UPDATE seems solved. But in case of INSERT null value in column "min_amount" of relation "ingredient" violates not-null constraint happens if we try to insert a None value, since min_amount FLOAT NOT NULL DEFAULT 0 and we need its default. entity_c.min_amount.unwrap_or_default() solves this problem but that means we need to copy existing database defaults for every object. This copies my solution N2 and I don't think its a good solution

[CommanderStorm]

Why is unwrap_or_default not a good solution?
Seems to solve the issue at hand and is readable..

If you really care about not having duplicates, remove the default from the db.

[sinder38]

I am currently using this solution but with unwrap_or() as its the best one available.

I don't like that sqlx forces me to implement value defaults in rust code instead of sql, because this creates split situation where some values' defaults are set by the database and some are set by rust.

Yea sure I can move all to rust, but then db scheme won't display a bigger picture and rust code will be messier and you have to make sure write these defaults for every query or everything will break.

[sinder38]

INSERT INTO ingredient (min_amount) values(DEFAULT);
can be used in postgresql to use scheme defaults but this solution involves using format! to fill NULL values with it. not well done or reusable.
COALESCE($1, DEFAULT) doesn't work unfortunately.