Spring Boot

Spring Boot PostgreSQL Integration: JSONB, Enums, Flyway and Testcontainers

Connect Spring Boot 3 to PostgreSQL and handle the parts that usually break: JSONB and array columns, native enums, upserts, and full-text search with an index that is actually used. One products schema runs through the entity, Flyway migration, queries and Testcontainers test.

Quick Start: Minimum Setup

The smallest working setup is two dependencies and three properties. The rest of this page builds the production-grade version on top of it: Flyway-managed schema, PostgreSQL-specific types, native queries and real-database tests. ``` spring-boot-starter-data-jpa + org.postgresql:postgresql spring.datasource.url / username / password ``` That is enough to start the app and run basic CRUD. Everything below handles what that minimal setup does not: JSONB, arrays, enums, upserts, full-text search and schema control.

Version Requirements at a Glance

Several features depend on the Spring Boot (and therefore Hibernate and Flyway) version. Check this table before copying code. | Feature | Needs | Spring Boot | |---|---|---| | `@JdbcTypeCode(SqlTypes.JSON)` | Hibernate 6.x + Jackson | 3.0+ | | `@JdbcTypeCode(SqlTypes.NAMED_ENUM)` | Hibernate 6.2+ | 3.1+ | | `@JdbcTypeCode(SqlTypes.ARRAY)` with `List<String>` | Hibernate 6.4+ | 3.2+ | | `@ServiceConnection` for Testcontainers | Spring Boot feature | 3.1+ | | `flyway-database-postgresql` module required | Flyway 10+ | 3.3+ (verify against your version) | Examples target Spring Boot 3.x. If you are on a newer major version, confirm dependency names and managed versions in its release notes.

Dependencies

Two dependencies are enough to connect: spring-boot-starter-data-jpa and the PostgreSQL JDBC driver. Add flyway-core and flyway-database-postgresql as well, because Flyway owns the schema and Hibernate only validates it. From Flyway 10 the database-specific module is required; without it, startup fails with "Unsupported Database: PostgreSQL". Hibernate 6 detects the PostgreSQL dialect from the JDBC URL, so there is no need to configure one.

XML

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

Configuration (application.yml)

The password comes from an environment variable so it never lands in version control. Three settings deserve explanation. - **ddl-auto: validate.** Flyway creates the schema; Hibernate only checks that entities match it, so a mismatch fails at startup instead of in production. - **open-in-view: false.** Database connections are not held during view rendering. - **max-lifetime.** Keep it shorter than any idle limit imposed by a proxy or load balancer in front of the database (PgBouncer, cloud load balancers), otherwise the pool hands out connections that were silently closed.

yaml

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: postgres
    password: ${DB_PASSWORD}
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5
      connection-timeout: 20000
      idle-timeout: 300000
      max-lifetime: 600000
      keepalive-time: 60000
      pool-name: myapp-pg-pool

  jpa:
    hibernate:
      ddl-auto: validate
    open-in-view: false
    show-sql: false
    properties:
      hibernate:
        format_sql: true
        jdbc:
          batch_size: 25
          batch_versioned_data: true
        default_batch_fetch_size: 25

  flyway:
    enabled: true
    locations: classpath:db/migration
    validate-on-migrate: true

Schema Migration with Flyway

Write the PostgreSQL-specific DDL by hand in a migration. Hibernate cannot generate native enums, GIN indexes or expression indexes for you. Note the last index: it covers the same expression (name plus description) that the full-text search query uses later. An index on name alone would be ignored by that query, and PostgreSQL would fall back to a sequential scan. The categories table is used by the recursive query further down.

sql

-- src/main/resources/db/migration/V1__create_products_table.sql
CREATE TYPE product_status AS ENUM ('ACTIVE', 'INACTIVE', 'DISCONTINUED');

CREATE TABLE products (
    id          UUID            NOT NULL DEFAULT gen_random_uuid(),
    name        VARCHAR(200)    NOT NULL,
    description TEXT,
    category    VARCHAR(100),
    price       NUMERIC(10,2)   NOT NULL,
    metadata    JSONB,
    tags        TEXT[],
    status      product_status  NOT NULL DEFAULT 'ACTIVE',
    created_at  TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id)
);

