Database Validation

Function Introduction

Admittedly, Jimmer's strongly typed SQL DSL can expose problems as much as possible at compile time, and most errors can be prevented before testing. But this is not enough.

The strongly typed SQL DSL can only improve the reliability of the entity model -> business code link, but cannot guarantee the reliability of the database structure -> entity model link.

There is also a derived problem. If developers get reliable applications in the test environment, how to ensure that the application remains reliable after being transplanted to the production environment?

Database validation is a very important feature that is used to verify the consistency between the database structure and the entity model definition.

info

Validation Rules

• Verify that table names, column names, sequence names are consistent with definitions in the entity model.

• Verify that the nullity of columns is consistent with the definitions of properties in the entity model.

  If a property is decorated with @OneToOne(inputNotNull = true) or @ManyToOne(inputNotNull = true), the nullity of the property itself is ignored. it is considered that the corresponding foreign key in the data cannot be null.

• For the id property of each entity in the entity model, verify that the database has a primary key constraint that exactly matches.

• For true foreign keys defined in the entity model (see True and False Foreign Keys), verify that the database has exactly matching foreign key constraints.

Enable Validation

There are two ways to start validation:

• Use Spring Boot Starter

  Modify application.yml (or application.properties)

  jimmer:
      database-validation-mode: ERROR 

• Use underlying API

  Java

  JSqlClient sqlClient = JSqlClient
      .newBuilder()
      .setDatabaseValidationMode(
          DatabaseValidationMode.ERROR
      )
      ...Omit other configurations...
      .build();

  Kotlin

  val sqlClient = newKSqlClient {
      setDatabaseValidationMode(
          DatabaseValidationMode.ERROR
      )
      ...Omit other configurations...
  }

DatabaseValidationMode is an enum type with three values:

• NONE: Don't validate database structure, this is the default behavior.

• WARNING: Validate database structure, if the database structure is inconsistent with the entity model definition, it does not prevent the program from running, just prints warning information in the log.

• ERROR: Validate database structure, if the database structure is inconsistent with the entity model definition, an exception will be thrown to prevent the program from running.

Resolve Table Conflicts

• Entity types must correspond to a table in the database, whether the table name is explicitly configured by the developer via @Table or automatically determined by the naming strategy.

• Association properties based on join tables must correspond to a real join table in the database, whether the table name is explicitly configured by the developer via @JoinTable or automatically determined by the naming strategy.

• The ID whose auto growth strategy is SEQUENCE must correspond to a sequence in the database, whether the sequence name is explicitly configured by the developer via @GeneratedValue or automatically determined by the naming strategy .

When the program starts, Jimmer needs to query the database metadata to verify whether these tables or sequences exist. And if they exist, whether their internal structures are correct (for tables).

However, since database often support multiple sub-databases, and sub-databases can authorize each other, the JDBC connection may see tables with the same name in different database users, such as db1.BOOK, db2.BOOK, and db3.BOOK. This is called table conflict.

Of course, Jimmer can extract the database name from the JDBC connection metadata, for example, extract "db1" from jdbc:mysql://localhost:3306/db1, and first look for table information in the automatically extracted sub-database for verification. However, this method is not always valid. As long as the specified table cannot be found in the current sub-database, it will look for it in other sub-databases, which may lead to table conflict issues.

To solve this problem, the following two methods are available:

• Specify strict table names or sequence names, for example:

  • Change @Table(name = "BOOK") to @Table(name = "db1.BOOK").

  • Change @JoinTable(name = "BOOK_AUTHOR_MAPPING") to @JoinTable(name = "db1.BOOK_AUTHOR_MAPPING").

  • Change @GeneratedValue(sequenceName = "BOOK_ID_SEQ") to @GeneratedValue(sequenceName = "db1.BOOK_ID_SEQ").

• Explicitly specify the sub-database name used for validation, which can be divided into two methods:

  There are two ways to start validation

  • Use Spring Boot Starter

    Modify application.yml (or application.properties)

    jimmer:
    database-validation:
        mode: ERROR
        catalog: db1
    info

    jimmer.database-validation-mode and jimmer.database-validation.mode are equivalent

  • Use underlying API

    Java

    JSqlClient sqlClient = JSqlClient
        .newBuilder()
        .setDatabaseValidationMode(
            DatabaseValidationMode.ERROR
        )
        .setDatabaseValidationCatalog("db1")
        ...Omit other configurations...
        .build();

    Kotlin

    val sqlClient = newKSqlClient {
        setDatabaseValidationMode(
            DatabaseValidationMode.ERROR
        )
        setDatabaseValidationCatalog("db1")
        ...Omit other configurations...
    }

Similarly, schema can also be specified, for example:

• Direct configuration: @Table(name = "mydatabase.myschema.BOOK")

• Configure in SpringBoot's application.yml file

  jimmer:
    database-validation:
      mode: ERROR
      catalog: mydatabase
      schema: myschema

• Underlying API configuration

  Java

  JSqlClient sqlClient = JSqlClient
      .newBuilder()
      .setDatabaseValidationMode(
          DatabaseValidationMode.ERROR
      )
      .setDatabaseValidationCatalog("mydatabase")
      setDatabaseValidationSchema("myschema")
      ...Omit other configurations...
      .build();

  Kotlin

  val sqlClient = newKSqlClient {
      setDatabaseValidationMode(
          DatabaseValidationMode.ERROR
      )
      setDatabaseValidationCatalog("mydatabase")
      setDatabaseValidationSchema("myschema")
      ...Omit other configurations...
  }

Temporarily ignore partial validation

Sometimes developers are developing a new feature that is incomplete and cannot be verified. Developers will naturally not run unfinished features and expect unfinished parts not to verify the database structure.

For this, Jimmer provides the annotation @org.babyfish.jimmer.sql.DatabaseValidationIgnore. This annotation has the following two usages:

• Annotate entity interfaces to indicate that the entire entity does not need to be verified.

• Annotate entity properties to indicate that specific properties do not need to be verified.