Working with Databases in Kotlin: PostgreSQL and MongoDB for Beginners

Introduction to Working with Databases in Kotlin

Kotlin is a modern statically typed language running on the JVM, which is rapidly gaining popularity due to its conciseness and safety. One of the key skills for any developer is the ability to interact with databases. In this article, we will look at how to connect and perform basic operations (CRUD) with two of the most popular types of databases: relational PostgreSQL and document-oriented MongoDB.

We will use modern libraries that are maximally adapted for idiomatic Kotlin:

\n
• Exposed — an ORM framework from JetBrains for SQL databases (PostgreSQL, MySQL, etc.).
\n
• KMongo — a reactive ODM client for MongoDB, built on coroutines.
\n

The article is intended for beginners: we will cover project setup, database connection, and simple examples of inserting, reading, updating, and deleting data.

1. Project Setup and Adding Dependencies

First, create a new Kotlin project (for example, via IntelliJ IDEA with Gradle or Maven). Add the necessary dependencies to the build.gradle.kts file.

Gradle dependencies for PostgreSQL (Exposed)

dependencies {\n    // Exposed\n    implementation("org.jetbrains.exposed:exposed-core:0.44.1")\n    implementation("org.jetbrains.exposed:exposed-dao:0.44.1")\n    implementation("org.jetbrains.exposed:exposed-jdbc:0.44.1")\n\n    // PostgreSQL Driver\n    implementation("org.postgresql:postgresql:42.7.1")\n\n    // Connection Pool (HikariCP)\n    implementation("com.zaxxer:HikariCP:5.1.0")\n}

Gradle dependencies for MongoDB (KMongo)

dependencies {\n    // KMongo (with coroutines)\n    implementation("org.litote.kmongo:kmongo-coroutine:4.11.0")\n\n    // Ktor Client (if reactive driver is needed)\n    implementation("org.litote.kmongo:kmongo-async:4.11.0")\n}

After synchronizing the project, you can proceed to the connection.

2. Working with PostgreSQL via Exposed

Exposed provides two approaches: DSL (type-safe SQL queries) and DAO (object-relational mapping). We will look at DAO — it is more intuitive for beginners.

Connecting to the Database

import org.jetbrains.exposed.sql.Database\nimport org.jetbrains.exposed.sql.transactions.transaction\nimport com.zaxxer.hikari.HikariConfig\nimport com.zaxxer.hikari.HikariDataSource\n\nfun connectToPostgres() {\n    val config = HikariConfig().apply {\n        jdbcUrl = "jdbc:postgresql://localhost:5432/mydb"\n        driverClassName = "org.postgresql.Driver"\n        username = "user"\n        password = "password"\n        maximumPoolSize = 10\n    }\n    val dataSource = HikariDataSource(config)\n    Database.connect(dataSource)\n    println("Connection to PostgreSQL established!")\n}

Defining a Table and Entity

import org.jetbrains.exposed.dao.id.IntIdTable\nimport org.jetbrains.exposed.dao.IntEntity\nimport org.jetbrains.exposed.dao.IntEntityClass\n\n// Define the table\nobject UsersTable : IntIdTable("users") {\n    val name = varchar("name", 100)\n    val email = varchar("email", 255).uniqueIndex()\n    val age = integer("age").default(18)\n}\n\n// Define the Entity (DAO)\nclass UserEntity(id: EntityID<Int>) : IntEntity(id) {\n    companion object : IntEntityClass<UserEntity>(UsersTable)\n\n    var name by UsersTable.name\n    var email by UsersTable.email\n    var age by UsersTable.age\n}

CRUD Operations

fun performCRUD() {\n    transaction {\n        // Create table (if not exists)\n        SchemaUtils.create(UsersTable)\n\n        // INSERT\n        val newUser = UserEntity.new {\n            name = "Ivan Petrov"\n            email = "ivan@example.com"\n            age = 25\n        }\n        println("Created user