Ktor Tutorial #7: CRUD Operations — Building a REST API

We have a database. We have JSON serialization. Now it is time to build a real REST API.

In this tutorial, you will build full CRUD (Create, Read, Update, Delete) operations for notes and users. You will learn the repository pattern, proper HTTP status codes, validation, and how to organize a production-ready API.

The Repository Pattern

In the previous tutorial, we put database queries directly in route handlers. This works for small projects but becomes messy as your API grows.

The repository pattern separates database logic from route logic:

Route Handler → Repository → Database
   (HTTP)        (Logic)      (SQL)

Each layer has one job:

Route Handler — Reads HTTP requests, validates input, sends responses
Repository — Runs database queries, returns domain objects
Database — Stores and retrieves data

Creating a Repository

class NoteRepository {

    private fun resultRowToNote(row: ResultRow) = NoteResponse(
        id = row[Notes.id],
        title = row[Notes.title],
        content = row[Notes.content]
    )

    suspend fun findAll(page: Int = 1, size: Int = 10): List<NoteResponse> = dbQuery {
        Notes.selectAll()
            .orderBy(Notes.id to SortOrder.DESC)
            .limit(size)
            .offset(((page - 1) * size).toLong())
            .map(::resultRowToNote)
    }

    suspend fun findById(id: Int): NoteResponse? = dbQuery {
        Notes.selectAll().where { Notes.id eq id }
            .map(::resultRowToNote)
            .singleOrNull()
    }

    suspend fun create(request: CreateNoteRequest): NoteResponse = dbQuery {
        val result = Notes.insert {
            it[title] = request.title
            it[content] = request.content
        }
        resultRowToNote(result.resultedValues!!.first())
    }

    suspend fun update(id: Int, request: UpdateNoteRequest): NoteResponse? = dbQuery {
        val updated = Notes.update({ Notes.id eq id }) {
            request.title?.let { value -> it[title] = value }
            request.content?.let { value -> it[content] = value }
        }
        if (updated == 0) null
        else Notes.selectAll().where { Notes.id eq id }
            .map(::resultRowToNote)
            .single()
    }

    suspend fun delete(id: Int): Boolean = dbQuery {
        Notes.deleteWhere { Notes.id eq id } > 0
    }
}

Every method is a suspend function that uses dbQuery to run on the IO dispatcher. The repository handles all database logic and returns simple data classes.

Adding Users to the Database

Let’s add a users table:

object Users : Table("users") {
    val id = integer("id").autoIncrement()
    val name = varchar("name", 100)
    val email = varchar("email", 255).uniqueIndex()

    override val primaryKey = PrimaryKey(id)
}

The .uniqueIndex() on email prevents duplicate emails at the database level.

User Repository

class UserRepository {

    private fun resultRowToUser(row: ResultRow) = UserResponse(
        id = row[Users.id],
        name = row[Users.name],
        email = row[Users.email]
    )

    suspend fun findAll(name: String? = null): List<UserResponse> = dbQuery {
        val query = if (name != null) {
            Users.selectAll().where {
                Users.name.lowerCase() like "%${name.lowercase()}%"
            }
        } else {
            Users.selectAll()
        }
        query.orderBy(Users.id to SortOrder.ASC).map(::resultRowToUser)
    }

    suspend fun findById(id: Int): UserResponse? = dbQuery {
        Users.selectAll().where { Users.id eq id }
            .map(::resultRowToUser)
            .singleOrNull()
    }

    suspend fun findByEmail(email: String): UserResponse? = dbQuery {
        Users.selectAll().where { Users.email eq email }
            .map(::resultRowToUser)
            .singleOrNull()
    }

    suspend fun create(request: CreateUserRequest): UserResponse = dbQuery {
        val result = Users.insert {
            it[Users.name] = request.name
            it[Users.email] = request.email
        }
        resultRowToUser(result.resultedValues!!.first())
    }

    suspend fun delete(id: Int): Boolean = dbQuery {
        Users.deleteWhere { Users.id eq id } > 0
    }
}

Injecting Repositories into Routes

Pass repositories to route functions as parameters:

// plugins/Routing.kt
fun Application.configureRouting() {
    val noteRepository = NoteRepository()
    val userRepository = UserRepository()

    routing {
        get("/") { call.respondText("Hello, Ktor!") }
        get("/health") { call.respondText("OK") }

        route("/api") {
            userRoutes(userRepository)
            noteRoutes(noteRepository)
        }
    }
}

Routes accept the repository:

fun Route.noteRoutes(repository: NoteRepository) {
    route("/notes") {
        get {
            val notes = repository.findAll()
            call.respond(notes)
        }
        // ...
    }
}

This makes routes easy to test — you can pass a mock repository in tests.

HTTP Status Codes

Using the right status codes makes your API professional and predictable:

