Menerapkan operasi Firebase SQL Connect menggunakan SQL native

Panduan untuk menulis operasi Firebase SQL Connect dengan SQL, bukan GraphQL. page_type: guide announcement: > SQL Native tersedia sebagai Pratinjau fitur, yang berarti fitur tersebut tidak tunduk pada SLA atau kebijakan penghentian layanan apa pun dan dapat berubah sehingga tidak kompatibel dengan versi sebelumnya. Jika Anda menggunakan fitur ini dengan prosedur tersimpan atau fungsi yang menjalankan SQL dinamis, ikuti praktik terbaik keamanan yang dijelaskan di bagian bawah halaman ini.

Firebase SQL Connect menawarkan beberapa cara untuk berinteraksi dengan database Cloud SQL:

• GraphQL Native: Tentukan jenis di schema.gql dan SQL Connect menerjemahkan operasi GraphQL Anda ke dalam SQL. Ini adalah pendekatan standar, yang menawarkan struktur strongly-typed dan diterapkan skemanya. Sebagian besar dokumentasi SQL Connect di luar halaman ini membahas opsi ini. Jika memungkinkan, Anda harus menggunakan metode ini untuk memanfaatkan keamanan jenis penuh dan dukungan alat.
• Direktif @view: Tentukan jenis GraphQL di schema.gql yang didukung oleh pernyataan SQL SELECT kustom. Hal ini berguna untuk membuat tampilan hanya baca, berjenis kuat berdasarkan logika SQL yang kompleks. Jenis ini dapat dikueri seperti jenis reguler. Lihat @view.
• SQL Native: Sematkan pernyataan SQL langsung dalam operasi bernama di file .gql menggunakan kolom root khusus. Hal ini memberikan fleksibilitas maksimum dan kontrol langsung, terutama untuk operasi yang tidak didukung oleh GraphQL standar, memanfaatkan fitur khusus database, atau menggunakan ekstensi PostgreSQL. Tidak seperti GraphQL dan direktif @view, SQL native tidak memberikan output yang sangat berjenis.

Panduan ini berfokus pada opsi SQL Native.

Kasus penggunaan umum untuk SQL native

Meskipun GraphQL native memberikan keamanan jenis penuh, dan direktif @view menawarkan hasil berjenis kuat untuk laporan SQL hanya baca, SQL native memberikan fleksibilitas yang diperlukan untuk:

• Ekstensi PostgreSQL: Langsung kueri dan gunakan ekstensi PostgreSQL yang terinstal (seperti PostGIS untuk data geospasial) tanpa perlu memetakan jenis kompleks dalam skema GraphQL Anda.
• Kueri Kompleks: Jalankan SQL rumit dengan gabungan, subkueri, agregasi, fungsi jendela, dan prosedur tersimpan.
• Manipulasi Data (DML): Lakukan operasi INSERT, UPDATE, DELETE secara langsung. (Namun, jangan gunakan SQL native untuk perintah Bahasa Definisi Data (DDL). Anda harus terus melakukan perubahan tingkat skema menggunakan GraphQL agar backend dan SDK yang dihasilkan tetap sinkron.)
• Fitur Khusus Database: Manfaatkan fungsi, operator, atau jenis data yang unik untuk PostgreSQL.
• Pengoptimalan Performa: Sesuaikan pernyataan SQL secara manual untuk jalur penting.

Kolom root SQL Native

Untuk menulis operasi dengan SQL, gunakan salah satu kolom root berikut dari jenis query atau mutation:

query kolom

mutation kolom

Catatan:

• Operasi dijalankan menggunakan izin yang diberikan ke akun layanan SQL Connect.

Aturan & batasan sintaksis

SQL Native menerapkan aturan parsing yang ketat untuk memastikan keamanan dan mencegah injeksi SQL. Perhatikan batasan berikut:

• Komentar: Gunakan komentar blok (/* ... */). Komentar baris (--) dilarang karena dapat memotong klausa berikutnya (seperti filter keamanan) selama penggabungan kueri.
• Parameter: Gunakan parameter posisional ($1, $2) yang cocok dengan urutan array params. Parameter bernama ($id, :name) tidak didukung.
• String: Literal string yang diperluas (E'...') dan string yang diberi tanda kutip dolar ($$...$$) didukung. Escape Unicode PostgreSQL (U&'...') tidak didukung.

Parameter dalam komentar

Parser mengabaikan semua yang ada di dalam komentar blok. Jika Anda mengomentari baris yang berisi parameter (misalnya, /* WHERE id = $1 */), Anda juga harus menghapus parameter tersebut dari daftar params, atau operasi akan gagal dengan error unused parameter: $1.

Konvensi penamaan

Saat menulis SQL native, Anda berinteraksi langsung dengan database PostgreSQL, sehingga Anda harus menggunakan nama database yang sebenarnya untuk tabel dan kolom. Secara default, SQL Connect otomatis memetakan nama dalam skema GraphQL ke snake case dalam database, kecuali jika Anda secara eksplisit menyesuaikan ID Postgres menggunakan direktif @table(name) dan @col(name).

