Skip to content

PostgreSQL

implementation("io.github.smyrgeorge:sqlx4k-postgres:1.17.0")

On native targets the driver is backed by sqlx (Rust), on the JVM by r2dbc-postgresql.

Connecting

val db = PostgreSQL(
    url = "postgresql://localhost:15432/test",
    username = "postgres",
    password = "postgres",
    options = options // see Connection pool
)

In a multiplatform setup, use the postgreSQL(...) function instead, which takes the same parameters.

The URL scheme can be either postgresql:// or postgres://, and each of the URL parts is optional:

postgresql://
postgresql://localhost
postgresql://localhost:5433
postgresql://localhost/mydb

See Connection pool for the pool options.

LISTEN / NOTIFY

// listen suspends while it listens: run it in a coroutine of its own, and cancel that to stop listening.
val listener = launch {
    db.listen("chan0") { notification: Postgres.Notification ->
        println(notification)
    }
}

(1..10).forEach {
    db.notify("chan0", "Hello $it")
    delay(1000)
}

listener.cancel() // Stops listening and closes the listener's connection.

listen also accepts a list of channels.

Notes

  • executeWithResult always reports a null lastInsertId on PostgreSQL: use INSERT ... RETURNING id with fetchAll instead (see Running queries).
  • Set the code generator's dialect to postgresql to enable the PostgreSQL array decoders (see Code generation). The Gradle plugin does this for you.
  • For message queues on top of PostgreSQL, see PGMQ.