PostgreSQL #

PostgreSQL is the database of choice for the Rust community. If MySQL suits general web applications and Oracle suits legacy enterprise systems, PostgreSQL offers the most complete feature set with an open-source license: rich data types (UUID, arrays, JSONB, enums), built-in full-text search, and capabilities like LISTEN/NOTIFY for real-time events. sqlx supports PostgreSQL very well — including PostgreSQL-exclusive types like UUID, TEXT[] arrays, and JSONB. This article covers the entire PostgreSQL stack from Rust: connections, queries, unique data types, transactions, and patterns that don’t exist in MySQL or MSSQL.

Installation #

[dependencies]
sqlx = { version = "0.7", features = [
    "runtime-tokio-native-tls",
    "postgres",      # PostgreSQL driver
    "macros",        # query! and query_as! macros
    "chrono",        # DateTime, NaiveDateTime
    "uuid",          # UUID type
    "json",          # serde_json::Value for JSONB
] }
tokio = { version = "1", features = ["full"] }
serde = { version = "1", features = ["derive"] }
serde_json = "1"
chrono = { version = "0.4", features = ["serde"] }
uuid = { version = "1", features = ["v4", "serde"] }

Install sqlx-cli for migrations:

cargo install sqlx-cli --no-default-features --features native-tls,postgres

Connections and Connection Pools #

use sqlx::postgres::PgPoolOptions;
use sqlx::PgPool;

async fn buat_pool(database_url: &str) -> Result<PgPool, sqlx::Error> {
    PgPoolOptions::new()
        .max_connections(20)
        .min_connections(2)
        .acquire_timeout(std::time::Duration::from_secs(5))
        .idle_timeout(std::time::Duration::from_secs(300))
        .max_lifetime(std::time::Duration::from_secs(1800))
        .connect(database_url)
        .await
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    // Format: postgres://user:***@host:port/database
    let url = std::env::var("DATABASE_URL")
        .unwrap_or_else(|_| "postgres://postgres:***@localhost/contoh".to_string());

    let pool = buat_pool(&url).await?;

    // Verify and check the version
    let versi: String = sqlx::query_scalar("SELECT version()")
        .fetch_one(&pool)
        .await?;
    println!("PostgreSQL: {}", &versi[..40]);

    // Run migrations at startup
    sqlx::migrate!("./migrations").run(&pool).await?;

    Ok(())
}

Creating Tables #

-- migrations/001_buat_tabel.sql