CREATE INDEX idx_products_category ON products (category);
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
CREATE INDEX idx_products_tags     ON products USING GIN (tags);
CREATE INDEX idx_products_fts      ON products
    USING GIN (to_tsvector('english', name || ' ' || COALESCE(description, '')));

CREATE TABLE categories (
    id        BIGINT       PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    parent_id BIGINT       REFERENCES categories (id)
);

Mapping JSONB, Arrays and Enums

Since Hibernate 6 you do not need custom AttributeConverter classes for these types. A converter that turns a Map into a String sends a varchar parameter to a jsonb column, which PostgreSQL rejects. Use `@JdbcTypeCode` so Hibernate binds the correct JDBC type: - `SqlTypes.JSON` for jsonb (needs Jackson, which Spring Boot already provides) - `SqlTypes.ARRAY` for text[] (see the version table) - `SqlTypes.NAMED_ENUM` for a native PostgreSQL enum `GenerationType.UUID` lets Hibernate generate the id in Java, which works alongside the database default.

Java

import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.JdbcTypeCode;
import org.hibernate.type.SqlTypes;

@Entity
@Table(name = "products")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    @Column(updatable = false)
    private UUID id;

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

    @Column(columnDefinition = "TEXT")
    private String description;

    private String category;

    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal price;

    @JdbcTypeCode(SqlTypes.JSON)
    @Column(columnDefinition = "jsonb")
    private Map<String, Object> metadata;

    @JdbcTypeCode(SqlTypes.ARRAY)
    @Column(columnDefinition = "text[]")
    private List<String> tags;

    @Enumerated(EnumType.STRING)
    @JdbcTypeCode(SqlTypes.NAMED_ENUM)
    @Column(columnDefinition = "product_status")
    private ProductStatus status;

    @CreationTimestamp
    @Column(nullable = false, updatable = false)
    private OffsetDateTime createdAt;

    // getters and setters omitted
}

public enum ProductStatus { ACTIVE, INACTIVE, DISCONTINUED }

PostgreSQL-Specific Native Queries

These queries use features JPQL cannot express. Two details matter. 1. Native enums and jsonb need an explicit `CAST` when the value arrives as a string parameter, otherwise PostgreSQL reports a type mismatch. 2. The full-text query repeats the exact expression from the `idx_products_fts` index, which is what lets PostgreSQL use it. The window-function and recursive queries return `Object[]` rows because their extra computed columns do not exist on the entity. In real code, map them to a projection interface or record. Caveat: `ARRAY[:tags]` expands to one placeholder per element, so an empty list produces invalid SQL. Check for an empty list before calling `findByAnyTag`.

Java

@Repository
public interface ProductRepository extends JpaRepository<Product, UUID> {

    // UPSERT: insert, or update when the id already exists
    @Modifying
    @Transactional
    @Query(value = """
            INSERT INTO products (id, name, price, status)
            VALUES (:id, :name, :price, CAST(:status AS product_status))
            ON CONFLICT (id) DO UPDATE
                SET name       = EXCLUDED.name,
                    price      = EXCLUDED.price,
                    status     = EXCLUDED.status,
                    updated_at = NOW()
            """, nativeQuery = true)
    void upsert(@Param("id") UUID id,
                @Param("name") String name,
                @Param("price") BigDecimal price,
                @Param("status") String status);

    // Full-text search, ranked; expression matches idx_products_fts
    @Query(value = """
            SELECT p.* FROM products p
            WHERE to_tsvector('english', p.name || ' ' || COALESCE(p.description, ''))
                  @@ plainto_tsquery('english', :query)
            ORDER BY ts_rank(
                to_tsvector('english', p.name || ' ' || COALESCE(p.description, '')),
                plainto_tsquery('english', :query)) DESC
            """, nativeQuery = true)
    List<Product> fullTextSearch(@Param("query") String query);

    // JSONB: extract a field as text
    @Query(value = "SELECT * FROM products WHERE metadata ->> 'brand' = :brand",
           nativeQuery = true)
    List<Product> findByBrand(@Param("brand") String brand);

