kotlin-exposed-patterns

仓库: affaan-m/everything-claude-code
安装量: 176
排名: #4902

安装

npx skills add https://github.com/affaan-m/everything-claude-code --skill kotlin-exposed-patterns
Kotlin Exposed Patterns
Comprehensive patterns for database access with JetBrains Exposed ORM, including DSL queries, DAO, transactions, and production-ready configuration.
When to Use
Setting up database access with Exposed
Writing SQL queries using Exposed DSL or DAO
Configuring connection pooling with HikariCP
Creating database migrations with Flyway
Implementing the repository pattern with Exposed
Handling JSON columns and complex queries
How It Works
Exposed provides two query styles: DSL for direct SQL-like expressions and DAO for entity lifecycle management. HikariCP manages a pool of reusable database connections configured via
HikariConfig
. Flyway runs versioned SQL migration scripts at startup to keep the schema in sync. All database operations run inside
newSuspendedTransaction
blocks for coroutine safety and atomicity. The repository pattern wraps Exposed queries behind an interface so business logic stays decoupled from the data layer and tests can use an in-memory H2 database.
Examples
DSL Query
suspend
fun
findUserById
(
id
:
UUID
)
:
UserRow
?
=
newSuspendedTransaction
{
UsersTable
.
selectAll
(
)
.
where
{
UsersTable
.
id eq id
}
.
map
{
it
.
toUser
(
)
}
.
singleOrNull
(
)
}
DAO Entity Usage
suspend
fun
createUser
(
request
:
CreateUserRequest
)
:
User
=
newSuspendedTransaction
{
UserEntity
.
new
{
name
=
request
.
name
email
=
request
.
email
role
=
request
.
role
}
.
toModel
(
)
}
HikariCP Configuration
val
hikariConfig
=
HikariConfig
(
)
.
apply
{
driverClassName
=
config
.
driver
jdbcUrl
=
config
.
url
username
=
config
.
username
password
=
config
.
password
maximumPoolSize
=
config
.
maxPoolSize
isAutoCommit
=
false
transactionIsolation
=
"TRANSACTION_READ_COMMITTED"
validate
(
)
}
Database Setup
HikariCP Connection Pooling
// DatabaseFactory.kt
object
DatabaseFactory
{
fun
create
(
config
:
DatabaseConfig
)
:
Database
{
val
hikariConfig
=
HikariConfig
(
)
.
apply
{
driverClassName
=
config
.
driver
jdbcUrl
=
config
.
url
username
=
config
.
username
password
=
config
.
password
maximumPoolSize
=
config
.
maxPoolSize
isAutoCommit
=
false
transactionIsolation
=
"TRANSACTION_READ_COMMITTED"
validate
(
)
}
return
Database
.
connect
(
HikariDataSource
(
hikariConfig
)
)
}
}
data
class
DatabaseConfig
(
val
url
:
String
,
val
driver
:
String
=
"org.postgresql.Driver"
,
val
username
:
String
=
""
,
val
password
:
String
=
""
,
val
maxPoolSize
:
Int
=
10
,
)
Flyway Migrations
// FlywayMigration.kt
fun
runMigrations
(
config
:
DatabaseConfig
)
{
Flyway
.
configure
(
)
.
dataSource
(
config
.
url
,
config
.
username
,
config
.
password
)
.
locations
(
"classpath:db/migration"
)
.
baselineOnMigrate
(
true
)
.
load
(
)
.
migrate
(
)
}
// Application startup
fun
Application
.
module
(
)
{
val
config
=
DatabaseConfig
(
url
=
environment
.
config
.
property
(
"database.url"
)
.
getString
(
)
,
username
=
environment
.
config
.
property
(
"database.username"
)
.
getString
(
)
,
password
=
environment
.
config
.
property
(
"database.password"
)
.
getString
(
)
,
)
runMigrations
(
config
)
val
database
=
DatabaseFactory
.
create
(
config
)
// ...
}
Migration Files
-- src/main/resources/db/migration/V1__create_users.sql
CREATE
TABLE
users
(
id UUID
PRIMARY
KEY
DEFAULT
gen_random_uuid
(
)
,
name
VARCHAR
(
100
)
NOT
NULL
,
email
VARCHAR
(
255
)
NOT
NULL
UNIQUE
,
role
VARCHAR
(
20
)
NOT
NULL
DEFAULT
'USER'
,
metadata JSONB
,
created_at TIMESTAMPTZ
NOT
NULL
DEFAULT
NOW
(
)
,
updated_at TIMESTAMPTZ
NOT
NULL
DEFAULT
NOW
(
)
)
;
CREATE
INDEX
idx_users_email
ON
users
(
email
)
;
CREATE
INDEX
idx_users_role
ON
users
(
role
)
;
Table Definitions
DSL Style Tables
// tables/UsersTable.kt
object
UsersTable
:
UUIDTable
(
"users"
)
{
val
name
=
varchar
(
"name"
,
100
)
val
email
=
varchar
(
"email"
,
255
)
.
uniqueIndex
(
)
val
role
=
enumerationByName
<
Role
>
(
"role"
,
20
)
val
metadata
=
jsonb
<
UserMetadata
>
(
"metadata"
,
Json
.
Default
)
.
nullable
(
)
val
createdAt
=
timestampWithTimeZone
(
"created_at"
)
.
defaultExpression
(
CurrentTimestampWithTimeZone
)
val
updatedAt
=
timestampWithTimeZone
(
"updated_at"
)
.
defaultExpression
(
CurrentTimestampWithTimeZone
)
}
object
OrdersTable
:
UUIDTable
(
"orders"
)
{
val
userId
=
uuid
(
"user_id"
)
.
references
(
UsersTable
.
id
)
val
status
=
enumerationByName
<
OrderStatus
>
(
"status"
,
20
)
val
totalAmount
=
long
(
"total_amount"
)
val
currency
=
varchar
(
"currency"
,
3
)
val
createdAt
=
timestampWithTimeZone
(
"created_at"
)
.
defaultExpression
(
CurrentTimestampWithTimeZone
)
}
object
OrderItemsTable
:
UUIDTable
(
"order_items"
)
{
val
orderId
=
uuid
(
"order_id"
)
.
references
(
OrdersTable
.
id
,
onDelete
=
ReferenceOption
.
CASCADE
)
val
productId
=
uuid
(
"product_id"
)
val
quantity
=
integer
(
"quantity"
)
val
unitPrice
=
long
(
"unit_price"
)
}
Composite Tables
object
UserRolesTable
:
Table
(
"user_roles"
)
{
val
userId
=
uuid
(
"user_id"
)
.
references
(
UsersTable
.
id
,
onDelete
=
ReferenceOption
.
CASCADE
)
val
roleId
=
uuid
(
"role_id"
)
.
references
(
RolesTable
.
id
,
onDelete
=
ReferenceOption
.
CASCADE
)
override
val
primaryKey
=
PrimaryKey
(
userId
,
roleId
)
}
DSL Queries
Basic CRUD
// Insert
suspend
fun
insertUser
(
name
:
String
,
email
:
String
,
role
:
Role
)
:
UUID
=
newSuspendedTransaction
{
UsersTable
.
insertAndGetId
{
it
[
UsersTable
.
name
]
=
name
it
[
UsersTable
.
email
]
=
email
it
[
UsersTable
.
role
]
=
role
}
.
value
}
// Select by ID
suspend
fun
findUserById
(
id
:
UUID
)
:
UserRow
?
=
newSuspendedTransaction
{
UsersTable
.
selectAll
(
)
.
where
{
UsersTable
.
id eq id
}
.
map
{
it
.
toUser
(
)
}
.
singleOrNull
(
)
}
// Select with conditions
suspend
fun
findActiveAdmins
(
)
:
List
<
UserRow
>
=
newSuspendedTransaction
{
UsersTable
.
selectAll
(
)
.
where
{
(
UsersTable
.
role eq Role
.
ADMIN
)
}
.
orderBy
(
UsersTable
.
name
)
.
map
{
it
.
toUser
(
)
}
}
// Update
suspend
fun
updateUserEmail
(
id
:
UUID
,
newEmail
:
String
)
:
Boolean
=
newSuspendedTransaction
{
UsersTable
.
update
(
{
UsersTable
.
id eq id
}
)
{
it
[
email
]
=
newEmail
it
[
updatedAt
]
=
CurrentTimestampWithTimeZone
}
>
0
}
// Delete
suspend
fun
deleteUser
(
id
:
UUID
)
:
Boolean
=
newSuspendedTransaction
{
UsersTable
.
deleteWhere
{
UsersTable
.
id eq id
}
>
0
}
// Row mapping
private
fun
ResultRow
.
toUser
(
)
=
UserRow
(
id
=
this
[
UsersTable
.
id
]
.
value
,
name
=
this
[
UsersTable
.
name
]
,
email
=
this
[
UsersTable
.
email
]
,
role
=
this
[
UsersTable
.
role
]
,
metadata
=
this
[
UsersTable
.
metadata
]
,
createdAt
=
this
[
UsersTable
.
createdAt
]
,
updatedAt
=
this
[
UsersTable
.
updatedAt
]
,
)
Advanced Queries
// Join queries
suspend
fun
findOrdersWithUser
(
userId
:
UUID
)
:
List
<
OrderWithUser
>
=
newSuspendedTransaction
{
(
OrdersTable innerJoin UsersTable
)
.
selectAll
(
)
.
where
{
OrdersTable
.
userId eq userId
}
.
orderBy
(
OrdersTable
.
createdAt
,
SortOrder
.
DESC
)
.
map
{
row
->
OrderWithUser
(
orderId
=
row
[
OrdersTable
.
id
]
.
value
,
status
=
row
[
OrdersTable
.
status
]
,
totalAmount
=
row
[
OrdersTable
.
totalAmount
]
,
userName
=
row
[
UsersTable
.
name
]
,
)
}
}
// Aggregation
suspend
fun
countUsersByRole
(
)
:
Map
<
Role
,
Long
>
=
newSuspendedTransaction
{
UsersTable
.
select
(
UsersTable
.
role
,
UsersTable
.
id
.
count
(
)
)
.
groupBy
(
UsersTable
.
role
)
.
associate
{
row
->
row
[
UsersTable
.
role
]
to
row
[
UsersTable
.
id
.
count
(
)
]
}
}
// Subqueries
suspend
fun
findUsersWithOrders
(
)
:
List
<
UserRow
>
=
newSuspendedTransaction
{
UsersTable
.
selectAll
(
)
.
where
{
UsersTable
.
id inSubQuery
OrdersTable
.
select
(
OrdersTable
.
userId
)
.
withDistinct
(
)
}
.
map
{
it
.
toUser
(
)
}
}
// LIKE and pattern matching — always escape user input to prevent wildcard injection
private
fun
escapeLikePattern
(
input
:
String
)
:
String
=
input
.
replace
(
"\"
,
"\\"
)
.
replace
(
"%"
,
"\%"
)
.
replace
(
"_"
,
"\_"
)
suspend
fun
searchUsers
(
query
:
String
)
:
List
<
UserRow
>
=
newSuspendedTransaction
{
val
sanitized
=
escapeLikePattern
(
query
.
lowercase
(
)
)
UsersTable
.
selectAll
(
)
.
where
{
(
UsersTable
.
name
.
lowerCase
(
)
like
"%
${
sanitized
}
%"
)
or
(
UsersTable
.
email
.
lowerCase
(
)
like
"%
${
sanitized
}
%"
)
}
.
map
{
it
.
toUser
(
)
}
}
Pagination
data
class
Page
<
T
>
(
val
data
:
List
<
T
>
,
val
total
:
Long
,
val
page
:
Int
,
val
limit
:
Int
,
)
{
val
totalPages
:
Int
get
(
)
=
(
(
total
+
limit
-
1
)
/
limit
)
.
toInt
(
)
val
hasNext
:
Boolean
get
(
)
=
page
<
totalPages
val
hasPrevious
:
Boolean
get
(
)
=
page
>
1
}
suspend
fun
findUsersPaginated
(
page
:
Int
,
limit
:
Int
)
:
Page
<
UserRow
>
=
newSuspendedTransaction
{
val
total
=
UsersTable
.
selectAll
(
)
.
count
(
)
val
data
=
UsersTable
.
selectAll
(
)
.
orderBy
(
UsersTable
.
createdAt
,
SortOrder
.
DESC
)
.
limit
(
limit
)
.
offset
(
(
(
page
-
1
)
*
limit
)
.
toLong
(
)
)
.
map
{
it
.
toUser
(
)
}
Page
(
data
=
data
,
total
=
total
,
page
=
page
,
limit
=
limit
)
}
Batch Operations
// Batch insert
suspend
fun
insertUsers
(
users
:
List
<
CreateUserRequest
>
)
:
List
<
UUID
>
=
newSuspendedTransaction
{
UsersTable
.
batchInsert
(
users
)
{
user
->
this
[
UsersTable
.
name
]
=
user
.
name
this
[
UsersTable
.
email
]
=
user
.
email
this
[
UsersTable
.
role
]
=
user
.
role
}
.
map
{
it
[
UsersTable
.
id
]
.
value
}
}
// Upsert (insert or update on conflict)
suspend
fun
upsertUser
(
id
:
UUID
,
name
:
String
,
email
:
String
)
{
newSuspendedTransaction
{
UsersTable
.
upsert
(
UsersTable
.
email
)
{
it
[
UsersTable
.
id
]
=
EntityID
(
id
,
UsersTable
)
it
[
UsersTable
.
name
]
=
name
it
[
UsersTable
.
email
]
=
email
it
[
updatedAt
]
=
CurrentTimestampWithTimeZone
}
}
}
DAO Pattern
Entity Definitions
// entities/UserEntity.kt
class
UserEntity
(
id
:
EntityID
<
UUID
>
)
:
UUIDEntity
(
id
)
{
companion
object
:
UUIDEntityClass
<
UserEntity
>
(
UsersTable
)
var
name
by
UsersTable
.
name
var
email
by
UsersTable
.
email
var
role
by
UsersTable
.
role
var
metadata
by
UsersTable
.
metadata
var
createdAt
by
UsersTable
.
createdAt
var
updatedAt
by
UsersTable
.
updatedAt
val
orders
by
OrderEntity referrersOn OrdersTable
.
userId
fun
toModel
(
)
:
User
=
User
(
id
=
id
.
value
,
name
=
name
,
email
=
email
,
role
=
role
,
metadata
=
metadata
,
createdAt
=
createdAt
,
updatedAt
=
updatedAt
,
)
}
class
OrderEntity
(
id
:
EntityID
<
UUID
>
)
:
UUIDEntity
(
id
)
{
companion
object
:
UUIDEntityClass
<
OrderEntity
>
(
OrdersTable
)
var
user
by
UserEntity referencedOn OrdersTable
.
userId
var
status
by
OrdersTable
.
status
var
totalAmount
by
OrdersTable
.
totalAmount
var
currency
by
OrdersTable
.
currency
var
createdAt
by
OrdersTable
.
createdAt
val
items
by
OrderItemEntity referrersOn OrderItemsTable
.
orderId
}
DAO Operations
suspend
fun
findUserByEmail
(
email
:
String
)
:
User
?
=
newSuspendedTransaction
{
UserEntity
.
find
{
UsersTable
.
email eq email
}
.
firstOrNull
(
)
?
.
toModel
(
)
}
suspend
fun
createUser
(
request
:
CreateUserRequest
)
:
User
=
newSuspendedTransaction
{
UserEntity
.
new
{
name
=
request
.
name
email
=
request
.
email
role
=
request
.
role
}
.
toModel
(
)
}
suspend
fun
updateUser
(
id
:
UUID
,
request
:
UpdateUserRequest
)
:
User
?
=
newSuspendedTransaction
{
UserEntity
.
findById
(
id
)
?
.
apply
{
request
.
name
?
.
let
{
name
=
it
}
request
.
email
?
.
let
{
email
=
it
}
updatedAt
=
OffsetDateTime
.
now
(
ZoneOffset
.
UTC
)
}
?
.
toModel
(
)
}
Transactions
Suspend Transaction Support
// Good: Use newSuspendedTransaction for coroutine support
suspend
fun
performDatabaseOperation
(
)
:
Result
<
User
>
=
runCatching
{
newSuspendedTransaction
{
val
user
=
UserEntity
.
new
{
name
=
"Alice"
email
=
"alice@example.com"
}
// All operations in this block are atomic
user
.
toModel
(
)
}
}
// Good: Nested transactions with savepoints
suspend
fun
transferFunds
(
fromId
:
UUID
,
toId
:
UUID
,
amount
:
Long
)
{
newSuspendedTransaction
{
val
from
=
UserEntity
.
findById
(
fromId
)
?:
throw
NotFoundException
(
"User
$
fromId
not found"
)
val
to
=
UserEntity
.
findById
(
toId
)
?:
throw
NotFoundException
(
"User
$
toId
not found"
)
// Debit
from
.
balance
-=
amount
// Credit
to
.
balance
+=
amount
// Both succeed or both fail
}
}
Transaction Isolation
suspend
fun
readCommittedQuery
(
)
:
List
<
User
>
=
newSuspendedTransaction
(
transactionIsolation
=
Connection
.
TRANSACTION_READ_COMMITTED
)
{
UserEntity
.
all
(
)
.
map
{
it
.
toModel
(
)
}
}
suspend
fun
serializableOperation
(
)
{
newSuspendedTransaction
(
transactionIsolation
=
Connection
.
TRANSACTION_SERIALIZABLE
)
{
// Strictest isolation level for critical operations
}
}
Repository Pattern
Interface Definition
interface
UserRepository
{
suspend
fun
findById
(
id
:
UUID
)
:
User
?
suspend
fun
findByEmail
(
email
:
String
)
:
User
?
suspend
fun
findAll
(
page
:
Int
,
limit
:
Int
)
:
Page
<
User
>
suspend
fun
search
(
query
:
String
)
:
List
<
User
>
suspend
fun
create
(
request
:
CreateUserRequest
)
:
User
suspend
fun
update
(
id
:
UUID
,
request
:
UpdateUserRequest
)
:
User
?
suspend
fun
delete
(
id
:
UUID
)
:
Boolean
suspend
fun
count
(
)
:
Long
}
Exposed Implementation
class
ExposedUserRepository
(
private
val
database
:
Database
,
)
:
UserRepository
{
override
suspend
fun
findById
(
id
:
UUID
)
:
User
?
=
newSuspendedTransaction
(
db
=
database
)
{
UsersTable
.
selectAll
(
)
.
where
{
UsersTable
.
id eq id
}
.
map
{
it
.
toUser
(
)
}
.
singleOrNull
(
)
}
override
suspend
fun
findByEmail
(
email
:
String
)
:
User
?
=
newSuspendedTransaction
(
db
=
database
)
{
UsersTable
.
selectAll
(
)
.
where
{
UsersTable
.
email eq email
}
.
map
{
it
.
toUser
(
)
}
.
singleOrNull
(
)
}
override
suspend
fun
findAll
(
page
:
Int
,
limit
:
Int
)
:
Page
<
User
>
=
newSuspendedTransaction
(
db
=
database
)
{
val
total
=
UsersTable
.
selectAll
(
)
.
count
(
)
val
data
=
UsersTable
.
selectAll
(
)
.
orderBy
(
UsersTable
.
createdAt
,
SortOrder
.
DESC
)
.
limit
(
limit
)
.
offset
(
(
(
page
-
1
)
*
limit
)
.
toLong
(
)
)
.
map
{
it
.
toUser
(
)
}
Page
(
data
=
data
,
total
=
total
,
page
=
page
,
limit
=
limit
)
}
override
suspend
fun
search
(
query
:
String
)
:
List
<
User
>
=
newSuspendedTransaction
(
db
=
database
)
{
val
sanitized
=
escapeLikePattern
(
query
.
lowercase
(
)
)
UsersTable
.
selectAll
(
)
.
where
{
(
UsersTable
.
name
.
lowerCase
(
)
like
"%
${
sanitized
}
%"
)
or
(
UsersTable
.
email
.
lowerCase
(
)
like
"%
${
sanitized
}
%"
)
}
.
orderBy
(
UsersTable
.
name
)
.
map
{
it
.
toUser
(
)
}
}
override
suspend
fun
create
(
request
:
CreateUserRequest
)
:
User
=
newSuspendedTransaction
(
db
=
database
)
{
UsersTable
.
insert
{
it
[
name
]
=
request
.
name
it
[
email
]
=
request
.
email
it
[
role
]
=
request
.
role
}
.
resultedValues
!!
.
first
(
)
.
toUser
(
)
}
override
suspend
fun
update
(
id
:
UUID
,
request
:
UpdateUserRequest
)
:
User
?
=
newSuspendedTransaction
(
db
=
database
)
{
val
updated
=
UsersTable
.
update
(
{
UsersTable
.
id eq id
}
)
{
request
.
name
?
.
let
{
name
->
it
[
UsersTable
.
name
]
=
name
}
request
.
email
?
.
let
{
email
->
it
[
UsersTable
.
email
]
=
email
}
it
[
updatedAt
]
=
CurrentTimestampWithTimeZone
}
if
(
updated
>
0
)
findById
(
id
)
else
null
}
override
suspend
fun
delete
(
id
:
UUID
)
:
Boolean
=
newSuspendedTransaction
(
db
=
database
)
{
UsersTable
.
deleteWhere
{
UsersTable
.
id eq id
}
>
0
}
override
suspend
fun
count
(
)
:
Long
=
newSuspendedTransaction
(
db
=
database
)
{
UsersTable
.
selectAll
(
)
.
count
(
)
}
private
fun
ResultRow
.
toUser
(
)
=
User
(
id
=
this
[
UsersTable
.
id
]
.
value
,
name
=
this
[
UsersTable
.
name
]
,
email
=
this
[
UsersTable
.
email
]
,
role
=
this
[
UsersTable
.
role
]
,
metadata
=
this
[
UsersTable
.
metadata
]
,
createdAt
=
this
[
UsersTable
.
createdAt
]
,
updatedAt
=
this
[
UsersTable
.
updatedAt
]
,
)
}
JSON Columns
JSONB with kotlinx.serialization
// Custom column type for JSONB
inline
fun
<
reified
T
:
Any
>
Table
.
jsonb
(
name
:
String
,
json
:
Json
,
)
:
Column
<
T
>
=
registerColumn
(
name
,
object
:
ColumnType
<
T
>
(
)
{
override
fun
sqlType
(
)
=
"JSONB"
override
fun
valueFromDB
(
value
:
Any
)
:
T
=
when
(
value
)
{
is
String
->
json
.
decodeFromString
(
value
)
is
PGobject
->
{
val
jsonString
=
value
.
value
?:
throw
IllegalArgumentException
(
"PGobject value is null for column '
$
name
'"
)
json
.
decodeFromString
(
jsonString
)
}
else
->
throw
IllegalArgumentException
(
"Unexpected value:
$
value
"
)
}
override
fun
notNullValueToDB
(
value
:
T
)
:
Any
=
PGobject
(
)
.
apply
{
type
=
"jsonb"
this
.
value
=
json
.
encodeToString
(
value
)
}
}
)
// Usage in table
@Serializable
data
class
UserMetadata
(
val
preferences
:
Map
<
String
,
String
>
=
emptyMap
(
)
,
val
tags
:
List
<
String
>
=
emptyList
(
)
,
)
object
UsersTable
:
UUIDTable
(
"users"
)
{
val
metadata
=
jsonb
<
UserMetadata
>
(
"metadata"
,
Json
.
Default
)
.
nullable
(
)
}
Testing with Exposed
In-Memory Database for Tests
class
UserRepositoryTest
:
FunSpec
(
{
lateinit
var
database
:
Database
lateinit
var
repository
:
UserRepository
beforeSpec
{
database
=
Database
.
connect
(
url
=
"jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;MODE=PostgreSQL"
,
driver
=
"org.h2.Driver"
,
)
transaction
(
database
)
{
SchemaUtils
.
create
(
UsersTable
)
}
repository
=
ExposedUserRepository
(
database
)
}
beforeTest
{
transaction
(
database
)
{
UsersTable
.
deleteAll
(
)
}
}
test
(
"create and find user"
)
{
val
user
=
repository
.
create
(
CreateUserRequest
(
"Alice"
,
"alice@example.com"
)
)
user
.
name shouldBe
"Alice"
user
.
email shouldBe
"alice@example.com"
val
found
=
repository
.
findById
(
user
.
id
)
found shouldBe user
}
test
(
"findByEmail returns null for unknown email"
)
{
val
result
=
repository
.
findByEmail
(
"unknown@example.com"
)
result
.
shouldBeNull
(
)
}
test
(
"pagination works correctly"
)
{
repeat
(
25
)
{
i
->
repository
.
create
(
CreateUserRequest
(
"User
$
i
"
,
"user
$
i
@example.com"
)
)
}
val
page1
=
repository
.
findAll
(
page
=
1
,
limit
=
10
)
page1
.
data shouldHaveSize
10
page1
.
total shouldBe
25
page1
.
hasNext shouldBe
true
val
page3
=
repository
.
findAll
(
page
=
3
,
limit
=
10
)
page3
.
data shouldHaveSize
5
page3
.
hasNext shouldBe
false
}
}
)
Gradle Dependencies
// build.gradle.kts
dependencies
{
// Exposed
implementation
(
"org.jetbrains.exposed:exposed-core:1.0.0"
)
implementation
(
"org.jetbrains.exposed:exposed-dao:1.0.0"
)
implementation
(
"org.jetbrains.exposed:exposed-jdbc:1.0.0"
)
implementation
(
"org.jetbrains.exposed:exposed-kotlin-datetime:1.0.0"
)
implementation
(
"org.jetbrains.exposed:exposed-json:1.0.0"
)
// Database driver
implementation
(
"org.postgresql:postgresql:42.7.5"
)
// Connection pooling
implementation
(
"com.zaxxer:HikariCP:6.2.1"
)
// Migrations
implementation
(
"org.flywaydb:flyway-core:10.22.0"
)
implementation
(
"org.flywaydb:flyway-database-postgresql:10.22.0"
)
// Testing
testImplementation
(
"com.h2database:h2:2.3.232"
)
}
Quick Reference: Exposed Patterns
Pattern
Description
object Table : UUIDTable("name")
Define table with UUID primary key
newSuspendedTransaction { }
Coroutine-safe transaction block
Table.selectAll().where { }
Query with conditions
Table.insertAndGetId { }
Insert and return generated ID
Table.update({ condition }) { }
Update matching rows
Table.deleteWhere { }
Delete matching rows
Table.batchInsert(items) { }
Efficient bulk insert
innerJoin
/
leftJoin
Join tables
orderBy
/
limit
/
offset
Sort and paginate
count()
/
sum()
/
avg()
Aggregation functions
Remember
Use the DSL style for simple queries and the DAO style when you need entity lifecycle management. Always use newSuspendedTransaction for coroutine support, and wrap database operations behind a repository interface for testability.
← 返回排行榜