Different set of fields to update and insert for Upsert method.

[atzedus]

Hello from sqlboiler :)

In generated code i want to use "Upsert" method, but i want insert one set of fields, but update on conflict different set.

Concrete example:

CREATE TABLE products (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text,
  sku text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz
);

CREATE UNIQUE INDEX ON products(sku);

After code generation

cols := models.ColumnNames.Products

models.Products.Upsert(
	ctx,
	db,
	true, // update on conflict
	[]string{cols.Products.SKU}, // conflict columns
	[]string{cols.Name, cols.UpdatedAt}, // update columns
	&models.ProductSetter{
		Name:             omit.From(model.Name),
		SKU:       omit.From(model.SKU),
		CreatedAt:        omit.From(time.Now()),
		UpdatedAt:        omit.From(time.Now()), // PROBLEM IS HERE
	},
)

I don't want insert any value in "updated_at" columns while creation, but i want to update it value when updating in conflict.

How can i achieve this in bob?

In sqlboiler for example i simply deal with Whitelist/Blacklist.

This is concrete example, but I'm sure there may be situations when i want insert one field set, but on conflict update completely different fields set.

[stephenafamo]

Well............ to be able to do this, we have to first look at what the SQL will look like

INSERT INTO products (name, sku, created_at) 
VALUES ('name', 'sku', '2020-01-02')
VALUES ('name', 'sku', '2020-01-02')
ON CONFLICT ('sku') DO UPDATE SET updated_at = '2020-01-03'

The reason why this isn't easy to do with Bob, is that the methods are designed to be able to do bulk operations.

In a bulk operation, we will not be able to set updated_at individually for each value, so we have to use EXCLUDED.updated_at but that will not be present if we do not insert updated_at in the first place.

There are a few options.

1. Use the DB to handle timestamps

First, I would recommend adding DEFAULT CURRENT_TIMESTAMP to your created_at field so you never have to manually set it.

Next, I would recommend using triggers for created/updated timestamps. This is what I do, and this is also why Bob does not auto-handle timestamps. I think it is much more robust to do this in the DB.

CREATE FUNCTION set_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

-- Then for each table
CREATE TRIGGER set_updated_at
BEFORE INSERT OR UPDATE ON public.products FOR EACH ROW 
EXECUTE PROCEDURE set_updated_at_column();
-- Note that this is INSERT OR UPDATE
-- if you do not want updated_at present on insert, use only BEFORE UPDATE

With this setup, you never have to concern yourself with setting timestamps in your code.

2. Build the upsert query by hand

If you would prefer to handle timestamps in code, you can handle this case by building the upsert query manually:

cols := models.ColumnNames.Products

models.Products.InsertQ(
    ctx, db,
    im.OnConflict(cols.Products.SKU).DoUpdate().
        Set(cols.UpdatedAt, psql.Arg(time.Now())).
        SetExcluded(cols.Name),
    &models.ProductSetter{
        Name:      omit.From(model.Name),
        SKU:       omit.From(model.SKU),
        CreatedAt: omit.From(time.Now()),
    }.Insert(),
    // Add other setters
)

[atzedus]

Oh, thanks!

1. Yes, it has DEFAULT now() in my example for created_at. And, yes, i know about triggers. Moreover, i also prefer set fields like created_at at database level.

I think that my example was too specific.
My question rather hypothetical. For example, if i have a table with fields A, B. How to make it so that when inserting, only A is inserted, and when updating, only B is updated.

Now, i researching for ability to migrate from sqlboiler to bob, because it more flexible and more thoughtful.

I think, that the second approach is quite suitable for solving my problem. I need to think about generics and own helper for this situation.

Anyway, thank you for your comments and thank you for your great work.

[stephenafamo]

I think, that the second approach is quite suitable for solving my problem. I need to think about generics and own helper for this situation.

Anyway, thank you for your comments and thank you for your great work.

Thank you

[atzedus]