    // JSONB: containment, served by the GIN index (filter e.g. '{"color":"red"}')
    @Query(value = "SELECT * FROM products WHERE metadata @> CAST(:filter AS jsonb)",
           nativeQuery = true)
    List<Product> findByMetadataContaining(@Param("filter") String jsonFilter);

    // Array: single value
    @Query(value = "SELECT * FROM products WHERE :tag = ANY(tags)",
           nativeQuery = true)
    List<Product> findByTag(@Param("tag") String tag);

    // Array: overlap with any of several values (list must not be empty)
    @Query(value = "SELECT * FROM products WHERE tags && CAST(ARRAY[:tags] AS text[])",
           nativeQuery = true)
    List<Product> findByAnyTag(@Param("tags") List<String> tags);

    // Window functions: per-category average and rank
    @Query(value = """
            SELECT p.id, p.name, p.price,
                   AVG(p.price) OVER (PARTITION BY p.category) AS category_avg_price,
                   RANK() OVER (PARTITION BY p.category ORDER BY p.price DESC)
                       AS price_rank_in_category,
                   LAG(p.price) OVER (ORDER BY p.created_at) AS prev_price
            FROM products p
            WHERE p.status = 'ACTIVE'
            """, nativeQuery = true)
    List<Object[]> findWithWindowStats();

    // Recursive CTE: walk a category hierarchy from the roots down
    @Query(value = """
            WITH RECURSIVE category_tree AS (
                SELECT id, name, parent_id, 0 AS depth
                FROM categories
                WHERE parent_id IS NULL
                UNION ALL
                SELECT c.id, c.name, c.parent_id, ct.depth + 1
                FROM categories c
                JOIN category_tree ct ON ct.id = c.parent_id
            )
            SELECT * FROM category_tree ORDER BY depth, name
            """, nativeQuery = true)
    List<Object[]> findCategoryTree();
}

Sequences for High-Volume Inserts: Migration

IDENTITY columns force Hibernate to insert rows one at a time, because it needs the generated id back immediately. A sequence with `allocationSize` lets Hibernate reserve a block of ids in one round trip and then batch the inserts. The rule that trips people up: **`INCREMENT BY` in the migration must equal `allocationSize` in the entity.** If they differ, Hibernate hands out ids that collide with later ones, and you get duplicate key errors that only appear under load. For tables without heavy insert volume, the UUID approach used for products is simpler.

sql

-- V2__create_orders_sequence.sql (Flyway)
CREATE SEQUENCE orders_id_seq START WITH 1 INCREMENT BY 50 CACHE 50;

CREATE TABLE orders (
    id         BIGINT        NOT NULL DEFAULT nextval('orders_id_seq'),
    reference  VARCHAR(50)   NOT NULL UNIQUE,
    total      NUMERIC(12,2) NOT NULL,
    created_at TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id)
);

Sequences for High-Volume Inserts: Entity

The entity side of the same setup. `allocationSize = 50` matches `INCREMENT BY 50` above.

Java

@Entity
@Table(name = "orders")
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "orders_seq")
    @SequenceGenerator(name = "orders_seq",
                       sequenceName = "orders_id_seq",
                       allocationSize = 50)
    private Long id;

    // other fields omitted
}

Local Development with Docker

Run the same major PostgreSQL version locally that you run in production. The credentials below are for local development only, never reuse them elsewhere. The `pg_stat_statements` settings are optional but useful: after running `CREATE EXTENSION pg_stat_statements;` once in your database, you can see which queries are slowest while you develop. Keep `ddl-auto` at `validate` here too. Letting Hibernate create tables locally while Flyway creates them in production is how schema drift starts.

yaml

# docker-compose.yml
services:
  postgres:
    image: postgres:16-alpine
    container_name: myapp-postgres
    environment:
      POSTGRES_DB: mydb
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD: apppassword   # local development only
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
    command: >
      postgres
        -c shared_preload_libraries=pg_stat_statements
        -c pg_stat_statements.track=all
        -c max_connections=100

volumes:
  postgres_data:

# application-dev.yml
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: appuser
    password: apppassword

Testing with Testcontainers: Dependencies

