↓Skip to main content

Connect Ktor Server to AWS Relational Database

·5 mins
Ktor Server Api Kotlin Rds Aws Database
Table of Contents

Setting up AWS RDS Database #

The database creation screen is quite straight forward. I recommend checking different configuration settings and choosing the best options for your use case.

In my case I just needed to try out the AWS RDS, so I tried to keep my configuration minimal as possible.

  • Database creation method: Standard
  • Engine: PostgreSQL, default engine selection - latest
  • Template: Free Tier, I am just trying things out. Availability zone selection is disabled for free tier.
  • DB Identifier: this is the identifier that will be written in AWS RDS console
  • Set credentials: self managed, choose master username and master password. AWS Secret manager has additional charges so I didn’t used it.
  • Instance configuration: burstable instances are the only selection in free tier, honestly it is more than enough in my use case.
  • Storage: 20GiB is enough for my use case. I disabled storage autoscaling in additional storage configuration.
  • Connectivity: don’t connect to an EC2 compute resource. Create a new VPC. Public Access: Yes. Create a new security group.
  • Database Authentication: password authentication
  • Disabled monitoring
  • all the other configurations are left with default values.
  • Click create database and you are done 🎉

App Runner Configuration #

Environment variables #

Add host, user, password details as Environment variables. We are going to use those values in the docker-compose.yaml to create a container image.

Network configuration #

Set outgoing network to custom VPC.

Create a new VPC connector, select the VPC, subnet and security groups.

Database Connection Implementation #

All there is left is adding database connection code to our ktor server.

Dependencies #

Add dependencies to version catalog

[versions]
exposed = "0.49.0"
postgres = "42.7.3"
hikari = "5.0.0"
h2 = "2.2.224"

[libraries]
exposed-core = { module = "org.jetbrains.exposed:exposed-core", version.ref = "exposed" }
exposed-dao = { module = "org.jetbrains.exposed:exposed-dao", version.ref = "exposed" }
exposed-jdbc = { module = "org.jetbrains.exposed:exposed-jdbc", version.ref = "exposed" }
postgresql = { module = "org.postgresql:postgresql", version.ref = "postgres" }
hikari = { module = "com.zaxxer:HikariCP", version.ref = "hikari" }
h2 = { module = "com.h2database:h2", version.ref = "h2" }

then add them to your build.gradle.kts file.

dependencies {
    implementation(libs.postgresql)
    implementation(libs.hikari)
    implementation(libs.exposed.core)
    implementation(libs.exposed.dao)
    implementation(libs.exposed.jdbc)
    implementation(libs.h2)
}

Ktor App Configuration #

We are going to get db related values from environment variables. Add DB related values to application configuration yaml.

(see. https://ktor.io/docs/server-configuration-file.html#environment-variables)

ktor:
    application:
        modules:
            - com.aslansari.ApplicationKt.module
    db:
        host: $POSTGRES_HOST
        user: $POSTGRES_USER
        password: $POSTGRES_PASSWORD
        pool: $POSTGRES_POOL
    deployment:
        port: 8080

Database Implementation #

Purpose of this example is just to show how to connect to a DB and may not be the best practice on how to manage database connections.

Once we add those values to the configuration file, we can fetch those from ApplicationEnvironment object. (see https://ktor.io/docs/server-configuration-file.html#read-configuration-in-code)

object DBFactory {
    fun init(environment: ApplicationEnvironment) {
        val dbUrl = environment.config.property("ktor.db.host").getString()
        val dbUser = environment.config.property("ktor.db.user").getString()
        val dbPassword = environment.config.property("ktor.db.password").getString()
        val dbPool = environment.config.property("ktor.db.pool").getString().toInt()

        val dataSource = hikari(dbUrl, dbUser, dbPassword, dbPool)
        Database.connect(dataSource)

        transaction {
            SchemaUtils.createMissingTablesAndColumns(
                IngredientsTable,
            )
        }
    }

    fun getIngredientDao(): IngredientDao = IngredientDaoImpl()
}

I used hikari to manage connection pool to database. Here is the hikari function. You can skip this part and connect to the database directly if you want a single connection.

fun hikari(
    dbUrl: String,
    dbUser: String,
    dbPassword: String,
    dbPool: Int,
): HikariDataSource {
    val config = HikariConfig().apply {
        jdbcUrl = dbUrl
        username = dbUser
        password = dbPassword
        maximumPoolSize = dbPool
        isAutoCommit = false
        transactionIsolation = "TRANSACTION_REPEATABLE_READ"
        driverClassName = "org.postgresql.Driver"
    }
    config.validate()
    return HikariDataSource(config)
}

Like I mentioned, this guide is not for detailed instructions on how to use exposed.

https://ktor.io/docs/server-integrate-database.html

I am going to show you a basic exposed table configuration.

object IngredientsTable : IntIdTable("ingredients") {
    val name = varchar("name", 128)
    val description = text("description")
    val shortDescription = text("short_description")
    val imageUrl = varchar("image_url", 128)
}

class IngredientEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<IngredientEntity>(IngredientsTable)

    var name by IngredientsTable.name
    var description by IngredientsTable.description
    var shortDescription by IngredientsTable.shortDescription
    var imageUrl by IngredientsTable.imageUrl
}

fun IngredientEntity.toExternalModel() = Ingredient(
    id = id.value,
    name = name,
    description = description,
    shortDescription = shortDescription,
    imageUrl = imageUrl,
)

// we are going to use this model as return value of the endpoint
@Serializable
data class Ingredient(
    val id: Int? = null,
    val name: String,
    val description: String,
    val shortDescription: String,
    val imageUrl: String,
)

Define a DAO interface to execute database queries.

interface IngredientDao {
    suspend fun getAllIngredients(): List<Ingredient>
}

And for the implementation, you can use IngredientEntity

class IngredientDaoImpl : IngredientDao {
    override suspend fun getAllIngredients(): List<Ingredient> = query {
        IngredientEntity.wrapRows(
            IngredientsTable.selectAll()
        ).map(IngredientEntity::toExternalModel)
    }
}

We are done with database implementations. Now you can pass dao interface to your route code in ktor and use it.

fun Route.ingredients(
    ingredientDao: IngredientDao,
) {
    route("/api/v1/ingredients") {
        get {
            val ingredients = ingredientDao.getAllIngredients()
            val response = BaseResponse.success(ingredients)
            call.respond(response)
        }
    }
}

All there is left to do is pushing the updated ktor app to ECR as container image and deploy the latest image to App Runner.

We are successfully implemented a RDS connection from our ktor app that runs in App Runner. Next steps are adding new endpoints for fetching an ingredient with id, adding a new ingredient and updating an existing ingredient, deleting an ingredient. Basic CRUD stuff. But this is a public api, we can’t just allow anyone to add a new ingredient or delete existing ones. For that we are going to add authentication in following articles.

Thanks for reading so far. I hope you like the article. Let me know if you have any feedback for this article or suggestions for next ones.