Kotlin: JDBC

Connects, creates a todo table if it does not exist, inserts a row and counts open rows. The code is the same against the original database and against MachSQL; only the address changes.

The only change

JDBC_URL=jdbc:postgresql://old-postgres:5432/app
JDBC_URL=jdbc:postgresql://machsql-host:5432/app

Nothing else changes: not the code, not the driver, not its version, not the SQL.

Versions

• Kotlin 2.4.21 on Java 25 (LTS)
• PostgreSQL JDBC driver 42.7.14
• Gradle 9.8

Run

export JDBC_URL=jdbc:postgresql://machsql-host:5432/app DB_PASSWORD=...
gradle run

Expected output: inserted todo <id>, <n> open.

Source

The program as it is kept and tested in the Mach Database for PostgreSQL repository; highlighted lines read the connection setting.

src/main/kotlin/Main.kt

// Kotlin over JDBC with the PostgreSQL driver.
import java.sql.DriverManager

fun main() {
    val url = System.getenv("JDBC_URL")
    DriverManager.getConnection(url, System.getenv("DB_USER") ?: "app", System.getenv("DB_PASSWORD")).use { conn ->
        conn.createStatement().use { it.execute("CREATE TABLE IF NOT EXISTS todo (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL, done boolean NOT NULL DEFAULT false)") }
        val id = conn.prepareStatement("INSERT INTO todo (title) VALUES (?) RETURNING id").use { ps ->
            ps.setString(1, "write the examples")
            ps.executeQuery().use { rs -> rs.next(); rs.getLong(1) }
        }
        val open = conn.createStatement().use { st ->
            st.executeQuery("SELECT count(*) FROM todo WHERE NOT done").use { rs -> rs.next(); rs.getLong(1) }
        }
        println("inserted todo $id, $open open")
    }
}

build.gradle.kts

plugins {
    kotlin("jvm") version "2.4.21"
    application
}

repositories {
    mavenCentral()
}

dependencies {
    implementation("org.postgresql:postgresql:42.7.14")
}

kotlin {
    jvmToolchain(25)
}

application {
    mainClass = "MainKt"
}

settings.gradle.kts

rootProject.name = "kotlin-jdbc"