ใช้การดำเนินการ Firebase SQL Connect โดยใช้ SQL ดั้งเดิม

คู่มือการเขียนการดำเนินการ Firebase SQL Connect ด้วย SQL แทน GraphQL page_type: guide announcement: > Native SQL พร้อมให้ใช้งานในเวอร์ชันแสดงตัวอย่าง ซึ่งหมายความว่าฟีเจอร์นี้ไม่อยู่ภายใต้นโยบาย SLA หรือนโยบายการเลิกใช้งานใดๆ และอาจมีการเปลี่ยนแปลงบางอย่างที่ใช้กับรุ่นก่อนหน้าไม่ได้ หากใช้ฟีเจอร์นี้กับฟังก์ชันหรือกระบวนงานที่เก็บไว้ซึ่งดำเนินการ SQL แบบไดนามิก ให้ทำตามแนวทางปฏิบัติแนะนำด้านความปลอดภัยที่อธิบายไว้ที่ด้านล่างของหน้านี้

Firebase SQL Connect มีวิธีโต้ตอบกับฐานข้อมูล Cloud SQL หลายวิธี ดังนี้

• Native GraphQL: กำหนดประเภทใน schema.gql แล้ว SQL Connect จะแปลการดำเนินการ GraphQL เป็น SQL ซึ่งเป็นแนวทางมาตรฐานที่ให้การพิมพ์ที่รัดกุมและโครงสร้างที่บังคับใช้สคีมา เอกสารประกอบส่วนใหญ่ของ SQL Connect นอกเหนือจากหน้านี้จะอธิบายตัวเลือกนี้ คุณควรใช้วิธีนี้เมื่อเป็นไปได้เพื่อใช้ประโยชน์จากความปลอดภัยในการกำหนดประเภทและการรองรับเครื่องมืออย่างเต็มที่
• คำสั่ง @view: กำหนดประเภท GraphQL ใน schema.gql ที่รองรับโดยคำสั่ง SQL ที่กำหนดเอง SELECT ซึ่งมีประโยชน์สำหรับการสร้างมุมมองแบบอ่านอย่างเดียวที่มีการพิมพ์ที่รัดกุมโดยอิงตามตรรกะ SQL ที่ซับซ้อน โดยประเภทเหล่านี้จะค้นหาได้เหมือนกับประเภทปกติ ดู @view
• Native SQL: ฝังคำสั่ง SQL ลงในการดำเนินการที่มีชื่อใน .gql ไฟล์โดยตรงโดยใช้ฟิลด์รากพิเศษ ซึ่งให้ความยืดหยุ่นและการควบคุมโดยตรงสูงสุด โดยเฉพาะอย่างยิ่งสำหรับการดำเนินการที่ GraphQL มาตรฐานไม่รองรับ การใช้ประโยชน์จากฟีเจอร์เฉพาะของฐานข้อมูล หรือการใช้ส่วนขยาย PostgreSQL Native SQL ไม่ได้ให้เอาต์พุตที่มีการพิมพ์ที่รัดกุมเหมือนกับ GraphQL และคำสั่ง @view

คู่มือนี้มุ่งเน้นที่ตัวเลือก Native SQL

กรณีการใช้งานทั่วไปสำหรับ Native SQL

แม้ว่า Native GraphQL จะให้ความปลอดภัยในการกำหนดประเภทอย่างเต็มที่ และคำสั่ง @view จะให้ผลลัพธ์ที่มีการพิมพ์ที่รัดกุมสำหรับรายงาน SQL แบบอ่านอย่างเดียว แต่ Native SQL ก็ให้ความยืดหยุ่นที่จำเป็นสำหรับสิ่งต่อไปนี้

• ส่วนขยาย PostgreSQL: ค้นหาและใช้ส่วนขยาย PostgreSQL ที่ติดตั้งไว้ (เช่น PostGIS สำหรับข้อมูลเชิงพื้นที่) ได้โดยตรงโดยไม่ต้องแมป ประเภทที่ซับซ้อนในสคีมา GraphQL
• การค้นหาที่ซับซ้อน: ดำเนินการ SQL ที่ซับซ้อนด้วยการรวม การค้นหาแบบย่อย, การรวม ฟังก์ชันหน้าต่าง และกระบวนงานที่เก็บไว้
• การจัดการข้อมูล (DML): ดำเนินการ INSERT, UPDATE, DELETE operations ได้โดยตรง (อย่างไรก็ตาม อย่าใช้ Native SQL สำหรับคำสั่งภาษานิยามข้อมูล (DDL) คุณต้องทำการเปลี่ยนแปลงระดับสคีมาโดยใช้ GraphQL ต่อไปเพื่อให้แบ็กเอนด์และ SDK ที่สร้างขึ้นซิงค์กัน)
• ฟีเจอร์เฉพาะของฐานข้อมูล: ใช้ฟังก์ชัน โอเปอเรเตอร์ หรือประเภทข้อมูล เฉพาะของ PostgreSQL
• การเพิ่มประสิทธิภาพ: ปรับแต่งคำสั่ง SQL ด้วยตนเองสำหรับเส้นทางที่สำคัญ