Unfortunately, I couldn’t implement the functionality I needed using InsertQ.

The fact is that the library does not have a method for the DoUpdate() expression, which would take a setter structure as input.

Instead of the solution you described from point 2, I think it would be useful to have the following option:

cols := models.ColumnNames.Products

models.Products.InsertQ(
    ctx, db,
    im.OnConflict(cols.Products.SKU).DoUpdate().
    SetMany(&models.ProductSetter{
        Name:      omit.From(model.Name),
        UpdatedAt: omit.From(time.Now()),
    }).
    &models.ProductSetter{
        Name:      omit.From(model.Name),
        SKU:       omit.From(model.SKU),
    }.Insert(),
    // Add other setters
)

I tried to implement this functionality in the library, I wanted to prepare a PR, but I just got lost in generics and don’t understand what I’m doing wrong. Perhaps you can suggest a solution?

I'll try to show parts from the code with changes, they are small.
I really hope you can help me, I'm have no ideas :(

Type-param added for *dialect.UpdateQuery in file https://github.com/stephenafamo/bob/blob/main/dialect/psql/im/qm.go#L65

// The column to target. Will auto add brackets
func OnConflict(columns ...any) mods.Conflict[*dialect.InsertQuery, *dialect.UpdateQuery] {
	return mods.Conflict[*dialect.InsertQuery, *dialect.UpdateQuery](func() clause.Conflict {
		return clause.Conflict{
			Target: clause.ConflictTarget{
				Columns: columns,
			},
		}
	})
}

func OnConflictOnConstraint(constraint string) mods.Conflict[*dialect.InsertQuery, *dialect.UpdateQuery] {
	return mods.Conflict[*dialect.InsertQuery, *dialect.UpdateQuery](func() clause.Conflict {
		return clause.Conflict{
			Target: clause.ConflictTarget{
				Constraint: constraint,
			},
		}
	})
}

Added type-param for setter, as well as the method itself.

https://github.com/stephenafamo/bob/blob/main/mods/conflict.go#L9

type Conflict[
	Q interface{ SetConflict(clause.Conflict) },
	U interface{ AppendSet(exprs ...any) },
] func() clause.Conflict

.....

func (c Conflict[Q, U]) SetMany(setter interface{ Apply(U) }) Conflict[Q, U] {
	conflict := c()

	setter.Apply(&conflict.Set) // PROBLEM IS HERE

	return Conflict[Q, U](func() clause.Conflict {
		return conflict
	})
}

setter.Apply(&conflict.Set) <-- problem is here.

