Streamlining Kotlin DB Operations with kotlin-exposed-patterns
機械翻訳 / Machine-translated
機械翻訳 / Machine-translated
Original article: Streamlining Kotlin DB Operations with kotlin-exposed-patterns (cccskills)
Skill page: kotlin-exposed-patterns
Summary of this article
If you're writing server-side Kotlin, adding kotlin-exposed-patterns lets the agent consistently propose code from JetBrains Exposed queries all the way through to tests.
This article covers the scope of what the skill handles, how to add it to Claude Code or Codex, and example prompts to send.
It also walks through things to verify before you start using it.
Once you add kotlin-exposed-patterns, the agent will structure Exposed code in the same shape every time.
For example, suppose you ask:
Implement user search and pagination with Exposed
The agent returns a table definition, a search function, and a data class carrying page information, all together.
Each function is wrapped in a newSuspendedTransaction block, making it safe to use inside coroutines (Kotlin's asynchronous processing mechanism).
Search terms are escaped for LIKE special characters before use, preventing unintended matches from user input.
Pagination results come back in a Page class with the following fields:
| Field | Description |
|---|---|
| data | List of records for that page |
| total | Total record count |
| page / limit | Current page number and records per page |
| totalPages | Total number of pages |
| hasNext / hasPrevious | Whether a next/previous page exists |
This skill is published on GitHub at affaan-m/ECC, with 288,823 stars in the repository.
The content is a single file, SKILL.md, centered on code examples using users and orders as subjects.
This skill supports data access with Exposed across four areas:
"Queries," "connections and schema changes," "transactions," and "isolation and testing."
The agent applies these examples to your own tables when writing code.
Exposed has two styles of writing code, and the skill includes examples of both.
Here's a guide for when to use each:
| Style | Characteristics | Best for |
|---|---|---|
| DSL | Written in SQL-like expressions | Simple searches and updates |
| DAO | Treats records as objects | Managing entity state such as creation and updates |
DSL examples cover everything from basic CRUD operations to table joins, aggregations, subqueries, and pagination.
Bulk inserts with batchInsert and upsert (which switches to update on duplicate) are also included.
There's even a custom column type definition for reading and writing PostgreSQL JSONB columns using kotlinx.serialization.
The connection setup combines HikariCP connection pooling (a mechanism for reusing connections) with Flyway migrations.
HikariCP is configured with a maximum connection count, auto-commit disabled, and isolation level READ_COMMITTED.
Flyway applies numbered SQL files in sequence at application startup to bring the table schema up to date.
Migration SQL files are placed at src/main/resources/db/migration/ with names like V1__create_users.sql.
The startup process runs migrations first, then creates the connection pool.
The core principle of this skill is that all database operations are executed inside a newSuspendedTransaction block.
Operations within the block either all succeed or all fail together.
There are examples for scenarios like transfers that require two updates to go through simultaneously, as well as examples for changing isolation levels such as SERIALIZABLE.
The repository pattern (a design that consolidates data access through a single interface) is used to separate business logic from data access.
A UserRepository interface defines find, create, update, delete, and count operations, with a separate Exposed implementation class.
Tests spin up an H2 in-memory database in PostgreSQL compatibility mode and verify the implementation class directly.
There are three test examples:
checking whether a created user can be retrieved, whether null is returned for an unregistered email, and whether pagination info is correct when splitting 25 records into pages of 10.
Tables are cleared before each test so tests don't interfere with one another.
There are two ways to install: running a command, or asking the agent.
Running the command lets you choose which agent to install into — Claude Code, Codex, or others.
npx skills add https://github.com/affaan-m/ECC --skill kotlin-exposed-patterns
If you prefer to let the agent handle it, send the following message as-is.
It instructs the agent to review the contents and then place the files:
Please install the following Agent Skill.
https://github.com/affaan-m/ECC/tree/main/skills/kotlin-exposed-patterns
First, check the contents of SKILL.md and any accompanying files to confirm there are no suspicious operations (such as sending data externally or reading credentials), then place them in ~/.claude/skills/kotlin-exposed-patterns/ for Claude Code, or ~/.agents/skills/kotlin-exposed-patterns/ for Codex.
Where to place the skill depends on the type of agent and the scope you want:
| Agent | Use across all projects | Use in a specific project only |
|---|---|---|
| Claude Code | ~/.claude/skills/ | .claude/skills/ in the project |
| Codex | ~/.agents/skills/ | .agents/skills/ in the project |
If you only have one project using Exposed, placing it in the project directory is the better approach.
Include your specific conditions in your prompts — things like table contents and connection targets.
That way the returned code will be tailored to your situation.
Set up a PostgreSQL connection using HikariCP and Flyway.
Set the max pool size to 10.
What comes back is a data class holding connection info, a factory that creates an Exposed Database from HikariCP, and a function to run Flyway.
It also includes an initial migration SQL file and logic to run migrations before connecting at startup.
Add an orders table and write a query to retrieve a specified user's orders in reverse chronological order.
You'll get an orders table definition referencing the users table, along with a search function that joins the two tables using innerJoin.
Results are sorted by creation date in descending order.
Following the skill's examples, results are packed into a data class holding order ID, status, amount, and username.
Separate data access behind an interface and test it with H2.
You'll get three things: a repository interface, an Exposed implementation class, and test code using H2.
Tests are in FunSpec style, with logic to clear the table before each test.
This skill is a collection of patterns, so you'll need to verify versions and connection targets against your project yourself.
The Gradle configuration in the documentation assumes the following versions:
| Library | Version in documentation |
|---|---|
| Exposed (core, dao, jdbc, kotlin-datetime, json) | 1.0.0 |
| PostgreSQL driver | 42.7.5 |
| HikariCP | 6.2.1 |
| Flyway (core, database-postgresql) | 10.22.0 |
| H2 (for testing) | 2.3.232 |
If your versions differ, specify them in your prompt before asking for code.
It's safer to run a build and tests before incorporating generated code.
The configuration examples and SQL assume PostgreSQL.
JSONB columns, primary key generation with gen_random_uuid(), and conversion using PGobject are all PostgreSQL-specific.
The skill does not include configuration examples for other database products.
You'll need to prepare the connection URL, driver, username, and password for your own environment.
The skill's examples read these from the application's configuration file.
Be careful not to write credentials directly in your code.
The license is MIT, and the only file included is SKILL.md.
No scripts are included.
Even so, it's a good idea to open SKILL.md and review its contents before installing.
A good rule of thumb: use DSL for simple searches and updates, and DAO when you want to work with records as objects you create and update.
The skill recommends this same distinction.
The skill's configuration examples and SQL are for PostgreSQL, and there are no instructions for other products.
The Exposed writing style itself is still useful as a reference.
However, you'll need to look into driver and database-specific details on your own.
The skill's test examples use H2 in-memory database running in PostgreSQL compatibility mode.
Tables are created at test startup and cleared before each test, so no external database server is needed.
kotlin-exposed-patterns is a skill for bringing Kotlin database operations using JetBrains Exposed into a single consistent shape — from queries to tests.
It covers DSL and DAO queries, HikariCP and Flyway connection setup, repository isolation, and testing with H2.
The agent can propose all of these following the same patterns.
The more you're already writing server-side code with PostgreSQL and Exposed, the more directly applicable this skill will be.
You can check the install location and description on the cccskills page.
This article is a repost of cccskills editorial piece "Streamlining Kotlin DB Operations with kotlin-exposed-patterns."
Full skill details and installation instructions can be found on the cccskills kotlin-exposed-patterns page.