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

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

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

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

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

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

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

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

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

query שדות

mutation שדות

הערות:

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

• אם מגדירים במפורש את שם הטבלה באמצעות ההוראה @table (@table(name: "ExampleTable")), צריך גם להוסיף מרכאות לשם הטבלה בהצהרות ה-SQL (SELECT field FROM "ExampleTable" ...).

  אם לא מוסיפים מירכאות, Data Connect ימיר את שם הטבלה ל-snake case ‏ (example_table).

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

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

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

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

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

דוגמאות

דוגמה 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!) {
  _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]
  )
}

דוגמה 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 דינמי ותהליכים מאוחסנים

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