Message from compiler cannot use &conflict.Set (value of type *clause.Set) as U value in argument to setter.Applycompiler[IncompatibleAssign](https://pkg.go.dev/golang.org/x/tools/internal/typesinternal#IncompatibleAssign)

After all, U is an interface with the AppendSet method and the conflict.Set structure contains this method.

I have no ideas what's wrong :(

[stephenafamo]

I can understand the issue, and the mental model you should keep in mind is that the query builder is mostly separate from the code generation.
The query builder does not know what a Setter is or what the model is. It only requires "Mods" that change the query.

With this in mind, what you can do is implement an OnProductConflict mod that does what you want. For example:

func OnProductConflict() bob.Mod[*dialect.InsertQuery] {
	cols := models.ColumnNames.Products

	// Columns with static values
	static := map[string]any{
		cols.UpdatedAt: time.Now(),
	}

	// Columns that use the inserted value
	useInserted := []string{cols.CreatedAt}

	set := []any{}
	for col, val := range static {
		set = append(set, psql.Quote(col).EQ(val))
	}

	for _, col := range useInserted {
		set = append(set, psql.Quote(col).EQ(psql.Raw(fmt.Sprintf("EXCLUDED.%s", expr.Quote(col)))))
	}

	return mods.Conflict[*dialect.InsertQuery](func() clause.Conflict {
		return clause.Conflict{
			Do: "UPDATE",
			Target: clause.ConflictTarget{
				Columns: []any{cols.Products.SKU},
			},
			Set: clause.Set{Set: set},
		}
	})
}

Then you can use this in your InsertQ query like this:

models.Products.InsertQ(
    ctx, db,
    OnProductConflict(), // HERE
    &models.ProductSetter{
        Name:      omit.From(model.Name),
        SKU:       omit.From(model.SKU),
        CreatedAt: omit.From(time.Now()),
    }.Insert(),
    // Add other setters
)

---

So, once the mod system is understood, you can play around with it and create all sorts of custom mods.

This is exactly how ProductSetter implements bob.Mod[*dialect.UpdateQuery] and ProductSetter.Insert() returns a mod for bob.Mod[*dialect.InsertQuery]

You can look at the generated code and see how it looks.

[atzedus]

Wow, exactly! Thank you very much! Now I understand that I chose the wrong way.

Your example is useful, but i think, that it very boilerplate. Why?
Because in my project, the upsert query is frequency case.

So, in generated code the Insert() method for setters is confusing me.
I think it would be more correct to naming it: InsertValues().

I suggest adding method in file https://github.com/stephenafamo/bob/blob/main/mods/conflict.go:

func (c Conflict[Q]) SetMany(exprs ...bob.Expression) Conflict[Q] {
	conflict := c()

	for _, e := range exprs {
		conflict.Set.Set = append(conflict.Set.Set, e)
	}

	return Conflict[Q](func() clause.Conflict {
		return conflict
	})
}

// OR/AND method

func (c Conflict[Q]) SetFromClause(cl clause.Set) Conflict[Q] {
	conflict := c()
	conflict.Set.Set = append(conflict.Set.Set, cl.Set...)

	return Conflict[Q](func() clause.Conflict {
		return conflict
	})
}

And in templates for generation i suggest to add something like this (based on my example):

func (s ProductSetter) SetClause() clause.Set {
	cs := clause.Set{}

	if !s.ID.IsUnset() {
		cs.AppendSet(expr.OP("=", psql.Quote("id"), psql.Arg(s.ID)))
	}
	if !s.Name.IsUnset() {
		cs.AppendSet(expr.OP("=", psql.Quote("name"), psql.Arg(s.Name)))
	}
	if !s.SKU.IsUnset() {
		cs.AppendSet(expr.OP("=", psql.Quote("sku"), psql.Arg(s.SKU)))
	}
	if !s.CreatedAt.IsUnset() {
		cs.AppendSet(expr.OP("=", psql.Quote("created_at"), psql.Arg(s.CreatedAt)))
	}
	if !s.UpdatedAt.IsUnset() {
		cs.AppendSet(expr.OP("=", psql.Quote("updated_at"), psql.Arg(s.UpdatedAt)))
	}

	return cs
}

// OR/AND method

func (s ProductSetter) Sets() []bob.Expression {
	cs := []bob.Expression{}

	if !s.ID.IsUnset() {
		cs = append(cs, expr.OP("=", psql.Quote("id"), psql.Arg(s.ID)))
	}
	if !s.Name.IsUnset() {
		cs = append(cs, expr.OP("=", psql.Quote("name"), psql.Arg(s.Name)))
	}
	if !s.SKU.IsUnset() {
	 	cs = append(cs, expr.OP("=", psql.Quote("sku"), psql.Arg(s.SKU)))
	}
	if !s.CreatedAt.IsUnset() {
		cs = append(cs, expr.OP("=", psql.Quote("created_at"), psql.Arg(s.CreatedAt)))
	}
	if !s.UpdatedAt.IsUnset() {
		cs = append(cs, expr.OP("=", psql.Quote("updated_at"), psql.Arg(s.UpdatedAt)))
	}

	return cs
}

As a result it then can be used like this in code:

	bmodels.Product.InsertQ(
		ctx, bob.DB{},
		im.OnConflict(cols.Products.SKU)
			DoUpdate().
			SetFromClause(bmodels.ProductSetter{
				Name:      omit.From(model.Name),
				UpdatedAt: omit.From(model.UpdatedAt),
			}.SetClause()),
		bmodels.ProductSetter{
			Name: omit.From(model.Name),
			SKU:  omit.From(model.SKU),
		}.Insert(),
	)

AND/OR

	bmodels.Product.InsertQ(
		ctx, bob.DB{},
		im.OnConflict(cols.Products.SKU)
			DoUpdate().
			SetMany(bmodels.ProductSetter{
				Name:      omit.From(model.Name),
				UpdatedAt: omit.From(model.UpdatedAt),
			}.Sets()...),
		bmodels.ProductSetter{
			Name: omit.From(model.Name),
			SKU:  omit.From(model.SKU),
		}.Insert(),
	)

What do think about it?

As variant, there it will be usefull to extend standard templates via configuration, as it made in sqlboiler.

[stephenafamo]

Your suggestion makes a lot of sense!!!!

I am leaning more towards using bob.Expression since that is easier to construct. I will also consider renaming the generated methods 🤔

As variant, there it will be usefull to extend standard templates via configuration, as it made in sqlboiler.

It is already possible, but unfortunately it is not well documented yet.

You would need to write a bit of code yourself. As a matter of fact all the generators are written in the same way.

Take a look at gen/bobgen-psql/main.go. You can use that as the base, copy and paste in say your-project/bob/main.go and tweak it as you please. You can add your own templates, modify the configuration, e.t.c.

[atzedus]

Yes, now I think that bob.Expression is really a better choice.
So, for the concept of “setter” it is quite logical to have methods for forming a list of “Values” and methods for forming a list of “Set” as a return type bob.Expression, so that they can be used in a variety of frequently used queries like:

• https://www.postgresql.org/docs/16/sql-insert.html#:~:text=NOTHING%0A%20%20%20%20DO%20UPDATE-,SET,-%7B%20column_name%20%3D%20%7B%20expression%20%7C%20DEFAULT
• https://www.postgresql.org/docs/16/sql-update.html#:~:text=table_name%20%5B%20*%20%5D%20%5B%20%5B%20AS%20%5D%20alias%20%5D-,SET,-%7B%20column_name%20%3D%20%7B%20expression%20%7C%20DEFAULT
• https://www.postgresql.org/docs/16/sql-merge.html#:~:text=merge_update%20is%3A%0A%0AUPDATE-,SET,-%7B%20column_name%20%3D%20%7B%20expression%20%7C%20DEFAULT

Take a look at gen/bobgen-psql/main.go. You can use that as the base, copy and paste in say your-project/bob/main.go and tweak it as you please. You can add your own templates, modify the configuration, e.t.c.

Hmm, yes, thanks, this really solution.
But I still wants to use such solution in the most extreme cases.
I hope we can achieve this by with minimal effort, as we discussed above.
I will experiment with this in my project and I think later I will be able to propose changes in PR.

[stephenafamo]

Thank you. I will likely add this soon, right now I'm doing a big refactor to fix some issues with multi-sided user-defined relationships. Once that is done I will likely implement this.

[stephenafamo]

Your concerns should be addressed by #140, #141 and #142

To make this easier to compose, OnConflict().DoUpdate() now takes conflict mods instead of using chainable methods.

You should be able to do this now:

	bmodels.Product.InsertQ(
		ctx, bob.DB{},
		im.OnConflict(cols.Products.SKU).DoUpdate(
			im.Set(bmodels.ProductSetter{
				Name:      omit.From(model.Name),
				UpdatedAt: omit.From(model.UpdatedAt),
			}.Expressions()...),
		),
		bmodels.ProductSetter{
			Name: omit.From(model.Name),
			SKU:  omit.From(model.SKU),
		}.InsertMod(),
	)