PostgreSQL #

PostgreSQL adalah sistem manajemen basis data relasional open-source yang paling kaya fitur — mendukung tipe data JSON native, array, full-text search, CTE (Common Table Expression), window function, dan banyak ekstensi seperti PostGIS untuk data geospasial. PostgreSQL sangat populer di kalangan developer Kotlin modern, terutama untuk backend yang butuh keandalan tinggi tanpa biaya lisensi. Dibanding MySQL, PostgreSQL lebih ketat dalam standar SQL, lebih kaya dalam tipe data, dan memiliki sistem transaksi yang lebih canggih. Dibanding Oracle, PostgreSQL gratis dan open-source dengan fitur yang semakin mendekati. Artikel ini membahas koneksi, CRUD, tipe data khas PostgreSQL, Exposed ORM, dan fitur-fitur yang menjadikan PostgreSQL pilihan utama untuk proyek Kotlin production.

Setup dan Dependensi #

// build.gradle.kts
dependencies {
    // PostgreSQL JDBC Driver (PgJDBC)
    implementation("org.postgresql:postgresql:42.7.3")

    // HikariCP
    implementation("com.zaxxer:HikariCP:5.1.0")

    // Exposed ORM
    implementation("org.jetbrains.exposed:exposed-core:0.49.0")
    implementation("org.jetbrains.exposed:exposed-dao:0.49.0")
    implementation("org.jetbrains.exposed:exposed-jdbc:0.49.0")
    implementation("org.jetbrains.exposed:exposed-java-time:0.49.0")
    implementation("org.jetbrains.exposed:exposed-json:0.49.0")  // untuk JSONB

    // Flyway
    implementation("org.flywaydb:flyway-core:10.10.0")
    implementation("org.flywaydb:flyway-database-postgresql:10.10.0")

    // Serialization untuk JSONB
    implementation("org.jetbrains.kotlinx:kotlinx-serialization-json:1.6.3")
}

Koneksi dengan HikariCP #

import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource

object DatabasePostgres {
    private val dataSource: HikariDataSource by lazy {
        val config = HikariConfig().apply {
            // Format: jdbc:postgresql://HOST:PORT/DATABASE
            jdbcUrl = buildString {
                val host = System.getenv("DB_HOST") ?: "localhost"
                val port = System.getenv("DB_PORT") ?: "5432"
                val db   = System.getenv("DB_NAME") ?: "myapp"
                append("jdbc:postgresql://$host:$port/$db")
                append("?sslmode=prefer")           // prefer SSL jika tersedia
                append("&currentSchema=public")     // schema default
                append("&ApplicationName=MyKotlinApp")  // nama aplikasi untuk pg_stat_activity
            }
            driverClassName = "org.postgresql.Driver"
            username = System.getenv("DB_USER") ?: "postgres"
            password = System.getenv("DB_PASSWORD") ?: "postgres"

            // Pool configuration
            minimumIdle = 2
            maximumPoolSize = 10
            idleTimeout = 300_000
            connectionTimeout = 30_000
            maxLifetime = 1_800_000
            poolName = "PG-Pool"
            connectionTestQuery = "SELECT 1"

            // PostgreSQL-specific optimizations
            addDataSourceProperty("prepareThreshold", "5")       // threshold prepared statement
            addDataSourceProperty("preparedStatementCacheQueries", "256")
            addDataSourceProperty("cachePrepStmts", "true")
        }
        HikariDataSource(config)
    }

    fun <T> gunakan(blok: (java.sql.Connection) -> T): T =
        dataSource.connection.use(blok)

    val ds get() = dataSource  // untuk Exposed

    fun tutup() { if (!dataSource.isClosed) dataSource.close() }
}

Tipe Data Khas PostgreSQL #

PostgreSQL punya tipe data yang jauh lebih kaya dibanding database lain. Ini adalah keunggulan utamanya:

-- Schema produk dengan tipe khas PostgreSQL
CREATE TABLE produk (
    id              BIGSERIAL PRIMARY KEY,        -- auto-increment, setara SERIAL tapi 8 byte
    nama            TEXT NOT NULL,                -- TEXT tanpa batas panjang (vs VARCHAR)
    deskripsi       TEXT,
    harga           NUMERIC(15,2) NOT NULL,
    stok            INTEGER NOT NULL DEFAULT 0,
    kategori        TEXT,
    tag             TEXT[] DEFAULT '{}',          -- ARRAY dari text
    metadata        JSONB,                        -- JSON binary, bisa diindex dan diquery
    aktif           BOOLEAN NOT NULL DEFAULT TRUE, -- BOOLEAN native (bukan 0/1)
    dibuat_pada     TIMESTAMPTZ DEFAULT NOW(),    -- TIMESTAMP WITH TIMEZONE
    diperbarui_pada TIMESTAMPTZ DEFAULT NOW(),

    CONSTRAINT ck_harga_positif CHECK (harga >= 0),
    CONSTRAINT ck_stok_nn CHECK (stok >= 0)
);

-- Index reguler
CREATE INDEX idx_produk_kategori ON produk(kategori);
CREATE INDEX idx_produk_aktif ON produk(aktif) WHERE aktif = TRUE;  -- partial index

-- Index untuk JSONB
CREATE INDEX idx_produk_metadata ON produk USING GIN(metadata);

-- Index untuk array
CREATE INDEX idx_produk_tag ON produk USING GIN(tag);

-- Trigger untuk update timestamp
CREATE OR REPLACE FUNCTION update_diperbarui_pada()
RETURNS TRIGGER AS $$
BEGIN
    NEW.diperbarui_pada = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_produk_update
BEFORE UPDATE ON produk
FOR EACH ROW EXECUTE FUNCTION update_diperbarui_pada();

CRUD dengan JDBC #

import kotlinx.serialization.Serializable
import kotlinx.serialization.json.Json
import java.math.BigDecimal
import java.sql.Array
import java.sql.ResultSet
import java.sql.Types

@Serializable
data class MetadataProduk(
    val berat: Double? = null,
    val dimensi: String? = null,
    val garansiTahun: Int? = null,
    val merekAsli: String? = null
)

data class Produk(
    val id: Long = 0,
    val nama: String,
    val deskripsi: String? = null,
    val harga: BigDecimal,
    val stok: Int = 0,
    val kategori: String? = null,
    val tag: List<String> = emptyList(),
    val metadata: MetadataProduk? = null,
    val aktif: Boolean = true
)

class ProdukRepositoryPostgres {

    private val json = Json { ignoreUnknownKeys = true }

    private fun ResultSet.toProduk(): Produk {
        // Baca array PostgreSQL
        val tagArray = getArray("tag")
        val tagList = (tagArray?.array as? Array<String>)?.toList() ?: emptyList()

        // Baca JSONB
        val metadataJson = getString("metadata")
        val metadata = metadataJson?.let {
            runCatching { json.decodeFromString<MetadataProduk>(it) }.getOrNull()
        }

        return Produk(
            id        = getLong("id"),
            nama      = getString("nama"),
            deskripsi = getString("deskripsi"),
            harga     = getBigDecimal("harga"),
            stok      = getInt("stok"),
            kategori  = getString("kategori"),
            tag       = tagList,
            metadata  = metadata,
            aktif     = getBoolean("aktif")  // PostgreSQL BOOLEAN → Kotlin Boolean langsung
        )
    }

