Spring Supabase Integration

Spring Boot + Supabase Integration

Complete Spring Boot configuration for Supabase PostgreSQL.

Use this skill when

• Configuring Spring Boot to use Supabase database
• Setting up JPA/Hibernate with Supabase
• Need production-ready database configuration
• User mentions "Spring Boot with Supabase"

Dependencies

<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>

<!-- Optional: Load .env files -->
<dependency>
    <groupId>me.paulschwarz</groupId>
    <artifactId>spring-dotenv</artifactId>
    <version>4.0.0</version>
</dependency>

Configuration

application.yml

spring:
  datasource:
    url: jdbc:postgresql://${SUPABASE_HOST}:${SUPABASE_PORT:5432}/${SUPABASE_DB:postgres}?sslmode=require
    username: ${SUPABASE_USER:postgres}
    password: ${SUPABASE_PASSWORD}
    driver-class-name: org.postgresql.Driver
    
    # HikariCP settings
    hikari:
      pool-name: SupabaseHikariPool
      maximum-pool-size: ${DB_POOL_SIZE:10}
      minimum-idle: ${DB_POOL_MIN_IDLE:2}
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      leak-detection-threshold: 60000
      
      # Connection test
      connection-test-query: SELECT 1

  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    hibernate:
      ddl-auto: ${JPA_DDL_AUTO:validate}
    show-sql: ${JPA_SHOW_SQL:false}
    open-in-view: false
    properties:
      hibernate:
        format_sql: true
        jdbc:
          time_zone: UTC
          batch_size: 20
        order_inserts: true
        order_updates: true
        default_batch_fetch_size: 20

application-dev.yml

spring:
  datasource:
    hikari:
      maximum-pool-size: 5
      minimum-idle: 1
      
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
    properties:
      hibernate:
        format_sql: true

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.type.descriptor.sql: TRACE

application-prod.yml

spring:
  datasource:
    # Use connection pooler for production
    url: jdbc:postgresql://${SUPABASE_POOLER_HOST}:${SUPABASE_POOLER_PORT:6543}/${SUPABASE_DB:postgres}?sslmode=require
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      
  jpa:
    hibernate:
      ddl-auto: validate
    show-sql: false

Environment Variables (.env)

# Direct connection
SUPABASE_HOST=db.xxxxx.supabase.co
SUPABASE_PORT=5432
SUPABASE_DB=postgres
SUPABASE_USER=postgres
SUPABASE_PASSWORD=your-password

# Connection pooler (for production)
SUPABASE_POOLER_HOST=xxxxx.pooler.supabase.com
SUPABASE_POOLER_PORT=6543

# JPA settings
JPA_DDL_AUTO=validate
JPA_SHOW_SQL=false

# Pool settings
DB_POOL_SIZE=10
DB_POOL_MIN_IDLE=2

JPA Configuration

@Configuration
@EnableJpaAuditing
@EnableJpaRepositories(basePackages = "com.example.app.repository")
public class JpaConfig {

    @Bean
    public AuditorAware<String> auditorProvider() {
        return () -> Optional.ofNullable(SecurityContextHolder.getContext())
            .map(SecurityContext::getAuthentication)
            .filter(Authentication::isAuthenticated)
            .map(Authentication::getName);
    }
}

Health Check

Spring Boot Actuator automatically adds database health check:

management:
  endpoints:
    web:
      exposure:
        include: health,info
  endpoint:
    health:
      show-details: when_authorized
      probes:
        enabled: true
  health:
    db:
      enabled: true

Response at /actuator/health:

{
  "status": "UP",
  "components": {
    "db": {
      "status": "UP",
      "details": {
        "database": "PostgreSQL",
        "validationQuery": "isValid()"
      }
    }
  }
}

Custom Health Indicator

@Component
public class SupabaseHealthIndicator implements HealthIndicator {

    private final DataSource dataSource;

    public SupabaseHealthIndicator(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public Health health() {
        try (Connection conn = dataSource.getConnection()) {
            if (conn.isValid(5)) {
                return Health.up()
                    .withDetail("database", "Supabase PostgreSQL")
                    .withDetail("connection", "valid")
                    .build();
            }
        } catch (SQLException e) {
            return Health.down()
                .withDetail("error", e.getMessage())
                .build();
        }
        return Health.unknown().build();
    }
}

Connection Pool Metrics

@Configuration
public class MetricsConfig {

    @Bean
    public MeterBinder hikariMetrics(DataSource dataSource) {
        if (dataSource instanceof HikariDataSource hikari) {
            return new HikariCPMetrics(hikari.getHikariPoolMXBean());
        }
        return registry -> {};
    }
}

Metrics available at /actuator/metrics:

• hikaricp.connections.active
• hikaricp.connections.idle
• hikaricp.connections.pending
• hikaricp.connections.timeout

Database Migrations with Flyway

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

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

Migration files in src/main/resources/db/migration/:

-- V1__create_users_table.sql
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

Row Level Security (RLS)

If using Supabase RLS, configure the connection to set the user context:

@Component
@RequiredArgsConstructor
public class SupabaseRlsInterceptor implements HandlerInterceptor {

    private final DataSource dataSource;

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) {
        Authentication auth = SecurityContextHolder.getContext().getAuthentication();
        if (auth != null && auth.isAuthenticated()) {
            String userId = auth.getName();
            try (Connection conn = dataSource.getConnection();
                 Statement stmt = conn.createStatement()) {
                stmt.execute("SET app.current_user_id = '" + userId + "'");
            } catch (SQLException e) {
                // Handle error
            }
        }
        return true;
    }
}

RLS Policy example in Supabase:

CREATE POLICY "Users can only see their own data"
ON users
FOR SELECT
USING (id::text = current_setting('app.current_user_id', true));

Testing with Testcontainers

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

@SpringBootTest
@Testcontainers
class UserRepositoryTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:15")
        .withDatabaseName("testdb")
        .withUsername("test")
        .withPassword("test");

    @DynamicPropertySource
    static void configureProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }

    @Autowired
    private UserRepository userRepository;

    @Test
    void shouldSaveUser() {
        User user = User.builder()
            .name("Test User")
            .email("test@example.com")
            .build();
        
        User saved = userRepository.save(user);
        
        assertThat(saved.getId()).isNotNull();
    }
}

Troubleshooting

Connection Issues

// Add this to debug connection issues
@EventListener(ApplicationReadyEvent.class)
public void testConnection(ApplicationReadyEvent event) {
    try (Connection conn = dataSource.getConnection()) {
        log.info("Database connection successful: {}", conn.getCatalog());
    } catch (SQLException e) {
        log.error("Database connection failed: {}", e.getMessage());
    }
}

Pool Exhaustion

# Increase pool size or reduce connection timeout
spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      connection-timeout: 10000
      leak-detection-threshold: 30000

Code Quality Checklist

• Environment variables for all credentials
• Different configs for dev/prod profiles
• Connection pooler for production
• JPA auditing enabled
• Health check configured
• Flyway for migrations
• Tests use Testcontainers
• open-in-view disabled

References

• See references/troubleshooting.md for common issues
• See examples/ for complete examples