Generating constraints for usage in errors/conflicts

[daddz]

I saw this nice PR: #237 and it sparked an idea but I don't know if it's feasible.

Would it be possible to also generate variables/fields for the (named?) constraints on a table?

Bogus example:

ALTER TABLE pilots ADD CONSTRAINT uniq_email UNIQUE (email);
ALTER TABLE pilots ADD CONSTRAINT multi_field_example UNIQUE (field1, field2);

Would generate some fields like this:

models.Pilots.Constraints.UniqEmail
models.Pilots.Constraints.MultiFieldExample

Which could be checked against in error cases:

if _, err := models.Pilots.Insert(...); err != nil {
  if models.Pilots.Constraints.UniqEmail.IsErr(err) {
    fmt.Println("email must be unique")
  }
  // alternative api idea
  if constraints.IsErr(models.Pilots.Constraints.UniqEmail, err) {
  // ...
  }
}

Maybe it could also be used in e.g. conflicts for Upsert for constraints that span multiple columns:

models.Pilots.Upsert(ctx, db, true, []string{models.Pilots.Constraints.MultiFieldExample}, ...)

I think something like this could greatly enhance error and conflict handling.

I didn't look into the PR in detail yet so the generation part might be solved/available already

[stephenafamo]

This would be nice.

The primary bottleneck will be reading the constraint information from each DB. Once that is done, generating code would be relatively straightforward.

[daddz]

@mbezhanov I saw you have some plans that (I think) are going in this direction (#237 (comment))

What are your thoughts on this?

[mbezhanov]

@daddz I'll look into this in the following days and make a proposal.

[mbezhanov]

@daddz @stephenafamo so I put some thought into this, and here's what I came up with.

As you know, the errors package in Go allows an error type to provide an Is method in order to be treated as equivalent to another error type. During code generation, we could create custom error types from the unique constraints in each table and then use these custom error types in conditional statements as follows:

if _, err := models.Pilots.Insert(...); err != nil {
  if errors.Is(models.PilotErrors.ErrUniqueName, err) {
    // do something to handle the error
  }
}

To facilitate this, we could have the following type definition in bob_main.go:

type errorUniqueConstraint struct {
  name string // the name of the unique constraint
}

func (e *errorUniqueConstraint) Error() string {
  return e.name
}

func (e *errorUniqueConstraint) Is(target error) bool {
  err, ok := target.(*mysql.MySQLError)
  if !ok {
    return false
  }
  return err.Number == 1062 && strings.Contains(err.Message, e.name)
}

Then, in the generated pilots.go file we could have something like this:

type pilotErrors struct {
  ErrUniqueName error
}

var PilotErrors = pilotErrors{
  ErrUniqueName: &errorUniqueConstraint{name: "pilots_name_IDX"},
}

Obviously some of the hard-coded things (e.g. the constraint name, the MySQL error code, etc.) can be defined as constants and variables.

What do you think?

[stephenafamo]

I'm conflicted...

On one hand, this makes it driver-independent.
It is also probably easier to customize for a user because they only need to provide a different regexp to match.

On the other hand, string matching for errors feels awkward.

[daddz]

Having something driver-independent would be great but I don't think it would be a nice/maintainable due to the differences in error reporting.

If the stdlib database/sql would provide better abstractions for error reporting from drivers then it would be a different story maybe.

Based on the assumption that the current driver landscape is pretty much stable it might be worth to think about e.g. supporting only the 1-2 most-widely used ones for each database.

And in order to make things simple, one might start with driver-specific code-generation only for the error parts. That way the whole driver-specific part should be fairly small and it's simpler to try out different concepts/ideas on how to do that in general.

Maybe the string matching could be used as a fall-back if a driver is not supported?

If that works out nicely it could later be extended to other parts that might benefit from driver-specific code generation.

[stephenafamo]

I think the best way would be to do it this way:

1. Have driver specific code:
   • Similar to how you can select your uuid package with the postgres generator, you can select your connection driver
   • Allow configuration for adding custom drivers.
   • To prevent surprises, perhaps we require the driver to be specified and either have an error if none is specified, or skip the driver specific code
2. Allow users to setup custom drivers. Either through configuration or templates.

[mbezhanov]

Sounds good. I'll work on preparing a PR, aiming to support the drivers you mentioned earlier.

[mbezhanov]

I prepared a draft PR over here to propose an implementation: #248