    // INSERT
    fun simpan(produk: Produk): Produk {
        val sql = """
            INSERT INTO produk (nama, deskripsi, harga, stok, kategori, tag, metadata, aktif)
            VALUES (?, ?, ?, ?, ?, ?, ?::jsonb, ?)
            RETURNING id
        """.trimIndent()

        return DatabasePostgres.gunakan { koneksi ->
            koneksi.prepareStatement(sql).use { stmt ->
                stmt.setString(1, produk.nama)
                stmt.setString(2, produk.deskripsi)
                stmt.setBigDecimal(3, produk.harga)
                stmt.setInt(4, produk.stok)
                stmt.setString(5, produk.kategori)

                // Set array PostgreSQL
                val tagArray = koneksi.createArrayOf("text", produk.tag.toTypedArray())
                stmt.setArray(6, tagArray)

                // Set JSONB — cast eksplisit dengan ::jsonb
                val metadataStr = produk.metadata?.let { json.encodeToString(MetadataProduk.serializer(), it) }
                stmt.setString(7, metadataStr)

                stmt.setBoolean(8, produk.aktif)

                stmt.executeQuery().use { rs ->
                    if (rs.next()) produk.copy(id = rs.getLong(1))
                    else throw RuntimeException("Gagal mendapat ID yang di-generate")
                }
            }
        }
    }

    // SELECT dengan filter dinamis
    fun cariSemua(
        kategori: String? = null,
        tagFilter: String? = null,     // cari produk yang memiliki tag ini
        hargaMaks: BigDecimal? = null,
        aktifSaja: Boolean = true,
        halaman: Int = 1,
        ukuran: Int = 20
    ): List<Produk> {
        val offset = (halaman - 1) * ukuran
        val kondisi = mutableListOf<String>()
        val params = mutableListOf<Any?>()

        if (aktifSaja) kondisi.add("aktif = TRUE")
        if (kategori != null) {
            kondisi.add("kategori = ?")
            params.add(kategori)
        }
        if (tagFilter != null) {
            kondisi.add("? = ANY(tag)")  // ANY untuk cek elemen dalam array
            params.add(tagFilter)
        }
        if (hargaMaks != null) {
            kondisi.add("harga <= ?")
            params.add(hargaMaks)
        }

        val where = if (kondisi.isNotEmpty()) "WHERE ${kondisi.joinToString(" AND ")}" else ""
        val sql = """
            SELECT * FROM produk
            $where
            ORDER BY nama
            LIMIT ? OFFSET ?
        """.trimIndent()

        return DatabasePostgres.gunakan { koneksi ->
            koneksi.prepareStatement(sql).use { stmt ->
                var idx = 1
                params.forEach { p ->
                    when (p) {
                        is String -> stmt.setString(idx++, p)
                        is BigDecimal -> stmt.setBigDecimal(idx++, p)
                        else -> stmt.setObject(idx++, p)
                    }
                }
                stmt.setInt(idx++, ukuran)
                stmt.setInt(idx, offset)

                stmt.executeQuery().use { rs ->
                    buildList { while (rs.next()) add(rs.toProduk()) }
                }
            }
        }
    }

    // UPSERT dengan ON CONFLICT DO UPDATE — fitur sangat berguna PostgreSQL
    fun upsert(produk: Produk): Produk {
        val sql = """
            INSERT INTO produk (nama, harga, stok, kategori, aktif)
            VALUES (?, ?, ?, ?, ?)
            ON CONFLICT (nama) DO UPDATE
            SET harga  = EXCLUDED.harga,
                stok   = EXCLUDED.stok,
                aktif  = EXCLUDED.aktif
            RETURNING id, nama, harga, stok, kategori, aktif
        """.trimIndent()

        return DatabasePostgres.gunakan { koneksi ->
            koneksi.prepareStatement(sql).use { stmt ->
                stmt.setString(1, produk.nama)
                stmt.setBigDecimal(2, produk.harga)
                stmt.setInt(3, produk.stok)
                stmt.setString(4, produk.kategori)
                stmt.setBoolean(5, produk.aktif)

                stmt.executeQuery().use { rs ->
                    if (rs.next()) {
                        produk.copy(
                            id    = rs.getLong("id"),
                            nama  = rs.getString("nama"),
                            harga = rs.getBigDecimal("harga"),
                            stok  = rs.getInt("stok")
                        )
                    } else produk
                }
            }
        }
    }
}

Fitur Khas PostgreSQL #

Query JSONB #

PostgreSQL memungkinkan query langsung ke dalam field JSONB:

// Cari produk dengan garansi lebih dari 1 tahun (dari field JSONB)
fun cariDenganGaransi(minTahun: Int): List<Produk> {
    val sql = """
        SELECT * FROM produk
        WHERE aktif = TRUE
          AND (metadata->>'garansiTahun')::int >= ?
        ORDER BY nama
    """.trimIndent()

    return DatabasePostgres.gunakan { koneksi ->
        koneksi.prepareStatement(sql).use { stmt ->
            stmt.setInt(1, minTahun)
            stmt.executeQuery().use { rs ->
                buildList { while (rs.next()) add(rs.toProduk()) }
            }
        }
    }
}

// Update field JSONB secara parsial menggunakan jsonb_set
fun tambahMetadata(id: Long, kunci: String, nilai: String) {
    val sql = """
        UPDATE produk
        SET metadata = jsonb_set(
            COALESCE(metadata, '{}'::jsonb),
            ?::text[],    -- path sebagai array
            ?::jsonb      -- nilai baru
        )
        WHERE id = ?
    """.trimIndent()

    DatabasePostgres.gunakan { koneksi ->
        koneksi.prepareStatement(sql).use { stmt ->
            stmt.setString(1, "{$kunci}")   // path: {key}
            stmt.setString(2, "\"$nilai\"") // nilai JSON
            stmt.setLong(3, id)
            stmt.executeUpdate()
        }
    }
}

Array Operations #

// Tambahkan tag ke produk yang sudah ada
fun tambahTag(id: Long, tagBaru: String) {
    val sql = """
        UPDATE produk
        SET tag = array_append(tag, ?)
        WHERE id = ? AND NOT (? = ANY(tag))  -- hindari duplikat
    """.trimIndent()

    DatabasePostgres.gunakan { koneksi ->
        koneksi.prepareStatement(sql).use { stmt ->
            stmt.setString(1, tagBaru)
            stmt.setLong(2, id)
            stmt.setString(3, tagBaru)
            stmt.executeUpdate()
        }
    }
}

// Hapus tag dari produk
fun hapusTag(id: Long, tag: String) {
    val sql = "UPDATE produk SET tag = array_remove(tag, ?) WHERE id = ?"

    DatabasePostgres.gunakan { koneksi ->
        koneksi.prepareStatement(sql).use { stmt ->
            stmt.setString(1, tag)
            stmt.setLong(2, id)
            stmt.executeUpdate()
        }
    }
}

Full-Text Search #

// Full-text search menggunakan tsvector dan tsquery
fun cariFTS(kueriTeks: String, limit: Int = 10): List<Produk> {
    val sql = """
        SELECT *,
               ts_rank(
                   to_tsvector('indonesian', nama || ' ' || COALESCE(deskripsi, '')),
                   plainto_tsquery('indonesian', ?)
               ) AS rank
        FROM produk
        WHERE aktif = TRUE
          AND to_tsvector('indonesian', nama || ' ' || COALESCE(deskripsi, ''))
              @@ plainto_tsquery('indonesian', ?)
        ORDER BY rank DESC
        LIMIT ?
    """.trimIndent()

    return DatabasePostgres.gunakan { koneksi ->
        koneksi.prepareStatement(sql).use { stmt ->
            stmt.setString(1, kueriTeks)
            stmt.setString(2, kueriTeks)
            stmt.setInt(3, limit)
            stmt.executeQuery().use { rs ->
                buildList { while (rs.next()) add(rs.toProduk()) }
            }
        }
    }
}

CTE (Common Table Expression) #