Jika Anda menentukan jenis tanpa direktif, nama tabel dan kolom GraphQL akan dipetakan ke ID Postgres snake_case default:

type UserProfile {
  userId: ID!
  displayName: String
}

query GetUserProfileDefault($id: ID!) {
  profile: _selectFirst(
    sql: """
      SELECT user_id, display_name
      FROM user_profile
      WHERE user_id = $1
    """,
    params: [$id]
  )
}

ID PostgreSQL tidak peka huruf besar/kecil secara default. Jika Anda menggunakan direktif seperti @table atau @col untuk menentukan nama yang berisi huruf kapital atau huruf campuran, Anda harus menyertakan ID tersebut dalam tanda kutip ganda dalam pernyataan SQL Anda.

Dalam contoh berikut, Anda harus menggunakan "UserProfiles" untuk nama tabel dan "profileId" untuk kolom userId. Kolom displayName mengikuti konversi default ke display_name:

type UserProfileCustom @table(name: "UserProfiles") {
  userId: ID! @col(name: "profileId")
  displayName: String
}

query GetUserProfileCustom($id: ID!) {
  profile: _selectFirst(
    sql: """
      SELECT "profileId", display_name
      FROM "UserProfiles"
      WHERE "profileId" = $1
    """,
    params: [$id]
  )
}

Contoh penggunaan

Contoh 1: SELECT dasar dengan pemberian alias kolom

Anda dapat membuat alias kolom root (misalnya, movies: _select) untuk membuat respons klien lebih bersih (data.movies, bukan data._select).

queries.gql:

query GetMoviesByGenre($genre: String!, $limit: Int!) @auth(level: PUBLIC) {
  movies: _select(
    sql: """
      SELECT id, title, release_year, rating
      FROM movie
      WHERE genre = $1
      ORDER BY release_year DESC
      LIMIT $2
    """,
    params: [$genre, $limit]
  )
}

Setelah menjalankan kueri menggunakan SDK klien, hasilnya akan ada di data.movies.

Contoh 2: UPDATE dasar

mutations.gql:

mutation UpdateMovieRating(
  $movieId: UUID!,
  $newRating: Float!
) @auth(level: NO_ACCESS) {
  _execute(
    sql: """
      UPDATE movie
      SET rating = $2
      WHERE id = $1
    """,
    params: [$movieId, $newRating]
  )
}

Setelah menjalankan mutasi menggunakan SDK klien, jumlah baris yang terpengaruh akan berada di data._execute.

Contoh 3: Agregasi dasar

queries.gql:

query GetTotalReviewCount @auth(level: PUBLIC) {
  stats: _selectFirst(
    sql: "SELECT COUNT(*) as total_reviews FROM \"Reviews\""
  )
}

Setelah menjalankan kueri menggunakan SDK klien, hasilnya akan ada di data.stats.total_reviews.

Contoh 4: Agregasi lanjutan dengan RANK

queries.gql:

query GetMoviesRankedByRating @auth(level: PUBLIC) {
  _select(
    sql: """
      SELECT
        id,
        title,
        rating,
        RANK() OVER (ORDER BY rating DESC) as rank
      FROM movie
      WHERE rating IS NOT NULL
      LIMIT 20
    """,
    params: []
  )
}

Setelah menjalankan kueri menggunakan SDK klien, hasilnya akan ada di data._select.

Contoh 5: UPDATE dengan RETURNING dan Konteks Auth

mutations.gql:

mutation UpdateMyReviewText(
  $movieId: UUID!,
  $newText: String!
) @auth(level: USER) {
  updatedReview: _executeReturningFirst(
    sql: """
      UPDATE "Reviews"
      SET review_text = $2
      WHERE movie_id = $1 AND user_id = $3
      RETURNING movie_id, user_id, rating, review_text
    """,
    params: [$movieId, $newText, {_expr: "auth.uid"}]
  )
}

Setelah menjalankan mutasi menggunakan SDK klien, data postingan yang diperbarui akan ada di data.updatedReview.

Contoh 6: CTE tingkat lanjut dengan operasi upsert (get-or-create atomik)

Pola ini berguna untuk memastikan keberadaan rekaman yang bergantung (seperti Pengguna atau Film) sebelum menyisipkan rekaman turunan (seperti Ulasan), semuanya dalam satu transaksi database.

mutations.gql:

mutation CreateMovieCTE($movieId: UUID!, $userId: UUID!, $reviewId: UUID!) @auth(level: USER) {
  _execute(
    sql: """
      WITH
      new_user AS (
        INSERT INTO "user" (id, username)
        VALUES ($2, 'Auto-Generated User')
        ON CONFLICT (id) DO NOTHING
        RETURNING id
      ),
      movie AS (
        INSERT INTO movie (id, title, image_url, release_year, genre)
        VALUES ($1, 'Auto-Generated Movie', 'https://placeholder.com', 2025, 'Sci-Fi')
        ON CONFLICT (id) DO NOTHING
        RETURNING id
      )
      INSERT INTO "Reviews" (id, movie_id, user_id, rating, review_text, review_date)
      VALUES (
        $3,
        $1,
        $2,
        5,
        'Good!',
        NOW()
      )
    """,
    params: [$movieId, $userId, $reviewId]
  )
}

