Working with Databases in Kotlin: PostgreSQL and MongoDB

Learn Python easily without overwhelming theory. Solve practical tasks with automatic checking, get hints in Russian, and write code directly in your browser — no installation required.

Start Course

Introduction: Why Kotlin for Working with Databases?

Kotlin is a modern statically typed language that is fully compatible with Java and is actively used for creating backend applications. Thanks to its concise syntax, null-safety, and powerful tools, Kotlin is ideal for working with relational (PostgreSQL) and document-oriented (MongoDB) databases. In this article, we will analyze practical examples of connection, performing CRUD operations, and query optimization.

We will look at two popular libraries: Exposed (for PostgreSQL) and KMongo (for MongoDB). You will learn how to set up the environment, write efficient queries, and avoid common mistakes.

1. Working with PostgreSQL via Exposed

Exposed is an ORM library for Kotlin that provides two APIs: DSL (Domain-Specific Language) for writing queries in SQL style and DAO (Data Access Object) for an object-oriented approach. Let's look at both options.

1.1. Connection and Setup

Add dependencies to build.gradle.kts:

dependencies {    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")}

Connecting to the database:

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

fun main() {    Database.connect(        url = "jdbc:postgresql://localhost:5432/mydb",        driver = "org.postgresql.Driver",        user = "user",        password = "password"    )    println("Connection to PostgreSQL established")}

1.2. Creating a Table and CRUD Operations (DSL)

Let's define the Users table:

import org.jetbrains.exposed.sql.Tableimport org.jetbrains.exposed.sql.insertimport org.jetbrains.exposed.sql.selectAllimport org.jetbrains.exposed.sql.update

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

Let's perform CRUD operations:

transaction {    // Creating a table    SchemaUtils.create(Users)

    // INSERT    Users.insert {        it[name] = "Alexey"        it[email] = "alex@example.com"    }

    // SELECT    val users = Users.selectAll().map {        "${it[Users.id]}: ${it[Users.name]} (${it[Users.email]})"    }    println(users)

    // UPDATE    Users.update({ Users.id eq 1 }) {        it[name] = "Alexey Ivanov"    }

    // DELETE (using deleteWhere)    Users.deleteWhere { Users.id eq 2 }}

1.3. DAO Approach

Let's create an entity and a DAO class:

import org.jetbrains.exposed.dao.IntEntityimport org.jetbrains.exposed.dao.IntEntityClassimport org.jetbrains.exposed.dao.id.EntityIDimport org.jetbrains.exposed.dao.id.IntIdTable

object UsersDAO : IntIdTable() {    val name = varchar("name", 50)    val email = varchar("email", 100)}

class User(id: EntityID<Int>) : IntEntity(id) {    companion object : IntEntityClass<User>(UsersDAO)    var name by UsersDAO.name    var email by UsersDAO.email}

// Usagetransaction {    val newUser = User.new {        name = "Maria"        email = "maria@example.com"    }    println("User created with ID: ${newUser.id.value}")}

2. Working with MongoDB via KMongo

KMongo is a coroutine-oriented driver for MongoDB, built on top of the official Java driver. It supports serialization via kotlinx.serialization and Jackson.