Quarkus Persistence

Quarkus Persistence

Overview

Full rules: .agents/standards/java/database.md. This is the workflow and the patterns.

When to use

• Creating or changing an entity or table
• Writing a Flyway migration
• Writing or optimising a query
• A slow endpoint suspected of being a data problem
• Deciding where @Transactional goes

Process

1 — Migration first. The schema is the source of truth; the entity follows it.

-- V20260912_1430__catalog_vendor.sql
CREATE TABLE vendor (
    id          BIGINT       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    taxId        VARCHAR(16)  NOT NULL,
    type        VARCHAR(30)  NOT NULL,
    is_active   BOOLEAN      NOT NULL DEFAULT TRUE,
    deleted_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
    created_by  VARCHAR(100) NOT NULL,
    updated_at  TIMESTAMPTZ,
    updated_by  VARCHAR(100),
    CONSTRAINT ck_vendor_type CHECK (type IN ('INDIVIDUAL','COMPANY','COOPERATIVE'))
);

CREATE UNIQUE INDEX uq_vendor_taxId_live ON vendor (taxId) WHERE deleted_at IS NULL;
CREATE INDEX ix_vendor_name ON vendor (lower(name));

2 — Entity. It mirrors the table; it does not create it.

@Entity
@Table(name = "vendor")
public class Vendor extends AuditableEntity {

    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Long id;

    @Column(nullable = false, length = 200)
    public String name;

    @Column(nullable = false, length = 16)
    public String taxId;

    @Enumerated(EnumType.STRING)
    @Column(nullable = false, length = 30)
    public VendorType type;

    @Column(name = "is_active", nullable = false)
    public boolean active = true;

    @Column(name = "deleted_at")
    public Instant deletedAt;
}

@Enumerated(EnumType.STRING) always. ORDINAL stores the enum's position in the source code — inserting one new value in the middle silently changes the meaning of every existing row.

3 — Repository.

@ApplicationScoped
public class VendorRepository implements PanacheRepository<Vendor> {

    public Optional<Vendor> findByTaxId(String taxId) {
        return find("taxId = ?1 and deletedAt is null", taxId).firstResultOptional();
    }

    public PanacheQuery<Vendor> search(String q, PageRequest page) {
        var query = (q == null || q.isBlank())
                ? find("deletedAt is null", page.toSort())
                : find("""
                       deletedAt is null
                       and (lower(name) like ?1 or taxId like ?1)
                       """, page.toSort(), "%" + q.toLowerCase() + "%");
        return query.page(page.page(), page.size());
    }
}

PanacheRepository, not active record — so services can be tested with a mock, without a database, in milliseconds.

4 — Automatic audit columns.

@MappedSuperclass
public abstract class AuditableEntity {
    @Column(name = "created_at", nullable = false, updatable = false)
    public Instant createdAt;
    @Column(name = "created_by", nullable = false, updatable = false)
    public String createdBy;
    @Column(name = "updated_at") public Instant updatedAt;
    @Column(name = "updated_by") public String updatedBy;

    @PrePersist void onCreate() { createdAt = Instant.now(); createdBy = CurrentUser.name(); }
    @PreUpdate  void onUpdate() { updatedAt = Instant.now(); updatedBy = CurrentUser.name(); }
}

Migrations that are safe on real data

The order table will be large. What locks:

-- WRONG — rewrites the whole table
ALTER TABLE order ADD COLUMN currency CHAR(3) NOT NULL DEFAULT 'USD';

-- RIGHT — three steps
ALTER TABLE order ADD COLUMN currency CHAR(3);
UPDATE order SET currency = 'USD' WHERE currency IS NULL;
ALTER TABLE order ALTER COLUMN currency SET NOT NULL;

Indexes on large tables:

-- flyway:executeInTransaction=false
CREATE INDEX CONCURRENTLY ix_order_date ON order (transaction_date);

Rules:

• Timestamp naming: V<YYYYMMDD>_<HHmm>__<description>.sql — sequential numbers collide as soon as two developers write a migration on the same day.
• Forward-only. Mistakes are corrected by a new migration.
• Merged files are never edited — Flyway's checksum will refuse startup in every environment that already ran it.
• Test against a populated dump, not an empty database.
• Backward-compatible by one release: do not drop a column in the same release that stops using it, so an application rollback stays possible.

Transactions

@Transactional only in services. One transaction equals one business operation.

// WRONG — an HTTP call holds the DB connection for its whole timeout
@Transactional
public void submit(Long id) {
    var p = order.findById(id);
    p.status = SUBMITTED;
    notificationClient.send(p);
}

// RIGHT — commit first, side effects after
public void submit(Long id) {
    var snapshot = changeStatus(id);        // @Transactional
    notificationClient.send(snapshot);      // outside
}

@Transactional
OrderSnapshot changeStatus(Long id) { ... }

Read-only: @Transactional(TxType.SUPPORTS).

N+1 — a bug, not merely slow

Enable it in dev and watch:

%dev.quarkus.hibernate-orm.log.sql=true

One request printing 50 SELECTs is a finding, not a coincidence.

// WRONG — 1 + N queries
var list = orderRepo.listAll();
list.forEach(p -> use(p.vendor.name));   // one SELECT per row

// RIGHT — one query
var list = orderRepo.find("""
        select p from Order p
        join fetch p.vendor
        where p.deletedAt is null
        """).page(page).list();

All associations are LAZY; load explicitly with join fetch where needed. EAGER moves the problem rather than removing it — it loads unused data on every other query.

Report queries

Aggregation happens in the database, not in Java.

// WRONG — loading 500,000 rows into memory to sum them
var all = orderRepo.listAll();
var total = all.stream().map(p -> p.amount).reduce(ZERO, BigDecimal::add);

// RIGHT
@ApplicationScoped
public class SummaryRepository {
    @Inject EntityManager em;

    public List<SummaryRow> summaryByType(LocalDate from, LocalDate to) {
        return em.createNativeQuery("""
                SELECT order_type, COUNT(*), SUM(amount)
                FROM order
                WHERE transaction_date BETWEEN :from AND :to AND deleted_at IS NULL
                GROUP BY order_type
                """, SummaryRow.class)
            .setParameter("from", from)
            .setParameter("to", to)
            .getResultList();
    }
}

Always parameters (?1, :name), never string concatenation of user input. Dynamic ORDER BY uses an allowlist — see quarkus-security.

Verification

• Migration exists before the entity change
• New columns are nullable or backfilled
• @Transactional is on the service, not the resource
• Collection queries cannot N+1
• Report aggregations run in SQL, not in Java

Red flags