Ktor Tutorial #8: Relationships and Advanced Queries

A real application has connected data. Users own notes. Notes have tags. Orders belong to customers. These connections are called relationships.

In this tutorial, you will add relationships between tables, write JOIN queries, and build advanced filtering, sorting, and pagination.

Types of Relationships

Type	Example	Implementation
One-to-Many	One user has many notes	Foreign key on notes table
Many-to-Many	Notes have many tags, tags belong to many notes	Join table (note_tags)
One-to-One	One user has one profile	Foreign key with unique constraint

One-to-Many: Users → Notes

A user can have many notes. Each note belongs to one user (or no user).

Table Definition

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)
}

object Notes : Table("notes") {
    val id = integer("id").autoIncrement()
    val title = varchar("title", 255)
    val content = text("content")
    val userId = integer("user_id").references(Users.id).nullable()
    override val primaryKey = PrimaryKey(id)
}

The .references(Users.id) creates a foreign key. .nullable() means a note can exist without a user.

JOIN Query

To get notes with their author names, use a LEFT JOIN:

Notes.leftJoin(Users)
    .selectAll()
    .map { row ->
        NoteResponse(
            id = row[Notes.id],
            title = row[Notes.title],
            content = row[Notes.content],
            userId = row.getOrNull(Notes.userId),
            authorName = row.getOrNull(Users.name)
        )
    }

leftJoin includes notes that have no user. innerJoin would exclude them.

Filter Notes by User

Notes.leftJoin(Users)
    .selectAll()
    .andWhere { Notes.userId eq userId }
    .map(::resultRowToNote)

Many-to-Many: Notes ↔ Tags

Notes can have multiple tags. Tags can belong to multiple notes. This requires a join table.

Table Definitions

object Tags : Table("tags") {
    val id = integer("id").autoIncrement()
    val name = varchar("name", 50).uniqueIndex()
    override val primaryKey = PrimaryKey(id)
}

object NoteTags : Table("note_tags") {
    val noteId = integer("note_id").references(Notes.id)
    val tagId = integer("tag_id").references(Tags.id)
    override val primaryKey = PrimaryKey(noteId, tagId)
}

The NoteTags join table has composite primary key — each note-tag combination is unique.

Getting Tags for a Note

private fun getTagsForNote(noteId: Int): List<String> {
    return (NoteTags innerJoin Tags)
        .selectAll().where { NoteTags.noteId eq noteId }
        .map { it[Tags.name] }
}

Creating a Note with Tags

suspend fun create(request: CreateNoteRequest): NoteResponse = dbQuery {
    // Insert the note
    val result = Notes.insert {
        it[title] = request.title
        it[content] = request.content
        it[userId] = request.userId
    }
    val noteId = result.resultedValues!!.first()[Notes.id]

    // Insert tags (get or create each tag)
    for (tagName in request.tags) {
        val tagId = getOrCreateTag(tagName)
        NoteTags.insert {
            it[NoteTags.noteId] = noteId
            it[NoteTags.tagId] = tagId
        }
    }

    findById(noteId)!!
}

private fun getOrCreateTag(name: String): Int {
    val existing = Tags.selectAll().where { Tags.name eq name }
        .map { it[Tags.id] }
        .singleOrNull()

    return existing ?: Tags.insert {
        it[Tags.name] = name
    }.resultedValues!!.first()[Tags.id]
}

Updating Tags

When updating tags, delete old associations and insert new ones:

if (request.tags != null) {
    NoteTags.deleteWhere { NoteTags.noteId eq id }
    for (tagName in request.tags) {
        val tagId = getOrCreateTag(tagName)
        NoteTags.insert {
            it[NoteTags.noteId] = id
            it[NoteTags.tagId] = tagId
        }
    }
}

Filtering by Tag

val noteIdsWithTag = (NoteTags innerJoin Tags)
    .selectAll().where { Tags.name eq tag }
    .map { it[NoteTags.noteId] }

Notes.selectAll()
    .andWhere { Notes.id inList noteIdsWithTag }

