Skip to main content
SGIVU uses PostgreSQL databases with Flyway migrations for schema management. This page documents the standard database configuration patterns.

​
Services with Databases

The following services require database configuration:

Auth Service

Manages OAuth2/OIDC data, user authentication, and session storage

User Service

Stores user profiles and related data

Client Service

Manages client/customer information

Vehicle Service

Stores vehicle inventory and details

Purchase-Sale Service

Tracks transactions and purchase/sale records

​
Standard Database Configuration

All data-access services use this base configuration pattern: 
spring:
  jpa:
    open-in-view: false
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: ${FLYWAY_BASELINE_ON_MIGRATE:false}
    baseline-version: 0
    validate-on-migrate: true

​
JPA Configuration

​
Open In View

​
spring.jpa.open-in-view
boolean
required
Controls whether JPA sessions remain open during view rendering. Always set to false in SGIVU services.
spring:
  jpa:
    open-in-view: false
Setting open-in-view: false is a best practice for REST APIs. It prevents lazy loading exceptions and enforces explicit transaction boundaries in controllers.

​
Hibernate DDL Mode (Profile-Specific)

Different profiles use different DDL strategies:

​
Development Profile

spring:
  jpa:
    hibernate:
      ddl-auto: validate
    show-sql: true
    properties:
      hibernate:
        format_sql: true
​
spring.jpa.hibernate.ddl-auto
string
Hibernate schema generation mode. Use validate to verify schema matches entities without modifying the database.
​
spring.jpa.show-sql
boolean
Enables SQL statement logging. Set to true in development for debugging.

​
Production Profile

spring:
  jpa:
    hibernate:
      ddl-auto: validate
Production profiles disable SQL logging for performance and security. Schema validation ensures entities match the database structure.

​
Flyway Migration Configuration

SGIVU uses Flyway for database schema versioning and migrations.

​
Base Configuration

​
spring.flyway.enabled
boolean
required
Enables Flyway migrations. Set to true for all services with databases.
​
spring.flyway.locations
string
required
Classpath location of migration scripts. Standard location is classpath:db/migration.
​
spring.flyway.baseline-on-migrate
boolean
Creates a baseline version when migrating an existing database. Controlled by FLYWAY_BASELINE_ON_MIGRATE environment variable.
​
spring.flyway.baseline-version
integer
Version number for the baseline. Set to 0 to mark the starting point.
​
spring.flyway.validate-on-migrate
boolean
Validates applied migrations before running new ones. Set to true to ensure migration integrity.
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: ${FLYWAY_BASELINE_ON_MIGRATE:false}
    baseline-version: 0
    validate-on-migrate: true

​
Profile-Specific Flyway Settings

​
Development Profile

spring:
  flyway:
    baseline-on-migrate: true
    clean-disabled: false
​
spring.flyway.clean-disabled
boolean
Prevents Flyway from dropping all database objects. Set to false in development to allow clean operations.
Enabling clean-disabled: false in development allows destructive operations. Never use this in production.

​
Production Profile

spring:
  flyway:
    clean-disabled: true
    baseline-on-migrate: ${FLYWAY_BASELINE_ON_MIGRATE:false}
Production profiles explicitly disable clean operations to prevent accidental data loss.

​
Database Connection Configuration

Database connection details are profile-specific and environment-driven:

​
Development Profile

spring:
  datasource:
    url: jdbc:postgresql://${DEV_AUTH_DB_HOST:host.docker.internal}:${DEV_AUTH_DB_PORT:5432}/${DEV_AUTH_DB_NAME}
    username: ${DEV_AUTH_DB_USERNAME}
    password: ${DEV_AUTH_DB_PASSWORD}
    driver-class-name: org.postgresql.Driver

​
Production Profile

spring:
  datasource:
    url: jdbc:postgresql://${PROD_AUTH_DB_HOST:host.docker.internal}:${PROD_AUTH_DB_PORT:5432}/${PROD_AUTH_DB_NAME}
    username: ${PROD_AUTH_DB_USERNAME}
    password: ${PROD_AUTH_DB_PASSWORD}
    driver-class-name: org.postgresql.Driver
Each service uses prefixed environment variables:Auth Service:
  • DEV_AUTH_DB_HOST / PROD_AUTH_DB_HOST
  • DEV_AUTH_DB_PORT / PROD_AUTH_DB_PORT
  • DEV_AUTH_DB_NAME / PROD_AUTH_DB_NAME
  • DEV_AUTH_DB_USERNAME / PROD_AUTH_DB_USERNAME
  • DEV_AUTH_DB_PASSWORD / PROD_AUTH_DB_PASSWORD
