JPA + PostgreSQL

Entity Design

Primary Keys

Use UUIDs. Do not use auto-increment integers as public-facing identifiers.

@Entity
@Table(name = "users")
class User(
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    val id: UUID = UUID.randomUUID(),

    @Column(name = "email", nullable = false, unique = true, length = 255)
    var email: String,

    @Column(name = "name", nullable = false, length = 100)
    var name: String
)

UUID generation strategy GenerationType.UUID is available in Hibernate 6+ (Spring Boot 3+). For earlier versions, use @UuidGenerator from Hibernate or generate manually.

Auditing

Enable automatic timestamp management with Spring Data auditing.

// Enable in main application class or a @Configuration class
@EnableJpaAuditing
@SpringBootApplication
class Application

// Base class for auditable entities
@MappedSuperclass
@EntityListeners(AuditingEntityListener::class)
abstract class AuditableEntity {

    @Column(name = "created_at", nullable = false, updatable = false)
    @CreatedDate
    lateinit var createdAt: Instant

    @Column(name = "updated_at", nullable = false)
    @LastModifiedDate
    lateinit var updatedAt: Instant
}

// Entity extends the base class
@Entity
@Table(name = "users")
class User(
    @Id @GeneratedValue(strategy = GenerationType.UUID)
    val id: UUID = UUID.randomUUID(),

    @Column(name = "email", nullable = false, unique = true)
    var email: String,

    var name: String
) : AuditableEntity()

Soft Delete

Do not delete rows — mark them as deleted and filter them transparently.

@Entity
@Table(name = "users")
@SQLDelete(sql = "UPDATE users SET deleted_at = NOW() WHERE id = ?")
@FilterDef(name = "deletedFilter", parameters = [ParamDef(name = "isDeleted", type = Boolean::class)])
@Filter(name = "deletedFilter", condition = "deleted_at IS NULL")
class User(
    @Id @GeneratedValue(strategy = GenerationType.UUID)
    val id: UUID = UUID.randomUUID(),

    @Column(name = "email", nullable = false, unique = true)
    var email: String,

    @Column(name = "deleted_at")
    var deletedAt: Instant? = null
)

Alternative using @Where (simpler, but applies globally without ability to disable):

@Entity
@Table(name = "users")
@SQLDelete(sql = "UPDATE users SET deleted_at = NOW() WHERE id = ?")
@Where(clause = "deleted_at IS NULL")
class User(...)

Column Annotations

Always be explicit. Never rely on Hibernate defaults.

@Column(
    name = "email",           // explicit column name
    nullable = false,         // maps to NOT NULL constraint
    unique = true,            // maps to UNIQUE constraint
    length = 255              // VARCHAR(255) — ignored for TEXT type
)
var email: String

For PostgreSQL TEXT type (unbounded), use columnDefinition:

@Column(name = "description", nullable = false, columnDefinition = "TEXT")
var description: String

Indexes

Define indexes on the entity, not in migration scripts, for searchable fields.

@Entity
@Table(
    name = "users",
    indexes = [
        Index(name = "idx_users_email", columnList = "email", unique = true),
        Index(name = "idx_users_created_at", columnList = "created_at")
    ]
)
class User(...)

For partial indexes (e.g., soft-delete), use a migration script — JPA cannot express partial indexes.

Relationship Mapping

One-to-Many

@Entity
@Table(name = "orders")
class Order(
    @Id @GeneratedValue(strategy = GenerationType.UUID)
    val id: UUID = UUID.randomUUID(),

    @OneToMany(
        mappedBy = "order",
        cascade = [CascadeType.ALL],
        fetch = FetchType.LAZY,   // ALWAYS lazy by default
        orphanRemoval = true
    )
    val items: MutableList<OrderItem> = mutableListOf()
)

@Entity
@Table(name = "order_items")
class OrderItem(
    @Id @GeneratedValue(strategy = GenerationType.UUID)
    val id: UUID = UUID.randomUUID(),

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "order_id", nullable = false)
    val order: Order,

    @Column(name = "quantity", nullable = false)
    var quantity: Int
)

Rules:

• fetch = FetchType.LAZY is the default for @OneToMany — make it explicit anyway
• CascadeType.ALL only when the child lifecycle is completely owned by the parent
• orphanRemoval = true when removing an item from the collection should delete the row

Many-to-One

@ManyToOne(fetch = FetchType.LAZY)   // LAZY — never EAGER
@JoinColumn(name = "user_id", nullable = false)
val user: User

FetchType.EAGER on @ManyToOne causes N+1 problems. Always use LAZY and fetch eagerly with JOIN FETCH when needed.

