پیاده‌سازی عملیات اتصال داده فایربیس با استفاده از SQL بومی

Firebase Data Connect روش‌های مختلفی برای تعامل با پایگاه داده Cloud SQL شما ارائه می‌دهد:

• GraphQL بومی : انواع را در schema.gql خود تعریف کنید و Data Connect عملیات GraphQL شما را به SQL ترجمه می‌کند. این رویکرد استاندارد است که ساختارهای تایپ قوی و schema-enforced را ارائه می‌دهد. اکثر مستندات Data Connect خارج از این صفحه در مورد این گزینه بحث می‌کنند. در صورت امکان، باید از این روش برای بهره‌مندی از ایمنی کامل نوع و پشتیبانی از ابزار استفاده کنید.
• دستورالعمل @view : یک نوع GraphQL را در schema.gql تعریف کنید که توسط یک دستور SELECT SQL سفارشی پشتیبانی می‌شود. این برای ایجاد نماهای فقط خواندنی و با نوع قوی بر اساس منطق پیچیده SQL مفید است. این نوع‌ها مانند انواع معمولی قابل پرس‌وجو هستند. به @view مراجعه کنید.
• SQL بومی : دستورات SQL را مستقیماً در عملیات نامگذاری شده در فایل‌های gql با استفاده از فیلدهای ریشه خاص جاسازی کنید. این امر حداکثر انعطاف‌پذیری و کنترل مستقیم را فراهم می‌کند، به خصوص برای عملیاتی که به راحتی در GraphQL استاندارد بیان نمی‌شوند، از ویژگی‌های خاص پایگاه داده استفاده می‌کنند یا از افزونه‌های PostgreSQL استفاده می‌کنند.

این راهنما بر روی گزینه Native SQL تمرکز دارد.

موارد استفاده رایج برای SQL بومی

در حالی که GraphQL بومی، ایمنی کامل نوع داده را ارائه می‌دهد و دستورالعمل @view نتایج strongly-typed را برای گزارش‌های SQL فقط خواندنی ارائه می‌دهد، SQL بومی انعطاف‌پذیری مورد نیاز برای موارد زیر را فراهم می‌کند:

• افزونه‌های PostgreSQL : مستقیماً از هر افزونه PostgreSQL نصب‌شده (مانند PostGIS برای داده‌های مکانی) کوئری بگیرید و استفاده کنید، بدون اینکه نیازی به نگاشت انواع پیچیده در طرح GraphQL خود داشته باشید.
• پرس‌وجوهای پیچیده : اجرای SQL پیچیده با استفاده از joinها، subqueryها، aggregationها، توابع پنجره‌ای و رویه‌های ذخیره‌شده.
• دستکاری داده‌ها (DML) : عملیات INSERT, UPDATE, DELETE را مستقیماً انجام دهید. (با این حال، از SQL بومی برای دستورات زبان تعریف داده (DDL) استفاده نکنید. شما باید به ایجاد تغییرات در سطح طرحواره با استفاده از GraphQL ادامه دهید تا backend و SDK های تولید شده خود را همگام نگه دارید.)
• ویژگی‌های خاص پایگاه داده : استفاده از توابع، عملگرها یا انواع داده‌های منحصر به فرد PostgreSQL.
• بهینه‌سازی عملکرد : تنظیم دستی دستورات SQL برای مسیرهای بحرانی.

فیلدهای ریشه SQL بومی

برای نوشتن عملیات با SQL، از یکی از فیلدهای ریشه زیر از انواع query یا mutation استفاده کنید:

فیلدهای query

میدان‌های mutation

یادداشت‌ها:

• عملیات با استفاده از مجوزهای اعطا شده به حساب سرویس Data Connect اجرا می‌شوند.

• اگر نام جدول را با استفاده از دستور @table به صورت صریح تنظیم می‌کنید ( @table(name: "ExampleTable") )، باید نام جدول را در دستورات SQL خود نیز داخل علامت نقل قول قرار دهید ( SELECT field FROM "ExampleTable" ... ).

  بدون علامت نقل قول، Data Connect نام جدول را به حالت مار ( example_table ) تبدیل می‌کند.

قوانین و محدودیت‌های نحوی

SQL بومی، قوانین تجزیه و تحلیل دقیقی را برای تضمین امنیت و جلوگیری از تزریق SQL اعمال می‌کند. از محدودیت‌های زیر آگاه باشید:

