Working with Databases in Kotlin: PostgreSQL and MongoDB in Practice

Introduction: Why Kotlin is an Excellent Choice for Working with Databases

Kotlin, being a modern and concise programming language, provides developers with powerful tools for interacting with various databases. Thanks to full compatibility with Java and its own ecosystem of libraries, Kotlin allows writing safe, expressive, and performant code for working with both relational (SQL) and document-oriented (NoSQL) storage systems.

In this article, we will use practical examples to cover connecting to and performing CRUD operations (Create, Read, Update, Delete) for two popular database management systems: PostgreSQL (relational) and MongoDB (document-based). You will learn how to set up a project, use modern libraries (Exposed for SQL and KMongo for MongoDB), and write clean code taking into account Kotlin's features.

1. Working with PostgreSQL in Kotlin: Using the Exposed Library

There are several approaches to working with relational databases in Kotlin. We will look at the Exposed library — a lightweight ORM wrapper written in Kotlin that provides a type-safe DSL (Domain-Specific Language) for building SQL queries.

1.1. Adding Dependencies

Add the following dependencies to your build.gradle.kts file:

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.6.0")    implementation("org.jetbrains.kotlinx:kotlinx-coroutines-core:1.7.3")}

1.2. Creating a Table and Connecting to the Database

Let's define a "User" entity and the corresponding table in code:

import org.jetbrains.exposed.sql.*import org.jetbrains.exposed.sql.transactions.transactionimport org.jetbrains.exposed.sql.SqlExpressionBuilder.eq

// Define the tableobject Users : Table() {    val id = integer("id").autoIncrement()    val name = varchar("name", 255)    val email = varchar("email", 255).uniqueIndex()

    override val primaryKey = PrimaryKey(id)}

// Data class for the userdata class User(val id: Int, val name: String, val email: String)

fun main() {    // Connect to the database    Database.connect(        url = "jdbc:postgresql://localhost:5432/mydb",        driver = "org.postgresql.Driver",        user = "postgres",        password = "password"    )

    // Create the table (if it does not exist)    transaction {        SchemaUtils.create(Users)    }}

1.3. CRUD Operations

Now let's implement the basic operations for creating, reading, updating, and deleting records:

fun createUser(name: String, email: String): User {    return transaction {        val insertId = Users.insert {            it[Users.name] = name            it[Users.email] = email        } get Users.id

        User(            id = insertId,            name = name,            email = email        )    }}

fun readUserById(id: Int): User? {    return transaction {        Users.selectAll().where { Users.id eq id }            .singleOrNull()            ?.let { row ->                User(                    id = row[Users.id],                    name = row[Users.name],                    email = row[Users.email]                )            }    }}

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

fun deleteUser(id: Int) {    transaction {        Users.deleteWhere { Users.id eq id }    }}

Thanks to Exposed, the Kotlin code is concise and type-safe. Errors in column or table names are caught