Whenever I try to create and insert a new User in the Users table (either with the User.new DAO method or the Users.insert method), an enumeration field accountstatus that is described as non-null, with a default value, makes the insertion fail as of null value in column "accountstatus" violates not-null constraint.

Here is the code for the table definition and the user DAO:

 object Users : UUIDTable() {
     val email = varchar("email", 255).uniqueIndex()
     val role = enumeration<Role>("role")
     val registrationDate =
         datetime("registrationDate").default(Clock.System.now().toLocalDateTime(TimeZone.currentSystemDefault()))
     val disabledDate = datetime("disabledDate").nullable()
     val accountStatus = enumeration<AccountStatus>("accountStatus").default(AccountStatus.ACTIVE)
 
     enum class Role {
         PROFESSOR, STUDENT
     }
 
     enum class AccountStatus {
         ACTIVE, DISABLED
     }
 }

 class User(id: EntityID<UUID>) : UUIDEntity(id), UserData {
     companion object : UUIDEntityClass<User>(Users)
 
     override var email by Users.email
     override var role by Users.role
     override val registrationDate by Users.registrationDate
     override var disabledDate by Users.disabledDate
     override var accountStatus by Users.accountStatus
     val sessions by Session referrersOn Sessions.userId
 }

Here is the code that throws the exception:

 // ...
 transaction {
     User.new {
         email = email /* logic omitted */
         role = role /* logic omitted */
     }
 }

This issue has been discussed at GitHub.

Question

For inserts Exposed will (for columns with no explicit value) populate all inserts with defaults/null from the Exposed table object and not actually use the one defined in the actual table on the database by leaving out the column in the insert statement.

This has a couple of problems:

It is impossible to actually perform an INSERT using the database default value. ie. it may not be known or controlled by another team. It boils down to a requirement to keep the code in sync with the database but this is at odds with always on services that by definition must have new and old code running on the database at the same time.

A more flexible and less opinionated design would involve:

Only use .default() and .defaultExpression() for table creation Open for simultaneous use of .clientDefault() (and .default()) which then serves as the application default (This is how I always assumed it worked) instead of having .default() act as both table and client insert defaults. Not have an implicit default of NULL for nullable columns. This is a requirement for first bullet.

A note is that I am not talking about Exposed Dao but the plain query dsl. Looking at the git history it seems that these defaults were introduced as opinionated defaults for dao which I guess makes much more sense (other than they are still impossible to opt out of). They spilled over into the dsl though which I find problematic since the dsl ought to support all (or atleast standard) sql. I assume the current behaviour can be moved a layer up into the dao code to keep the behavior of dao intact.

I might be willing to do the bulk of the work involved in this but would like to confirm if it would be desirable or not.

Similar issue #345

Answer

It looks like the problem is already solved if I understand the problem correct.

If the problem is that Exposed aggressively requires to define values for all the fields, and does not allows to rely on the db side values generation, it could be solved using column method databaseGenerated() (and withDefinition() additionally)

Actually, at the current moment there are 3 ways (examples are made with PostgresDB):

default()/defaultExpression() (customizes column DDL, and applied to insert statements)

object TestTable : IntIdTable("test") {
    val key = uuid("key")
        .default(UUID.fromString("a344698c-3c0d-46a7-9847-dc308665d9c8"))
}

TestTable.insert {  }

Performed sql:

CREATE TABLE IF NOT EXISTS test (
  id SERIAL PRIMARY KEY, 
  "key" uuid DEFAULT 'a344698c-3c0d-46a7-9847-dc308665d9c8' NOT NULL
)

INSERT INTO test ("key") VALUES ('a344698c-3c0d-46a7-9847-dc308665d9c8')

clientDefault() (does not affect DDL):

object TestTable : IntIdTable("test") {
  val key = uuid("key")
      .clientDefault { UUID.randomUUID() }
}

TestTable.insert {  }

CREATE TABLE IF NOT EXISTS test (
  id SERIAL PRIMARY KEY, 
  "key" uuid NOT NULL
)

INSERT INTO test ("key") VALUES ('e69515f6-6bed-4c95-aa75-9c0ba7a0ac77')

databaseGenerated().withDefinition() (this variant modifies table column, and the value is skipped for insert)

object TestTable : IntIdTable("test") {
    val key = uuid("key")
        .databaseGenerated()
        .withDefinition("DEFAULT gen_random_uuid()")
}

TestTable.insert {  }

CREATE TABLE IF NOT EXISTS test (
  id SERIAL PRIMARY KEY, 
  "key" uuid DEFAULT gen_random_uuid() NOT NULL
)

INSERT INTO test  DEFAULT VALUES

Looking at these variants, it's probably, that users could be confused, because there are variants for client-side generation, db-side generation and magic default() that applied to both.

Probably we actually do not need the default() variant, because it customized DDL to generate values on the database side, but after that anyway puts the same values into the queries.

Probably it would be better to have only two options (client or db side generations) and consistent methods set like:

• clientGenerated(value)
• clientGeneratedByExpression(expression: )
• databaseGenerated()
• databaseGeneratedByDefinition(definition)

I think that the most interesting question here: are there any use cases when default() can not be replaced by clientDefault() / databaseGenerated() options? If there are no such use cases we may mark default() as deprecated at least. @e5l @bog-walk what do you think about it?

Anyway, it's just some proposals of how to make it more consistent. According to the initial question of the issue, it looks to me like the problem is gone, if not, let me know about it.

Summary

databaseGenerated().withDefinition() seems to be what you are looking for.