Database Configuration

This document provides a comprehensive guide to configuring, managing, and optimizing the database connection for this application. It covers the connection to our MySQL instance, the configuration of the JPA/Hibernate persistence layer, and best practices for schema management and performance tuning.

MySQL Connection

The application connects to a MySQL database using a standard JDBC driver. All connection properties are managed externally in the application.properties file, allowing for different configurations across environments (development, staging, production).

See the full configuration file at /configuration/application_properties.md.

JDBC URL Format and Parameters

The connection string is defined by the spring.datasource.url property.

# src/main/resources/application.properties

spring.datasource.url=jdbc:mysql://legacydb.next26.slalomlab.com:3306/ticketing?createDatabaseIfNotExist=true

The URL is structured as follows:

• jdbc:mysql://: The protocol and subprotocol for the MySQL JDBC driver.
• legacydb.next26.slalomlab.com:3306: The database host and port. For local development using the provided Docker Compose setup, this should be changed to localhost:3306. See /deployment/docker.md for local environment setup.
• /ticketing: The name of the database (schema) to connect to.
• ?createDatabaseIfNotExist=true: A driver-specific parameter that instructs the driver to create the ticketing database if it's not already present. Warning: This is convenient for development but should be disabled in production environments where schema creation must be strictly controlled.

Username and Password Settings

Credentials are set directly in the properties file.

# src/main/resources/application.properties

spring.datasource.username=ticketing_user
spring.datasource.password=changeme

Security Advisory Hardcoding credentials, especially default passwords like changeme, is a significant security risk. For production deployments, these properties must be externalized using environment variables, a secrets management service (like HashiCorp Vault or AWS Secrets Manager), or Spring Cloud Config with an encrypted backend.

Example using environment variables:

spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}

Connection Pool Configuration

Spring Boot automatically configures a connection pool when spring-boot-starter-data-jpa is on the classpath. The default and recommended implementation is HikariCP.

While the current application.properties does not contain any specific HikariCP settings, the default configuration is used. For production environments, it is critical to tune the connection pool. Configuration is done via spring.datasource.hikari.* properties.

# Example of tuned HikariCP properties (add to application.properties as needed)

# Maximum number of connections in the pool.
# Should be sized based on the number of concurrent threads that need DB access.
spring.datasource.hikari.maximum-pool-size=20

# Minimum number of idle connections to maintain.
spring.datasource.hikari.minimum-idle=5

# Maximum time (in ms) a connection can be idle before being retired.
spring.datasource.hikari.idle-timeout=600000

# Maximum time (in ms) to wait for a connection from the pool.
spring.datasource.hikari.connection-timeout=30000

JPA and Hibernate

Data persistence is managed by Spring Data JPA with Hibernate as the persistence provider. This abstracts most of the boilerplate data access logic.

DDL Auto Mode

The ddl-auto property controls Hibernate's behavior regarding Schema Definition Language (DDL) generation.

# src/main/resources/application.properties

spring.jpa.hibernate.ddl-auto=update

• Current Setting: update
  • Behavior: Hibernate compares the application's entity model (e.g., Ticket.java) with the existing database schema and attempts to generate and execute ALTER statements to match them.
  • Use Case: Extremely useful for rapid development and prototyping.
  • Warning: DO NOT USE update IN PRODUCTION. It is not safe for migrations. It cannot handle complex changes like column renames, and a mistake in the entity model could lead to destructive changes or data loss. For production, this property must be set to validate or none.

Show SQL Logging

The application is currently configured not to log SQL statements to the console.

# src/main/resources/application.properties

spring.jpa.show-sql=false
spring.jpa.properties.hibernate.format_sql=true

• spring.jpa.show-sql=false: Disables logging of executed SQL statements to standard output.
• spring.jpa.properties.hibernate.format_sql=true: If show-sql were enabled, this would format the SQL for better readability.

For debugging purposes, it is better to use logger-based SQL logging, which offers more control. To enable it, set the following logging levels in application.properties:

# For viewing SQL statements
logging.level.org.hibernate.SQL=DEBUG

# For viewing JDBC parameter bindings
logging.level.org.hibernate.type.descriptor.sql=TRACE

Dialect Configuration

The dialect tells Hibernate how to generate SQL optimized for a specific database vendor.

# src/main/resources/application.properties

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect

This configuration explicitly sets the dialect for MySQL. While Spring Boot can often auto-detect the correct dialect from the JDBC driver, setting it explicitly prevents potential issues and makes the configuration clearer. The docker-compose.yml specifies mysql:8.0, and MySQLDialect is compatible.