_executeReturning dan _executeReturningFirst membungkus kueri Anda dalam CTE induk untuk memformat output sebagai JSON. PostgreSQL tidak mengizinkan penyusunan CTE pengubah data di dalam pernyataan pengubah data lain, sehingga menyebabkan kueri gagal.

Contoh 7: Menggunakan ekstensi Postgres

SQL Native memungkinkan Anda menggunakan ekstensi Postgres, seperti PostGIS, tanpa perlu memetakan jenis geometri yang kompleks ke dalam skema GraphQL atau mengubah tabel pokok.

Dalam contoh ini, misalkan aplikasi restoran Anda memiliki tabel yang menyimpan data lokasi dalam kolom JSON metadata (misalnya, {"latitude": 37.3688, "longitude": -122.0363}). Jika Anda telah mengaktifkan ekstensi PostGIS, Anda dapat menggunakan operator JSON Postgres standar (->>) untuk mengekstrak nilai ini secara langsung dan meneruskannya ke fungsi ST_MakePoint PostGIS.

query GetNearbyActiveRestaurants(
  $userLong: Float!,
  $userLat: Float!,
  $maxDistanceMeters: Float!
) @auth(level: USER) {
  nearby: _select(
    sql: """
      SELECT
        id,
        name,
        tags,
        ST_Distance(
          ST_MakePoint(
            (metadata->>'longitude')::float,
            (metadata->>'latitude')::float
          )::geography,
          ST_MakePoint($1, $2)::geography
        ) as distance_meters
      FROM restaurant
      WHERE active = true
        AND metadata ? 'longitude' AND metadata ? 'latitude'
        AND ST_DWithin(
          ST_MakePoint(
            (metadata->>'longitude')::float,
            (metadata->>'latitude')::float
          )::geography,
          ST_MakePoint($1, $2)::geography,
          $3
        )
      ORDER BY distance_meters ASC
      LIMIT 10
    """,
    params: [$userLong, $userLat, $maxDistanceMeters]
  )
}

Setelah menjalankan kueri menggunakan SDK klien, hasilnya akan ada di data.nearby.

Praktik terbaik keamanan: SQL dinamis & prosedur tersimpan

SQL Connect memparameterkan semua input secara aman di batas GraphQL-ke-database, sehingga sepenuhnya melindungi kueri SQL standar Anda dari injeksi SQL tingkat pertama. Namun, jika Anda menggunakan SQL untuk memanggil prosedur atau fungsi tersimpan Postgres kustom yang menjalankan SQL dinamis, Anda harus memastikan kode PL/pgSQL internal Anda menangani parameter ini dengan aman.

Jika prosedur tersimpan Anda secara langsung menggabungkan input pengguna ke dalam string EXECUTE, prosedur tersebut akan melewati parameterisasi dan membuat kerentanan injeksi SQL tingkat kedua:

-- INSECURE: Do not concatenate parameters into dynamic strings!
CREATE OR REPLACE PROCEDURE unsafe_update(user_input TEXT)
LANGUAGE plpgsql AS $$
BEGIN
    -- A malicious user_input (e.g., "val'; DROP TABLE users; --")
    -- will execute as code.
    EXECUTE 'UPDATE target_table SET status = ''' || user_input || '''';
END;
$$;

Untuk menghindarinya, ikuti praktik terbaik berikut:

• Gunakan klausa USING: Saat menulis SQL dinamis dalam prosedur tersimpan, selalu gunakan klausa USING untuk mengikat parameter data dengan aman.
• Gunakan format() untuk ID: Gunakan format() dengan tanda %I untuk injeksi ID database yang aman (seperti nama tabel).
• Izinkan ID secara ketat: Jangan biarkan aplikasi klien memilih ID database secara arbitrer. Jika prosedur Anda memerlukan ID dinamis, validasi input terhadap daftar yang diizinkan hard code dalam logika PL/pgSQL Anda sebelum dieksekusi.

-- SECURE: Use format() for identifiers and USING for data values
CREATE OR REPLACE PROCEDURE secure_update(
    target_table TEXT, new_value TEXT, row_id INT
)
LANGUAGE plpgsql AS $$
BEGIN
    -- Validate the dynamic table name against an allowlist
    IF target_table NOT IN ('orders', 'users', 'inventory') THEN
        RAISE EXCEPTION 'Invalid table name';
    END IF;

    -- Execute securely
    EXECUTE format('UPDATE %I SET status = $1 WHERE id = $2', target_table)
    USING new_value, row_id;
END;
$$;