ฟิลด์ราก Native SQL

หากต้องการเขียนการดำเนินการด้วย SQL ให้ใช้ฟิลด์รากใดฟิลด์หนึ่งต่อไปนี้ของประเภท query หรือ mutation

ฟิลด์ query

ฟิลด์ mutation

หมายเหตุ:

• ระบบจะดำเนินการโดยใช้สิทธิ์ที่มอบให้กับบัญชีบริการ SQL Connect

กฎและข้อจำกัดของไวยากรณ์

Native SQL บังคับใช้กฎการแยกวิเคราะห์ที่เข้มงวดเพื่อให้มั่นใจในความปลอดภัยและป้องกันการแทรก SQL โปรดรับทราบข้อจำกัดต่อไปนี้

• ความคิดเห็น: ใช้ความคิดเห็นแบบบล็อก (/* ... */) ระบบไม่อนุญาตให้ใช้ความคิดเห็นแบบบรรทัด (--) เนื่องจากอาจตัดอนุประโยคที่ตามมา (เช่น ตัวกรองความปลอดภัย) ระหว่างการเชื่อมคำค้นหา
• พารามิเตอร์: ใช้พารามิเตอร์ตามตำแหน่ง ($1, $2) ที่ตรงกับลำดับอาร์เรย์ params ระบบไม่รองรับพารามิเตอร์ที่มีชื่อ ($id, :name)
• สตริง: ระบบรองรับตัวอักษรสตริงแบบขยาย (E'...') และสตริงที่ใช้เครื่องหมายดอลลาร์ ($$...$$) ระบบไม่รองรับการหลีก Unicode ของ PostgreSQL (U&'...')

พารามิเตอร์ในความคิดเห็น

ตัวแยกวิเคราะห์จะละเว้นทุกอย่างภายในความคิดเห็นแบบบล็อก หากคุณแสดงความคิดเห็นในบรรทัดที่มีพารามิเตอร์ (เช่น /* WHERE id = $1 */) คุณต้องนำพารามิเตอร์นั้นออกจากรายการ params ด้วย ไม่เช่นนั้นการดำเนินการจะล้มเหลวโดยมีข้อผิดพลาด unused parameter: $1

รูปแบบการตั้งชื่อ

เมื่อเขียน Native SQL คุณจะโต้ตอบกับฐานข้อมูล PostgreSQL โดยตรง ดังนั้นคุณต้องใช้ชื่อฐานข้อมูลจริงสำหรับตารางและคอลัมน์ โดย ค่าเริ่มต้น SQL Connect จะแมปชื่อในสคีมา GraphQL กับรูปแบบ Snake Case ในฐานข้อมูลโดยอัตโนมัติ เว้นแต่คุณจะปรับแต่งตัวระบุ PostgreSQL อย่างชัดเจนโดยใช้คำสั่ง @table(name) และ @col(name)

หากคุณกำหนดประเภทโดยไม่มีคำสั่ง ระบบจะแมปชื่อตารางและฟิลด์ GraphQL กับตัวระบุ snake_case เริ่มต้นของ PostgreSQL ดังนี้

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]
  )
}

โดยค่าเริ่มต้น ตัวระบุ PostgreSQL จะไม่คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ หากคุณใช้คำสั่ง เช่น @table หรือ @col เพื่อระบุชื่อที่มีตัวอักษรตัวพิมพ์ใหญ่หรือตัวอักษรผสม คุณต้อง ใส่ตัวระบุนั้นไว้ในเครื่องหมายคำพูดคู่ในคำสั่ง SQL

ในตัวอย่างต่อไปนี้ คุณต้องใช้ "UserProfiles" สำหรับชื่อตารางและ "profileId" สำหรับคอลัมน์ userId ฟิลด์ displayName จะใช้การแปลงเป็น 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]
  )
}

ตัวอย่างการใช้

ตัวอย่างที่ 1: SELECT พื้นฐานที่มีการตั้งชื่อแทนฟิลด์

คุณสามารถตั้งชื่อแทนฟิลด์ราก (เช่น movies: _select) เพื่อให้การตอบกลับของไคลเอ็นต์สะอาดขึ้น (data.movies แทน 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]
  )
}

หลังจากเรียกใช้การค้นหาโดยใช้ Client SDK ผลลัพธ์จะอยู่ใน data.movies

ตัวอย่างที่ 2: UPDATE พื้นฐาน

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]
  )
}

หลังจากเรียกใช้การเปลี่ยนแปลงโดยใช้ Client SDK จำนวนแถวที่ได้รับผลกระทบจะอยู่ใน data._execute

ตัวอย่างที่ 3: การรวมพื้นฐาน

queries.gql:

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

หลังจากเรียกใช้การค้นหาโดยใช้ Client SDK ผลลัพธ์จะอยู่ใน data.stats.total_reviews

