Architectural Miscalculation and Hibernate Problem "Type UUID but Expression Is of Type Bytea"

Nowadays, it is difficult to find a service that works on its own and does not communicate with other services, especially modern systems that are built on a microservice architecture. In this regard, there are difficulties in obtaining data from one or another service since not all the data necessary for the operation of the service is stored in one database, and you cannot simply make a "join." I want to talk about one of these problems and its solution in this article.

Case Description

A huge number of projects use Spring + Hibernate. This bundle gives an advantage in development speed, reducing the amount of code and blah blah blah. But there are also disadvantages.

Let's imagine the following situation; we have some entity "Document" which contains some fields among which there are (Id, Title, Author_id). And at the moment, the table with the authors' data is stored in the same database. We also have other services that store any entities with a link to the authors. As you may have guessed, the authors' data is difficult to keep up to date with this approach. They should be stored in a separate service that keeps them up to date.

A simple Java code example of when data is stored in the same database:

@Entity
public class Author {

  @Id
  private UUID id;
...

public class Document {

  @Id
  private Integer id;
  @Column(name = "title")
  private String title;
  @Column(name = "author_id")
  @ManyToOne
  private Author author;
  ...

So, we need to edit the author field so that the data is filled not from the database of this service but from another service. Our database contains only the author's identifier.

The simplest thing that comes to mind is to declare the author field as a UUID and, after requesting this entity, convert it to a DocumentDto that will contain the author field as an object and enrich it from another source.

This option is suitable when there are few references to this field in the code, and you use REST.

In our case, GraphQL is used, there are many references to the Document entity and we do not need a DTO.

The most common solution is to use a converter.

Code Example

public class Author {
  private UUID id;
  private String firstname;
  private String lastname;
  ...

In class Document

@Column(name = "author_id")
@Convert(converter = AuthorConverter.class)
private Author author;

Converter

@Converter
public class AuthorConverter implements AttributeConverter<Author, UUID> {

  @Override
  public UUID convertToDatabaseColumn(Author author) {
    return author.getId();
  }

  @Override
  public Author convertToEntityAttribute(UUID id) {
    Author author = new Author();
    author.setId(id);
    return author;
  }
}

This is where the main problem lies if you use the UUID type as the identifier author.

When trying to save or modify the Document Entity in the database using the save method from JpaRepository, we will get an exception.

When using Hibernate-core 5.4.29. Final, the UUID is defined as a Varbinary type when converted and throws the following exception:

ERROR: column "author_id" is of type uuid but the expression is of type bytea.

Screenshot from debugger.

When using Hibernate-core 5.6.14. Final, the UUID is defined as a Varchar type when converted and throws the following exception:

ERROR: column "author_id" is of type uuid but the expression is of type character varying.

After searching on Google, I came to the conclusion that many simply change the type from UUID to String and live with this solution. This is clearly a crutch and I do not like it.

The second crutch that I found in the code of my colleagues is to add one more field to the Document entity (oh my eyes), as shown below:

Code Example

@Column(name = "author_id", updatable = false, insertable = false)
@Convert(converter = AuthorConverter.class)
private Author author;

@Column(name = "author_id")
private UUID authorId;

This method can confuse other developers since it is not immediately clear which field the author to fill in when saving the entity.

In both cases, saving via the save method will work for you.

One way around this problem is to save and modify the entity with native queries. As soon as it came to mind, I checked it, and it works. However, although I love native queries, I don't like this option either because if you have many fields in the entity, then calling the save or change method with all these fields will look terrible.

Code Example

@Query(nativeQuery = true, value = "INSERT INTO public.document (title, author_id)"
    + " VALUES (?1, ?2)")
void insert(String title, UUID authorId);

The fourth crutch will work for Postgres. Since this database does not support the Varbinary type, you can change the type of this field in the database to Bytea. I have not tried this solution with Oracle DB.

Of all the above crutches, I would most likely choose the third one (native queries), but I do not like the code with crutches and I continued to think about solving the problem.

Having debugged the code a bit, I decided to go the other way and not use the converter. You can do without a converter by making the author a built-in entity (@Embedded), but since we use only the author's identifier to save in our database, the rest of the fields need to be moved to the superclass. Here is an example test code.

Sample Code

...
@Entity
public class Document {

  @Id
  private Integer id;

  @Column(name = "title")
  private String title;

  @Embedded
  @AttributeOverrides(@AttributeOverride(name = "id", column = @Column(name = "author_id")))
  private Author author;
}

...
@Embeddable
public class Author extends AuthorFields {

  private UUID id;

}
...
public class AuthorFields {

  private String firstname;
  private String lastname;
}

This method works great; no need to change the data type in the database or make native queries to save the data. However, this is where I stopped.

Conclusion

In this article, I tried to describe the existing problem and ways to solve it without unnecessary text.

Once again, I was convinced that you should not rush to the first solution that came across on StackOverflow, although all the solutions to this problem that I found there were to use String.

Perhaps this article will help someone and make life easier)

Thank you for your attention!