Operation	Success Code	Description
GET (list)	200 OK	Returns list of items
GET (single)	200 OK	Returns one item
POST (create)	201 Created	Item created successfully
PUT (update)	200 OK	Returns updated item
DELETE	204 No Content	Item deleted, no body
Validation error	400 Bad Request	Invalid input
Not found	404 Not Found	Item does not exist
Duplicate	409 Conflict	Resource already exists

Example: Create User with Conflict Detection

post {
    val request = call.receive<CreateUserRequest>()

    if (request.name.isBlank() || request.email.isBlank()) {
        call.respond(
            HttpStatusCode.BadRequest,
            ErrorResponse("Name and email required", 400)
        )
        return@post
    }

    // Check if email already exists
    val existing = repository.findByEmail(request.email)
    if (existing != null) {
        call.respond(
            HttpStatusCode.Conflict,
            ErrorResponse("Email already exists", 409)
        )
        return@post
    }

    val user = repository.create(request)
    call.respond(HttpStatusCode.Created, user)
}

Complete Note Routes

fun Route.noteRoutes(repository: NoteRepository) {
    route("/notes") {
        get {
            val page = call.queryParameters["page"]?.toIntOrNull() ?: 1
            val size = call.queryParameters["size"]?.toIntOrNull() ?: 10
            val notes = repository.findAll(page, size)
            call.respond(notes)
        }

        get("/{id}") {
            val id = call.parameters["id"]?.toIntOrNull()
            if (id == null) {
                call.respond(HttpStatusCode.BadRequest, ErrorResponse("Invalid ID", 400))
                return@get
            }
            val note = repository.findById(id)
            if (note == null) {
                call.respond(HttpStatusCode.NotFound, ErrorResponse("Note not found", 404))
                return@get
            }
            call.respond(note)
        }

        post {
            val request = call.receive<CreateNoteRequest>()
            if (request.title.isBlank()) {
                call.respond(HttpStatusCode.BadRequest, ErrorResponse("Title required", 400))
                return@post
            }
            val note = repository.create(request)
            call.respond(HttpStatusCode.Created, note)
        }

        put("/{id}") {
            val id = call.parameters["id"]?.toIntOrNull()
            if (id == null) {
                call.respond(HttpStatusCode.BadRequest, ErrorResponse("Invalid ID", 400))
                return@put
            }
            val request = call.receive<UpdateNoteRequest>()
            val note = repository.update(id, request)
            if (note == null) {
                call.respond(HttpStatusCode.NotFound, ErrorResponse("Note not found", 404))
                return@put
            }
            call.respond(note)
        }

        delete("/{id}") {
            val id = call.parameters["id"]?.toIntOrNull()
            if (id == null) {
                call.respond(HttpStatusCode.BadRequest, ErrorResponse("Invalid ID", 400))
                return@delete
            }
            if (repository.delete(id)) {
                call.respond(HttpStatusCode.NoContent)
            } else {
                call.respond(HttpStatusCode.NotFound, ErrorResponse("Note not found", 404))
            }
        }
    }
}

Testing with curl

Start the server and test each endpoint:

# Create a note
curl -X POST http://localhost:8080/api/notes \
  -H "Content-Type: application/json" \
  -d '{"title": "My Note", "content": "Hello World"}'

# List all notes
curl http://localhost:8080/api/notes

# Get a single note
curl http://localhost:8080/api/notes/1

# Update a note
curl -X PUT http://localhost:8080/api/notes/1 \
  -H "Content-Type: application/json" \
  -d '{"title": "Updated Title"}'

# Delete a note
curl -X DELETE http://localhost:8080/api/notes/1

# Create a user
curl -X POST http://localhost:8080/api/users \
  -H "Content-Type: application/json" \
  -d '{"name": "Alex", "email": "alex@example.com"}'

Automated Tests

@Test
fun `create and get user`() = testApplication {
    application { module() }
    val client = jsonClient()

    val created = client.post("/api/users") {
        contentType(ContentType.Application.Json)
        setBody(CreateUserRequest("Alex", "alex@example.com"))
    }.body<UserResponse>()
    assertEquals("Alex", created.name)

    val fetched = client.get("/api/users/${created.id}").body<UserResponse>()
    assertEquals("Alex", fetched.name)
}

@Test
fun `create user with duplicate email returns 409`() = testApplication {
    application { module() }
    val client = jsonClient()

    client.post("/api/users") {
        contentType(ContentType.Application.Json)
        setBody(CreateUserRequest("Alex", "alex@example.com"))
    }

    val response = client.post("/api/users") {
        contentType(ContentType.Application.Json)
        setBody(CreateUserRequest("Alex2", "alex@example.com"))
    }
    assertEquals(HttpStatusCode.Conflict, response.status)
}

