Backend tutorial

Ktor REST Apis - Integrating SQL Database using Ktorm

Is it in the Database?

Source: BMC Software

In my previous articles, it is shown that how can you set up, create and test simple static rest apis using Ktor framework. This article is an extension of those three article series and in this one, we’ll explore how can we add database to our apis. We’ll be using SQL database.

In case you want to start from the beginning, please read the following and you won’t be disappointed.

Ktor REST Apis — Part 1 (Project Set up)
Ktor REST Apis — Part 2 (Create Routes)
Ktor REST Apis — Part 3 (Testing Routes)

Database Server Set up

Firstly, we need a local database server up and running to connect to SQL client app. Sql client app will render our database and let us perform queries on it very smoothly. Follow the steps to set it up:

• Download any SQL client app. I used MySqlWorkBench
• Install the app
• Start the server by going into your System preferences and you’ll see an Icon at the bottom. Click on it and start the server.

System Preferences -> MySql

The work bench app would look like this

That’s it for the set up. Let’s move to next step.

Database set up

Because we’re reading this, it implies that we’re aware of basic structure and terminologies of Ktor. So directly setting up our database would be the first step.

Though Ktor provides basic SQL features support but we would use an ORM framework Ktorm.

Ktorm is a lightweight and efficient ORM Framework for Kotlin directly based on pure JDBC and is open source. It provides strong-typed and flexible SQL DSL and convenient sequence APIs to reduce our duplicated effort on database operations. All the SQL statements, of course, are generated automatically.

To add Ktorm, we’ll add the following dependencies in the build.gradle file.

// ktorm
implementation 'org.ktorm:ktorm-core:3.5.0'
implementation 'org.ktorm:ktorm-support-mysql:3.5.0'

That is all we need to add as dependencies.

Now we need to connect to our database. Using Ktorm it is quite straightforward.

fun database() = Database.connect(
    dbUrl,
    user = dbUser,
    password = dbPwd
)

Yes! That is it.

Next, we’ll add some config in our application.conf file as follows:

db {
    config {
        db_url = ${?DB_URL}
        db_user = ${?DB_USER}
        db_pwd = ${?DB_PWD}
    }
}

Why this? Because keeping the database (or any type of) credentials inside the code is not safe. So we access our variables via the config file and values for these config variable comes from the System Environment Variables.

Environment Variable Set up

Let’s add values in our environment as follows.

Step 1: Click on the Application option in toolbar and then go to Edit Configurations.

Step 2: Go to VM options and copy paste the following

Step 3: Now create an object DBHelper.kt as follows

Point to note here that the environment variables can be accessed under the application context. So we’ve created an extension function configureDbVariables for application context and now we can extract the environment variables values as follows

dbUrl = environment.config.propertyOrNull(KEY_DB_URL)?.getString() ?: ""

We would need these values in the very beginning of our application so we’ll call this function from our main module itself.

fun Application.module() {
    configureDbVariables()
}

Alright! Now our set up to access and configure database is complete.

Database Manager

Now we will set up a manager layer for our database so that our database is not exposed from this layer and in future if we want to provide a different implementation for it then we can easily do that too.

It’ll be an interface that our repository will access. We’ll see its implementation later in the article.

Creating the Table

Tables are created as objects in Ktorm which extends Table<Entity>(table_name). But because we’re not using any entity binding here so we pass Nothing.

To create a column, we simply define the datatype with column name as parameter. For instance, int is sql type provided by Ktorm and “id” is the column name.

val id = int("id").primaryKey()

Similarly for strings we use varchar and so on for other data types which you can find on this page.

So, now our table is defined. Now comes the important part of our database integration in which we need to query our database for CRUD operations. Let’s see how can we do that in Ktorm.

Querying the Database

As we discussed above that, we’ll set up our database objects in the very beginning of our app. So by the time we access them in our api end points, the database would be ready.

Database object can be accessed via

private val database = DBHelper.database()

Add an object

We can call the functions on the database object as follows

override fun addUser(user: User): User {
    database.insert(UserTable) {
        set(it.first_name, user.first_name)
        set(it.last_name, user.last_name)
        set(it.dob, user.dob)
        set(it.age, user.age)
    }
    return user
}

It returns us a generated id for this user.

Delete an object

override fun delete(id: Int): Boolean {
    val affectedRow = database.delete(UserTable) { it.id eq id }
    return affectedRow == 1
}

It returns us the number of rows affected by our operations.

Get all objects

override fun getAllUsers(): List<User> {
    val dbUser = database.from(UserTable).select()
    return dbUser.rowSet.asIterable().map { row ->
        User(
            row[UserTable.id] ?: -1,
            "${row[UserTable.first_name] ?: ""}",
            "${row[UserTable.last_name] ?: ""}",
            row[UserTable.age] ?: -1,
            row[UserTable.dob] ?: "",
            row[UserTable.city] ?: ""
        )
    }
}

Here, we call select() functions which returns us a Query object. This query object contains all the rows returned. So we treat it as an iterable and map each UserTable object in our domain User object.

Get Object By Id

override fun getById(id: Int): User? {
    val dbUser = database.from(UserTable).select().where { UserTable.id eq id }
    val row = dbUser.rowSet.asIterable().firstOrNull()
    return row?.let {
        User(
            it[UserTable.id] ?: -1,
            "${it[UserTable.first_name] ?: ""}",
            "${it[UserTable.last_name] ?: ""}",
            it[UserTable.age] ?: -1,
            it[UserTable.dob] ?: "",
            it[UserTable.city] ?: ""
        )
    }?: null
}

This query is similar to Get All Users but here we add a where clause and checks on the first item of the iterable.

And Bamn! We’ve successfully integrated Database with our Rest APIs using Ktorm. Now we can play around with different queries to see all of it in action. I’ll write for more advance part in further articles. But for now this is good to get you started.

GitHub - aqua30/Apis-using-Ktor at database-set-up

That is all for now! Stay tuned!

Connect with me on medium(if the content is helpful to you) or on GitHub and subscribe to email to be in sync for further interesting topics on Android/IOS/Backend/Web.

Until next time…

Cheers!