Supabase Jdbc

Supabase JDBC Connection

Direct JDBC connection to Supabase PostgreSQL.

Use this skill when

• Connecting Java application to Supabase database
• Need JDBC connection string for Supabase
• Configuring connection pooling for Supabase
• User mentions "Supabase", "PostgreSQL connection", or "database setup"

Do not use this skill when

• User wants Supabase JS client (frontend)
• User needs Spring Boot specific config (use spring-supabase-integration)
• User wants to use Supabase REST API instead of direct DB

Getting Supabase Credentials

1. Go to Supabase Dashboard
2. Select your project
3. Go to Settings > Database
4. Copy connection info:
   • Host: db.[project-ref].supabase.co
   • Port: 5432 (or 6543 for connection pooler)
   • Database: postgres
   • User: postgres
   • Password: Your database password

Connection Modes

Direct Connection (Port 5432)

Best for: Long-running applications, migrations, admin tasks.

jdbc:postgresql://db.[project-ref].supabase.co:5432/postgres

Connection Pooler (Port 6543)

Best for: Serverless, many short-lived connections.

jdbc:postgresql://[project-ref].pooler.supabase.com:6543/postgres

Pooler Modes:

• Transaction mode (default): Connection returned after each transaction
• Session mode: Connection held for entire session

Dependencies

<!-- PostgreSQL JDBC Driver -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.1</version>
</dependency>

<!-- HikariCP Connection Pool -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
</dependency>

Environment Variables

# .env - DO NOT COMMIT
SUPABASE_HOST=db.xxxxx.supabase.co
SUPABASE_PORT=5432
SUPABASE_DB=postgres
SUPABASE_USER=postgres
SUPABASE_PASSWORD=your-password-here

# For connection pooler (optional)
SUPABASE_POOLER_HOST=xxxxx.pooler.supabase.com
SUPABASE_POOLER_PORT=6543

Basic JDBC Connection

public class SupabaseConnection {

    private static final String HOST = System.getenv("SUPABASE_HOST");
    private static final String PORT = System.getenv("SUPABASE_PORT");
    private static final String DATABASE = System.getenv("SUPABASE_DB");
    private static final String USER = System.getenv("SUPABASE_USER");
    private static final String PASSWORD = System.getenv("SUPABASE_PASSWORD");

    public static Connection getConnection() throws SQLException {
        String url = String.format(
            "jdbc:postgresql://%s:%s/%s?sslmode=require",
            HOST, PORT, DATABASE
        );
        
        Properties props = new Properties();
        props.setProperty("user", USER);
        props.setProperty("password", PASSWORD);
        props.setProperty("ssl", "true");
        props.setProperty("sslmode", "require");
        
        return DriverManager.getConnection(url, props);
    }
}

HikariCP Connection Pool

public class SupabaseDataSource {

    private static HikariDataSource dataSource;

    public static synchronized DataSource getDataSource() {
        if (dataSource == null) {
            HikariConfig config = new HikariConfig();
            
            // Connection URL
            String url = String.format(
                "jdbc:postgresql://%s:%s/%s",
                System.getenv("SUPABASE_HOST"),
                System.getenv("SUPABASE_PORT"),
                System.getenv("SUPABASE_DB")
            );
            config.setJdbcUrl(url);
            config.setUsername(System.getenv("SUPABASE_USER"));
            config.setPassword(System.getenv("SUPABASE_PASSWORD"));
            
            // SSL Configuration (required for Supabase)
            config.addDataSourceProperty("ssl", "true");
            config.addDataSourceProperty("sslmode", "require");
            
            // Pool Configuration
            config.setMaximumPoolSize(10);
            config.setMinimumIdle(2);
            config.setConnectionTimeout(30000);     // 30 seconds
            config.setIdleTimeout(600000);          // 10 minutes
            config.setMaxLifetime(1800000);         // 30 minutes
            
            // Recommended settings
            config.setPoolName("SupabasePool");
            config.setAutoCommit(true);
            
            // Connection test query
            config.setConnectionTestQuery("SELECT 1");
            
            dataSource = new HikariDataSource(config);
        }
        return dataSource;
    }

    public static void close() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
        }
    }
}

Usage Example

public class UserRepository {

    private final DataSource dataSource;

    public UserRepository() {
        this.dataSource = SupabaseDataSource.getDataSource();
    }

    public Optional<User> findById(UUID id) {
        String sql = "SELECT id, name, email, created_at FROM users WHERE id = ?";
        
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setObject(1, id);
            
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapRow(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("Error finding user", e);
        }
        
        return Optional.empty();
    }

    public List<User> findAll() {
        String sql = "SELECT id, name, email, created_at FROM users ORDER BY created_at DESC";
        List<User> users = new ArrayList<>();
        
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                users.add(mapRow(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException("Error listing users", e);
        }
        
        return users;
    }

    public User save(User user) {
        String sql = """
            INSERT INTO users (id, name, email, created_at)
            VALUES (?, ?, ?, ?)
            ON CONFLICT (id) DO UPDATE SET
                name = EXCLUDED.name,
                email = EXCLUDED.email
            RETURNING *
            """;
        
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            UUID id = user.id() != null ? user.id() : UUID.randomUUID();
            stmt.setObject(1, id);
            stmt.setString(2, user.name());
            stmt.setString(3, user.email());
            stmt.setTimestamp(4, Timestamp.valueOf(LocalDateTime.now()));
            
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return mapRow(rs);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("Error saving user", e);
        }
        
        throw new RuntimeException("Failed to save user");
    }

    public void delete(UUID id) {
        String sql = "DELETE FROM users WHERE id = ?";
        
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setObject(1, id);
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("Error deleting user", e);
        }
    }

    private User mapRow(ResultSet rs) throws SQLException {
        return new User(
            rs.getObject("id", UUID.class),
            rs.getString("name"),
            rs.getString("email"),
            rs.getTimestamp("created_at").toLocalDateTime()
        );
    }
}

public record User(UUID id, String name, String email, LocalDateTime createdAt) {}

SSL Configuration Options

// Option 1: sslmode=require (recommended for Supabase)
// Encrypts connection, doesn't verify certificate
config.addDataSourceProperty("sslmode", "require");

// Option 2: sslmode=verify-full (maximum security)
// Requires CA certificate
config.addDataSourceProperty("sslmode", "verify-full");
config.addDataSourceProperty("sslrootcert", "/path/to/ca-certificate.crt");

// Option 3: For development/testing only
config.addDataSourceProperty("sslmode", "prefer");

Troubleshooting

Connection Refused

Check:
1. IP address is in Supabase allowed list (Database Settings)
2. Correct host/port
3. Firewall allows outbound port 5432/6543

SSL Handshake Failed

Add to connection properties:
config.addDataSourceProperty("sslfactory", "org.postgresql.ssl.NonValidatingFactory");

Too Many Connections

1. Use connection pooler (port 6543)
2. Reduce pool size
3. Check for connection leaks (connections not closed)

Authentication Failed

1. Verify password is correct
2. Check user has access to database
3. Ensure using correct user (postgres for direct, postgres.user for pooler)

Code Quality Checklist

• Using environment variables for credentials
• SSL enabled (sslmode=require minimum)
• Connection pool configured
• Connections properly closed (try-with-resources)
• Prepared statements for SQL injection prevention
• .env file in .gitignore

References

• See references/connection-string-formats.md
• See examples/ for complete examples