Many-to-Many

Do not use @ManyToMany directly. Use a join entity with explicit fields.

// bad — opaque join table, no room for additional fields
@ManyToMany
@JoinTable(name = "user_roles")
val roles: MutableSet<Role>

// good — explicit join entity
@Entity
@Table(name = "user_roles")
class UserRole(
    @Id @GeneratedValue(strategy = GenerationType.UUID)
    val id: UUID = UUID.randomUUID(),

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", nullable = false)
    val user: User,

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "role_id", nullable = false)
    val role: Role,

    @Column(name = "assigned_at", nullable = false)
    val assignedAt: Instant = Instant.now()
)

The join entity approach is more flexible — you can add fields like assignedAt, assignedBy, and query through a repository.

N+1 Problem

The N+1 problem occurs when loading a collection results in one query for the parent and N additional queries for each child, where N is the number of parents.

Detection:

Enable SQL logging in tests:

# application-test.yml
spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true
logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.type.descriptor.sql.BasicBinder: TRACE

Count the queries. If you see SELECT * FROM order_items WHERE order_id = ? executed once per order, you have N+1.

Fix with @EntityGraph:

@Repository
interface OrderRepository : JpaRepository<Order, UUID> {

    @EntityGraph(attributePaths = ["items", "items.product"])
    fun findWithItemsById(id: UUID): Optional<Order>

    @EntityGraph(attributePaths = ["items"])
    fun findAllWithItems(): List<Order>
}

Fix with JPQL JOIN FETCH:

@Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.id = :id")
fun findByIdWithItems(@Param("id") id: UUID): Optional<Order>

@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.items WHERE o.status = :status")
fun findByStatusWithItems(@Param("status") status: OrderStatus): List<Order>

Use DISTINCT with JOIN FETCH on collections to avoid duplicate parent rows in the result.

Rule: never access a lazy collection outside a transaction. This causes LazyInitializationException. If a service method needs related data, fetch it within the same transaction using @EntityGraph or JOIN FETCH.

Projections for Read Queries

When you only need a subset of fields, do not load the full entity. Use projections.

Interface projection:

interface UserSummary {
    val id: UUID
    val email: String
    val name: String
}

@Repository
interface UserRepository : JpaRepository<User, UUID> {
    fun findAllProjectedBy(): List<UserSummary>
    fun findProjectedById(id: UUID): UserSummary?
}

DTO projection with constructor expression:

data class UserDto(val id: UUID, val email: String)

@Query("SELECT new com.example.user.dto.UserDto(u.id, u.email) FROM User u WHERE u.active = true")
fun findActiveUserDtos(): List<UserDto>

Use projections for list endpoints and reports. Load full entities only when you need to modify them.

Flyway Migrations

Naming Convention

V{timestamp}__{description}.sql

Use a timestamp, not a sequential number, to avoid conflicts in parallel branches.

V20260507120000__create_users_table.sql
V20260507120001__add_users_email_index.sql
V20260508090000__add_orders_table.sql

Migration Rules

• Never modify a migration that has already been applied in any environment
• Each migration must be forward-only — Flyway does not support automatic rollbacks
• DDL changes and data migrations must be in separate files
• Destructive operations (DROP COLUMN, DROP TABLE) require a multi-step process:
  1. Migration 1: deploy code that no longer uses the column
  2. Migration 2: drop the column (safe once all instances are updated)

Schema Migration Example

-- V20260507120000__create_users_table.sql
CREATE TABLE users (
    id          UUID        NOT NULL PRIMARY KEY,
    email       VARCHAR(255) NOT NULL,
    name        VARCHAR(100) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at  TIMESTAMPTZ
);

CREATE UNIQUE INDEX idx_users_email ON users (email) WHERE deleted_at IS NULL;

Rollback Strategy

Document rollback SQL in comments at the top of each migration file:

-- V20260507120001__add_user_role_column.sql
-- Rollback: ALTER TABLE users DROP COLUMN role;

ALTER TABLE users ADD COLUMN role VARCHAR(50) NOT NULL DEFAULT 'USER';

Automated rollback is not used. Manual rollback is applied only in emergencies.

PostgreSQL Specifics

Column Types

Use TIMESTAMPTZ (with timezone), not TIMESTAMP. Store all timestamps in UTC.

JSONB

Use JSONB for semi-structured data that does not warrant its own table.

@Column(name = "metadata", columnDefinition = "JSONB")
@Type(JsonType::class)   // requires hypersistence-utils dependency
var metadata: Map<String, Any> = emptyMap()

JSONB is indexable. JSON is not. Always use JSONB.

Partial Indexes

