הטמעה של פעולות Firebase SQL Connect באמצעות SQL מקורי

מדריך לכתיבת פעולות של Firebase SQL Connect באמצעות SQL במקום GraphQL. page_type: guide announcement: > Native SQL is available as a feature גרסת טרום-השקה (Preview), which means that it isn't subject to any הסכם רמת שירות (SLA) or מדיניות הוצאה משימוש and could change in backward-incompatible ways. אם אתם משתמשים בתכונה הזו עם פרוצדורות מאוחסנות או עם פונקציות שמבצעות SQL דינמי, עליכם לפעול בהתאם לשיטות המומלצות לאבטחה שמפורטות בחלק התחתון של הדף הזה.

Firebase SQL Connect יש כמה דרכים לאינטראקציה עם מסד הנתונים של Cloud SQL:

• GraphQL מקורי: מגדירים סוגים ב-schema.gql ו-SQL Connect מתרגם את פעולות ה-GraphQL ל-SQL. זו הגישה הסטנדרטית, והיא מציעה הקלדה חזקה ומבנים עם אכיפת סכימה. רוב מאמרי העזרה בנושא SQL Connect שלא מופיעים בדף הזה מתייחסים לאפשרות הזו. אם אפשר, כדאי להשתמש בשיטה הזו כדי ליהנות מבטיחות מלאה של סוגי הנתונים ומתמיכה בכלי פיתוח.
• ההנחיה @view: מגדירים סוג GraphQL ב-schema.gql שמגובה על ידי הצהרת SQL מותאמת אישית SELECT. האפשרות הזו שימושית ליצירת תצוגות לקריאה בלבד עם הקלדה חזקה שמבוססות על לוגיקת SQL מורכבת. אפשר להריץ שאילתות על הסוגים האלה כמו על סוגים רגילים. מידע נוסף מפורט ב@view.
• ‫Native SQL: הטמעת הצהרות SQL ישירות בפעולות בעלות שם בקובצי .gql באמצעות שדות שורש מיוחדים. האפשרות הזו מספקת גמישות מקסימלית ושליטה ישירה, במיוחד בפעולות שלא נתמכות על ידי GraphQL רגיל, תוך ניצול תכונות ספציפיות למסד הנתונים או שימוש בתוספים של PostgreSQL. בניגוד ל-GraphQL ולהוראה @view, ‏ SQL מקורי לא מספק פלט עם הקלדה חזקה.

במדריך הזה אנחנו מתמקדים באפשרות Native SQL.

תרחישים נפוצים לדוגמה לשימוש ב-SQL מקורי

GraphQL מקורי מספק מניעת שגיאות הקלדה מלאה, וההוראה @view מציעה תוצאות עם הקלדה חזקה לדוחות SQL לקריאה בלבד. לעומת זאת, SQL מקורי מספק את הגמישות שנדרשת כדי:

• ‫PostgreSQL Extensions: אפשר להריץ שאילתות ישירות על כל תוסף PostgreSQL מותקן (כמו PostGIS לנתונים גיאו-מרחביים) ולהשתמש בו בלי למפות סוגים מורכבים בסכימת GraphQL.
• שאילתות מורכבות: ביצוע SQL מורכב עם שאילתות איחוד (join), שאילתות משנה, צבירות, פונקציות חלון ופרוצדורות מאוחסנות.
• טיפול בנתונים (DML): ביצוע פעולות INSERT, UPDATE, DELETE ישירות. (עם זאת, לא מומלץ להשתמש ב-SQL מקורי לפקודות של שפת הגדרת נתונים (DDL). אתם צריכים להמשיך לבצע שינויים ברמת הסכימה באמצעות GraphQL כדי לשמור על סנכרון בין ה-Backend לבין ערכות ה-SDK שנוצרו).
• תכונות ספציפיות למסד נתונים: שימוש בפונקציות, באופרטורים או בסוגי נתונים שייחודיים ל-PostgreSQL.
• אופטימיזציה של הביצועים: כוונון ידני של הצהרות SQL עבור נתיבים קריטיים.

שדות בסיסיים של SQL מקורי

כדי לכתוב פעולות באמצעות SQL, משתמשים באחד משדות הבסיס הבאים של הסוגים query או mutation:

query שדות

mutation שדות

הערות:

• הפעולות מבוצעות באמצעות ההרשאות שניתנו לחשבון השירות SQL Connect.

כללי תחביר והגבלות

‫SQL מקורי אוכף כללי ניתוח מחמירים כדי להבטיח אבטחה ולמנוע הזרקת SQL. חשוב לשים לב למגבלות הבאות:

• תגובות: צריך להשתמש בתגובות של בלוקים (/* ... */). אסור להשתמש בתגובות של שורות (--) כי הן עלולות לקטוע סעיפים עוקבים (כמו מסנני אבטחה) במהלך שרשור השאילתות.
• פרמטרים: משתמשים בפרמטרים מיקומיים ($1, $2) שתואמים לסדר המערך params. אין תמיכה בפרמטרים עם שם ($id, :name).
• מחרוזות: המערכת תומכת במחרוזות מורחבות (E'...') ובמחרוזות עם סימן דולר ($$...$$). לא נתמכים תווי בריחה מסוג Unicode ב-PostgreSQL‏ (U&'...').

פרמטרים בתגובות

הכלי לניתוח מתעלם מכל מה שנמצא בתוך הערה של בלוק. אם מוסיפים הערה לשורה שמכילה פרמטר (לדוגמה, /* WHERE id = $1 */), צריך גם להסיר את הפרמטר הזה מהרשימה params, אחרת הפעולה תיכשל עם השגיאה unused parameter: $1.

מוסכמות למתן שמות

כשכותבים SQL מקורי, האינטראקציה היא ישירות עם מסד הנתונים של PostgreSQL, ולכן צריך להשתמש בשמות הטבלאות והעמודות בפועל. כברירת מחדל, SQL Connect ממפה באופן אוטומטי את השמות בסכימת GraphQL ל-snake case במסד הנתונים, אלא אם מבצעים התאמה אישית של מזהי Postgres באמצעות ההנחיות @table(name) ו-@col(name).

אם מגדירים סוג ללא הנחיות, שמות הטבלאות והשדות ב-GraphQL ממופים למזהים של snake_case Postgres שמוגדרים כברירת מחדל:

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

אחרי שמריצים את השינוי באמצעות 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"}]
  )
}

אחרי שמריצים את המוטציה באמצעות SDK של לקוח, נתוני הפוסט המעודכנים יהיו ב-data.updatedReview.

דוגמה 6: CTE מתקדם עם פעולות upsert (פעולות get-or-create אטומיות)

התבנית הזו שימושית כדי לוודא שרשומות תלויות (כמו משתמשים או סרטים) קיימות לפני שמוסיפים רשומת צאצא (כמו ביקורת), והכול בטרנזקציה אחת במסד נתונים.

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: שימוש בתוספים של Postgres

‫SQL מקורי מאפשר לכם להשתמש בתוספים של Postgres, כמו PostGIS, בלי שתצטרכו למפות סוגים מורכבים של גיאומטריה לסכימת GraphQL או לשנות את הטבלאות הבסיסיות.

בדוגמה הזו, נניח שלאפליקציית המסעדה שלכם יש טבלה שמאחסנת נתוני מיקום בעמודת JSON של מטא-נתונים (לדוגמה, {"latitude": 37.3688, "longitude": -122.0363}). אם הפעלתם את התוסף PostGIS, אתם יכולים להשתמש באופרטורים סטנדרטיים של JSON ב-Postgres ‏ (->>) כדי לחלץ את הערכים האלה תוך כדי תנועה ולהעביר אותם לפונקציה 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 מסדר ראשון. עם זאת, אם אתם משתמשים ב-SQL כדי לקרוא לפונקציות או לפרוצדורות מאוחסנות מותאמות אישית של Postgres שמבצעות SQL דינמי, אתם צריכים לוודא שהקוד הפנימי של PL/pgSQL מטפל בפרמטרים האלה בצורה מאובטחת.

אם הפרוצדורה המאוחסנת משרשרת ישירות קלט של משתמשים למחרוזת EXECUTE string, היא עוקפת את הפרמטריזציה ויוצרת נקודת חולשה של הזרקת SQL מסדר שני:

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