Testcontainers starts a throwaway PostgreSQL in Docker for the test run, so JSONB operators, ON CONFLICT and enum casts are tested against the real database instead of an in-memory substitute that behaves differently. You need `spring-boot-testcontainers`, the Testcontainers PostgreSQL module and the JUnit 5 integration module (`junit-jupiter`, which provides `@Testcontainers` and `@Container`). Versions are managed by the Spring Boot BOM. Artifact names changed in Testcontainers 2.x, so check the names your Boot version manages.

XML

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-testcontainers</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>junit-jupiter</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <scope>test</scope>
</dependency>

Testing with Testcontainers: Test Class

From Spring Boot 3.1, `@ServiceConnection` wires the datasource automatically and replaces the old `@DynamicPropertySource` boilerplate. Flyway runs your migrations against the container on startup, so the test also proves that migrations and entities agree. Docker must be running when the tests execute.

Java

@SpringBootTest
@Testcontainers
class ProductRepositoryTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    private ProductRepository productRepository;

    @Test
    void upsertCreatesThenUpdates() {
        UUID id = UUID.randomUUID();
        productRepository.upsert(id, "Widget", new BigDecimal("9.99"), "ACTIVE");
        productRepository.upsert(id, "Widget Pro", new BigDecimal("19.99"), "ACTIVE");

        Product found = productRepository.findById(id).orElseThrow();
        assertThat(found.getName()).isEqualTo("Widget Pro");
        assertThat(productRepository.count()).isEqualTo(1);
    }
}

Errors You Will Probably Hit

Most first-day problems with this setup come from a handful of causes. | Error | Cause | Fix | |---|---|---| | `column "metadata" is of type jsonb but expression is of type character varying` | A String-based converter feeds a jsonb column | Use `@JdbcTypeCode(SqlTypes.JSON)` | | Same message for `product_status` | Native enum parameter in a native query | `CAST(:param AS product_status)` | | `Unsupported Database: PostgreSQL` | Flyway 10+ without the database module | Add `flyway-database-postgresql` | | Flyway checksum mismatch | An applied migration was edited | Never edit applied migrations; add a new versioned file | | Duplicate key errors under load | Sequence `INCREMENT BY` differs from `allocationSize` | Make both values equal | | Slow query despite an index | Query expression differs from the index expression | Run `EXPLAIN ANALYZE` and match the expression exactly |

sql

-- Check whether PostgreSQL actually uses your index
EXPLAIN ANALYZE
SELECT * FROM products
WHERE to_tsvector('english', name || ' ' || COALESCE(description, ''))
      @@ plainto_tsquery('english', 'wireless mouse');
-- Look for "Bitmap Index Scan on idx_products_fts"; "Seq Scan" means no index use.

When Not to Use JSONB or Native Enums

JSONB suits attributes that vary from row to row, such as optional product specifications. Any field you filter or join on constantly deserves its own column, where it gets proper types, constraints and cheaper indexes. GIN indexes also make writes slower, so index only the JSONB and array columns you really query. Native enums are awkward to evolve: values can be added with `ALTER TYPE ... ADD VALUE` (the new value cannot be used until that transaction commits) but cannot be removed. If the list of values will change over time, a VARCHAR column with a CHECK constraint is easier to maintain.

sql

-- Alternative to a native enum: easy to change later
ALTER TABLE products
    ADD CONSTRAINT chk_products_status
    CHECK (status IN ('ACTIVE', 'INACTIVE', 'DISCONTINUED'));

-- Changing the allowed values is a simple drop and re-add
ALTER TABLE products DROP CONSTRAINT chk_products_status;
ALTER TABLE products
    ADD CONSTRAINT chk_products_status
    CHECK (status IN ('ACTIVE', 'INACTIVE', 'DISCONTINUED', 'ARCHIVED'));

Production Checklist

- Schema owned by Flyway, `ddl-auto: validate` in every environment - Password and URL supplied by environment variables, not committed - `open-in-view: false` - Pool `max-lifetime` shorter than any proxy or load balancer idle timeout - GIN indexes only on JSONB and array columns you actually query - Index expressions match query expressions (verified with `EXPLAIN ANALYZE`) - Sequence `INCREMENT BY` equals `allocationSize` - Tests run against a real PostgreSQL via Testcontainers, not H2 - Same PostgreSQL major version in development, CI and production

MySQL Integration

MongoDB Integration