Update field to be null with Some(None)

[tqwewe]

When creating a prepared statement in something like sqlx or tokio-postgres, I'm having trouble creating a prepared update statement which supports updating fields to be null).

This would optimally be done with an Option<Option<T>> type (in my case I'd actually use it with MaybeUndefined from the async-graphql crate.
Where:

• None would not update the field at all
• Some(None) would update the field to be null
• Some(Some(value)) would update the field with the value

The main issue is that the statement would include fields only where applicable.
Eg:

UPDATE foos SET a = $1, b = $2, c = $3 WHERE id = $4;

Or if a is None, b is Some(None) and c is Some(Some(value)):

UPDATE foos SET b = null, c = $1 WHERE id = $2;

I looked through the sea-query docs but could not see anything which would provide this kind of functionality.

Is this supported? Or planned to be supported?

[tyt2y3]

Hi, can you post your current snippet of sea-query, and what you imagine the API could be?

[tqwewe]

I haven't actually used sea-query, I'm just looking at options for this case. Currently Diesel supports this because it's an ORM.

How I imagine the API may look, would be perhaps something like:

let table_update = TableUpdate {
  column_one: None, // Don't update column_one
  column_two: Some(None), // Update column_two to be null
  column_three: Some(Some(10)), // Update column_three to be 10
};

let (prepared_query, values): (String, Vec<&(dyn ToSql + Sync)>) = Query::update()
    .table(MyTable::Table)
    .values(&table_update)
    .where(Expr::col(MyTable::Id).eq(1)
    .build_prepared(PostgresQueryBuilder);
    
assert_eq!(
  prepared_query,
  "UPDATE my_table SET column_two = NULL, column_three = $1 WHERE id = $2"
);

assert_eq!(
  values,
  vec![Some(10), 1]
);

Where .values accepts a struct which derives a trait (perhaps Deserialize or whatever suits sea-query), and it returns a tuple of a prepared SQL statement, and a vector of the value references.

This would be optimal for my situation :)

[tyt2y3]

Thank you for the snippet. I can see your use case now. This is exactly one of the use cases we are tackling in SeaORM.

To make this happen in sea_query, we'd need:

1. a derive macro to interate over the struct fields
2. trait impl to convert Option<Option<T>> into Option<sea_query::Value>
3. an insert API to handle (Column, Option<sea_query::Value>)

I think we'd need a procedural macro (in a separate crate anyway).
I have made a conception of the generated code.

Are you familiar with proc marco and interested in contributing?

[tqwewe]

I'd truly love to contribute to this, but unfortunately I haven't written any macros in Rust before. I'd love to start learning it soon though.
If I get a chance to look into this further or have any updates I'll be sure to post them here.

[tyt2y3]

Actually we have a very similar piece of macro in SeaORM. I can let you take a peek on that.
My concern is, if we do too much at this level, we might end up implementing a half-baked ORM.
It'd still be quite a while before we actually release SeaORM, so meanwhile you might want to whip your own.

[tqwewe]

Yeah perhaps. If you're worried about it becoming bloated, you could consider using some feature flags.
Also I had a look online and on git for SeaORM but couldn't find it. Is it public?

I do have a use case for this in my project with an app using tokio-postgres.

[tyt2y3]

Invited you as our early access VIP ;)

[tyt2y3]

Now that SeaORM is out, what is your opinion on this matter? @acidic9

[tqwewe]

@tyt2y3 I'll have a deeper play with it and see how seaorm solves this probelm. Will get back to you soon!