Library

jOOQ (Kotlin)

Overview

jOOQ (Java Object Oriented Querying) is "a type-safe SQL query builder with an SQL-first philosophy" that can be effectively utilized in Kotlin for database access. With the "SQL-first" philosophy, it provides compile-time type safety without compromising SQL's expressiveness. By automatically generating Kotlin code from database schemas, it enables IDE auto-completion and compile-time checks that significantly reduce runtime SQL errors. Complex JOINs, window functions, aggregate queries, and all SQL features can be written in Kotlin's type-safe DSL, achieving both reliability and productivity in enterprise-level database application development.

Details

jOOQ 2025 edition is optimized for modern Kotlin application development with Kotlin Multiplatform support and Coroutines integration. It automatically detects database schema changes and regenerates corresponding Kotlin code, maintaining consistency between schema and code at all times. Comprehensive support for major database dialects and proprietary features including PostgreSQL, MySQL, Oracle, SQL Server, H2, allowing type-safe utilization of vendor-specific SQL functions. Provides comprehensive features necessary for enterprise applications including batch processing, transaction management, connection pool integration, and result set streaming.

Key Features

• SQL-First Philosophy: Type-safe query construction maximizing SQL's expressiveness
• Schema Synchronization: Automatic Kotlin code generation from database schemas
• Complete Type Safety: Compile-time SQL syntax and type checking
• Database Dialect Support: Full support for major database proprietary features
• Kotlin Integration: Utilization of Coroutines, extension functions, and type inference
• Enterprise Features: Batch processing, transactions, and performance optimization

Pros and Cons

Pros

• Advanced query description capabilities without compromising SQL's expressiveness
• Significant reduction of runtime errors through compile-time type checking
• Automatic synchronization between database schema and code
• Powerful IDE auto-completion and refactoring support
• Ability to express complex business logic within SQL
• Rich database dialect support and portability

Cons

• High learning curve requiring knowledge of both SQL and jOOQ
• Code generation step required, complicating build process
• Excessive overhead for simple CRUD operations in early development
• Generated code is voluminous, increasing project size
• License fees may be required (when using commercial databases)
• Learning barrier for developers accustomed to Active Record pattern

Reference Pages

• jOOQ Official Site
• jOOQ GitHub Repository
• jOOQ Kotlin Documentation

Code Examples

Setup

// build.gradle.kts
plugins {
    kotlin("jvm") version "1.9.22"
    id("nu.studer.jooq") version "8.2.1"
}

dependencies {
    implementation("org.jooq:jooq:3.18.7")
    implementation("org.jooq:jooq-kotlin:3.18.7")
    implementation("org.jooq:jooq-kotlin-coroutines:3.18.7")
    
    // Database drivers
    implementation("org.postgresql:postgresql:42.7.1")
    // or implementation("mysql:mysql-connector-java:8.0.33")
    
    // Connection pool
    implementation("com.zaxxer:HikariCP:5.1.0")
    
    // For jOOQ code generation
    jooqGenerator("org.postgresql:postgresql:42.7.1")
}

// jOOQ configuration
jooq {
    configurations {
        create("main") {
            generateSchemaSourceOnCompilation.set(true)
            
            generator.apply {
                name = "org.jooq.codegen.KotlinGenerator"
                
                database.apply {
                    name = "org.jooq.meta.postgres.PostgresDatabase"
                    inputSchema = "public"
                    
                    // Database connection settings
                    properties.add(
                        org.jooq.meta.jaxb.Property().apply {
                            key = "dialect"
                            value = "POSTGRES"
                        }
                    )
                }
                
                target.apply {
                    packageName = "com.example.jooq.generated"
                    directory = "src/main/kotlin"
                }
                
                generate.apply {
                    isRecords = true
                    isPojos = true
                    isImmutablePojos = true
                    isFluentSetters = true
                    isKotlinSetterJvmNameAnnotationsOnIsPrefix = true
                }
            }
        }
    }
}

// Database setup (application.conf)
/*
database {
  driver = "org.postgresql.Driver"
  url = "jdbc:postgresql://localhost:5432/mydb"
  user = "postgres"
  password = "password"
  connectionPool {
    maximumPoolSize = 10
    minimumIdle = 5
    connectionTimeout = 30000
  }
}
*/

