Working with Databases in Kotlin: PostgreSQL and MongoDB for Beginners

Introduction to Working with Databases in Kotlin

Kotlin is a modern programming language actively used for server-side applications, Android development, and backend services. One of the key skills for any developer is the ability to work with databases. In this article, we will cover how to connect to the relational database PostgreSQL and the document-oriented MongoDB using Kotlin. The material is aimed at beginners: we will look at driver installation, connection setup, and performing basic CRUD operations (create, read, update, delete).

1. Environment Setup and Dependencies

For this work, you will need a Kotlin project (for example, using Gradle or Maven). We will use Gradle — the most popular build tool in the Kotlin ecosystem. Add the following dependencies to the build.gradle.kts file:

dependencies {    // For PostgreSQL    implementation("org.jetbrains.exposed:exposed-core:0.44.1")    implementation("org.jetbrains.exposed:exposed-dao:0.44.1")    implementation("org.jetbrains.exposed:exposed-jdbc:0.44.1")    implementation("org.postgresql:postgresql:42.7.1")

    // For MongoDB    implementation("org.mongodb:mongodb-driver-kotlin-sync:4.11.1")    implementation("org.mongodb:bson-kotlinx:4.11.1")}

Exposed is a lightweight ORM library for Kotlin that simplifies working with SQL databases. For MongoDB, we use the official Kotlin driver from MongoDB.

2. Working with PostgreSQL via Exposed

2.1. Connecting to the Database

First, let's create an object for connecting to PostgreSQL. Make sure the PostgreSQL server is running on your computer and a database (e.g., testdb) has been created.

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

fun main() {    // Connection to local DB    Database.connect(        url = "jdbc:postgresql://localhost:5432/testdb",        driver = "org.postgresql.Driver",        user = "postgres",        password = "your_password"    )

    println("Connection to PostgreSQL established successfully")}

After connecting, you can execute SQL queries. Exposed uses a DSL (Domain-Specific Language) for writing queries in Kotlin.

2.2. Creating a Table and CRUD Operations

Let's define a simple Users table with fields id, name, and email:

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

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

    override val primaryKey = PrimaryKey(id)}

fun main() {    Database.connect(...)

    transaction {        // Create table        SchemaUtils.create(Users)

        // INSERT (create)        Users.insert {            it[name] = "Ivan Petrov"            it[email] = "ivan@example.com"        }

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

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

        // DELETE (delete)        Users.deleteWhere { Users.id eq 1 }    }}

As you can see, the code is very similar to regular SQL but written in pure Kotlin. Exposed automatically escapes names and protects against SQL injection.

3. Working with MongoDB via Kotlin Driver

3.1. Connecting to MongoDB

MongoDB is a NoSQL database that stores data in JSON-like documents. For connection, we use the synchronous driver:

import com.mongodb.kotlin.client.coroutine.MongoClientimport kot