agentsclimarketplace

Jooq patterns

Skill ashchupliak/dream-team/skills/jooq-patterns

JOOQ type-safe SQL patterns - use for database queries, repositories, complex SQL operations, and PostgreSQL-specific featuresFrom its SKILL.md

Install
npx -y skills add ashchupliak/dream-team --skill jooq-patterns

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

2 things to look at

  • no licenseNo license file was found in the repository. Code published without one is not open source by default, so using it at work is a question for whoever answers licensing questions where you are.
  • 5 stars5 stars. Stars are a popularity signal and not a quality one, but at this level it is likely that nobody has read this closely except its author, and you would be relying on your own review.

SKILL.md

7.3 KB, as published. Nobody here has run it

JOOQ Database Patterns

Repository Structure

@Repository
class EnvironmentRepository(
    private val dsl: DSLContext
) {

    fun findById(id: UUID): Environment? =
        dsl.selectFrom(ENVIRONMENT)
            .where(ENVIRONMENT.ID.eq(id))
            .fetchOne()
            ?.toEntity()

    fun findByName(name: String): Environment? =
        dsl.selectFrom(ENVIRONMENT)
            .where(ENVIRONMENT.NAME.eq(name))
            .fetchOne()
            ?.toEntity()

    fun findAll(): List<Environment> =
        dsl.selectFrom(ENVIRONMENT)
            .orderBy(ENVIRONMENT.CREATED_AT.desc())
            .fetch()
            .map { it.toEntity() }

    fun save(entity: Environment): Environment =
        dsl.insertInto(ENVIRONMENT)
            .set(ENVIRONMENT.ID, entity.id)
            .set(ENVIRONMENT.NAME, entity.name)
            .set(ENVIRONMENT.STATUS, entity.status.name)
            .set(ENVIRONMENT.CREATED_AT, entity.createdAt)
            .returning()
            .fetchOne()!!
            .toEntity()

    fun update(entity: Environment): Environment =
        dsl.update(ENVIRONMENT)
            .set(ENVIRONMENT.STATUS, entity.status.name)
            .set(ENVIRONMENT.UPDATED_AT, Instant.now())
            .where(ENVIRONMENT.ID.eq(entity.id))
            .returning()
            .fetchOne()!!
            .toEntity()

    fun delete(id: UUID): Boolean =
        dsl.deleteFrom(ENVIRONMENT)
            .where(ENVIRONMENT.ID.eq(id))
            .execute() > 0
}

Record to Entity Mapping

// Extension function on generated Record
private fun EnvironmentRecord.toEntity() = Environment(
    id = id,
    name = name,
    status = EnvironmentStatus.valueOf(status),
    createdAt = createdAt,
    updatedAt = updatedAt
)

// For complex mappings with joins
private fun Record.toEnvironmentWithTags() = Environment(
    id = get(ENVIRONMENT.ID),
    name = get(ENVIRONMENT.NAME),
    status = EnvironmentStatus.valueOf(get(ENVIRONMENT.STATUS)),
    createdAt = get(ENVIRONMENT.CREATED_AT),
    updatedAt = get(ENVIRONMENT.UPDATED_AT),
    tags = get("tags", List::class.java) as List<String>
)

Complex Queries

Joins

fun findWithOwner(id: UUID): EnvironmentWithOwner? =
    dsl.select(
        ENVIRONMENT.asterisk(),
        USER.NAME.`as`("owner_name"),
        USER.EMAIL.`as`("owner_email")
    )
    .from(ENVIRONMENT)
    .join(USER).on(ENVIRONMENT.OWNER_ID.eq(USER.ID))
    .where(ENVIRONMENT.ID.eq(id))
    .fetchOne()
    ?.let { record ->
        EnvironmentWithOwner(
            environment = record.into(ENVIRONMENT).toEntity(),
            ownerName = record.get("owner_name", String::class.java),
            ownerEmail = record.get("owner_email", String::class.java)
        )
    }

Filtering and Pagination

fun findByFilters(
    status: EnvironmentStatus?,
    search: String?,
    page: Int,
    size: Int
): Page<Environment> {
    val conditions = mutableListOf<Condition>()

    status?.let { conditions.add(ENVIRONMENT.STATUS.eq(it.name)) }
    search?.let { conditions.add(ENVIRONMENT.NAME.likeIgnoreCase("%$it%")) }

    val baseQuery = dsl.selectFrom(ENVIRONMENT)
        .where(conditions)

    val total = dsl.selectCount()
        .from(ENVIRONMENT)
        .where(conditions)
        .fetchOne(0, Long::class.java) ?: 0L

    val items = baseQuery
        .orderBy(ENVIRONMENT.CREATED_AT.desc())
        .limit(size)
        .offset(page * size)
        .fetch()
        .map { it.toEntity() }

    return Page(items, total, page, size)
}

