Building RESTful CRUD APIs using Ktor and PostgreSQL

Building RESTful CRUD APIs using Ktor and PostgreSQL is a powerful way to create scalable and efficient web applications. Below is a step-by-step guide to building a simple RESTful API that interacts with a PostgreSQL database, where you can create, read, update, and delete records.

1. Setting Up the Project

Start by setting up a Ktor project. Here’s an outline of the steps:

1. Add dependencies to build.gradle.kts (Kotlin DSL):

plugins {
    kotlin("jvm") version "1.8.20"
    application
}

dependencies {
    implementation("io.ktor:ktor-server-core:2.2.4")
    implementation("io.ktor:ktor-server-netty:2.2.4")
    implementation("io.ktor:ktor-serialization-kotlinx-json:2.2.4")
    implementation("org.jetbrains.exposed:exposed-core:0.41.1")
    implementation("org.jetbrains.exposed:exposed-dao:0.41.1")
    implementation("org.jetbrains.exposed:exposed-jdbc:0.41.1")
    implementation("org.postgresql:postgresql:42.5.0")
    implementation("io.ktor:ktor-server-host-common:2.2.4")
    implementation("io.ktor:ktor-server-auth:2.2.4")
    implementation("io.ktor:ktor-server-content-negotiation:2.2.4")
    implementation("io.ktor:ktor-serialization-jackson:2.2.4")
}

application {
    mainClass.set("com.example.ApplicationKt")
}

2. Configure the Database:

In your application.conf, define PostgreSQL connection details:

ktor {
    deployment {
        port = 8080
    }
    application {
        modules = [ com.example.Application ]
    }
}

postgres {
    url = "jdbc:postgresql://localhost:5432/mydb"
    driver = "org.postgresql.Driver"
    user = "postgres"
    password = "yourpassword"
}

3. Install Ktor features:

• Content Negotiation: For serializing and deserializing JSON data.
• Exposed: For interacting with PostgreSQL using ORM.
• Authentication: (optional) If needed for securing the API.

2. Setup Database Connection

In your Application.kt, initialize the database connection using Exposed.

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction
import io.ktor.application.*
import io.ktor.features.ContentNegotiation
import io.ktor.jackson.jackson
import io.ktor.server.engine.embeddedServer
import io.ktor.server.netty.Netty
import io.ktor.routing.*
import io.ktor.response.respond
import io.ktor.request.receive
import io.ktor.application.install

fun initDb() {
    Database.connect(
        url = "jdbc:postgresql://localhost:5432/mydb",
        driver = "org.postgresql.Driver",
        user = "postgres",
        password = "yourpassword"
    )
}

fun Application.module() {
    install(ContentNegotiation) {
        jackson { }
    }

    // Initialize Database Connection
    initDb()

    routing {
        // Define CRUD API routes here
    }
}

3. Define the Data Model

Create a data class and a corresponding Exposed table to represent the User entity.

import org.jetbrains.exposed.sql.Table

// Define the Users table
object Users : Table() {
    val id = integer("id").autoIncrement().primaryKey() // Primary Key
    val name = varchar("name", 255)
    val email = varchar("email", 255)
}

// Data class for User entity
data class User(val id: Int, val name: String, val email: String)

4. Implement CRUD Operations

Now, let’s implement the CRUD operations: Create, Read, Update, and Delete.

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction

// Create a new user
fun createUser(name: String, email: String): Int {
    return transaction {
        Users
            .insertAndGetId {
                it[Users.name] = name
                it[Users.email] = email
            }.value
    }
}

// Get all users
fun getUsers(): List<User> {
    return transaction {
        Users.selectAll().map {
            User(it[Users.id], it[Users.name], it[Users.email])
        }
    }
}

// Get user by ID
fun getUserById(id: Int): User? {
    return transaction {
        Users.select { Users.id eq id }
            .map { User(it[Users.id], it[Users.name], it[Users.email]) }
            .singleOrNull()
    }
}

// Update a user by ID
fun updateUser(id: Int, name: String, email: String): Boolean {
    return transaction {
        val updatedRows = Users.update({ Users.id eq id }) {
            it[Users.name] = name
            it[Users.email] = email
        }
        updatedRows > 0
    }
}

// Delete user by ID
fun deleteUser(id: Int): Boolean {
    return transaction {
        val deletedRows = Users.deleteWhere { Users.id eq id }
        deletedRows > 0
    }
}

5. Define API Routes

Now, define the routes for the RESTful API to handle the HTTP requests.

import io.ktor.application.*
import io.ktor.routing.*
import io.ktor.http.HttpStatusCode
import io.ktor.server.application.call
import io.ktor.server.response.respond
import io.ktor.server.request.receive
import io.ktor.server.plugins.statuspages.StatusPages

fun Application.module() {
    install(ContentNegotiation) {
        jackson { }
    }

    // Initialize Database Connection
    initDb()

    routing {
        route("/users") {
            // Create User
            post {
                val user = call.receive<User>()
                val id = createUser(user.name, user.email)
                call.respond(HttpStatusCode.Created, "User created with ID: $id")
            }

            // Get All Users
            get {
                val users = getUsers()
                call.respond(users)
            }
        }

        // Get User by ID
        get("/users/{id}") {
            val id = call.parameters["id"]?.toIntOrNull()
            if (id != null) {
                val user = getUserById(id)
                if (user != null) {
                    call.respond(user)
                } else {
                    call.respond(HttpStatusCode.NotFound, "User not found")
                }
            } else {
                call.respond(HttpStatusCode.BadRequest, "Invalid user ID")
            }
        }

        // Update User by ID
        put("/users/{id}") {
            val id = call.parameters["id"]?.toIntOrNull()
            val user = call.receive<User>()
            if (id != null && updateUser(id, user.name, user.email)) {
                call.respond(HttpStatusCode.OK, "User updated")
            } else {
                call.respond(HttpStatusCode.NotFound, "User not found")
            }
        }

        // Delete User by ID
        delete("/users/{id}") {
            val id = call.parameters["id"]?.toIntOrNull()
            if (id != null && deleteUser(id)) {
                call.respond(HttpStatusCode.OK, "User deleted")
            } else {
                call.respond(HttpStatusCode.NotFound, "User not found")
            }
        }
    }
}

6. Run the Application

To run your Ktor application:

import io.ktor.server.engine.embeddedServer
import io.ktor.server.netty.Netty

fun main() {
    embeddedServer(Netty, port = 8080, module = Application::module).start(wait = true)
}

7. Example API Usage

Now, you can test your API with the following endpoints:

1. Create User: POST /users with JSON body:

   { "name": "John", "email": "john@example.com" }

2. Get All Users: GET /users

3. Get User by ID: GET /users/{id}

4. Update User: PUT /users/{id} with JSON body:

   { "name": "John Updated", "email": "john_updated@example.com" }

5. Delete User: DELETE /users/{id}

Conclusion

You now have a fully functional Ktor application with PostgreSQL using Exposed ORM for database interaction. This setup supports RESTful CRUD operations for managing User entities. You can extend this setup by adding authentication, validation, error handling, and more advanced features depending on your needs.