@Test
fun `update note preserves unchanged fields`() = testApplication {
    application { module() }
    val client = jsonClient()

    val created = client.post("/api/notes") {
        contentType(ContentType.Application.Json)
        setBody(CreateNoteRequest("Original", "Original content"))
    }.body<NoteResponse>()

    val updated = client.put("/api/notes/${created.id}") {
        contentType(ContentType.Application.Json)
        setBody(UpdateNoteRequest(title = "Updated Title"))
    }.body<NoteResponse>()

    assertEquals("Updated Title", updated.title)
    assertEquals("Original content", updated.content)
}

Run all tests:

./gradlew test

Error Handling Patterns

There are several patterns for handling errors in a CRUD API. Here are the most common:

Pattern 1: Return Early

Check for errors at each step and return early:

get("/{id}") {
    val id = call.parameters["id"]?.toIntOrNull()
    if (id == null) {
        call.respond(HttpStatusCode.BadRequest, ErrorResponse("Invalid ID", 400))
        return@get
    }
    val note = repository.findById(id)
    if (note == null) {
        call.respond(HttpStatusCode.NotFound, ErrorResponse("Not found", 404))
        return@get
    }
    call.respond(note)
}

This is simple and explicit. You see every check in the code.

Pattern 2: Custom Exceptions

Throw custom exceptions and let StatusPages handle them:

class NotFoundException(message: String) : RuntimeException(message)
class BadRequestException(message: String) : RuntimeException(message)
class ConflictException(message: String) : RuntimeException(message)

// In StatusPages plugin
install(StatusPages) {
    exception<NotFoundException> { call, cause ->
        call.respond(HttpStatusCode.NotFound, ErrorResponse(cause.message ?: "Not found", 404))
    }
    exception<BadRequestException> { call, cause ->
        call.respond(HttpStatusCode.BadRequest, ErrorResponse(cause.message ?: "Bad request", 400))
    }
    exception<ConflictException> { call, cause ->
        call.respond(HttpStatusCode.Conflict, ErrorResponse(cause.message ?: "Conflict", 409))
    }
}

// In route handler
get("/{id}") {
    val id = call.parameters["id"]?.toIntOrNull()
        ?: throw BadRequestException("Invalid ID")
    val note = repository.findById(id)
        ?: throw NotFoundException("Note $id not found")
    call.respond(note)
}

This is cleaner for large APIs. The route handler focuses on the happy path.

Pattern 3: Result Type

Use Kotlin’s Result or a custom sealed class:

sealed class ApiResult<out T> {
    data class Success<T>(val data: T) : ApiResult<T>()
    data class Error(val message: String, val code: Int) : ApiResult<Nothing>()
}

This is useful for service layer logic where you want to return errors without throwing exceptions.

Pagination Response

For production APIs, include pagination metadata in the response:

@Serializable
data class PaginatedResponse<T>(
    val data: List<T>,
    val page: Int,
    val size: Int,
    val total: Long
)

Then use it in routes:

get {
    val page = call.queryParameters["page"]?.toIntOrNull() ?: 1
    val size = call.queryParameters["size"]?.toIntOrNull() ?: 10
    val notes = repository.findAll(page, size)
    val total = repository.count()
    call.respond(PaginatedResponse(notes, page, size, total))
}

The client gets:

{
    "data": [...],
    "page": 1,
    "size": 10,
    "total": 42
}

Now the client knows there are 42 total items and can calculate the number of pages (5 pages at 10 per page).

Project Structure So Far

src/main/kotlin/com/kemalcodes/
├── Application.kt
├── db/
│   ├── DatabaseFactory.kt
│   └── Tables.kt
├── models/
│   ├── Note.kt
│   └── User.kt
├── plugins/
│   ├── Database.kt
│   ├── Routing.kt
│   ├── Serialization.kt
│   └── StatusPages.kt
└── repository/
    ├── NoteRepository.kt
    └── UserRepository.kt

Clean separation. Each layer has its own directory. Easy to navigate, easy to test.

Source Code

You can find the source code for this tutorial on GitHub:

github.com/kemalcodes/ktor-tutorial — Branch: tutorial-07-crud

What’s Next?

In the next tutorial, we will add relationships between tables — connecting notes to users with foreign keys, JOIN queries, and advanced filtering.

Ktor Tutorial #8: Relationships and Advanced Queries

Ktor Tutorial #6: Database Setup — Exposed ORM with H2
Ktor Tutorial #5: Serialization — JSON with kotlinx.serialization
SQL Cheat Sheet — Quick reference for SQL queries

The Repository Pattern#

Creating a Repository#

Adding Users to the Database#

User Repository#

Injecting Repositories into Routes#

HTTP Status Codes#

Example: Create User with Conflict Detection#

Complete Note Routes#

Testing with curl#

Automated Tests#

Error Handling Patterns#

Pattern 1: Return Early#

Pattern 2: Custom Exceptions#

Pattern 3: Result Type#

Pagination Response#

Project Structure So Far#

Source Code#

What’s Next?#

Related Articles#