Aggregations

fun countByStatus(): Map<EnvironmentStatus, Long> =
    dsl.select(ENVIRONMENT.STATUS, DSL.count())
        .from(ENVIRONMENT)
        .groupBy(ENVIRONMENT.STATUS)
        .fetch()
        .associate { record ->
            EnvironmentStatus.valueOf(record.value1()) to record.value2().toLong()
        }

Batch Operations

fun saveAll(entities: List<Environment>): List<Environment> {
    if (entities.isEmpty()) return emptyList()

    val records = entities.map { entity ->
        dsl.newRecord(ENVIRONMENT).apply {
            id = entity.id
            name = entity.name
            status = entity.status.name
            createdAt = entity.createdAt
        }
    }

    dsl.batchInsert(records).execute()

    return entities
}

fun updateStatuses(ids: List<UUID>, status: EnvironmentStatus): Int =
    dsl.update(ENVIRONMENT)
        .set(ENVIRONMENT.STATUS, status.name)
        .set(ENVIRONMENT.UPDATED_AT, Instant.now())
        .where(ENVIRONMENT.ID.`in`(ids))
        .execute()

JSON Fields (PostgreSQL)

// For JSONB columns
fun findByMetadata(key: String, value: String): List<Environment> =
    dsl.selectFrom(ENVIRONMENT)
        .where(
            DSL.field("metadata->>'{0}'", String::class.java, key)
                .eq(value)
        )
        .fetch()
        .map { it.toEntity() }

// Store JSON
fun updateMetadata(id: UUID, metadata: Map<String, Any>): Environment =
    dsl.update(ENVIRONMENT)
        .set(ENVIRONMENT.METADATA, JSONB.jsonb(objectMapper.writeValueAsString(metadata)))
        .where(ENVIRONMENT.ID.eq(id))
        .returning()
        .fetchOne()!!
        .toEntity()

Upsert (ON CONFLICT)

fun upsert(entity: Environment): Environment =
    dsl.insertInto(ENVIRONMENT)
        .set(ENVIRONMENT.ID, entity.id)
        .set(ENVIRONMENT.NAME, entity.name)
        .set(ENVIRONMENT.STATUS, entity.status.name)
        .set(ENVIRONMENT.CREATED_AT, entity.createdAt)
        .onConflict(ENVIRONMENT.NAME)
        .doUpdate()
        .set(ENVIRONMENT.STATUS, entity.status.name)
        .set(ENVIRONMENT.UPDATED_AT, Instant.now())
        .returning()
        .fetchOne()!!
        .toEntity()

Transaction Handling

// In service layer - explicit transaction control
@Service
class EnvironmentService(
    private val dsl: DSLContext,
    private val repository: EnvironmentRepository
) {

    fun createWithResources(request: CreateRequest): Environment =
        dsl.transactionResult { config ->
            val txDsl = DSL.using(config)

            // Create environment
            val env = repository.save(request.toEnvironment())

            // Create related resources in same transaction
            request.resources.forEach { resource ->
                txDsl.insertInto(RESOURCE)
                    .set(RESOURCE.ENVIRONMENT_ID, env.id)
                    .set(RESOURCE.TYPE, resource.type)
                    .execute()
            }

            env
        }
}

Custom SQL Functions

// Using PostgreSQL functions
fun findNearExpiry(days: Int): List<Environment> =
    dsl.selectFrom(ENVIRONMENT)
        .where(
            ENVIRONMENT.EXPIRES_AT.lessOrEqual(
                DSL.currentTimestamp().plus(DSL.interval(days, DatePart.DAY))
            )
        )
        .fetch()
        .map { it.toEntity() }

// Array operations
fun findByTags(tags: List<String>): List<Environment> =
    dsl.selectFrom(ENVIRONMENT)
        .where(
            DSL.field("tags").cast(SQLDataType.VARCHAR.array())
                .contains(tags.toTypedArray())
        )
        .fetch()
        .map { it.toEntity() }

Keep looking

Skills are one crate of 610,220. Ordering is by how many stacks a row turns up in, so the top of any crate is what has actually been picked rather than what has the most stars.