Updated Data Models

Add relationship fields to the response model:

@Serializable
data class NoteResponse(
    val id: Int,
    val title: String,
    val content: String,
    val userId: Int? = null,
    val authorName: String? = null,
    val tags: List<String> = emptyList()
)

@Serializable
data class CreateNoteRequest(
    val title: String,
    val content: String,
    val userId: Int? = null,
    val tags: List<String> = emptyList()
)

Advanced Queries

Sorting

val order = if (sortOrder == "asc") SortOrder.ASC else SortOrder.DESC
val orderColumn = when (sortBy) {
    "title" -> Notes.title
    else -> Notes.id
}

Notes.selectAll()
    .orderBy(orderColumn to order)

Pagination

val page = call.queryParameters["page"]?.toIntOrNull() ?: 1
val size = call.queryParameters["size"]?.toIntOrNull() ?: 10
val offset = ((page - 1) * size).toLong()

Notes.selectAll()
    .limit(size)
    .offset(offset)

Combined Filtering, Sorting, and Pagination

The route handler supports all query parameters:

// GET /api/notes?page=1&size=10&userId=1&tag=kotlin&sortBy=title&sortOrder=asc
get {
    val page = call.queryParameters["page"]?.toIntOrNull() ?: 1
    val size = call.queryParameters["size"]?.toIntOrNull() ?: 10
    val userId = call.queryParameters["userId"]?.toIntOrNull()
    val tag = call.queryParameters["tag"]
    val sortBy = call.queryParameters["sortBy"] ?: "id"
    val sortOrder = call.queryParameters["sortOrder"] ?: "desc"

    val notes = repository.findAll(page, size, userId, tag, sortBy, sortOrder)
    call.respond(notes)
}

Testing Relationships

@Test
fun `create note with tags`() = testApplication {
    application { module() }
    val client = jsonClient()

    val note = client.post("/api/notes") {
        contentType(ContentType.Application.Json)
        setBody(CreateNoteRequest("Tagged", "Content", tags = listOf("kotlin", "ktor")))
    }.body<NoteResponse>()

    assertEquals(2, note.tags.size)
    assertTrue(note.tags.contains("kotlin"))
}

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

    val user = client.post("/api/users") {
        contentType(ContentType.Application.Json)
        setBody(CreateUserRequest("Sam", "sam@example.com"))
    }.body<UserResponse>()

    val note = client.post("/api/notes") {
        contentType(ContentType.Application.Json)
        setBody(CreateNoteRequest("Sam's Note", "Content", userId = user.id))
    }.body<NoteResponse>()

    assertEquals(user.id, note.userId)
    assertEquals("Sam", note.authorName)
}

@Test
fun `filter notes by tag`() = testApplication {
    application { module() }
    val client = jsonClient()

    client.post("/api/notes") {
        contentType(ContentType.Application.Json)
        setBody(CreateNoteRequest("Kotlin Note", "X", tags = listOf("kotlin")))
    }
    client.post("/api/notes") {
        contentType(ContentType.Application.Json)
        setBody(CreateNoteRequest("Python Note", "X", tags = listOf("python")))
    }

    val filtered = client.get("/api/notes?tag=kotlin").body<List<NoteResponse>>()
    assertEquals(1, filtered.size)
    assertEquals("Kotlin Note", filtered.first().title)
}

Deleting with Relationships

When you delete a note that has tags, you must delete the tag associations first. Otherwise, the foreign key constraint will fail:

suspend fun delete(id: Int): Boolean = dbQuery {
    // Delete tag associations first (child records)
    NoteTags.deleteWhere { NoteTags.noteId eq id }
    // Then delete the note itself
    Notes.deleteWhere { Notes.id eq id } > 0
}

For users, you might want to set notes’ userId to null instead of deleting them:

suspend fun deleteUser(id: Int): Boolean = dbQuery {
    // Remove user from their notes (set to null)
    Notes.update({ Notes.userId eq id }) {
        it[userId] = null
    }
    // Then delete the user
    Users.deleteWhere { Users.id eq id } > 0
}