User Service:
  • DEV_USER_DB_HOST / PROD_USER_DB_HOST
  • (same pattern for other variables)
This prefix pattern is used for all services with databases.

​
Migration Script Organization

Flyway migrations should be placed in src/main/resources/db/migration with the following naming convention: 
V{version}__{description}.sql

​
Examples

V1__initial_schema.sql
V2__add_user_roles.sql
V3__create_vehicle_table.sql
V4__add_transaction_indexes.sql
Version numbers should be sequential integers. Flyway applies migrations in order and tracks which versions have been applied.

​
Complete Service Examples

​
Auth Service (sgivu-auth.yml)

spring:
  jpa:
    open-in-view: false
  session:
    store-type: jdbc
    jdbc:
      initialize-schema: never
      table-name: SPRING_SESSION
      cleanup-cron: 0 */15 * * * *
      flush-mode: on_save
      save-mode: on_get_attribute
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: ${FLYWAY_BASELINE_ON_MIGRATE:false}
    baseline-version: 0
    validate-on-migrate: true
The Auth Service additionally uses JDBC-backed sessions, which requires database configuration for the SPRING_SESSION table.

​
User Service (sgivu-user.yml)

spring:
  jpa:
    open-in-view: false
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: ${FLYWAY_BASELINE_ON_MIGRATE:false}
    baseline-version: 0
    validate-on-migrate: true

​
Vehicle Service (sgivu-vehicle.yml)

spring:
  jpa:
    open-in-view: false
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: ${FLYWAY_BASELINE_ON_MIGRATE:false}
    baseline-version: 0
    validate-on-migrate: true

​
Client Service (sgivu-client.yml)

spring:
  jpa:
    open-in-view: false
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: ${FLYWAY_BASELINE_ON_MIGRATE:false}
    baseline-version: 0
    validate-on-migrate: true

​
Purchase-Sale Service (sgivu-purchase-sale.yml)

spring:
  jpa:
    open-in-view: false
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: ${FLYWAY_BASELINE_ON_MIGRATE:false}
    baseline-version: 0
    validate-on-migrate: true

​
Session Storage (Auth Service)

The Auth Service uses JDBC-backed session storage:
​
spring.session.store-type
string
Session storage mechanism. Set to jdbc for database-backed sessions.
​
spring.session.jdbc.initialize-schema
string
Controls session table creation. Set to never since Flyway manages the schema.
​
spring.session.jdbc.table-name
string
Name of the session table. Standard value is SPRING_SESSION.
​
spring.session.jdbc.cleanup-cron
string
Cron expression for expired session cleanup. Runs every 15 minutes.
spring:
  session:
    store-type: jdbc
    jdbc:
      initialize-schema: never
      table-name: SPRING_SESSION
      cleanup-cron: 0 */15 * * * *
      flush-mode: on_save
      save-mode: on_get_attribute
Set initialize-schema: never to prevent Spring Session from creating tables. Flyway should manage all schema changes.

​
Troubleshooting

Error: Found non-empty schema without schema history tableSolution: Set the FLYWAY_BASELINE_ON_MIGRATE environment variable to true for the initial migration:
FLYWAY_BASELINE_ON_MIGRATE=true
This creates a baseline version and allows Flyway to start managing an existing database.
Error: Validate failed: Migration checksum mismatchCauses:
  • Migration file was modified after being applied
  • Different migration file with same version number
Solutions:
  1. Never modify applied migrations - create a new migration instead
  2. If in development, clean the database and re-run migrations
  3. If in production, repair the Flyway schema history table
Error: LazyInitializationException: could not initialize proxy - no SessionCause: Attempting to access lazy-loaded relationships outside a transaction.Solution: With open-in-view: false, you must:
  1. Explicitly fetch required relationships in your queries
  2. Use @Transactional on service methods that load entities
  3. Use DTO projections to avoid lazy loading issues
Symptoms:
  • Slow response times
  • Timeout errors
  • “Connection is not available” exceptions
Solutions:
  1. Ensure transactions are properly closed (use @Transactional)
  2. Configure connection pool settings (HikariCP)
  3. Check for connection leaks in application code
  4. Increase pool size if needed (with proper resource analysis)

Spring Configuration

JPA and session management settings

Environment Variables

Database connection environment variables

Service Configuration

Individual service database requirements

Deployment

Database setup in different environments

Build docs developers (and LLMs) love

Get started for freeTalk to us