Skate is a SQL generation and query library for Kotlin. It makes it easy to get your data as Kotlin data classes without the pitfalls of an ORM.
Entity
An entity is a data class that represents a row in a database table. Use the @TableName annotation to specify the table name.
@TableName("users")
data class User(
override val id: UUID = UUID.randomUUID(),
val name: String? = null,
val email: String? = null,
val createdAt: OffsetDateTime = OffsetDateTime.now(),
val updatedAt: OffsetDateTime? = null,
) : Entity
Generating SQL
Only the Postgresql generator is currently supported. It’s easy to add more generators if you need them.
val psql = skate.generator.Postgresql()
Use selectAll to fetch all fields in a table.
User::class
.selectAll()
.where(User::email eq "[email protected]")
.generate(psql)
sql:
SELECT * FROM "users" WHERE "email" = ?
values:
["john@doe"]
Use insert to add a list of entities to a table.
User::class
.insert() // can specify which fields to insert here
.values(User(name = "John Doe", email = "[email protected]"))
.generate(psql)
sql:
INSERT INTO "users" ("created_at", "email", "id", "name", "updated_at")
VALUES (#users0.createdAt, #users0.email, #users0.id, #users0.name, #users0.updatedAt)
values:
[User(...)]
Unlike an ORM, update requires you to specify exactly which fields to update.
User::class
.update(
User::name to "Jane Doe",
User::email to "[email protected]"
)
.where(User::email eq "[email protected]")
.generate(psql)
sql:
UPDATE "users" SET "name" = ?, "email" = ? WHERE ("email" = ?)
values:
["Jane Doe", "jane@doe", "john@doe"]
Use delete to delete rows from a table. But usually you’ll want to use update to set a deletedAt field instead.
Users::class
.delete()
.where(User::id.eq(id))
.generate(psql)
sql:
DELETE FROM "users" WHERE ("id" = ?)
values:
UUID(...)
Querying
Executing generated SQL in the database just requires calling either query or execute depending on whether you want to observe the results.
User::class
.selectAll()
.where(User::name.like("John %"))
.generate(psql)
.query(jdmiHandle, timeoutSeconds = 10)
List<User>(...)
