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:
- SeaQuery or something similar (using a separate crate just to build INSERTS is very sad).
- 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).
- require form to include all needed data from the start (it is not possible sometimes).
- Manually construct all queries with format (literal torture).
- 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()
}