-- Sample schema for code generation
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INTEGER NOT NULL CHECK (age >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_published ON posts(published);

Basic Usage

import org.jooq.*
import org.jooq.impl.DSL
import org.jooq.kotlin.*
import kotlinx.coroutines.*
import com.example.jooq.generated.Tables.*
import com.example.jooq.generated.tables.records.*
import java.time.LocalDateTime

// Database connection setup
class DatabaseConfig {
    companion object {
        fun createDSLContext(): DSLContext {
            val dataSource = HikariDataSource().apply {
                jdbcUrl = "jdbc:postgresql://localhost:5432/mydb"
                username = "postgres"
                password = "password"
                maximumPoolSize = 10
                minimumIdle = 5
            }
            
            return DSL.using(dataSource, SQLDialect.POSTGRES)
        }
    }
}

// Basic CRUD operations
class UserRepository(private val create: DSLContext) {
    
    // Insert user
    suspend fun createUser(name: String, email: String, age: Int): UsersRecord? = withContext(Dispatchers.IO) {
        create.insertInto(USERS)
            .set(USERS.NAME, name)
            .set(USERS.EMAIL, email)
            .set(USERS.AGE, age)
            .set(USERS.CREATED_AT, LocalDateTime.now())
            .returning()
            .fetchOne()
    }
    
    // Get user by ID
    suspend fun getUserById(id: Long): UsersRecord? = withContext(Dispatchers.IO) {
        create.selectFrom(USERS)
            .where(USERS.ID.eq(id))
            .fetchOne()
    }
    
    // Get all users
    suspend fun getAllUsers(): List<UsersRecord> = withContext(Dispatchers.IO) {
        create.selectFrom(USERS)
            .orderBy(USERS.CREATED_AT.desc())
            .fetch()
    }
    
    // Search users by name
    suspend fun searchUsersByName(namePattern: String): List<UsersRecord> = withContext(Dispatchers.IO) {
        create.selectFrom(USERS)
            .where(USERS.NAME.likeIgnoreCase("%$namePattern%"))
            .orderBy(USERS.NAME)
            .fetch()
    }
    
    // Update user
    suspend fun updateUser(id: Long, name: String, email: String, age: Int): Int = withContext(Dispatchers.IO) {
        create.update(USERS)
            .set(USERS.NAME, name)
            .set(USERS.EMAIL, email)
            .set(USERS.AGE, age)
            .set(USERS.UPDATED_AT, LocalDateTime.now())
            .where(USERS.ID.eq(id))
            .execute()
    }
    
    // Delete user
    suspend fun deleteUser(id: Long): Int = withContext(Dispatchers.IO) {
        create.deleteFrom(USERS)
            .where(USERS.ID.eq(id))
            .execute()
    }
    
    // Get users by age range
    suspend fun getUsersByAgeRange(minAge: Int, maxAge: Int): List<UsersRecord> = withContext(Dispatchers.IO) {
        create.selectFrom(USERS)
            .where(USERS.AGE.between(minAge, maxAge))
            .orderBy(USERS.AGE)
            .fetch()
    }
    
    // Get user count
    suspend fun getUserCount(): Int = withContext(Dispatchers.IO) {
        create.selectCount()
            .from(USERS)
            .fetchOne(0, Int::class.java) ?: 0
    }
}

// Usage example
suspend fun demonstrateBasicOperations() {
    val create = DatabaseConfig.createDSLContext()
    val userRepo = UserRepository(create)
    
    // Create users
    val user1 = userRepo.createUser("Alice Johnson", "[email protected]", 28)
    val user2 = userRepo.createUser("Bob Smith", "[email protected]", 32)
    val user3 = userRepo.createUser("Charlie Brown", "[email protected]", 25)
    
    println("Created users: ${listOfNotNull(user1, user2, user3)}")
    
    // Search users
    val searchResults = userRepo.searchUsersByName("Alice")
    println("Search results for 'Alice': $searchResults")
    
    // Get users by age range
    val youngUsers = userRepo.getUsersByAgeRange(25, 30)
    println("Users aged 25-30: $youngUsers")
    
    // Update user
    user1?.let { user ->
        val updateCount = userRepo.updateUser(user.id, "Alice Johnson Smith", "[email protected]", 29)
        println("Updated $updateCount user(s)")
    }
    
    // Get all users
    val allUsers = userRepo.getAllUsers()
    println("All users: $allUsers")
}

Complex Queries and Joins

import org.jooq.impl.DSL.*

class PostRepository(private val create: DSLContext) {
    
    // Create post
    suspend fun createPost(title: String, content: String, userId: Long, published: Boolean = false): PostsRecord? = 
        withContext(Dispatchers.IO) {
            create.insertInto(POSTS)
                .set(POSTS.TITLE, title)
                .set(POSTS.CONTENT, content)
                .set(POSTS.USER_ID, userId)
                .set(POSTS.PUBLISHED, published)
                .set(POSTS.CREATED_AT, LocalDateTime.now())
                .returning()
                .fetchOne()
        }
    
    // Get posts with authors (JOIN)
    suspend fun getPostsWithAuthors(): List<Pair<PostsRecord, UsersRecord>> = withContext(Dispatchers.IO) {
        create.select()
            .from(POSTS)
            .join(USERS).on(POSTS.USER_ID.eq(USERS.ID))
            .orderBy(POSTS.CREATED_AT.desc())
            .fetch { record ->
                Pair(
                    record.into(POSTS),
                    record.into(USERS)
                )
            }
    }
    
    // Get published posts only
    suspend fun getPublishedPosts(): List<PostsRecord> = withContext(Dispatchers.IO) {
        create.selectFrom(POSTS)
            .where(POSTS.PUBLISHED.eq(true))
            .orderBy(POSTS.CREATED_AT.desc())
            .fetch()
    }
    
    // Get user post count using aggregation
    suspend fun getUserPostCounts(): List<Pair<String, Int>> = withContext(Dispatchers.IO) {
        create.select(USERS.NAME, count(POSTS.ID))
            .from(USERS)
            .leftJoin(POSTS).on(USERS.ID.eq(POSTS.USER_ID))
            .groupBy(USERS.ID, USERS.NAME)
            .orderBy(count(POSTS.ID).desc())
            .fetch { record ->
                Pair(
                    record.get(USERS.NAME),
                    record.get(count(POSTS.ID))
                )
            }
    }
    
    // Complex WHERE clause with multiple conditions
    suspend fun searchPosts(
        titlePattern: String? = null,
        userId: Long? = null,
        publishedOnly: Boolean = false,
        fromDate: LocalDateTime? = null,
        toDate: LocalDateTime? = null
    ): List<PostsRecord> = withContext(Dispatchers.IO) {
        var query = create.selectFrom(POSTS)
        
        // Dynamic WHERE conditions
        val conditions = mutableListOf<Condition>()
        
        titlePattern?.let { pattern ->
            conditions.add(POSTS.TITLE.likeIgnoreCase("%$pattern%"))
        }
        
        userId?.let { id ->
            conditions.add(POSTS.USER_ID.eq(id))
        }
        
        if (publishedOnly) {
            conditions.add(POSTS.PUBLISHED.eq(true))
        }
        
        fromDate?.let { date ->
            conditions.add(POSTS.CREATED_AT.ge(date))
        }
        
        toDate?.let { date ->
            conditions.add(POSTS.CREATED_AT.le(date))
        }
        
        if (conditions.isNotEmpty()) {
            query = query.where(and(conditions))
        }
        
        query.orderBy(POSTS.CREATED_AT.desc()).fetch()
    }
    
    // Window functions example: Ranking posts by user
    suspend fun getPostRankingsByUser(): List<PostRankingResult> = withContext(Dispatchers.IO) {
        create.select(
            POSTS.ID,
            POSTS.TITLE,
            USERS.NAME,
            POSTS.CREATED_AT,
            rowNumber().over(
                partitionBy(POSTS.USER_ID)
                    .orderBy(POSTS.CREATED_AT.desc())
            ).`as`("post_rank")
        )
        .from(POSTS)
        .join(USERS).on(POSTS.USER_ID.eq(USERS.ID))
        .orderBy(USERS.NAME, field("post_rank"))
        .fetch { record ->
            PostRankingResult(
                postId = record.get(POSTS.ID),
                title = record.get(POSTS.TITLE),
                authorName = record.get(USERS.NAME),
                createdAt = record.get(POSTS.CREATED_AT),
                rank = record.get("post_rank", Int::class.java)
            )
        }
    }
    
    // Subquery example: Users with most posts
    suspend fun getUsersWithMostPosts(limit: Int = 5): List<Pair<UsersRecord, Int>> = withContext(Dispatchers.IO) {
        val postCountSubquery = create.select(
            POSTS.USER_ID,
            count(POSTS.ID).`as`("post_count")
        )
        .from(POSTS)
        .groupBy(POSTS.USER_ID)
        .asTable("user_post_counts")
        
        create.select(USERS.asterisk(), postCountSubquery.field("post_count"))
            .from(USERS)
            .join(postCountSubquery).on(USERS.ID.eq(postCountSubquery.field(POSTS.USER_ID)))
            .orderBy(postCountSubquery.field("post_count").desc())
            .limit(limit)
            .fetch { record ->
                Pair(
                    record.into(USERS),
                    record.get("post_count", Int::class.java)
                )
            }
    }
}

// Data class for complex query results
data class PostRankingResult(
    val postId: Long,
    val title: String,
    val authorName: String,
    val createdAt: LocalDateTime,
    val rank: Int
)

// Usage example for complex queries
suspend fun demonstrateComplexQueries() {
    val create = DatabaseConfig.createDSLContext()
    val postRepo = PostRepository(create)
    val userRepo = UserRepository(create)
    
    // Create some sample data
    val user = userRepo.createUser("John Doe", "[email protected]", 30)
    user?.let { u ->
        postRepo.createPost("First Post", "This is my first post!", u.id, true)
        postRepo.createPost("Second Post", "Another interesting post", u.id, false)
        postRepo.createPost("Published Post", "This is published", u.id, true)
    }
    
    // Complex search
    val searchResults = postRepo.searchPosts(
        titlePattern = "Post",
        publishedOnly = true,
        fromDate = LocalDateTime.now().minusDays(7)
    )
    println("Search results: $searchResults")
    
    // Get posts with authors
    val postsWithAuthors = postRepo.getPostsWithAuthors()
    println("Posts with authors: $postsWithAuthors")
    
    // User post counts
    val userPostCounts = postRepo.getUserPostCounts()
    println("User post counts: $userPostCounts")
    
    // Post rankings by user
    val rankings = postRepo.getPostRankingsByUser()
    println("Post rankings: $rankings")
    
    // Users with most posts
    val topUsers = postRepo.getUsersWithMostPosts(3)
    println("Top users by post count: $topUsers")
}

Transactions and Batch Operations

import org.jooq.TransactionProvider
import org.jooq.ConnectionProvider

class UserService(private val create: DSLContext) {
    
    // Transaction example
    suspend fun transferPostsBetweenUsers(fromUserId: Long, toUserId: Long): Boolean = withContext(Dispatchers.IO) {
        try {
            create.transaction { config ->
                val ctx = DSL.using(config)
                
                // Check if both users exist
                val fromUser = ctx.selectFrom(USERS).where(USERS.ID.eq(fromUserId)).fetchOne()
                val toUser = ctx.selectFrom(USERS).where(USERS.ID.eq(toUserId)).fetchOne()
                
                if (fromUser == null || toUser == null) {
                    throw IllegalArgumentException("One or both users not found")
                }
                
                // Transfer all posts
                val transferCount = ctx.update(POSTS)
                    .set(POSTS.USER_ID, toUserId)
                    .set(POSTS.UPDATED_AT, LocalDateTime.now())
                    .where(POSTS.USER_ID.eq(fromUserId))
                    .execute()
                
                println("Transferred $transferCount posts from user $fromUserId to $toUserId")
            }
            true
        } catch (e: Exception) {
            println("Transaction failed: ${e.message}")
            false
        }
    }
    
    // Batch insert
    suspend fun batchCreateUsers(usersData: List<Triple<String, String, Int>>): List<UsersRecord> = 
        withContext(Dispatchers.IO) {
            val insertQuery = create.insertInto(USERS, USERS.NAME, USERS.EMAIL, USERS.AGE, USERS.CREATED_AT)
            
            usersData.forEach { (name, email, age) ->
                insertQuery.values(name, email, age, LocalDateTime.now())
            }
            
            insertQuery.returning().fetch()
        }
    
    // Batch update
    suspend fun batchUpdateUserAges(updates: List<Pair<Long, Int>>): Int = withContext(Dispatchers.IO) {
        create.transaction { config ->
            val ctx = DSL.using(config)
            var totalUpdated = 0
            
            updates.forEach { (userId, newAge) ->
                val updated = ctx.update(USERS)
                    .set(USERS.AGE, newAge)
                    .set(USERS.UPDATED_AT, LocalDateTime.now())
                    .where(USERS.ID.eq(userId))
                    .execute()
                totalUpdated += updated
            }
            
            totalUpdated
        }
    }
    
    // Complex transaction: Create user with initial posts
    suspend fun createUserWithPosts(
        userName: String,
        userEmail: String,
        userAge: Int,
        initialPosts: List<Pair<String, String>>
    ): Pair<UsersRecord?, List<PostsRecord>> = withContext(Dispatchers.IO) {
        create.transactionResult { config ->
            val ctx = DSL.using(config)
            
            // Create user
            val user = ctx.insertInto(USERS)
                .set(USERS.NAME, userName)
                .set(USERS.EMAIL, userEmail)
                .set(USERS.AGE, userAge)
                .set(USERS.CREATED_AT, LocalDateTime.now())
                .returning()
                .fetchOne()
                
            if (user == null) {
                throw RuntimeException("Failed to create user")
            }
            
            // Create initial posts
            val posts = mutableListOf<PostsRecord>()
            initialPosts.forEach { (title, content) ->
                val post = ctx.insertInto(POSTS)
                    .set(POSTS.TITLE, title)
                    .set(POSTS.CONTENT, content)
                    .set(POSTS.USER_ID, user.id)
                    .set(POSTS.PUBLISHED, false)
                    .set(POSTS.CREATED_AT, LocalDateTime.now())
                    .returning()
                    .fetchOne()
                
                post?.let { posts.add(it) }
            }
            
            Pair(user, posts)
        }
    }
    
    // Conditional batch operations
    suspend fun conditionalUserCleanup(inactiveDays: Int): CleanupResult = withContext(Dispatchers.IO) {
        create.transactionResult { config ->
            val ctx = DSL.using(config)
            val cutoffDate = LocalDateTime.now().minusDays(inactiveDays.toLong())
            
            // Find inactive users (no posts in the last N days)
            val inactiveUserIds = ctx.select(USERS.ID)
                .from(USERS)
                .whereNotExists(
                    selectOne()
                        .from(POSTS)
                        .where(POSTS.USER_ID.eq(USERS.ID))
                        .and(POSTS.CREATED_AT.gt(cutoffDate))
                )
                .fetch(USERS.ID)
            
            // Count posts to be deleted
            val postsToDelete = ctx.selectCount()
                .from(POSTS)
                .where(POSTS.USER_ID.`in`(inactiveUserIds))
                .fetchOne(0, Int::class.java) ?: 0
            
            // Delete posts first (due to foreign key constraints)
            val deletedPosts = ctx.deleteFrom(POSTS)
                .where(POSTS.USER_ID.`in`(inactiveUserIds))
                .execute()
            
            // Delete users
            val deletedUsers = ctx.deleteFrom(USERS)
                .where(USERS.ID.`in`(inactiveUserIds))
                .execute()
            
            CleanupResult(
                deletedUsers = deletedUsers,
                deletedPosts = deletedPosts,
                inactiveUserIds = inactiveUserIds
            )
        }
    }
}

// Data class for cleanup results
data class CleanupResult(
    val deletedUsers: Int,
    val deletedPosts: Int,
    val inactiveUserIds: List<Long>
)

// Usage example for transactions and batch operations
suspend fun demonstrateTransactionsAndBatch() {
    val create = DatabaseConfig.createDSLContext()
    val userService = UserService(create)
    
    // Batch user creation
    val newUsersData = listOf(
        Triple("Emma Wilson", "[email protected]", 26),
        Triple("David Chen", "[email protected]", 31),
        Triple("Sarah Johnson", "[email protected]", 29)
    )
    
    val batchCreatedUsers = userService.batchCreateUsers(newUsersData)
    println("Batch created users: $batchCreatedUsers")
    
    // Create user with initial posts
    val (userWithPosts, initialPosts) = userService.createUserWithPosts(
        userName = "Content Creator",
        userEmail = "[email protected]",
        userAge = 35,
        initialPosts = listOf(
            "Welcome Post" to "Hello, this is my first post!",
            "About Me" to "Let me tell you about myself...",
            "Getting Started" to "Here's how to get started with this platform."
        )
    )
    
    println("Created user with posts: $userWithPosts")
    println("Initial posts: $initialPosts")
    
    // Batch age updates
    val ageUpdates = batchCreatedUsers.map { user ->
        Pair(user.id, user.age + 1) // Age everyone by 1 year
    }
    val updatedCount = userService.batchUpdateUserAges(ageUpdates)
    println("Updated ages for $updatedCount users")
    
    // Cleanup inactive users (example with 30 days)
    val cleanupResult = userService.conditionalUserCleanup(30)
    println("Cleanup result: $cleanupResult")
}

Error Handling

// Custom exception classes
sealed class DatabaseException(message: String, cause: Throwable? = null) : Exception(message, cause) {
    class UserNotFoundException(userId: Long) : DatabaseException("User not found: $userId")
    class DuplicateEmailException(email: String) : DatabaseException("Email already exists: $email")
    class InvalidDataException(message: String) : DatabaseException("Invalid data: $message")
    class DatabaseConnectionException(cause: Throwable) : DatabaseException("Database connection failed", cause)
    class TransactionException(message: String, cause: Throwable? = null) : DatabaseException("Transaction failed: $message", cause)
}

// Result wrapper for safe operations
sealed class DatabaseResult<out T> {
    data class Success<T>(val data: T) : DatabaseResult<T>()
    data class Error(val exception: DatabaseException) : DatabaseResult<Nothing>()
    
    inline fun <R> map(transform: (T) -> R): DatabaseResult<R> = when (this) {
        is Success -> Success(transform(data))
        is Error -> this
    }
    
    inline fun <R> flatMap(transform: (T) -> DatabaseResult<R>): DatabaseResult<R> = when (this) {
        is Success -> transform(data)
        is Error -> this
    }
    
    fun getOrNull(): T? = when (this) {
        is Success -> data
        is Error -> null
    }
}

class SafeUserRepository(private val create: DSLContext) {
    
    // Safe user creation with validation
    suspend fun createUserSafely(name: String, email: String, age: Int): DatabaseResult<UsersRecord> = 
        withContext(Dispatchers.IO) {
            try {
                // Input validation
                if (name.isBlank()) {
                    return@withContext DatabaseResult.Error(DatabaseException.InvalidDataException("Name cannot be empty"))
                }
                if (!email.contains("@") || email.length < 5) {
                    return@withContext DatabaseResult.Error(DatabaseException.InvalidDataException("Invalid email format"))
                }
                if (age < 0 || age > 150) {
                    return@withContext DatabaseResult.Error(DatabaseException.InvalidDataException("Age must be between 0 and 150"))
                }
                
                // Check for duplicate email
                val existingUser = create.selectFrom(USERS)
                    .where(USERS.EMAIL.eq(email))
                    .fetchOne()
                
                if (existingUser != null) {
                    return@withContext DatabaseResult.Error(DatabaseException.DuplicateEmailException(email))
                }
                
                // Create user
                val user = create.insertInto(USERS)
                    .set(USERS.NAME, name)
                    .set(USERS.EMAIL, email)
                    .set(USERS.AGE, age)
                    .set(USERS.CREATED_AT, LocalDateTime.now())
                    .returning()
                    .fetchOne()
                
                if (user != null) {
                    DatabaseResult.Success(user)
                } else {
                    DatabaseResult.Error(DatabaseException.TransactionException("Failed to create user"))
                }
                
            } catch (e: Exception) {
                when (e) {
                    is DatabaseException -> DatabaseResult.Error(e)
                    else -> DatabaseResult.Error(DatabaseException.DatabaseConnectionException(e))
                }
            }
        }
    
    // Safe user retrieval
    suspend fun getUserSafely(userId: Long): DatabaseResult<UsersRecord> = withContext(Dispatchers.IO) {
        try {
            val user = create.selectFrom(USERS)
                .where(USERS.ID.eq(userId))
                .fetchOne()
            
            if (user != null) {
                DatabaseResult.Success(user)
            } else {
                DatabaseResult.Error(DatabaseException.UserNotFoundException(userId))
            }
        } catch (e: Exception) {
            DatabaseResult.Error(DatabaseException.DatabaseConnectionException(e))
        }
    }
    
    // Safe user update
    suspend fun updateUserSafely(
        userId: Long,
        name: String,
        email: String,
        age: Int
    ): DatabaseResult<UsersRecord> = withContext(Dispatchers.IO) {
        try {
            // Validation
            if (name.isBlank()) {
                return@withContext DatabaseResult.Error(DatabaseException.InvalidDataException("Name cannot be empty"))
            }
            if (!email.contains("@")) {
                return@withContext DatabaseResult.Error(DatabaseException.InvalidDataException("Invalid email format"))
            }
            if (age < 0 || age > 150) {
                return@withContext DatabaseResult.Error(DatabaseException.InvalidDataException("Invalid age"))
            }
            
            create.transactionResult { config ->
                val ctx = DSL.using(config)
                
                // Check if user exists
                val existingUser = ctx.selectFrom(USERS)
                    .where(USERS.ID.eq(userId))
                    .fetchOne()
                
                if (existingUser == null) {
                    throw DatabaseException.UserNotFoundException(userId)
                }
                
                // Check for email conflicts (excluding current user)
                val emailConflict = ctx.selectFrom(USERS)
                    .where(USERS.EMAIL.eq(email))
                    .and(USERS.ID.ne(userId))
                    .fetchOne()
                
                if (emailConflict != null) {
                    throw DatabaseException.DuplicateEmailException(email)
                }
                
                // Update user
                val updateCount = ctx.update(USERS)
                    .set(USERS.NAME, name)
                    .set(USERS.EMAIL, email)
                    .set(USERS.AGE, age)
                    .set(USERS.UPDATED_AT, LocalDateTime.now())
                    .where(USERS.ID.eq(userId))
                    .execute()
                
                if (updateCount > 0) {
                    val updatedUser = ctx.selectFrom(USERS)
                        .where(USERS.ID.eq(userId))
                        .fetchOne()!!
                    
                    DatabaseResult.Success(updatedUser)
                } else {
                    DatabaseResult.Error(DatabaseException.TransactionException("Failed to update user"))
                }
            }
        } catch (e: DatabaseException) {
            DatabaseResult.Error(e)
        } catch (e: Exception) {
            DatabaseResult.Error(DatabaseException.DatabaseConnectionException(e))
        }
    }
    
    // Safe batch operation
    suspend fun batchCreateUsersSafely(
        usersData: List<Triple<String, String, Int>>
    ): DatabaseResult<List<UsersRecord>> = withContext(Dispatchers.IO) {
        try {
            // Validate all data first
            usersData.forEachIndexed { index, (name, email, age) ->
                if (name.isBlank()) {
                    return@withContext DatabaseResult.Error(
                        DatabaseException.InvalidDataException("Name cannot be empty at index $index")
                    )
                }
                if (!email.contains("@")) {
                    return@withContext DatabaseResult.Error(
                        DatabaseException.InvalidDataException("Invalid email at index $index: $email")
                    )
                }
                if (age < 0 || age > 150) {
                    return@withContext DatabaseResult.Error(
                        DatabaseException.InvalidDataException("Invalid age at index $index: $age")
                    )
                }
            }
            
            // Check for duplicate emails in batch
            val emails = usersData.map { it.second }
            if (emails.size != emails.toSet().size) {
                return@withContext DatabaseResult.Error(
                    DatabaseException.InvalidDataException("Duplicate emails in batch data")
                )
            }
            
            val result = create.transactionResult { config ->
                val ctx = DSL.using(config)
                
                // Check for existing emails
                val existingEmails = ctx.select(USERS.EMAIL)
                    .from(USERS)
                    .where(USERS.EMAIL.`in`(emails))
                    .fetch(USERS.EMAIL)
                
                if (existingEmails.isNotEmpty()) {
                    throw DatabaseException.DuplicateEmailException("Existing emails found: ${existingEmails.joinToString()}")
                }
                
                // Batch insert
                val insertQuery = ctx.insertInto(USERS, USERS.NAME, USERS.EMAIL, USERS.AGE, USERS.CREATED_AT)
                
                usersData.forEach { (name, email, age) ->
                    insertQuery.values(name, email, age, LocalDateTime.now())
                }
                
                insertQuery.returning().fetch()
            }
            
            DatabaseResult.Success(result)
            
        } catch (e: DatabaseException) {
            DatabaseResult.Error(e)
        } catch (e: Exception) {
            DatabaseResult.Error(DatabaseException.DatabaseConnectionException(e))
        }
    }
    
    // Connection health check
    suspend fun checkDatabaseHealth(): DatabaseResult<String> = withContext(Dispatchers.IO) {
        try {
            val result = create.select(DSL.one()).fetchOne()
            if (result != null) {
                DatabaseResult.Success("Database connection healthy")
            } else {
                DatabaseResult.Error(DatabaseException.DatabaseConnectionException(RuntimeException("Health check failed")))
            }
        } catch (e: Exception) {
            DatabaseResult.Error(DatabaseException.DatabaseConnectionException(e))
        }
    }
}

// Usage example for error handling
suspend fun demonstrateErrorHandling() {
    val create = DatabaseConfig.createDSLContext()
    val safeUserRepo = SafeUserRepository(create)
    
    // Test connection health
    when (val healthResult = safeUserRepo.checkDatabaseHealth()) {
        is DatabaseResult.Success -> println("✓ ${healthResult.data}")
        is DatabaseResult.Error -> println("✗ Health check failed: ${healthResult.exception.message}")
    }
    
    // Safe user creation
    when (val result = safeUserRepo.createUserSafely("Jane Doe", "[email protected]", 28)) {
        is DatabaseResult.Success -> {
            println("✓ Successfully created user: ${result.data}")
            
            // Safe update
            when (val updateResult = safeUserRepo.updateUserSafely(
                result.data.id, "Jane Smith", "[email protected]", 29
            )) {
                is DatabaseResult.Success -> println("✓ Successfully updated user: ${updateResult.data}")
                is DatabaseResult.Error -> println("✗ Update failed: ${updateResult.exception.message}")
            }
        }
        is DatabaseResult.Error -> println("✗ User creation failed: ${result.exception.message}")
    }
    
    // Test invalid data
    when (val invalidResult = safeUserRepo.createUserSafely("", "invalid-email", -5)) {
        is DatabaseResult.Success -> println("This shouldn't happen")
        is DatabaseResult.Error -> println("✓ Correctly caught validation error: ${invalidResult.exception.message}")
    }
    
    // Test duplicate email
    when (val duplicateResult = safeUserRepo.createUserSafely("Another Jane", "[email protected]", 30)) {
        is DatabaseResult.Success -> println("This shouldn't happen")
        is DatabaseResult.Error -> println("✓ Correctly caught duplicate email: ${duplicateResult.exception.message}")
    }
    
    // Test safe batch creation
    val batchData = listOf(
        Triple("User 1", "[email protected]", 25),
        Triple("User 2", "[email protected]", 30),
        Triple("", "[email protected]", 35) // Invalid name
    )
    
    when (val batchResult = safeUserRepo.batchCreateUsersSafely(batchData)) {
        is DatabaseResult.Success -> println("✓ Batch creation successful: ${batchResult.data}")
        is DatabaseResult.Error -> println("✓ Correctly caught batch validation error: ${batchResult.exception.message}")
    }
}