I’ve generated a new Ktor project in which I could find a UsersSchema.kt
file.
@Serializable
data class ExposedUser(val name: String, val age: Int)
class UserService(database: Database) {
object Users : Table() {
val id = integer("id").autoIncrement()
val name = varchar("name", length = 50)
val age = integer("age")
override val primaryKey = PrimaryKey(id)
}
init {
transaction(database) {
SchemaUtils.create(Users)
}
}
suspend fun create(user: ExposedUser): Int = dbQuery {
Users.insert {
it[name] = user.name
it[age] = user.age
}[Users.id]
}
suspend fun read(id: Int): ExposedUser? {
return dbQuery {
Users.selectAll()
.where { Users.id eq id }
.map { ExposedUser(it[Users.name], it[Users.age]) }
.singleOrNull()
}
}
suspend fun update(id: Int, user: ExposedUser) {
dbQuery {
Users.update({ Users.id eq id }) {
it[name] = user.name
it[age] = user.age
}
}
}
suspend fun delete(id: Int) {
dbQuery {
Users.deleteWhere { Users.id.eq(id) }
}
}
private suspend fun <T> dbQuery(block: suspend () -> T): T =
newSuspendedTransaction(Dispatchers.IO) { block() }
}
Based on this example, I created a CategorySchema.kt
file.
@Serializable
data class Category(
val id: String,
val name: String,
val image: String?,
val slug: String,
)
class CategoryService(database: Database) {
object CategoryTable: Table(""Category"") {
val id = varchar("id", 36).uniqueIndex()
val name = varchar("name", 50).uniqueIndex()
val image = text("image").nullable()
val slug = varchar("slug", 50).uniqueIndex()
val createdAt = datetime("createdAt")
val updatedAt = datetime("updatedAt")
override val primaryKey = PrimaryKey(id)
}
init {
transaction(database) {
SchemaUtils.create(CategoryTable)
addLogger(StdOutSqlLogger)
}
}
suspend fun getAll(): List<Category> {
try {
return dbQuery {
CategoryTable.selectAll().map {
Category(
it[CategoryTable.id],
it[CategoryTable.name],
it[CategoryTable.image],
it[CategoryTable.slug]
)
}
}
} catch (e: Exception) {
exposedLogger.error("Aucune catégorie existante")
return emptyList()
}
}
suspend fun getOne(id: String): Category? {
try {
return dbQuery {
CategoryTable
.selectAll()
.where { CategoryTable.id eq id }
.map {
Category(
it[CategoryTable.id],
it[CategoryTable.name],
it[CategoryTable.image],
it[CategoryTable.slug]
)
}
.singleOrNull()
}
} catch (e: Exception) {
exposedLogger.error("La catégorie recherchée n'existe pas")
return null
}
}
private suspend fun <T> dbQuery(block: suspend () -> T): T =
newSuspendedTransaction(Dispatchers.IO) { block() }
}
Then I realised that many of my tables have similar methods, and as I didn’t want to repeat myself, I decided to create a BaseService
class.
abstract class BaseService<Entity, ExposedTable : Table>(
private val table: ExposedTable,
private val mapper: (ResultRow) -> Entity
) {
suspend fun getAll(): List<Entity> {
try {
return dbQuery {
table.selectAll().map(mapper)
}
} catch (e: Exception) {
exposedLogger.error("Erreur lors de la récupération des éléments issus de ${table.tableName}")
return emptyList()
}
}
private suspend fun <T> dbQuery(block: suspend () -> T): T =
newSuspendedTransaction(Dispatchers.IO) { block() }
}
However, I don’t know how I should write the getOne()
method.
What I’ve tried:
suspend fun getOne(id: String): Entity? {
try {
return dbQuery {
table
.selectAll()
.where { table.primaryKey eq id }
.map(mapper)
.singleOrNull()
}
} catch (e: Exception) {
exposedLogger.error("Erreur lors de la récupération de l'élément issu de ${table.tableName}")
return null
}
}
Error:
Type mismatch. Required: Op Found: Unit
What I want to do:
I would like to create an API with only GET and POST methods. I should be able to get data from all my tables and update some of them. As the GET methods always work in the same way, I wanted to create a generic service with two methods getAll()
and getOne()
. Thanks to this, I could pass them parameters and use them in all my services.