MODULE 7 - Databases and Transactions

Welcome to Module 7! 🗄️

You now know how to use Spring Data JPA to create repositories and execute queries. But do you know how Spring actually manages database connections? And more importantly, do you understand transactions?

This module reveals the essential mechanisms to manage your data in a reliable and professional manner.

---

🎓 Learning Objectives

By the end of this module, you will be able to:

1. ✅ Configure Spring Boot datasources (HikariCP)
2. ✅ Understand and use @Transactional correctly
3. ✅ Manage propagation and isolation levels
4. ✅ Version your DB schema with Flyway
5. ✅ Compare Flyway and Liquibase
6. ✅ Connect your application to different DBs (H2, PostgreSQL, MySQL)

---

📚 Prerequisites

Before starting this module, you should have:

• ✅ Completed Modules 1 to 6
• ✅ Created JPA entities with relationships
• ✅ Used Spring Data JPA repositories
• ✅ Understood the 3-layer architecture (Controller, Service, Repository)

If these prerequisites are not met, go back to the previous modules!

---

🗺️ Module Outline

Theoretical Part (4 chapters)

Chapter 1: Datasource Configuration

Duration: 45 minutes

Content:

• What is a Datasource?
• Configuration in application.properties/yml
• HikariCP: the default connection pool
• Advanced configuration (min/max connections, timeout)
• Multi-datasource configuration

Why important? Understand how Spring Boot manages connections

---

Chapter 2: Transaction Management (@Transactional)

Duration: 1 hour

Content:

• What is a transaction? (ACID)
• @Transactional explained in detail
• Propagation (REQUIRED, REQUIRES_NEW, etc.)
• Isolation levels
• Rollback and exception handling
• Common pitfalls to avoid

Why important? Ensure data integrity

---

Chapter 3: Schema Migration with Flyway

Duration: 45 minutes

Content:

• Why version the DB schema?
• Flyway installation and configuration
• Naming convention (V1__, V2__, etc.)
• SQL vs Java migration
• Rollback and repair
• Best practices

Why important? Deploy DB changes in production

---

Chapter 4: Migration with Liquibase

Duration: 30 minutes

Content:

• Flyway vs Liquibase: comparison
• XML, YAML, JSON format
• Changelog and changesets
• Automatic rollback
• When to choose Liquibase?

Why important? Know the alternatives

---

Practical Part (3 Labs)

Lab 7.1: Datasource and Pool Configuration

Duration: 1h

Objective: Configure and optimize DB connections

You will:

• Configure HikariCP for H2 and PostgreSQL
• Monitor the connection pool
• Test connection limits

---

Lab 7.2: Transactions and Rollback

Duration: 1h30

Objective: Master @Transactional

You will:

• Create transactional operations
• Simulate errors and observe rollbacks
• Test propagation (REQUIRED vs REQUIRES_NEW)
• Manage read-only transactions

---

Lab 7.3: Migration with Flyway

Duration: 1h30

Objective: Version the DB schema

You will:

• Configure Flyway
• Create migration scripts
• Simulate a schema evolution
• Manage initial data

---

📊 Total Duration

Type	Duration
Theory	3h (4 chapters)
Practice	4h (3 Labs)
Total	7h (~2-3 sessions)

---

🎯 What You Will Understand

Before this Module

// You wrote this without really understanding:
@Service
public class TransferService {
    
    @Transactional  // ← Why? What is it exactly?
    public void transfer(Long from, Long to, BigDecimal amount) {
        accountRepo.debit(from, amount);
        accountRepo.credit(to, amount);
    }
}

Unanswered questions:

• What happens if credit() fails after debit()?
• Why @Transactional and not just try/catch?
• How does Spring manage DB connections?
• How to deploy schema changes in production?

---

After this Module

@Service
public class TransferService {
    
    @Transactional(
        propagation = Propagation.REQUIRED,  // ← Reuse or create a transaction
        isolation = Isolation.READ_COMMITTED, // ← Isolation level
        rollbackFor = Exception.class         // ← Rollback on any exception
    )
    public void transfer(Long from, Long to, BigDecimal amount) {
        // If credit() fails, debit() is AUTOMATICALLY rolled back!
        accountRepo.debit(from, amount);
        accountRepo.credit(to, amount);
    }
}

You will know:

• ✅ How Spring manages connections (HikariCP pool)
• ✅ When and how to use @Transactional
• ✅ Avoid common pitfalls (proxy, self-invocation)
• ✅ Migrate schema with Flyway risk-free
• ✅ Choose between Flyway and Liquibase

---

🚀 Why This Module Is Crucial

1. Data Integrity

Catastrophic scenario (without transaction):
1. Debit account A: -100€  ✓
2. Credit account B: ERROR! ✗
3. Result: 100€ "disappeared" 💀

Transactions ensure: ALL or NOTHING.

---

2. Production Performance

Without connection pool:
- Each request = new connection = SLOW
- 1000 users = 1000 connections = OVERLOADED DB

With HikariCP (pool):
- Reused connections = FAST
- 1000 users = 10 connections = HAPPY DB 😊

---

3. Stress-Free Deployment

Without Flyway:
- "Did you run the SQL script?" 🤔
- "Which one? V3 or V4?" 😰
- "Oops, I forgot a column" 💀

With Flyway:
- Automatic migrations on startup ✓
- Tracked versioning ✓
- Peaceful deployment ✓

---

🗺️ Training Progression

Module 7 = Master the foundations of persistence!

---

📖 How to Approach This Module

Theory BEFORE Practice

1. Read the 4 theoretical chapters in order

   • Chapter 1: Datasources (foundations)
   • Chapter 2: Transactions (the most important!)
   • Chapter 3: Flyway (main migration)
   • Chapter 4: Liquibase (alternative)

2. Do the Labs in order

   • Lab 7.1: Configuration
   • Lab 7.2: Transactions
   • Lab 7.3: Flyway

---

Recommended Time

Intensive Option (1 day):

• Morning: Chapters 1 and 2 + Lab 7.1
• Afternoon: Lab 7.2 + Chapters 3 and 4
• Late afternoon: Lab 7.3

Progressive Option (2 sessions):

• Session 1: Chapters 1-2 + Lab 7.1 and Lab 7.2
• Session 2: Chapters 3-4 + Lab 7.3

---

✅ Self-Assessment

Before starting, answer:

1. Do you know what a datasource is? ☐ Yes ☐ No
2. Are you familiar with ACID properties? ☐ Yes ☐ No
3. Do you know what @Transactional does? ☐ Yes ☐ No
4. Have you ever used Flyway or Liquibase? ☐ Yes ☐ No

If you answered "No" to any question → This module is for you!

---

🎯 Next Step

Ready to master connections and transactions?

➡️ Start with Chapter 1: Datasource Configuration

---

⬅️ Back to Course Outline