ตัวอย่างที่ 4: การรวมขั้นสูงด้วย 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: []
  )
}

หลังจากเรียกใช้การค้นหาโดยใช้ Client SDK ผลลัพธ์จะอยู่ใน data._select

ตัวอย่างที่ 5: UPDATE ที่มี RETURNING และบริบทการตรวจสอบสิทธิ์

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"}]
  )
}

หลังจากเรียกใช้การเปลี่ยนแปลงโดยใช้ Client SDK ข้อมูลโพสต์ที่อัปเดตจะอยู่ใน data.updatedReview

ตัวอย่างที่ 6: CTE ขั้นสูงที่มีการแทรกหรืออัปเดต (การรับหรือสร้างแบบอะตอม)

รูปแบบนี้มีประโยชน์สำหรับการตรวจสอบว่ามีระเบียนที่ขึ้นต่อกัน (เช่น ผู้ใช้หรือภาพยนตร์) ก่อนที่จะแทรกระเบียนย่อย (เช่น รีวิว) ทั้งหมดในการทำธุรกรรมฐานข้อมูลเดียว

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 และ _executeReturningFirst จะรวมคำค้นหาไว้ใน CTE หลักเพื่อจัดรูปแบบเอาต์พุตเป็น JSON PostgreSQL ไม่อนุญาตให้ซ้อน CTE ที่แก้ไขข้อมูลไว้ในคำสั่งที่แก้ไขข้อมูลอื่น ซึ่งทำให้คำค้นหาล้มเหลว

ตัวอย่างที่ 7: การใช้ส่วนขยาย PostgreSQL

Native SQL ช่วยให้คุณใช้ส่วนขยาย PostgreSQL เช่น PostGIS ได้โดยไม่ต้องแมปประเภทเรขาคณิตที่ซับซ้อนลงในสคีมา GraphQL หรือแก้ไขตารางพื้นฐาน

ในตัวอย่างนี้ สมมติว่าแอปภัตตาคารมีตารางที่จัดเก็บข้อมูลสถานที่ ในคอลัมน์ JSON ของข้อมูลเมตา (เช่น {"latitude": 37.3688, "longitude": -122.0363}) หากคุณเปิดใช้ส่วนขยาย PostGIS คุณสามารถ ใช้โอเปอเรเตอร์ JSON มาตรฐานของ PostgreSQL (->>) เพื่อแยกค่าเหล่านี้แบบเรียลไทม์ และส่งค่าไปยังฟังก์ชัน 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]
  )
}

หลังจากเรียกใช้การค้นหาโดยใช้ Client SDK ผลลัพธ์จะอยู่ใน data.nearby

แนวทางปฏิบัติแนะนำด้านความปลอดภัย: SQL แบบไดนามิกและกระบวนงานที่เก็บไว้

SQL Connect จะกำหนดพารามิเตอร์อินพุตทั้งหมดอย่างปลอดภัยที่ ขอบเขต GraphQL ไปยังฐานข้อมูล ซึ่งช่วยปกป้องคำค้นหา SQL มาตรฐาน จากการแทรก SQL ระดับที่ 1 อย่างเต็มที่ อย่างไรก็ตาม หากคุณใช้ SQL เพื่อเรียกกระบวนงานหรือฟังก์ชันที่เก็บไว้ของ PostgreSQL ที่กำหนดเองซึ่งดำเนินการ SQL แบบไดนามิก คุณต้องตรวจสอบว่าโค้ด PL/pgSQL ภายในจัดการพารามิเตอร์เหล่านี้อย่างปลอดภัย

หากกระบวนงานที่เก็บไว้เชื่อมอินพุตของผู้ใช้เข้ากับสตริง EXECUTE โดยตรง กระบวนงานดังกล่าวจะข้ามการกำหนดพารามิเตอร์และสร้างช่องโหว่การแทรก SQL ระดับที่ 2

-- 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;
$$;

ให้ทำตามแนวทางปฏิบัติแนะนำต่อไปนี้เพื่อหลีกเลี่ยงปัญหานี้

• ใช้อนุประโยค USING: เมื่อเขียน SQL แบบไดนามิกในกระบวนงานที่เก็บไว้ ให้ใช้อนุประโยค USING เสมอเพื่อผูกพารามิเตอร์ข้อมูลอย่างปลอดภัย
• ใช้ format() สำหรับตัวระบุ: ใช้ format() กับแฟล็ก %I สำหรับการแทรกตัวระบุฐานข้อมูลอย่างปลอดภัย (เช่น ชื่อตาราง)
• อนุญาตตัวระบุอย่างเข้มงวด: ไม่อนุญาตให้แอปพลิเคชันไคลเอ็นต์เลือกตัวระบุฐานข้อมูลโดยพลการ หากกระบวนงานต้องใช้ตัวระบุแบบไดนามิก ให้ตรวจสอบอินพุตกับรายการที่อนุญาตที่ฮาร์ดโค้ดไว้ภายในตรรกะ PL/pgSQL ก่อนดำเนินการ

-- 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;
$$;