Menerapkan operasi Firebase SQL Connect menggunakan SQL native

Panduan untuk menulis operasi Firebase SQL Connect dengan SQL, bukan GraphQL.page_type: guideannouncement: >Native SQL tersedia sebagai Pratinjau fitur, yang berarti tidak tunduk pada kebijakan penghentian layanan atau SLA apa pun dan dapat berubah sehingga tidak kompatibel dengan versi sebelumnya. Jika Anda menggunakan fitur ini dengan prosedur atau fungsi tersimpan 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 Anda: Cloud SQL

• GraphQL Native: Tentukan jenis di schema.gql dan SQL Connect akan menerjemahkan operasi GraphQL Anda ke dalam SQL. Ini adalah pendekatan standar, yang menawarkan pengetikan yang kuat dan struktur yang diterapkan skema. Sebagian besar dokumentasi SQL Connect di luar halaman ini membahas opsi ini. Jika memungkinkan, Anda harus menggunakan metode ini untuk memanfaatkan dukungan alat dan keamanan jenis penuh.
• Direktif @view: Tentukan jenis GraphQL di schema.gql yang didukung oleh pernyataan SQL SELECT kustom. Hal ini berguna untuk membuat tampilan baca-saja yang diketik dengan 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 .gql file 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 diketik dengan kuat.

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 yang diketik dengan kuat untuk laporan SQL baca-saja, SQL native memberikan fleksibilitas yang diperlukan untuk:

• Ekstensi PostgreSQL: Langsung kueri dan gunakan ekstensi PostgreSQL yang diinstal (seperti PostGIS untuk data geospasial) tanpa perlu memetakan jenis kompleks dalam skema GraphQL Anda.
• Kueri Kompleks: Jalankan SQL yang 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: Gunakan 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 dari jenis query atau mutation:

Kolom query

Kolom mutation

Catatan:

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

Aturan &batasan sintaksis

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

• Komentar: Gunakan komentar blok (/* ... */). Komentar baris (--) dilarang karena dapat memangkas klausa berikutnya (seperti filter keamanan) selama penggabungan kueri.
• Parameter: Gunakan parameter posisi ($1, $2) yang cocok dengan params urutan array. Parameter bernama ($id, :name) tidak didukung.
• String: Literal string yang diperluas (E'...') dan string yang dikutip 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 Anda ke snake case di database, kecuali jika Anda secara eksplisit menyesuaikan ID PostgreSQL menggunakan @table(name) dan @col(name) direktif.

Jika Anda menentukan jenis tanpa direktif, tabel GraphQL dan nama kolom akan dipetakan ke ID PostgreSQL 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 besar atau campuran, Anda harus menyertakan ID tersebut dalam tanda kutip ganda dalam pernyataan SQL Anda.

Pada 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 memberi alias pada 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 berada 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 berada 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 berada di data._select.

Contoh 5: UPDATE dengan RETURNING dan Konteks Autentikasi

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 berada di data.updatedReview.

Contoh 6: CTE lanjutan dengan upsert (get-or-create atomik)

Pola ini berguna untuk memastikan catatan dependen (seperti Pengguna atau Film) ada sebelum menyisipkan catatan 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 menggabungkan kueri Anda dalam CTE induk untuk memformat output sebagai JSON. PostgreSQL tidak mengizinkan CTE yang mengubah data untuk disarangkan di dalam pernyataan lain yang mengubah data, sehingga menyebabkan kueri gagal.

Contoh 7: Menggunakan ekstensi PostgreSQL

SQL native memungkinkan Anda menggunakan ekstensi PostgreSQL, seperti PostGIS, tanpa perlu memetakan jenis geometri yang kompleks ke dalam skema GraphQL atau mengubah tabel yang mendasarinya.

Dalam contoh ini, misalkan aplikasi restoran Anda memiliki tabel yang menyimpan data lokasi di kolom JSON metadata (misalnya, {"latitude": 37.3688, "longitude": -122.0363}). Jika telah mengaktifkan ekstensi PostGIS, Anda dapat menggunakan operator JSON PostgreSQL standar (->>) untuk mengekstrak nilai ini dengan cepat 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 berada di data.nearby.

Praktik terbaik keamanan: SQL dinamis &prosedur tersimpan

SQL Connect memparameterkan semua input dengan 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 PostgreSQL kustom yang menjalankan SQL dinamis, Anda harus memastikan kode PL/pgSQL internal Anda menangani parameter ini dengan aman.

Jika prosedur tersimpan Anda 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 ini:

• 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 flag %I untuk injeksi ID database yang aman (seperti nama tabel).
• Izinkan ID secara ketat: Jangan izinkan aplikasi klien memilih ID database secara arbitrer. Jika prosedur Anda memerlukan ID dinamis, validasi input terhadap daftar yang diizinkan yang dikodekan secara permanen di 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;
$$;