• توضیحات : از توضیحات بلوکی ( /* ... */ ) استفاده کنید. توضیحات خطی ( -- ) ممنوع است زیرا می‌توانند بندهای بعدی (مانند فیلترهای امنیتی) را در طول الحاق پرس‌وجو کوتاه کنند.
• پارامترها : از پارامترهای موقعیتی ( $1 ، $2 ) که با ترتیب آرایه params مطابقت دارند استفاده کنید. پارامترهای نامگذاری شده ( $id ، :name ) پشتیبانی نمی‌شوند.
• رشته‌ها : رشته‌های بسط‌یافته ( E'...' ) و رشته‌های دارای علامت نقل قول دلاری ($$...$$ ) پشتیبانی می‌شوند. اسکیپ‌های یونیکد PostgreSQL ( U&'...' ) پشتیبانی نمی‌شوند.

پارامترها در نظرات

تجزیه‌گر هر چیزی را که درون یک بلوک کامنت باشد نادیده می‌گیرد. اگر خطی حاوی پارامتر (مثلاً /* WHERE id = $1 */ ) را کامنت کنید، باید آن پارامتر را از لیست params نیز حذف کنید، در غیر این صورت عملیات با خطای unused parameter: $1 با شکست مواجه می‌شود.

مثال‌ها

مثال ۱: SELECT پایه با نام مستعار فیلد

شما می‌توانید به فیلد ریشه یک نام مستعار بدهید (برای مثال، movies: _select ) تا پاسخ کلاینت تمیزتر شود (به جای data._select از data.movies استفاده کنید).

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

پس از اجرای کوئری با استفاده از SDK کلاینت، نتیجه در data.movies خواهد بود.

مثال ۲: به‌روزرسانی اولیه

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 خواهد بود.

مثال ۳: تجمیع پایه

queries.gql :

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

پس از اجرای کوئری با استفاده از SDK کلاینت، نتیجه در data.stats.total_reviews خواهد بود.

مثال ۴: تجمیع پیشرفته با 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: []
  )
}

پس از اجرای کوئری با استفاده از SDK کلاینت، نتیجه در data._select خواهد بود.

مثال ۵: به‌روزرسانی با برگرداندن و زمینه احراز هویت

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 قرار خواهند گرفت.

مثال ۶: CTE پیشرفته با upsertها (گرفتن یا ایجاد اتمی)

این الگو برای اطمینان از وجود رکوردهای وابسته (مانند Users یا Movies) قبل از درج یک رکورد فرزند (مانند Review) مفید است، که همگی در یک تراکنش پایگاه داده واحد انجام می‌شوند.

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

مثال ۷: استفاده از افزونه‌های Postgres

SQL بومی به شما امکان می‌دهد بدون نیاز به نگاشت انواع هندسه پیچیده به طرح GraphQL یا تغییر جداول زیربنایی، از افزونه‌های Postgres مانند PostGIS استفاده کنید.

در این مثال، فرض کنید برنامه رستوران شما جدولی دارد که داده‌های مکان را در یک ستون JSON فراداده ذخیره می‌کند (برای مثال، {"latitude": 37.3688, "longitude": -122.0363} ). اگر افزونه PostGIS را فعال کرده باشید، می‌توانید از عملگرهای استاندارد Postgres JSON ( ->> ) برای استخراج این مقادیر درجا و ارسال آنها به تابع PostGIS ST_MakePoint استفاده کنید.

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

پس از اجرای کوئری با استفاده از SDK کلاینت، نتیجه در data.nearby خواهد بود.

بهترین شیوه‌های امنیتی: SQL پویا و رویه‌های ذخیره شده

Data Connect به طور ایمن تمام ورودی‌ها را در مرز GraphQL به پایگاه داده پارامتری می‌کند و به طور کامل از کوئری‌های استاندارد SQL شما در برابر تزریق SQL مرتبه اول محافظت می‌کند. با این حال، اگر از SQL برای فراخوانی رویه‌های ذخیره شده Postgres سفارشی یا توابعی که SQL پویا را اجرا می‌کنند استفاده می‌کنید، باید مطمئن شوید که کد داخلی PL/pgSQL شما این پارامترها را به طور ایمن مدیریت می‌کند.

اگر روال ذخیره شده شما مستقیماً ورودی‌های کاربر را در یک رشته EXECUTE ادغام کند، از پارامترسازی عبور کرده و یک آسیب‌پذیری تزریق 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;
$$;