Working with Databases in Kotlin: PostgreSQL and MongoDB

Introduction: Why Kotlin and Databases Are the Perfect Pair

Kotlin is a modern statically typed language that runs on top of the JVM. Thanks to its concise syntax, null safety, and full compatibility with Java, Kotlin has become one of the fastest-growing languages for backend development. In this article, we will explore how to connect and perform CRUD operations with two popular DBMS: the relational PostgreSQL and the document-oriented MongoDB.

We will cover two main approaches: using the Exposed library for SQL and the KMongo framework for working with MongoDB. All code examples are written in pure Kotlin and are ready to run.

1. Working with PostgreSQL via the Exposed Library

1.1. Setting Up Dependencies

First, add the following dependencies to your build.gradle.kts file:

dependencies {    implementation("org.jetbrains.exposed:exposed-core:0.44.0")    implementation("org.jetbrains.exposed:exposed-dao:0.44.0")    implementation("org.jetbrains.exposed:exposed-jdbc:0.44.0")    implementation("org.postgresql:postgresql:42.6.0")    implementation("com.zaxxer:HikariCP:5.0.1")}

HikariCP is a high-performance connection pool that we will use to manage database connections.

1.2. Connecting to the Database

Let's create an object to manage the connection:

import com.zaxxer.hikari.HikariConfigimport com.zaxxer.hikari.HikariDataSourceimport org.jetbrains.exposed.sql.Database

object DatabaseFactory {    fun init() {        val config = HikariConfig().apply {            driverClassName = "org.postgresql.Driver"            jdbcUrl = "jdbc:postgresql://localhost:5432/my_db"            username = "user"            password = "password"            maximumPoolSize = 10            isAutoCommit = false        }

        val dataSource = HikariDataSource(config)        Database.connect(dataSource)    }}

1.3. Defining Tables and CRUD Operations

Exposed uses a DSL style for describing tables. Let's define a simple users table:

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

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

    override val primaryKey = PrimaryKey(id)}

// Insert datafun insertUser(name: String, email: String) {    transaction {        Users.insert {            it[Users.name] = name            it[Users.email] = email        }    }}

// Read all usersfun getAllUsers(): List<ResultRow> {    return transaction {        Users.selectAll().toList()    }}

// Update emailfun updateUserEmail(id: Int, newEmail: String) {    transaction {        Users.update({ Users.id eq id }) {            it[email] = newEmail        }    }}

// Delete userfun deleteUser(id: Int) {    transaction {        Users.deleteWhere { Users.id eq id }    }}

1.4. Exposed Features

• Transactions: All operations are performed inside a transaction block.
• Type Safety: Errors in SQL queries are caught at compile time.
• Migrations: Exposed supports automatic table creation via SchemaUtils.create(Users).

2. Working with MongoDB via KMongo

2.1. Setting Up Dependencies

Add to build.gradle.kts:

dependencies {    implementation("org.litote.kmongo:kmongo-coroutine:4.9.0")    implementation("org.litote.kmongo:kmongo-id:4.9.0")}<