Entity Scanning

Spring Boot automatically scans for classes annotated with @Entity in the base package of the application and its sub-packages. The Ticket class is a primary example.

// src/main/java/com/slalom/demo/ticketing/model/Ticket.java

package com.slalom.demo.ticketing.model;

import jakarta.persistence.*;
// ... other imports

@Entity
@Table(name = "tickets")
@Data
// ...
public class Ticket {

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

    // ... other fields
}

• @Entity: Marks this class as a JPA entity.
• @Table(name = "tickets"): Maps the entity to the tickets table in the database.
• @Id and @GeneratedValue(strategy = GenerationType.IDENTITY): Configures the id field as the primary key and specifies that its value is auto-generated by the database's identity column mechanism (e.g., AUTO_INCREMENT in MySQL).

Schema Management

Proper schema management is crucial for application stability and maintainability, especially as the application evolves.

Automatic Schema Updates (Development)

The current development workflow relies on spring.jpa.hibernate.ddl-auto=update. The Java entity classes, such as Ticket.java, serve as the source of truth for the database schema. Any changes to @Column, @Table, or other JPA annotations will trigger Hibernate to attempt an ALTER TABLE statement on application startup.

For more details on the current entity structure, see /development/database_schema.md.

Manual Migrations (Production)

For production and shared environments (staging, QA), relying on ddl-auto is not a viable strategy. A structured, version-controlled migration process is required. The recommended approach is to disable automatic DDL generation and use a dedicated database migration tool.

Recommended Production Configuration:

spring.jpa.hibernate.ddl-auto=validate

Setting ddl-auto to validate will cause Hibernate to check if the schema matches the entity model on startup and fail fast if there are discrepancies, preventing the application from running with a mismatched schema.

Migration Tools (Flyway, Liquibase)

To manage production schema changes, the team should integrate a database migration tool like Flyway or Liquibase.

• How they work: These tools manage a series of versioned SQL scripts. When the application starts, the tool checks a special metadata table in the database to see which scripts have already been applied and executes any new ones in order.
• Benefits:
  • Version Control: Schema changes are checked into source control alongside code.
  • Reliability: Migrations are repeatable and predictable across all environments.
  • Auditing: A clear history of all schema changes is maintained.
  • Flexibility: Allows for complex changes (data transformations, column renames) that ddl-auto cannot handle.

Recommendation: Integrate Flyway. It is simple to set up with Spring Boot. Just add the dependency and place SQL migration scripts (e.g., V1__Create_ticket_table.sql) in src/main/resources/db/migration.

Performance Tuning

Database performance is a key factor in overall application responsiveness. The following areas should be considered for optimization.

Connection Pool Sizing

As mentioned previously, tuning the HikariCP connection pool is the first step. The optimal size depends on the application's workload and deployment environment. A pool that is too small will cause threads to block waiting for a connection, while a pool that is too large can waste resources and even degrade database performance.

Query Optimization

Spring Data JPA's derived query methods are convenient but can hide performance issues.

// src/main/java/com/slalom/demo/ticketing/repository/TicketRepository.java

@Repository
public interface TicketRepository extends JpaRepository<Ticket, Long> {

    List<Ticket> findByStatus(TicketStatus status);

    List<Ticket> findByRequesterEmail(String requesterEmail);

    List<Ticket> findByAssignedTo(String assignedTo);
}

Each of these methods generates a SELECT query with a WHERE clause on the corresponding column. For large tables, these queries will be slow without proper indexing. Developers should use the SQL logging mechanisms described above to inspect the generated SQL and use tools like EXPLAIN to analyze query plans.

Indexing Strategies

Based on the repository methods in TicketRepository, the following columns are prime candidates for database indexes:

• status
• requester_email
• assigned_to

Without indexes, the database will have to perform a full table scan for each query, which is highly inefficient as the tickets table grows.

Recommendation: Add indexes to these columns. This can be done declaratively in the entity for Hibernate to pick up during initial schema generation, but the best practice is to add them via a database migration script (Flyway/Liquibase).

Example Migration Script (Flyway):

-- V2__Add_indexes_to_tickets_table.sql

CREATE INDEX idx_tickets_status ON tickets(status);
CREATE INDEX idx_tickets_requester_email ON tickets(requester_email);
CREATE INDEX idx_tickets_assigned_to ON tickets(assigned_to);

This ensures that indexes are created explicitly and are part of the version-controlled schema history.