How to error on Not Null constraint (Postgres)?

[dhax]

If you're having a generation problem please answer these questions before submitting your issue. Thanks!

What version of SQLBoiler are you using (sqlboiler --version)?

2.5.0

If this happened at generation time what was the full SQLBoiler command you used to generate your models? (if not applicable leave blank)

If this happened at runtime what code produced the issue? (if not applicable leave blank)

What is the output of the command above with the -d flag added to it? (Provided you are comfortable sharing this, it contains a blueprint of your schema)

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

CREATE TABLE users (
  id serial NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  email varchar(255) NOT NULL UNIQUE, // doesn't matter if varchar or text
  name varchar(255) NOT NULL,
  roles varchar[] NOT NULL,
  PRIMARY KEY (id)
);

Further information. What did you do, what did you expect?

I might just get something wrong here, but can't figure out if this is the intended behavior. From the docs I get the idea that if I try to insert a user struct with no email or name field set like this and without whitelist option:

user :=&models.User{Name: "test"}
err := user.Insert(db)

Then error should be: models: unable to insert into users: pq: null value in column "email" violates not-null constraint

But instead the entry is successfully inserted with empty string. I guess this is because the struct is initialized with its null value for string, an empty string.

I am only able to get the desired solution if I define the column like this in my sql: name text NOT NULL DEFAULT NULL,

Also what makes me wonder is, that for above table if I try to insert a user without roles field, which in this case is a varchar array, then I get the expected error of not null violation.

What am I missing here, should the above return a not null constraint violation error on insert? How can I achieve that the not null constraint is respected?

Is there a better in-built solution than defining it on the database level like so?

alter table users add constraint email_length check (length(email) >= 5);

Thank you

[aarondl]

@dhax Can you show the generated struct for User? For all null fields you should see the NullString field which should behave appropriately. There may be a bug in the null detection for your circumstance.

[dhax]

// User is an object representing the database table.
type User struct {
    ID               int               `boil:"id" json:"id" toml:"id" yaml:"id"`
    CreatedAt        null.Time         `boil:"created_at" json:"created_at,omitempty" toml:"created_at" yaml:"created_at,omitempty"`
    UpdatedAt        null.Time         `boil:"updated_at" json:"updated_at,omitempty" toml:"updated_at" yaml:"updated_at,omitempty"`
    Email            string            `boil:"email" json:"email" toml:"email" yaml:"email"`
    Name             string            `boil:"name" json:"name" toml:"name" yaml:"name"`
    Role             types.StringArray `boil:"role" json:"role" toml:"role" yaml:"role"`
        LoginToken       null.String       `boil:"login_token" json:"login_token,omitempty" toml:"login_token" yaml:"login_token,omitempty"`
    R *userR `boil:"-" json:"-" toml:"-" yaml:"-"`
    L userL  `boil:"-" json:"-" toml:"-" yaml:"-"`
}

This looks good I guess and all tests in models/ pass. The NullString behaves correctly. So should this attempt to insert following struct result in an error? Because it does not:

user = &models.User{
    Roles: []string{"USER"},
}
if err := user.InsertG(); err != nil {
    return err
}

[aarondl]

Ah yeah. So your expectations are a bit off from what sqlboiler actually does for null.

By virtue that it's a string and not a nullstring means that just like in Go terms, the string cannot be null. However EMPTY string is a completely different matter. And when you're saving your record in the database it's going to save an empty string. Although this might not be what you want I'd argue that it's correct behavior. I'd encourage use of a validation library or database length constraints to ensure data integrity.

I'll close this as an issue because it's as designed, but please continue the conversation if you have more questions or would like to debate how it's being done.

[dhax]

Thank you very much for clarifying this. This behavior is totally fine, no reason to argue about it :)