Database Persistence and Flyway in Quarkus
Building a Production-Ready Product Management System with PostgreSQL
Introduction
In modern application development, managing database persistence and schema evolution are critical challenges. This comprehensive guide walks you through building a production-ready REST API using Quarkus, PostgreSQL, and Flyway for database migrations.
We'll build a Product Management System with a hierarchical warehouse positioning structure—a real-world scenario that demonstrates:
- ⚡ Efficient database connection pooling with Agroal
- 💾 Object-relational mapping with Hibernate and Panache
- 🔄 Version-controlled schema migrations with Flyway
- 🌳 Complex tree structures for warehouse management
- 🚀 RESTful CRUD operations with proper transaction handling
- 📊 Production-ready monitoring and health checks
By the end of this article, you'll have a complete understanding of how to configure, develop, and deploy a Quarkus application with robust database persistence.
Table of Contents
- Technology Stack Overview
- Project Setup and Dependencies
- Datasource Configuration
- Hibernate ORM and Panache
- Flyway Database Migrations
- Domain Model Design
- RESTful API Implementation
- Environment Configuration
- Testing and Monitoring
- Production Deployment
- Best Practices and Optimization
- Conclusion
1. Technology Stack Overview
Our application leverages the following technologies:
| Component | Technology | Purpose |
|---|---|---|
| Framework | Quarkus 3.x | Cloud-native, supersonic subatomic Java |
| Database | PostgreSQL 15 | Open-source relational database |
| Connection Pool | Agroal | High-performance JDBC pooling |
| ORM | Hibernate 6.x | Object-relational mapping |
| Data Access | Panache | Active Record pattern for Hibernate |
| Migration | Flyway | Database version control |
| REST | JAX-RS (RESTEasy) | RESTful web services |
| JSON | Jackson | JSON serialization/deserialization |
Why Quarkus?
Quarkus is designed for cloud-native and serverless applications, offering:
- Fast startup time: < 1 second (native mode)
- Low memory footprint: ~30MB RSS
- Developer joy: Live reload, unified configuration
- Container-first: Optimized for Docker and Kubernetes
Why PostgreSQL?
PostgreSQL provides enterprise-grade features:
- Advanced SQL support with CTE (Common Table Expressions)
- Native JSON/JSONB support
- Excellent performance and reliability
- Strong ACID compliance
2. Project Setup and Dependencies
Creating the Project
Start by creating a new Quarkus project using Maven:
mvn io.quarkus:quarkus-maven-plugin:3.6.0:create \\
-DprojectGroupId=com.bmw.product \\
-DprojectArtifactId=product-management-system \\
-DclassName="com.bmw.product.resource.ProductResource" \\
-Dpath="/api/products"
Maven Dependencies
Add the following dependencies to your pom.xml:
<dependencies>
<!-- PostgreSQL JDBC Driver + Datasource -->
<dependency>
<groupId>io.quarkus</groupId>
<artifactId>quarkus-jdbc-postgresql</artifactId>
</dependency>
<!-- Hibernate ORM with Panache -->
<dependency>
<groupId>io.quarkus</groupId>
<artifactId>quarkus-hibernate-orm-panache</artifactId>
</dependency>
<!-- Flyway Database Migration -->
<dependency>
<groupId>io.quarkus</groupId>
<artifactId>quarkus-flyway</artifactId>
</dependency>
<!-- RESTEasy Reactive with Jackson -->
<dependency>
<groupId>io.quarkus</groupId>
<artifactId>quarkus-resteasy-reactive-jackson</artifactId>
</dependency>
<!-- Bean Validation -->
<dependency>
<groupId>io.quarkus</groupId>
<artifactId>quarkus-hibernate-validator</artifactId>
</dependency>
<!-- Health Checks -->
<dependency>
<groupId>io.quarkus</groupId>
<artifactId>quarkus-smallrye-health</artifactId>
</dependency>
</dependencies>
Project Structure
Organize your project as follows:
src/
├── main/
│ ├── java/
│ │ └── com/bmw/product/
│ │ ├── model/
│ │ │ ├── Product.java
│ │ │ └── Position.java
│ │ ├── resource/
│ │ │ ├── ProductResource.java
│ │ │ └── PositionResource.java
│ │ └── health/
│ │ └── DatabaseHealthCheck.java
│ └── resources/
│ ├── application.properties
│ └── db/
│ └── migration/
│ ├── V1.0.0__create_positions_table.sql
│ ├── V1.0.1__create_products_table.sql
│ └── V1.0.2__insert_initial_data.sql
└── test/
└── java/
└── com/bmw/product/
├── ProductResourceTest.java
└── PositionResourceTest.java
3. Datasource Configuration
The datasource is the foundation of database connectivity. Quarkus uses the Agroal connection pool, which offers excellent performance and native compilation support.
Basic Configuration
Add the following to src/main/resources/application.properties:
# ==========================================
# DATASOURCE CONFIGURATION
# ==========================================
# Database Type
quarkus.datasource.db-kind=postgresql
# Credentials
quarkus.datasource.username=postgres
quarkus.datasource.password=secret
# JDBC URL
quarkus.datasource.jdbc.url=jdbc:postgresql://localhost:5432/productdb
# JDBC Driver (auto-detected, but can be explicit)
quarkus.datasource.jdbc.driver=org.postgresql.Driver
What happens here:
- Quarkus detects PostgreSQL as the database type
- Automatically loads the PostgreSQL JDBC driver
- Configures the Agroal connection pool
- Validates the connection on startup
Connection Pool Configuration
Optimize connection pooling for production:
# ==========================================
# AGROAL CONNECTION POOL SETTINGS
# ==========================================
# Pool Size Configuration
quarkus.datasource.jdbc.min-size=5
quarkus.datasource.jdbc.max-size=20
quarkus.datasource.jdbc.initial-size=5
# Timeout Settings
quarkus.datasource.jdbc.acquisition-timeout=10
quarkus.datasource.jdbc.background-validation-interval=2M
quarkus.datasource.jdbc.idle-removal-interval=5M
quarkus.datasource.jdbc.max-lifetime=30M
# Connection Validation
quarkus.datasource.jdbc.validation-query-sql=SELECT 1
quarkus.datasource.jdbc.detect-statement-leaks=true
quarkus.datasource.jdbc.leak-detection-interval=5M
# Transaction Isolation Level
quarkus.datasource.jdbc.transaction-isolation-level=read-committed
Key configuration parameters:
- min-size/max-size: Balance between resource usage and performance. Formula:
(core_count * 2) + effective_spindle_count - acquisition-timeout: How long to wait for a connection (in seconds)
- validation-query-sql: Ensures connections are alive before use
- leak-detection: Identifies unclosed connections (critical for debugging)
Health Checks and Metrics
Enable datasource monitoring:
# Health Check Configuration
quarkus.datasource.health.enabled=true
# Metrics Configuration
quarkus.datasource.metrics.enabled=true
This enables:
- Health endpoint:
http://localhost:8080/q/health - Metrics endpoint:
http://localhost:8080/q/metrics
SSL/TLS Configuration (Production)
For production environments, enable SSL:
quarkus.datasource.jdbc.url=jdbc:postgresql://prod-db.bmw.com:5432/productdb?\\
ssl=true&\\
sslmode=verify-full&\\
sslrootcert=/path/to/ca.crt
4. Hibernate ORM and Panache
Hibernate ORM handles the object-relational mapping, while Panache provides a simplified, active record-style API.
Hibernate Configuration
# ==========================================
# HIBERNATE ORM CONFIGURATION
# ==========================================
# Database Generation Strategy
# CRITICAL: Set to 'none' when using Flyway
quarkus.hibernate-orm.database.generation=none
# SQL Logging (Development)
quarkus.hibernate-orm.log.sql=true
quarkus.hibernate-orm.log.format-sql=true
quarkus.hibernate-orm.log.bind-parameters=true
# Disable Default Import Script
quarkus.hibernate-orm.sql-load-script=no-file
# Dialect (auto-detected)
quarkus.hibernate-orm.dialect=org.hibernate.dialect.PostgreSQLDialect
# Statistics (for monitoring)
quarkus.hibernate-orm.statistics=true
Important: Setting database.generation=none is critical when using Flyway. This prevents Hibernate from automatically creating/updating schema, which would conflict with Flyway's versioned migrations.
Why Panache?
Panache simplifies Hibernate usage by:
- Eliminating boilerplate repository code
- Providing an intuitive API
- Supporting both Active Record and Repository patterns
- Offering type-safe queries
Without Panache:
@Entity
public class Product {
@Id @GeneratedValue
private Long id;
private String name;
// getters, setters...
}
@ApplicationScoped
public class ProductRepository {
@Inject EntityManager em;
public List<Product> listAll() {
return em.createQuery("SELECT p FROM Product p", Product.class)
.getResultList();
}
}
With Panache:
@Entity
public class Product extends PanacheEntity {
public String name;
// No getters/setters needed for public fields
}
// Usage: Product.listAll()
5. Flyway Database Migrations
Flyway enables version-controlled, repeatable database migrations—essential for production applications.
Flyway Configuration
# ==========================================
# FLYWAY CONFIGURATION
# ==========================================
# Migration Behavior
quarkus.flyway.migrate-at-start=true
quarkus.flyway.baseline-on-migrate=true
quarkus.flyway.validate-on-migrate=true
quarkus.flyway.clean-at-start=false
# Baseline Configuration
quarkus.flyway.baseline-version=1.0.0
quarkus.flyway.baseline-description=Initial baseline
# Migration File Locations
quarkus.flyway.locations=db/migration
quarkus.flyway.schemas=public
quarkus.flyway.create-schemas=true
# Placeholders (for dynamic SQL)
quarkus.flyway.placeholders.tablespace=production_ts
# Flyway Table Name
quarkus.flyway.table=flyway_schema_history
Key settings:
- migrate-at-start: Automatically runs migrations on application startup
- baseline-on-migrate: Creates baseline for existing databases
- validate-on-migrate: Ensures migration integrity
- clean-at-start: ⚠️ DANGER! Drops all database objects (only for testing)
Migration File Naming Convention
Flyway uses a specific naming pattern:
Versioned Migrations:
V{version}__{description}.sql
Examples:
V1.0.0__create_positions_table.sql
V1.0.1__create_products_table.sql
V2.0.0__add_categories.sql
Repeatable Migrations:
R__{description}.sql
Examples:
R__create_product_view.sql
R__create_statistics_function.sql
Execution rules:
- Versioned migrations run once, in order
- Repeatable migrations run when their checksum changes
- Never modify applied migrations (create new ones instead)
Migration V1: Positions Table
Create src/main/resources/db/migration/V1.0.0__create_positions_table.sql:
-- ==========================================
-- POSITIONS TABLE (Tree Structure)
-- ==========================================
CREATE TABLE positions (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
code VARCHAR(50) UNIQUE NOT NULL,
description TEXT,
level INTEGER NOT NULL DEFAULT 0,
parent_id BIGINT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP,
-- Self-referencing foreign key for tree structure
CONSTRAINT fk_position_parent
FOREIGN KEY (parent_id)
REFERENCES positions(id)
ON DELETE CASCADE,
-- Business rules
CONSTRAINT chk_level_positive
CHECK (level >= 0),
CONSTRAINT chk_code_format
CHECK (code ~ '^[A-Z0-9\\-]+$')
);
-- Performance indexes
CREATE INDEX idx_position_parent ON positions(parent_id);
CREATE INDEX idx_position_code ON positions(code);
CREATE INDEX idx_position_level ON positions(level);
CREATE INDEX idx_position_name ON positions(name);
-- Documentation
COMMENT ON TABLE positions IS 'Hierarchical warehouse infrastructure positions';
COMMENT ON COLUMN positions.level IS '0=Factory, 1=Warehouse, 2=Zone, 3=Aisle, 4=Shelf, 5=Bin';
Design notes:
- Self-referencing FK: Enables tree structure (parent-child relationships)
- Indexes: Critical for performance on parent lookups and tree traversal
- Constraints: Enforce data integrity at database level
- CASCADE DELETE: Automatically removes child positions when parent is deleted
Migration V2: Products Table
Create src/main/resources/db/migration/V1.0.1__create_products_table.sql:
-- ==========================================
-- PRODUCTS TABLE
-- ==========================================
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
quantity INTEGER NOT NULL DEFAULT 0,
unit_of_measurement VARCHAR(50),
stock INTEGER NOT NULL DEFAULT 0,
codebar VARCHAR(100) UNIQUE,
position_id BIGINT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP,
-- Foreign key to positions
CONSTRAINT fk_product_position
FOREIGN KEY (position_id)
REFERENCES positions(id)
ON DELETE SET NULL,
-- Business constraints
CONSTRAINT chk_quantity_positive
CHECK (quantity >= 0),
CONSTRAINT chk_stock_positive
CHECK (stock >= 0),
CONSTRAINT chk_stock_not_exceed_quantity
CHECK (stock <= quantity)
);
-- Indexes for common queries
CREATE INDEX idx_product_codebar ON products(codebar);
CREATE INDEX idx_product_position ON products(position_id);
CREATE INDEX idx_product_name ON products(name);
CREATE INDEX idx_product_stock ON products(stock);
-- Full-text search index (PostgreSQL specific)
CREATE INDEX idx_product_name_fts
ON products
USING gin(to_tsvector('english', name));
-- Documentation
COMMENT ON TABLE products IS 'Product inventory with warehouse positioning';
COMMENT ON COLUMN products.codebar IS 'Barcode/QR code for scanning';
Migration V3: Initial Data
Create src/main/resources/db/migration/V1.0.2__insert_initial_data.sql:
-- ==========================================
-- INITIAL WAREHOUSE STRUCTURE
-- ==========================================
-- Level 0: Factory
INSERT INTO positions (id, name, code, level, parent_id)
VALUES (1, 'BMW Factory Munich', 'FAC-MUC-001', 0, NULL);
-- Level 1: Warehouses
INSERT INTO positions (id, name, code, level, parent_id)
VALUES
(2, 'Main Warehouse', 'WH-001', 1, 1),
(3, 'Secondary Warehouse', 'WH-002', 1, 1);
-- Level 2: Zones
INSERT INTO positions (id, name, code, level, parent_id)
VALUES
(4, 'Storage Zone A', 'ZN-A01', 2, 2),
(5, 'Storage Zone B', 'ZN-A02', 2, 2),
(6, 'Returns Zone', 'ZN-R01', 2, 3);
-- Level 3: Aisles
INSERT INTO positions (id, name, code, level, parent_id)
VALUES
(7, 'Aisle 1', 'AI-A01-1', 3, 4),
(8, 'Aisle 2', 'AI-A01-2', 3, 4);
-- Level 4: Shelves
INSERT INTO positions (id, name, code, level, parent_id)
VALUES
(9, 'Shelf Level 1', 'SH-A01-1-1', 4, 7),
(10, 'Shelf Level 2', 'SH-A01-1-2', 4, 7);
-- Reset sequence
SELECT setval('positions_id_seq', (SELECT MAX(id) FROM positions) + 1);
-- ==========================================
-- SAMPLE PRODUCTS
-- ==========================================
INSERT INTO products (name, quantity, unit_of_measurement, stock, codebar, position_id)
VALUES
('Bolts M8x20', 10000, 'pieces', 8500, 'BOLT-M8-20-001', 9),
('Washers M8', 15000, 'pieces', 12000, 'WASH-M8-001', 9),
('Engine Oil 5W30', 500, 'liters', 450, 'OIL-5W30-001', 10),
('Brake Pads Front', 200, 'sets', 150, 'BRAKE-F-001', 10);
This creates a realistic warehouse hierarchy:
BMW Factory Munich
├── Main Warehouse
│ ├── Storage Zone A
│ │ ├── Aisle 1
│ │ │ ├── Shelf Level 1 (Bolts, Washers)
│ │ │ └── Shelf Level 2 (Oil, Brakes)
│ │ └── Aisle 2
│ └── Storage Zone B
└── Secondary Warehouse
└── Returns Zone
6. Domain Model Design
Now let's implement the Java entities using Panache.
Position Entity
Create src/main/java/com/bmw/product/model/Position.java:
package com.bmw.product.model;
import io.quarkus.hibernate.orm.panache.PanacheEntity;
import com.fasterxml.jackson.annotation.JsonIgnore;
import javax.persistence.*;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Min;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
@Entity
@Table(name = "positions")
public class Position extends PanacheEntity {
@NotBlank(message = "Position name is required")
@Column(nullable = false, length = 255)
public String name;
@NotBlank(message = "Position code is required")
@Column(unique = true, nullable = false, length = 50)
public String code;
@Column(columnDefinition = "TEXT")
public String description;
@Min(value = 0, message = "Level must be non-negative")
@Column(nullable = false)
public Integer level = 0;
// Self-referencing relationship for tree structure
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "parent_id")
@JsonIgnore
public Position parent;
@OneToMany(mappedBy = "parent", cascade = CascadeType.ALL, orphanRemoval = true)
public List<Position> children = new ArrayList<>();
@Column(name = "created_at", nullable = false)
public LocalDateTime createdAt = LocalDateTime.now();
@Column(name = "updated_at")
public LocalDateTime updatedAt;
// Custom query methods using Panache
public static List<Position> findRootPositions() {
return list("parent is null ORDER BY code");
}
public static List<Position> findByLevel(int level) {
return list("level", level);
}
public static Position findByCode(String code) {
return find("code", code).firstResult();
}
// Business logic methods
public String getFullPath() {
List<String> path = new ArrayList<>();
Position current = this;
while (current != null) {
path.add(0, current.name);
current = current.parent;
}
return String.join(" > ", path);
}
}
Key features:
- PanacheEntity: Provides built-in
idfield and common methods - Tree structure: Self-referencing
parentfield enables hierarchical data - JsonIgnore: Prevents infinite recursion when serializing to JSON
- Custom queries: Leverages Panache's simplified query API
Product Entity
Create src/main/java/com/bmw/product/model/Product.java:
package com.bmw.product.model;
import io.quarkus.hibernate.orm.panache.PanacheEntity;
import javax.persistence.*;
import javax.validation.constraints.*;
import java.time.LocalDateTime;
import java.util.List;
@Entity
@Table(name = "products")
public class Product extends PanacheEntity {
@NotBlank(message = "Product name is required")
@Size(min = 3, max = 255)
@Column(nullable = false, length = 255)
public String name;
@Min(value = 0, message = "Quantity must be non-negative")
@Column(nullable = false)
public Integer quantity = 0;
@Column(name = "unit_of_measurement", length = 50)
public String unitOfMeasurement;
@Min(value = 0, message = "Stock must be non-negative")
@Column(nullable = false)
public Integer stock = 0;
@Column(unique = true, length = 100)
public String codebar;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "position_id")
public Position position;
@Column(name = "created_at", nullable = false)
public LocalDateTime createdAt = LocalDateTime.now();
@Column(name = "updated_at")
public LocalDateTime updatedAt;
// Custom query methods using Panache
public static Product findByCodebar(String codebar) {
return find("codebar", codebar).firstResult();
}
public static List<Product> findByPosition(Long positionId) {
return list("position.id", positionId);
}
public static List<Product> findLowStock(int threshold) {
return list("stock < ?1 ORDER BY stock ASC", threshold);
}
// Business logic methods
public void reduceStock(int amount) {
if (amount > stock) {
throw new IllegalArgumentException(
"Cannot reduce stock by " + amount + ". Only " + stock + " available."
);
}
this.stock -= amount;
this.updatedAt = LocalDateTime.now();
}
public void addStock(int amount) {
if (amount < 0) {
throw new IllegalArgumentException("Cannot add negative stock");
}
this.stock += amount;
this.quantity += amount;
this.updatedAt = LocalDateTime.now();
}
}
7. RESTful API Implementation
Now let's expose our entities through REST endpoints.
ProductResource
Create src/main/java/com/bmw/product/resource/ProductResource.java:
package com.bmw.product.resource;
import com.bmw.product.model.Product;
import javax.transaction.Transactional;
import javax.validation.Valid;
import javax.ws.rs.*;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;
import java.time.LocalDateTime;
import java.util.List;
@Path("/api/products")
@Produces(MediaType.APPLICATION_JSON)
@Consumes(MediaType.APPLICATION_JSON)
public class ProductResource {
@GET
public List<Product> listAll() {
return Product.listAll();
}
@GET
@Path("/{id}")
public Response getById(@PathParam("id") Long id) {
Product product = Product.findById(id);
if (product == null) {
return Response.status(Response.Status.NOT_FOUND)
.entity("{\\"error\\": \\"Product not found\\"}")
.build();
}
return Response.ok(product).build();
}
@GET
@Path("/barcode/{codebar}")
public Response findByCodebar(@PathParam("codebar") String codebar) {
Product product = Product.findByCodebar(codebar);
if (product == null) {
return Response.status(Response.Status.NOT_FOUND)
.entity("{\\"error\\": \\"Product not found\\"}")
.build();
}
return Response.ok(product).build();
}
@POST
@Transactional
public Response create(@Valid Product product) {
if (product.codebar != null && Product.findByCodebar(product.codebar) != null) {
return Response.status(Response.Status.CONFLICT)
.entity("{\\"error\\": \\"Product with this barcode already exists\\"}")
.build();
}
product.createdAt = LocalDateTime.now();
product.persist();
return Response.status(Response.Status.CREATED)
.entity(product)
.build();
}
@PUT
@Path("/{id}")
@Transactional
public Response update(@PathParam("id") Long id, @Valid Product updatedProduct) {
Product product = Product.findById(id);
if (product == null) {
return Response.status(Response.Status.NOT_FOUND).build();
}
product.name = updatedProduct.name;
product.quantity = updatedProduct.quantity;
product.stock = updatedProduct.stock;
product.unitOfMeasurement = updatedProduct.unitOfMeasurement;
product.codebar = updatedProduct.codebar;
product.position = updatedProduct.position;
product.updatedAt = LocalDateTime.now();
return Response.ok(product).build();
}
@DELETE
@Path("/{id}")
@Transactional
public Response delete(@PathParam("id") Long id) {
boolean deleted = Product.deleteById(id);
if (!deleted) {
return Response.status(Response.Status.NOT_FOUND).build();
}
return Response.noContent().build();
}
}
PositionResource
Create src/main/java/com/bmw/product/resource/PositionResource.java:
package com.bmw.product.resource;
import com.bmw.product.model.Position;
import com.bmw.product.model.Product;
import javax.transaction.Transactional;
import javax.validation.Valid;
import javax.ws.rs.*;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;
import java.time.LocalDateTime;
import java.util.List;
@Path("/api/positions")
@Produces(MediaType.APPLICATION_JSON)
@Consumes(MediaType.APPLICATION_JSON)
public class PositionResource {
@GET
public List<Position> listAll() {
return Position.listAll();
}
@GET
@Path("/{id}")
public Response getById(@PathParam("id") Long id) {
Position position = Position.findById(id);
if (position == null) {
return Response.status(Response.Status.NOT_FOUND).build();
}
return Response.ok(position).build();
}
@GET
@Path("/{id}/products")
public List<Product> getProducts(@PathParam("id") Long id) {
return Product.findByPosition(id);
}
@POST
@Transactional
public Response create(@Valid Position position) {
if (Position.findByCode(position.code) != null) {
return Response.status(Response.Status.CONFLICT)
.entity("{\\"error\\": \\"Position with this code already exists\\"}")
.build();
}
position.createdAt = LocalDateTime.now();
position.persist();
return Response.status(Response.Status.CREATED)
.entity(position)
.build();
}
@DELETE
@Path("/{id}")
@Transactional
public Response delete(@PathParam("id") Long id) {
boolean deleted = Position.deleteById(id);
if (!deleted) {
return Response.status(Response.Status.NOT_FOUND).build();
}
return Response.noContent().build();
}
}
API Endpoints Summary
| Method | Endpoint | Description |
|---|---|---|
| GET | /api/products | List all products |
| GET | /api/products/{id} | Get product by ID |
| GET | /api/products/barcode/{code} | Find by barcode |
| POST | /api/products | Create new product |
| PUT | /api/products/{id} | Update product |
| DELETE | /api/products/{id} | Delete product |
| GET | /api/positions | List all positions |
| GET | /api/positions/{id} | Get position by ID |
| GET | /api/positions/{id}/products | Get products at position |
| POST | /api/positions | Create position |
| DELETE | /api/positions/{id} | Delete position |
8. Environment Configuration
Managing configurations across multiple environments is crucial for production deployments.
Profile-Based Configuration
Development Profile:
# ==========================================
# DEV PROFILE
# ==========================================
%dev.quarkus.datasource.username=postgres
%dev.quarkus.datasource.password=secret
%dev.quarkus.datasource.jdbc.url=jdbc:postgresql://localhost:5432/productdb_dev
# Enable SQL logging in dev
%dev.quarkus.hibernate-orm.log.sql=true
%dev.quarkus.hibernate-orm.log.format-sql=true
# Flyway dev settings
%dev.quarkus.flyway.migrate-at-start=true
%dev.quarkus.flyway.clean-at-start=false
# Dev Services: Auto-start PostgreSQL container
%dev.quarkus.datasource.devservices.enabled=true
%dev.quarkus.datasource.devservices.image-name=postgres:15-alpine
Production Profile:
# ==========================================
# PROD PROFILE
# ==========================================
# Use environment variables for credentials
%prod.quarkus.datasource.username=${DB_USERNAME}
%prod.quarkus.datasource.password=${DB_PASSWORD}
%prod.quarkus.datasource.jdbc.url=${DATABASE_URL}
# Production pool settings
%prod.quarkus.datasource.jdbc.max-size=50
# Disable SQL logging
%prod.quarkus.hibernate-orm.log.sql=false
# Flyway production settings
%prod.quarkus.flyway.migrate-at-start=true
%prod.quarkus.flyway.clean-at-start=false
Docker Compose for Local Development
Create docker-compose.yml:
version: '3.8'
services:
postgres:
image: postgres:15-alpine
container_name: product-db
environment:
POSTGRES_DB: productdb
POSTGRES_USER: postgres
POSTGRES_PASSWORD: secret
ports:
- "5432:5432"
volumes:
- postgres-data:/var/lib/postgresql/data
healthcheck:
test: ["CMD-SHELL", "pg_isready -U postgres"]
interval: 10s
timeout: 5s
retries: 5
pgadmin:
image: dpage/pgadmin4:latest
container_name: pgadmin
environment:
PGADMIN_DEFAULT_EMAIL: admin@example.com
PGADMIN_DEFAULT_PASSWORD: admin
ports:
- "5050:80"
depends_on:
- postgres
volumes:
postgres-data:
Start the environment:
# Start PostgreSQL
docker-compose up -d postgres
# Start PostgreSQL + pgAdmin
docker-compose up -d
# Stop everything
docker-compose down
9. Testing and Monitoring
Database Health Check
Create src/main/java/com/bmw/product/health/DatabaseHealthCheck.java:
package com.bmw.product.health;
import org.eclipse.microprofile.health.HealthCheck;
import org.eclipse.microprofile.health.HealthCheckResponse;
import org.eclipse.microprofile.health.Readiness;
import javax.enterprise.context.ApplicationScoped;
import javax.inject.Inject;
import javax.sql.DataSource;
import java.sql.Connection;
@Readiness
@ApplicationScoped
public class DatabaseHealthCheck implements HealthCheck {
@Inject
DataSource datasource;
@Override
public HealthCheckResponse call() {
try (Connection connection = datasource.getConnection()) {
boolean isValid = connection.isValid(5);
return HealthCheckResponse
.named("Database connection")
.status(isValid)
.withData("database", "PostgreSQL")
.build();
} catch (Exception e) {
return HealthCheckResponse
.named("Database connection")
.down()
.withData("error", e.getMessage())
.build();
}
}
}
Access health endpoints:
# Overall health
curl http://localhost:8080/q/health
# Readiness (includes database check)
curl http://localhost:8080/q/health/ready
# Liveness
curl http://localhost:8080/q/health/live
Integration Tests
Create src/test/java/com/bmw/product/ProductResourceTest.java:
package com.bmw.product;
import io.quarkus.test.junit.QuarkusTest;
import io.restassured.http.ContentType;
import org.junit.jupiter.api.*;
import static io.restassured.RestAssured.given;
import static org.hamcrest.CoreMatchers.*;
@QuarkusTest
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
public class ProductResourceTest {
@Test
public void testListAllProducts() {
given()
.when().get("/api/products")
.then()
.statusCode(200)
.contentType(ContentType.JSON);
}
@Test
public void testCreateProduct() {
String requestBody = """
{
"name": "Test Product",
"quantity": 100,
"unitOfMeasurement": "pieces",
"stock": 80,
"codebar": "TEST-PROD-001"
}
""";
given()
.contentType(ContentType.JSON)
.body(requestBody)
.when().post("/api/products")
.then()
.statusCode(201)
.body("name", equalTo("Test Product"));
}
}
Run tests:
./mvnw test
10. Production Deployment
Building the Application
JAR Build:
./mvnw clean package
java -jar target/quarkus-app/quarkus-run.jar
Native Build:
./mvnw package -Pnative
./target/product-management-system-1.0.0-SNAPSHOT-runner
Benefits of native builds:
- ⚡ Startup time: < 0.1 seconds
- 💾 Memory usage: ~30MB RSS
- 📦 No JVM required
- 🚀 Instant scale-to-zero
Docker Containerization
Build Docker image:
# Build application
./mvnw package
# Build Docker image
docker build -f src/main/docker/Dockerfile.jvm -t product-management-system:latest .
# Run container
docker run -i --rm -p 8080:8080 \\
-e QUARKUS_DATASOURCE_JDBC_URL=jdbc:postgresql://host.docker.internal:5432/productdb \\
-e QUARKUS_DATASOURCE_USERNAME=postgres \\
-e QUARKUS_DATASOURCE_PASSWORD=secret \\
product-management-system:latest
Kubernetes Deployment
deployment.yaml:
apiVersion: apps/v1
kind: Deployment
metadata:
name: product-management-system
spec:
replicas: 3
selector:
matchLabels:
app: product-management
template:
metadata:
labels:
app: product-management
spec:
containers:
- name: product-app
image: product-management-system:latest
ports:
- containerPort: 8080
env:
- name: QUARKUS_DATASOURCE_JDBC_URL
value: jdbc:postgresql://postgres-service:5432/productdb
- name: QUARKUS_DATASOURCE_USERNAME
valueFrom:
secretKeyRef:
name: db-credentials
key: username
- name: QUARKUS_DATASOURCE_PASSWORD
valueFrom:
secretKeyRef:
name: db-credentials
key: password
livenessProbe:
httpGet:
path: /q/health/live
port: 8080
readinessProbe:
httpGet:
path: /q/health/ready
port: 8080
Deploy:
kubectl apply -f k8s/deployment.yaml
kubectl get pods
kubectl logs -f deployment/product-management-system
11. Best Practices and Optimization
Database Best Practices
1. Index Strategy
-- Composite index for common queries
CREATE INDEX idx_product_position_stock
ON products(position_id, stock)
WHERE stock < 100;
-- Partial index for active products
CREATE INDEX idx_product_active
ON products(name, codebar)
WHERE quantity > 0;
2. Query Optimization
// BAD: N+1 query problem
List<Product> products = Product.listAll();
products.forEach(p -> System.out.println(p.position.name));
// GOOD: Fetch join
@Entity
public class Product extends PanacheEntity {
public static List<Product> findAllWithPositions() {
return find("SELECT p FROM Product p LEFT JOIN FETCH p.position").list();
}
}
3. Connection Pool Tuning
# Formula: (core_count * 2) + effective_spindle_count
quarkus.datasource.jdbc.max-size=20
quarkus.datasource.jdbc.background-validation-interval=2M
quarkus.datasource.jdbc.idle-removal-interval=5M
Flyway Best Practices
1. Never Modify Applied Migrations
# WRONG: Editing V1.0.0 after it's been applied
# RIGHT: Create V1.0.1 with changes
2. Use Semantic Versioning
V1.0.0__initial_schema.sql # Major
V1.1.0__add_categories.sql # Minor
V1.1.1__fix_category_constraint.sql # Patch
3. Zero-Downtime Migrations
-- Step 1: Add nullable column
ALTER TABLE products ADD COLUMN category VARCHAR(50);
-- Step 2: Populate defaults
UPDATE products SET category = 'GENERAL' WHERE category IS NULL;
-- Step 3: Make NOT NULL (after deployment)
ALTER TABLE products ALTER COLUMN category SET NOT NULL;
Performance Optimization
1. Enable Second-Level Cache
quarkus.hibernate-orm.second-level-caching-enabled=true
@Entity
@Cacheable
public class Position extends PanacheEntity {
// Frequently accessed data
}
2. Use Pagination
@GET
public Response listAll(
@QueryParam("page") @DefaultValue("0") int page,
@QueryParam("size") @DefaultValue("20") int size
) {
return Response.ok(
Product.findAll().page(page, size).list()
).build();
}
Security Best Practices
1. Use Environment Variables
# Never hardcode credentials!
quarkus.datasource.password=${DB_PASSWORD}
2. Enable SSL in Production
%prod.quarkus.datasource.jdbc.url=jdbc:postgresql://db:5432/productdb?ssl=true
3. Prevent SQL Injection
// GOOD: Parameterized queries
Product.find("name = ?1", name).list();
// BAD: String concatenation
Product.find("name = '" + name + "'").list(); // NEVER!
12. Conclusion
We've built a comprehensive, production-ready application demonstrating:
✅ Complete datasource configuration with Agroal connection pooling
✅ Hibernate ORM integration with Panache for simplified data access
✅ Flyway-based migrations for version-controlled schema evolution
✅ RESTful CRUD API with proper transaction handling
✅ Tree-based positioning system for warehouse management
✅ Multi-environment configuration for dev, test, and production
✅ Health checks and monitoring for production observability
✅ Docker and Kubernetes deployment strategies
Key Takeaways
- Quarkus + PostgreSQL provides a powerful, cloud-native stack
- Flyway is essential for managing database schema evolution
- Panache dramatically simplifies data access code
- Proper configuration management prevents production issues
- Native compilation offers incredible performance benefits
Next Steps
To extend this application, consider:
- 🔐 Authentication & Authorization: Add JWT/OAuth2 security
- 🔍 Full-Text Search: Implement Elasticsearch
- 📊 Analytics Dashboard: Build reporting
- 🔔 Event-Driven Architecture: Add Kafka
- 📱 Frontend Application: Build React/Angular SPA
- 🤖 Machine Learning: Implement demand forecasting
Testing the API
Create a product:
curl -X POST http://localhost:8080/api/products \\
-H "Content-Type: application/json" \\
-d '{
"name": "BMW Logo Badge",
"quantity": 1000,
"unitOfMeasurement": "pieces",
"stock": 850,
"codebar": "BMW-BADGE-001",
"position": {"id": 9}
}'
Get all products:
curl http://localhost:8080/api/products
Search by barcode:
curl http://localhost:8080/api/products/barcode/BMW-BADGE-001
Running the Application
# Start PostgreSQL
docker-compose up -d postgres
# Run in dev mode
./mvnw quarkus:dev
# Access:
# - API: http://localhost:8080/api/products
# - Dev UI: http://localhost:8080/q/dev
# - Health: http://localhost:8080/q/health
Additional Resources
Thank you for reading! If you found this guide helpful, please share it with your team. Feel free to leave comments or questions below.
Happy coding with Quarkus! 🚀
Tags: #Quarkus #PostgreSQL #Flyway #Hibernate #REST #Microservices #Java #CloudNative #Docker #Kubernetes
Last updated: July 30, 2026