Partial indexes cannot be expressed through JPA annotations. Use a migration:

-- For soft-deleted tables: only index active (non-deleted) rows
CREATE UNIQUE INDEX idx_users_email_active ON users (email) WHERE deleted_at IS NULL;

-- For status-filtered queries
CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'PENDING';

Native Queries with RETURNING

Use RETURNING to avoid an extra SELECT after an INSERT or UPDATE:

@Modifying
@Query(
    value = "INSERT INTO audit_log (user_id, action, created_at) VALUES (:userId, :action, NOW()) RETURNING id",
    nativeQuery = true
)
fun insertAndReturnId(
    @Param("userId") userId: UUID,
    @Param("action") action: String
): UUID

Complex Queries

Use native queries when JPQL cannot express what you need:

@Query(
    value = """
        SELECT u.id, u.email, COUNT(o.id) as order_count
        FROM users u
        LEFT JOIN orders o ON o.user_id = u.id AND o.deleted_at IS NULL
        WHERE u.deleted_at IS NULL
        GROUP BY u.id, u.email
        HAVING COUNT(o.id) > :minOrders
        ORDER BY order_count DESC
        LIMIT :limit
    """,
    nativeQuery = true
)
fun findActiveUsersWithMinOrders(
    @Param("minOrders") minOrders: Int,
    @Param("limit") limit: Int
): List<Map<String, Any>>

Query Performance

Pagination

Always paginate. Never call findAll() on a large table.

@Repository
interface UserRepository : JpaRepository<User, UUID> {
    fun findAll(pageable: Pageable): Page<UserSummary>
    fun findByRole(role: UserRole, pageable: Pageable): Page<UserSummary>
}

// In service
fun list(page: Int, size: Int): Page<UserSummary> {
    val pageable = PageRequest.of(page, size, Sort.by("createdAt").descending())
    return userRepository.findAll(pageable)
}

Use Slice<T> instead of Page<T> when you do not need the total count (cheaper — no COUNT query).

QueryHints for Fetch Control

@QueryHints(
    QueryHint(name = HINT_FETCHGRAPH, value = "User.withOrders")
)
fun findById(id: UUID): Optional<User>

Requires a named entity graph:

@Entity
@NamedEntityGraph(
    name = "User.withOrders",
    attributeNodes = [NamedAttributeNode("orders")]
)
class User(...)

Testing

Unit — @DataJpaTest with H2

Fast. No server required.

@DataJpaTest
class UserRepositoryTest {

    @Autowired
    private lateinit var userRepository: UserRepository

    @Test
    fun `should find user by email`() {
        val saved = userRepository.save(User(email = "test@example.com", name = "Test User"))
        val found = userRepository.findByEmail("test@example.com")
        assertThat(found).isNotNull
        assertThat(found?.id).isEqualTo(saved.id)
    }

    @Test
    fun `should not find deleted user by email`() {
        val user = userRepository.save(User(email = "deleted@example.com", name = "Gone"))
        userRepository.delete(user)   // triggers @SQLDelete — sets deleted_at
        val found = userRepository.findByEmail("deleted@example.com")
        assertThat(found).isNull()
    }
}

Integration — Testcontainers with PostgreSQL

Real database. Use for migrations, JSONB, partial indexes, and native queries.

@SpringBootTest
@Testcontainers
class UserRepositoryIntegrationTest {

    companion object {
        @Container
        @JvmStatic
        val postgres = PostgreSQLContainer<Nothing>("postgres:16").apply {
            withDatabaseName("testdb")
            withUsername("test")
            withPassword("test")
        }

        @DynamicPropertySource
        @JvmStatic
        fun registerProperties(registry: DynamicPropertyRegistry) {
            registry.add("spring.datasource.url", postgres::getJdbcUrl)
            registry.add("spring.datasource.username", postgres::getUsername)
            registry.add("spring.datasource.password", postgres::getPassword)
        }
    }

    @Autowired
    private lateinit var userRepository: UserRepository

    @Test
    fun `should enforce unique email constraint at db level`() {
        userRepository.save(User(email = "dup@example.com", name = "First"))
        assertThrows<DataIntegrityViolationException> {
            userRepository.save(User(email = "dup@example.com", name = "Second"))
        }
    }
}

Use Testcontainers for:

• Flyway migration validation
• PostgreSQL-specific features (JSONB, partial indexes, native queries)
• Constraint enforcement (unique, foreign key, check constraints)
• Query performance checks that differ between H2 and PostgreSQL

Use @DataJpaTest with H2 for:

• Derived query methods
• Simple CRUD operations
• JPQL queries with standard JPA behavior