-- Extension for UUID (available in all modern PostgreSQL)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE pengguna (
    id          BIGSERIAL       PRIMARY KEY,
    uuid        UUID            NOT NULL DEFAULT gen_random_uuid(),
    nama        TEXT            NOT NULL,
    email       TEXT            NOT NULL UNIQUE,
    password    TEXT            NOT NULL,
    peran       TEXT            NOT NULL DEFAULT 'user'
                                CHECK (peran IN ('user', 'admin', 'moderator')),
    aktif       BOOLEAN         NOT NULL DEFAULT TRUE,
    tag         TEXT[]          NOT NULL DEFAULT '{}',  -- array of text
    metadata    JSONB,                                   -- queryable JSON
    dibuat_pada TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    diperbarui  TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE TABLE artikel (
    id              BIGSERIAL       PRIMARY KEY,
    pengguna_id     BIGINT          NOT NULL REFERENCES pengguna(id) ON DELETE CASCADE,
    judul           TEXT            NOT NULL,
    slug            TEXT            NOT NULL UNIQUE,
    konten          TEXT            NOT NULL,
    -- Full-text search vector
    tsv             TSVECTOR        GENERATED ALWAYS AS (
                        to_tsvector('indonesian', judul || ' ' || konten)
                    ) STORED,
    diterbitkan     BOOLEAN         NOT NULL DEFAULT FALSE,
    dibuat_pada     TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_pengguna_email ON pengguna(email);
CREATE INDEX idx_artikel_pengguna ON artikel(pengguna_id);
CREATE INDEX idx_artikel_diterbitkan ON artikel(diterbitkan, dibuat_pada DESC);
-- GIN index for full-text search
CREATE INDEX idx_artikel_tsv ON artikel USING GIN(tsv);
-- GIN index for JSONB queries
CREATE INDEX idx_pengguna_metadata ON pengguna USING GIN(metadata);

-- Trigger for automatic updates to the diperbarui column
CREATE OR REPLACE FUNCTION update_diperbarui()
RETURNS TRIGGER AS $$
BEGIN
    NEW.diperbarui = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_pengguna_diperbarui
    BEFORE UPDATE ON pengguna
    FOR EACH ROW EXECUTE FUNCTION update_diperbarui();

Structs with PostgreSQL-Exclusive Types #

use chrono::{DateTime, Utc};
use serde::{Deserialize, Serialize};
use sqlx::FromRow;
use uuid::Uuid;

#[derive(Debug, FromRow, Serialize, Deserialize, Clone)]
struct Pengguna {
    pub id: i64,                        // BIGSERIAL → i64
    pub uuid: Uuid,                     // UUID → uuid::Uuid
    pub nama: String,                   // TEXT → String
    pub email: String,
    #[serde(skip_serializing)]
    pub password: String,
    pub peran: String,
    pub aktif: bool,                    // BOOLEAN → bool (native, no conversion needed)
    pub tag: Vec<String>,               // TEXT[] → Vec<String>
    pub metadata: Option<serde_json::Value>, // JSONB → Option<serde_json::Value>
    pub dibuat_pada: DateTime<Utc>,     // TIMESTAMPTZ → DateTime<Utc>
    pub diperbarui: DateTime<Utc>,
}

#[derive(Debug, FromRow, Serialize)]
struct Artikel {
    pub id: i64,
    pub pengguna_id: i64,
    pub judul: String,
    pub slug: String,
    pub konten: String,
    pub diterbitkan: bool,
    pub dibuat_pada: DateTime<Utc>,
}

PostgreSQL Type	Rust Type
`SMALLINT`	`i16`
`INTEGER`	`i32`
`BIGINT` / `BIGSERIAL`	`i64`
`REAL`	`f32`
`DOUBLE PRECISION`	`f64`
`BOOLEAN`	`bool`
`TEXT` / `VARCHAR`	`String`
`BYTEA`	`Vec<u8>`
`UUID`	`uuid::Uuid`
`TIMESTAMPTZ`	`chrono::DateTime<Utc>`
`TIMESTAMP`	`chrono::NaiveDateTime`
`DATE`	`chrono::NaiveDate`
`TEXT[]` / `INT[]`	`Vec<String>` / `Vec<i32>`
`JSONB` / `JSON`	`serde_json::Value`
`NUMERIC` / `DECIMAL`	`rust_decimal::Decimal`

Queries and Fetching #

PostgreSQL uses $1, $2 as parameter binding — different from ? (MySQL) and :nama (Oracle). sqlx handles this automatically.

use sqlx::PgPool;

async fn ambil_semua_pengguna(pool: &PgPool) -> Result<Vec<Pengguna>, sqlx::Error> {
    sqlx::query_as!(
        Pengguna,
        r#"SELECT id, uuid, nama, email, password, peran, aktif,
                  tag, metadata, dibuat_pada, diperbarui
           FROM pengguna
           ORDER BY dibuat_pada DESC"#
    )
    .fetch_all(pool)
    .await
}

async fn cari_pengguna_by_uuid(
    pool: &PgPool,
    uuid: Uuid,
) -> Result<Option<Pengguna>, sqlx::Error> {
    sqlx::query_as!(
        Pengguna,
        r#"SELECT id, uuid, nama, email, password, peran, aktif,
                  tag, metadata, dibuat_pada, diperbarui
           FROM pengguna
           WHERE uuid = $1"#,
        uuid  // $1 automatically
    )
    .fetch_optional(pool)
    .await
}

// Query with an array parameter — filter by roles in a list
async fn pengguna_dengan_peran(
    pool: &PgPool,
    peran_list: &[String],
) -> Result<Vec<Pengguna>, sqlx::Error> {
    sqlx::query_as!(
        Pengguna,
        r#"SELECT id, uuid, nama, email, password, peran, aktif,
                  tag, metadata, dibuat_pada, diperbarui
           FROM pengguna
           WHERE peran = ANY($1)"#,
        peran_list as &[String]  // array as a parameter
    )
    .fetch_all(pool)
    .await
}

INSERT with `RETURNING` #

PostgreSQL’s RETURNING is far more flexible than MySQL’s — it can return the entire row, not just the ID:

async fn buat_pengguna(
    pool: &PgPool,
    nama: &str,
    email: &str,
    password_hash: &str,
) -> Result<Pengguna, sqlx::Error> {
    // RETURNING * — return the whole newly inserted row
    sqlx::query_as!(
        Pengguna,
        r#"INSERT INTO pengguna (nama, email, password)
           VALUES ($1, $2, $3)
           RETURNING id, uuid, nama, email, password, peran, aktif,
                     tag, metadata, dibuat_pada, diperbarui"#,
        nama,
        email,
        password_hash
    )
    .fetch_one(pool)
    .await
}

// Insert many rows at once with UNNEST
async fn buat_banyak_pengguna(
    pool: &PgPool,
    nama_list: &[String],
    email_list: &[String],
) -> Result<Vec<i64>, sqlx::Error> {
    // UNNEST turns an array into rows
    let ids: Vec<i64> = sqlx::query_scalar!(
        r#"INSERT INTO pengguna (nama, email, password)
           SELECT nama, email, 'temp_password'
           FROM UNNEST($1::TEXT[], $2::TEXT[]) AS t(nama, email)
           RETURNING id"#,
        nama_list as &[String],
        email_list as &[String]
    )
    .fetch_all(pool)
    .await?;

    Ok(ids)
}

Update, Upsert, and DELETE #

async fn perbarui_pengguna(
    pool: &PgPool,
    id: i64,
    nama: &str,
    tag_baru: &[String],
) -> Result<Option<Pengguna>, sqlx::Error> {
    // RETURNING after UPDATE — no separate SELECT needed
    sqlx::query_as!(
        Pengguna,
        r#"UPDATE pengguna
           SET nama = $1, tag = $2
           WHERE id = $3
           RETURNING id, uuid, nama, email, password, peran, aktif,
                     tag, metadata, dibuat_pada, diperbarui"#,
        nama,
        tag_baru as &[String],
        id
    )
    .fetch_optional(pool)
    .await
}

// INSERT OR UPDATE (UPSERT) with ON CONFLICT
async fn upsert_pengguna(
    pool: &PgPool,
    nama: &str,
    email: &str,
    password_hash: &str,
) -> Result<Pengguna, sqlx::Error> {
    sqlx::query_as!(
        Pengguna,
        r#"INSERT INTO pengguna (nama, email, password)
           VALUES ($1, $2, $3)
           ON CONFLICT (email) DO UPDATE
               SET nama = EXCLUDED.nama,
                   diperbarui = NOW()
           RETURNING id, uuid, nama, email, password, peran, aktif,
                     tag, metadata, dibuat_pada, diperbarui"#,
        nama, email, password_hash
    )
    .fetch_one(pool)
    .await
}

Transactions with SAVEPOINT #

PostgreSQL supports SAVEPOINT — save points within a transaction that can be partially rolled back:

use sqlx::{PgPool, Postgres, Transaction};

async fn proses_batch(
    pool: &PgPool,
    items: &[(&str, &str)],
) -> Result<Vec<Result<i64, String>>, sqlx::Error> {
    let mut tx = pool.begin().await?;
    let mut hasil = Vec::new();

    for (nama, email) in items {
        // Create a savepoint before each item
        sqlx::query("SAVEPOINT sp_item")
            .execute(&mut *tx)
            .await?;

        match sqlx::query_scalar!(
            "INSERT INTO pengguna (nama, email, password)
             VALUES ($1, $2, 'temp')
             RETURNING id",
            nama, email
        )
        .fetch_one(&mut *tx)
        .await
        {
            Ok(id) => {
                // Success — release the savepoint
                sqlx::query("RELEASE SAVEPOINT sp_item")
                    .execute(&mut *tx)
                    .await?;
                hasil.push(Ok(id));
            }
            Err(e) => {
                // Failed — roll back to the savepoint (not the whole transaction)
                sqlx::query("ROLLBACK TO SAVEPOINT sp_item")
                    .execute(&mut *tx)
                    .await?;
                hasil.push(Err(e.to_string()));
            }
        }
    }

    tx.commit().await?;
    Ok(hasil)
}

PostgreSQL-Exclusive Data Types #

Arrays #

async fn contoh_array(pool: &PgPool) -> Result<(), sqlx::Error> {
    // Insert with an array
    sqlx::query!(
        "UPDATE pengguna SET tag = $1 WHERE id = $2",
        &["rust", "backend", "api"] as &[&str],
        1i64
    )
    .execute(pool)
    .await?;

    // Query with the @> array operator (contains)
    let pengguna_rust: Vec<String> = sqlx::query_scalar!(
        "SELECT nama FROM pengguna WHERE tag @> ARRAY['rust']"
    )
    .fetch_all(pool)
    .await?;
    println!("Users tagged 'rust': {:?}", pengguna_rust);

    // Query with ANY
    let aktif_tertentu: Vec<i64> = sqlx::query_scalar!(
        "SELECT id FROM pengguna WHERE $1 = ANY(tag)",
        "admin"
    )
    .fetch_all(pool)
    .await?;

    Ok(())
}

JSONB #

PostgreSQL JSONB can be queried directly from SQL:

async fn contoh_jsonb(pool: &PgPool) -> Result<(), sqlx::Error> {
    // Insert JSONB metadata
    let metadata = serde_json::json!({
        "preferensi": {"tema": "gelap", "bahasa": "id"},
        "profil": {"kota": "Jakarta", "pekerjaan": "Developer"},
        "login_terakhir": "2024-08-24T10:30:00Z"
    });

    sqlx::query!(
        "UPDATE pengguna SET metadata = $1 WHERE id = $2",
        metadata,
        1i64
    )
    .execute(pool)
    .await?;

    // Query a specific field in JSONB with the -> operator
    let tema: Option<serde_json::Value> = sqlx::query_scalar!(
        r#"SELECT metadata->'preferensi'->>'tema' FROM pengguna WHERE id = $1"#,
        1i64
    )
    .fetch_optional(pool)
    .await?
    .flatten();
    println!("Theme: {:?}", tema);

    // Filter by a value inside JSONB with @>
    let dev_jakarta: Vec<String> = sqlx::query_scalar!(
        r#"SELECT nama FROM pengguna
           WHERE metadata @> '{"profil": {"kota": "Jakarta"}}'::jsonb"#
    )
    .fetch_all(pool)
    .await?;
    println!("Developers in Jakarta: {:?}", dev_jakarta);

    Ok(())
}

Full-Text Search #

async fn cari_artikel_fts(
    pool: &PgPool,
    query: &str,
) -> Result<Vec<Artikel>, sqlx::Error> {
    // websearch_to_tsquery: more user-friendly syntax (supports "", -, OR)
    sqlx::query_as!(
        Artikel,
        r#"SELECT id, pengguna_id, judul, slug, konten, diterbitkan, dibuat_pada
           FROM artikel
           WHERE tsv @@ websearch_to_tsquery('indonesian', $1)
             AND diterbitkan = TRUE
           ORDER BY ts_rank(tsv, websearch_to_tsquery('indonesian', $1)) DESC
           LIMIT 20"#,
        query
    )
    .fetch_all(pool)
    .await
}

// Highlight matching words (snippet)
async fn cari_dengan_highlight(
    pool: &PgPool,
    query: &str,
) -> Result<Vec<(String, String)>, sqlx::Error> {
    let rows = sqlx::query!(
        r#"SELECT
            judul,
            ts_headline(
                'indonesian',
                konten,
                websearch_to_tsquery('indonesian', $1),
                'StartSel=<b>, StopSel=</b>, MaxWords=50, MinWords=25'
            ) AS snippet
           FROM artikel
           WHERE tsv @@ websearch_to_tsquery('indonesian', $1)
             AND diterbitkan = TRUE
           ORDER BY ts_rank(tsv, websearch_to_tsquery('indonesian', $1)) DESC
           LIMIT 10"#,
        query
    )
    .fetch_all(pool)
    .await?;

    Ok(rows.into_iter().map(|r| (r.judul, r.snippet.unwrap_or_default())).collect())
}

LISTEN/NOTIFY — Real-Time Events #

PostgreSQL has a built-in pub/sub mechanism useful for real-time notification between processes or connections:

use sqlx::postgres::PgListener;

// Listener — receives notifications
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let url = "postgres://postgres:***@localhost/contoh";
    let pool = PgPoolOptions::new().max_connections(5).connect(url).await?;

    // Listener to receive notifications
    let mut listener = PgListener::connect_with(&pool).await?;
    listener.listen_all(vec!["pesanan_baru", "pembayaran_berhasil"]).await?;

    println!("Listening for notifications...");

    // Spawn a separate task to send notifications
    let pool_kirim = pool.clone();
    tokio::spawn(async move {
        tokio::time::sleep(std::time::Duration::from_secs(1)).await;
        sqlx::query("SELECT pg_notify('pesanan_baru', $1)")
            .bind(r#"{"id": 1001, "total": 150000}"#)
            .execute(&pool_kirim)
            .await
            .unwrap();
        println!("Notification sent");
    });

    // Receive notifications
    while let Ok(notif) = listener.recv().await {
        println!(
            "Notification on channel '{}': {}",
            notif.channel(),
            notif.payload()
        );

        // Parse the JSON payload
        if let Ok(data) = serde_json::from_str::<serde_json::Value>(notif.payload()) {
            println!("Data: {:?}", data);
        }

        break; // for the demo — usually loops forever
    }

    Ok(())
}

Differences from MySQL and MSSQL #

MySQL/MSSQL                          PostgreSQL

Parameter binding
  ? (MySQL)                           $1, $2, $3, ...
  @P1 (MSSQL)

RETURNING after INSERT
  LAST_INSERT_ID() (MySQL)            RETURNING id  (or RETURNING *)
  OUTPUT INSERTED.id (MSSQL)

Auto-increment
  AUTO_INCREMENT (MySQL)              BIGSERIAL  or  GENERATED ALWAYS AS IDENTITY
  IDENTITY(1,1) (MSSQL)

String type
  VARCHAR, TEXT (MySQL)               TEXT  (no limit, or VARCHAR(n))
  NVARCHAR (MSSQL)

Boolean
  TINYINT(1) (MySQL)                  BOOLEAN  (native type)
  BIT (MSSQL)

UUID
  VARCHAR(36) (manual)                UUID  (native type, compact 16 bytes)

JSON
  JSON (not indexable) (MySQL)        JSONB  (indexable with GIN)
  (no JSON type) (MSSQL)

Array
  No array type                       TEXT[], INT[], BOOLEAN[], etc.

Full-text search
  FULLTEXT index (MySQL)              TSVECTOR + GIN index (more powerful)

String case sensitivity
  Case-insensitive (MySQL)            Sensitive (use ILIKE for case-insensitive)

Upsert
  INSERT ... ON DUPLICATE KEY UPDATE  INSERT ... ON CONFLICT DO UPDATE

Schema
  Database = Schema (MySQL)           Separate schemas within a database

Summary #

sqlx + PostgreSQL is the best combination in Rust — native async, compile-time query checking, and full support for PostgreSQL types including UUID, arrays, and JSONB.
$1, $2 parameter binding — PostgreSQL uses numbered parameters, different from ? (MySQL). sqlx handles this automatically from the arguments given.
RETURNING * after INSERT/UPDATE — PostgreSQL can return the entire newly inserted or updated row, so no separate SELECT is needed.
TIMESTAMPTZ for timezone-aware timestamps — always use TIMESTAMPTZ (not TIMESTAMP) to avoid timezone ambiguity in multi-server environments.
Native BOOLEAN — no manual conversion from NUMBER(1) or BIT like in Oracle/MSSQL. Rust’s bool maps directly.
JSONB for semi-structured data — queryable with the ->, ->>, @> operators and indexable with GIN. More powerful than JSON in other databases.
TEXT[] for arrays — maps directly to Vec<String>. Use @> for “contains” and ANY() for “any of them match”.
Built-in full-text search — TSVECTOR + GIN index + websearch_to_tsquery provides very capable FTS without external dependencies.
LISTEN/NOTIFY — PostgreSQL’s built-in real-time pub/sub, useful for event notification between services or cache invalidation without polling.
ON CONFLICT DO UPDATE for upserts — far more elegant than MySQL’s ON DUPLICATE KEY UPDATE because it can use EXCLUDED to reference the values being inserted.

← Previous: Oracle Next: MongoDB →