Micronaut Data JDBC: How to map a one-to-many relation using enums?

Question

I would like to map a unidirectional one-to-many relation, using Micronaut Data annotations. Considering the object and database models described below, how to map that using Micronaut annotations?

Object model:

import io.micronaut.serde.annotation.Serdeable

@Serdeable
enum class Role {
    Admin, Manager
}

import io.micronaut.data.annotation.GeneratedValue
import io.micronaut.data.annotation.Id
import io.micronaut.data.annotation.MappedEntity
import io.micronaut.serde.annotation.Serdeable

@Serdeable
@MappedEntity
data class User(

    @field:Id
    @field:GeneratedValue
    var id: Long? = null,
    
    val username: String,

    // How to map?
    var roles: Set<Role>?
)

Database model with many-to-many (DDLs for PostgreSQL):

CREATE TABLE "user"
(
    id       SERIAL PRIMARY KEY,
    username VARCHAR(30) NOT NULL UNIQUE
);

CREATE TABLE "role"
(
    id INTEGER PRIMARY KEY,
    name VARCHAR(40) NOT NULL
);

CREATE TABLE "user_role"
(
    user_id INTEGER REFERENCES "user" (id),
    role_id INTEGER REFERENCES "role" (id)
);

INSERT INTO "role" (id, name) VALUES (1, 'Admin'), (2, 'Manager');

Or another option, a simpler database model using one-to-many (PostgeSQL):

CREATE TABLE "user"
(
    id       SERIAL PRIMARY KEY,
    username VARCHAR(30) NOT NULL UNIQUE
);

CREATE TYPE role_name AS ENUM ('Admin', 'Manager');

CREATE TABLE "user_role"
(
    user_id INTEGER NOT NULL REFERENCES "user" (id),
    role_id INTEGER NOT NULL REFERENCES "role" (id),
    CONSTRAINT user_role_uk UNIQUE (user_id, role_id)
);

You may suggest modifications to any database model to allow the one-to-many relation in the object model.

Your schema is one-to-one. Table `user_role` -> `user_id INTEGER PRIMARY KEY REFERENCES "user" (id),`. With the Primary Key on user_id, would only allow a single role per user. — ShingJo, Jan 30 '23 at 18:09
ShingJo, that's true, I didn't notice. I have updated the question with two examples of a database model, one for many-to-many and the other for one-to-many. In the object domain, the relation should be unidirectional one-to-many. — Alexandre V., Jan 30 '23 at 18:27

score 0 · Answer 1 · answered Feb 02 '23 at 13:48

Here are two out of the multiple ways that it could be done. I combined them into one example out of laziness.

@Serdeable
enum class Role {
    Admin, Manager;
}

@Serdeable
@MappedEntity
data class UserRole(
    @field:Id
    @GeneratedValue
    var id: Long? = null,
    @Nullable
    @Relation(value = Relation.Kind.MANY_TO_ONE)
    val user: User?,
    val role: Role
)

@Serdeable
@MappedEntity
class User(

    @field:Id
    @field:GeneratedValue
    var id: Long? = null,

    val username: String,

    @MappedProperty(definition = "VARCHAR(30)[]", type = DataType.STRING_ARRAY)
    var roles: Set<Role>?,

    @Relation(value = Relation.Kind.ONE_TO_MANY, mappedBy = "user", cascade = [Cascade.ALL])
    var userRoles: Set<UserRole>?
)

@JdbcRepository(dialect = Dialect.POSTGRES)
@Join(value = "userRoles", type = Join.Type.LEFT_FETCH)
interface UserRepository : CrudRepository<User, Long> {
}

CREATE TYPE role_name AS ENUM ('Admin', 'Manager');

CREATE TABLE "user"
(
    id       SERIAL PRIMARY KEY,
    username VARCHAR(30) NOT NULL UNIQUE,
    roles VARCHAR(15)[]
);

CREATE TABLE "user_role"
(
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id INTEGER REFERENCES "user" (id),
    role role_name NOT NULL,
    CONSTRAINT user_role_uk UNIQUE (user_id, role)
);

Micronaut Data JDBC: How to map a one-to-many relation using enums?

1 Answers1