This is called a “soft unlink.” The notes survive, but they no longer belong to anyone.

Performance Tips

N+1 Query Problem

Our getTagsForNote function runs one query per note. If you fetch 100 notes, that is 101 queries (1 for notes + 100 for tags). This is the N+1 problem.

For small datasets, this is fine. For large datasets, you can batch-load tags:

// Fetch all tags for multiple notes in one query
private fun getTagsForNotes(noteIds: List<Int>): Map<Int, List<String>> {
    return (NoteTags innerJoin Tags)
        .selectAll().where { NoteTags.noteId inList noteIds }
        .groupBy(
            keySelector = { it[NoteTags.noteId] },
            valueTransform = { it[Tags.name] }
        )
}

Indexes

Add indexes on columns you frequently filter or join on:

-- In a migration file
CREATE INDEX idx_notes_user_id ON notes(user_id);
CREATE INDEX idx_note_tags_note_id ON note_tags(note_id);
CREATE INDEX idx_note_tags_tag_id ON note_tags(tag_id);

Indexes make queries faster but slow down inserts slightly. Add them for columns that appear in WHERE clauses and JOIN conditions.

Counting Results

For pagination, you often need the total count:

suspend fun findAll(page: Int, size: Int): Pair<List<NoteResponse>, Long> = dbQuery {
    val total = Notes.selectAll().count()
    val notes = Notes.selectAll()
        .limit(size)
        .offset(((page - 1) * size).toLong())
        .map(::resultRowToNote)
    Pair(notes, total)
}

Return both the page of results and the total count so the client knows how many pages exist.

Testing with curl

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

# Create a note linked to user with tags
curl -X POST http://localhost:8080/api/notes \
  -H "Content-Type: application/json" \
  -d '{"title": "My Note", "content": "Hello", "userId": 1, "tags": ["kotlin", "ktor"]}'

# Filter notes by user
curl http://localhost:8080/api/notes?userId=1

# Filter notes by tag
curl http://localhost:8080/api/notes?tag=kotlin

# Sort notes by title ascending
curl "http://localhost:8080/api/notes?sortBy=title&sortOrder=asc"

Exposed Query Cheat Sheet

Query	Exposed DSL
Inner join	`Notes.innerJoin(Users).selectAll()`
Left join	`Notes.leftJoin(Users).selectAll()`
Filter	`.where { Notes.userId eq 1 }`
AND filter	`.andWhere { Notes.title like "%kotlin%" }`
IN list	`.where { Notes.id inList listOf(1, 2, 3) }`
Order by	`.orderBy(Notes.id to SortOrder.DESC)`
Limit	`.limit(10).offset(20)`
Count	`.count()`
Like	`.where { Notes.title like "%search%" }`
Case-insensitive	`.where { Notes.title.lowerCase() like "%search%" }`

Source Code

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

github.com/kemalcodes/ktor-tutorial — Branch: tutorial-08-relationships

What’s Next?

In the next tutorial, we will add file uploads and static file serving. You will learn how to handle multipart form data and store uploaded files.

Ktor Tutorial #9: File Uploads and Static Files

Ktor Tutorial #7: CRUD Operations — Building a REST API
Ktor Tutorial #6: Database Setup — Exposed ORM with H2
SQL Cheat Sheet — Quick reference for SQL queries

Types of Relationships#

One-to-Many: Users → Notes#

Table Definition#

JOIN Query#

Filter Notes by User#

Many-to-Many: Notes ↔ Tags#

Table Definitions#

Getting Tags for a Note#

Creating a Note with Tags#

Updating Tags#

Filtering by Tag#

Updated Data Models#

Advanced Queries#

Sorting#

Pagination#

Combined Filtering, Sorting, and Pagination#

Testing Relationships#

Deleting with Relationships#

Performance Tips#

N+1 Query Problem#

Indexes#

Counting Results#

Testing with curl#

Exposed Query Cheat Sheet#

Source Code#

What’s Next?#

Related Articles#