// Laporan aggregasi dengan CTE
fun laporanPerKategori(): List<Map<String, Any?>> {
    val sql = """
        WITH statistik AS (
            SELECT
                kategori,
                COUNT(*) AS jumlah_produk,
                SUM(stok) AS total_stok,
                AVG(harga) AS rata_harga,
                MIN(harga) AS harga_termurah,
                MAX(harga) AS harga_termahal
            FROM produk
            WHERE aktif = TRUE
            GROUP BY kategori
        )
        SELECT *
        FROM statistik
        ORDER BY jumlah_produk DESC
    """.trimIndent()

    return DatabasePostgres.gunakan { koneksi ->
        koneksi.createStatement().use { stmt ->
            stmt.executeQuery(sql).use { rs ->
                buildList {
                    while (rs.next()) {
                        add(mapOf(
                            "kategori"      to rs.getString("kategori"),
                            "jumlahProduk"  to rs.getInt("jumlah_produk"),
                            "totalStok"     to rs.getInt("total_stok"),
                            "rataHarga"     to rs.getBigDecimal("rata_harga"),
                            "hargaTermurah" to rs.getBigDecimal("harga_termurah"),
                            "hargaTermahal" to rs.getBigDecimal("harga_termahal")
                        ))
                    }
                }
            }
        }
    }
}

Exposed ORM dengan PostgreSQL #

Exposed mendukung PostgreSQL dengan sangat baik, termasuk tipe data khas seperti JSONB dan array:

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.json.jsonb
import org.jetbrains.exposed.sql.transactions.transaction
import org.jetbrains.exposed.dao.*
import org.jetbrains.exposed.dao.id.*

fun inisialisasiExposedPostgres() {
    Database.connect(DatabasePostgres.ds)

    transaction {
        SchemaUtils.create(TabelProdukPg)
    }
}

// Definisi tabel dengan tipe PostgreSQL
object TabelProdukPg : LongIdTable("produk") {
    val nama      = text("nama")
    val deskripsi = text("deskripsi").nullable()
    val harga     = decimal("harga", 15, 2)
    val stok      = integer("stok").default(0)
    val kategori  = text("kategori").nullable()
    val aktif     = bool("aktif").default(true)
    val dibuatPada = kotlinx.datetime.Instant::class.let {
        // Gunakan timestamp dengan timezone
        varchar("dibuat_pada", 50).default(org.jetbrains.exposed.sql.javatime.CurrentTimestamp.toString())
    }
}

// CRUD dengan Exposed
fun tambahProdukExposed(nama: String, harga: java.math.BigDecimal, stok: Int): Long {
    return transaction {
        TabelProdukPg.insertAndGetId {
            it[TabelProdukPg.nama]  = nama
            it[TabelProdukPg.harga] = harga
            it[TabelProdukPg.stok]  = stok
        }.value
    }
}

fun cariProdukExposed(kategori: String? = null): List<ResultRow> {
    return transaction {
        TabelProdukPg
            .select { TabelProdukPg.aktif eq true }
            .apply {
                if (kategori != null) {
                    andWhere { TabelProdukPg.kategori eq kategori }
                }
            }
            .orderBy(TabelProdukPg.nama)
            .toList()
    }
}

Migrasi dengan Flyway #

import org.flywaydb.core.Flyway

fun jalankanMigrasiPostgres() {
    val flyway = Flyway.configure()
        .dataSource(DatabasePostgres.ds)
        .locations("classpath:db/migration/postgres")
        .defaultSchema("public")
        .baselineOnMigrate(true)
        .validateOnMigrate(true)
        .load()

    val hasil = flyway.migrate()
    println("PostgreSQL Migration: ${hasil.migrationsExecuted} migrasi dijalankan")
}

File migrasi PostgreSQL:

-- V1__create_produk.sql
CREATE TABLE IF NOT EXISTS produk (
    id              BIGSERIAL PRIMARY KEY,
    nama            TEXT NOT NULL,
    deskripsi       TEXT,
    harga           NUMERIC(15,2) NOT NULL CHECK (harga >= 0),
    stok            INTEGER NOT NULL DEFAULT 0 CHECK (stok >= 0),
    kategori        TEXT,
    tag             TEXT[] DEFAULT '{}',
    metadata        JSONB,
    aktif           BOOLEAN NOT NULL DEFAULT TRUE,
    dibuat_pada     TIMESTAMPTZ DEFAULT NOW(),
    diperbarui_pada TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_produk_kategori ON produk(kategori);
CREATE INDEX IF NOT EXISTS idx_produk_aktif ON produk(aktif) WHERE aktif = TRUE;
CREATE INDEX IF NOT EXISTS idx_produk_tag ON produk USING GIN(tag);
CREATE INDEX IF NOT EXISTS idx_produk_metadata ON produk USING GIN(metadata);
CREATE INDEX IF NOT EXISTS idx_produk_fts ON produk
    USING GIN(to_tsvector('indonesian', nama || ' ' || COALESCE(deskripsi, '')));

-- V2__add_update_trigger.sql
CREATE OR REPLACE FUNCTION update_diperbarui_pada()
RETURNS TRIGGER AS $$
BEGIN
    NEW.diperbarui_pada = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_produk_update
BEFORE UPDATE ON produk
FOR EACH ROW EXECUTE FUNCTION update_diperbarui_pada();

Tips Performa PostgreSQL #

// 1. EXPLAIN ANALYZE untuk analisis query
fun analyzeQuery(sql: String) {
    DatabasePostgres.gunakan { koneksi ->
        koneksi.createStatement().use { stmt ->
            stmt.executeQuery("EXPLAIN ANALYZE $sql").use { rs ->
                while (rs.next()) println(rs.getString(1))
            }
        }
    }
}

// 2. Connection pool sizing — rumus umum:
// maxPoolSize = (jumlah_core * 2) + jumlah_disk_spindle
// Untuk PostgreSQL, biasanya cukup 10-20 koneksi per aplikasi

// 3. Prepared statement caching — PostgreSQL akan cache execution plan
// setelah dipanggil sebanyak prepareThreshold (default 5)
// addDataSourceProperty("prepareThreshold", "5")

// 4. COPY untuk bulk insert (lebih cepat dari batch INSERT)
fun bulkInsert(produk: List<Produk>) {
    val copySQL = "COPY produk (nama, harga, stok, kategori) FROM STDIN WITH CSV"

    DatabasePostgres.gunakan { koneksi ->
        val pgConn = koneksi.unwrap(org.postgresql.PGConnection::class.java)
        val copyManager = pgConn.copyAPI

        val csvData = buildString {
            produk.forEach { p ->
                appendLine("${p.nama},${p.harga},${p.stok},${p.kategori ?: ""}")
            }
        }

        copyManager.copyIn(copySQL, java.io.StringReader(csvData))
        println("Berhasil insert ${produk.size} produk via COPY")
    }
}

Ringkasan #

PostgreSQL adalah pilihan terbaik untuk proyek Kotlin baru — open-source, kaya fitur, mendukung JSON native, array, full-text search, dan standar SQL yang lebih ketat dari MySQL.
BIGSERIAL atau GENERATED ALWAYS AS IDENTITY — keduanya valid untuk auto-increment. SERIAL menggunakan sequence di balik layar; IDENTITY adalah standar SQL yang lebih modern.
BOOLEAN native, TIMESTAMPTZ, TEXT — tidak perlu TINYINT(1) untuk boolean, DATETIME tanpa timezone, atau khawatir panjang VARCHAR. PostgreSQL punya tipe yang lebih tepat.
JSONB bukan JSON — selalu gunakan JSONB (binary, terindex, bisa diquery) bukan JSON (teks biasa). JSONB lebih lambat saat insert tapi jauh lebih cepat saat query.
ON CONFLICT DO UPDATE untuk upsert — fitur upsert PostgreSQL sangat ekspresif: tentukan kolom konflik dan kolom yang di-update jika ada konflik. Lebih bersih dari INSERT IGNORE atau REPLACE MySQL.
GIN index untuk JSONB dan array — gunakan CREATE INDEX ... USING GIN untuk field JSONB dan array agar query @>, ?, dan ANY() tetap cepat saat data besar.
Full-text search native — PostgreSQL punya sistem full-text search yang sangat baik dengan to_tsvector dan to_tsquery. Tidak perlu Elasticsearch untuk search dasar.
COPY untuk bulk insert — untuk memasukkan ribuan baris sekaligus, COPY FROM STDIN adalah cara tercepat — jauh lebih efisien dari batch INSERT.

← Sebelumnya: Oracle Berikutnya: MongoDB →