Triển khai các thao tác Firebase Data Connect bằng SQL gốc

Firebase Data Connect cung cấp nhiều cách để tương tác với cơ sở dữ liệu Cloud SQL:

• GraphQL gốc: Xác định các loại trong schema.gql và Data Connect sẽ dịch các thao tác GraphQL của bạn thành SQL. Đây là phương pháp tiêu chuẩn, cung cấp khả năng nhập mạnh và các cấu trúc được thực thi theo lược đồ. Hầu hết tài liệu Data Connect bên ngoài trang này đều thảo luận về lựa chọn này. Nếu có thể, bạn nên sử dụng phương thức này để tận dụng tính an toàn về kiểu và khả năng hỗ trợ công cụ đầy đủ.
• Chỉ thị @view: Xác định một loại GraphQL trong schema.gql được hỗ trợ bằng một câu lệnh SQL SELECT tuỳ chỉnh. Điều này rất hữu ích khi tạo các khung hiển thị chỉ có thể đọc, được nhập mạnh mẽ dựa trên logic SQL phức tạp. Các loại này có thể truy vấn như các loại thông thường. Hãy xem @view.
• SQL gốc: Nhúng trực tiếp các câu lệnh SQL vào các thao tác được đặt tên trong .gql bằng cách sử dụng các trường gốc đặc biệt. Điều này mang lại tính linh hoạt tối đa và khả năng kiểm soát trực tiếp, đặc biệt đối với những thao tác không dễ dàng thể hiện trong GraphQL tiêu chuẩn, tận dụng các tính năng dành riêng cho cơ sở dữ liệu hoặc sử dụng các tiện ích PostgreSQL.

Hướng dẫn này tập trung vào lựa chọn SQL gốc.

Các trường hợp sử dụng phổ biến cho SQL gốc

Mặc dù GraphQL gốc cung cấp khả năng an toàn về kiểu đầy đủ và chỉ thị @view mang lại kết quả có kiểu dữ liệu mạnh cho các báo cáo SQL chỉ đọc, nhưng SQL gốc mang lại sự linh hoạt cần thiết cho:

• Tiện ích PostgreSQL: Trực tiếp truy vấn và sử dụng mọi tiện ích PostgreSQL đã cài đặt (chẳng hạn như PostGIS cho dữ liệu không gian địa lý) mà không cần ánh xạ các loại phức tạp trong giản đồ GraphQL.
• Truy vấn phức tạp: Thực thi SQL phức tạp bằng các phép liên kết, truy vấn phụ, hàm tổng hợp, hàm cửa sổ và quy trình được lưu trữ.
• Thao tác dữ liệu (DML): Thực hiện trực tiếp các thao tác INSERT, UPDATE, DELETE. (Tuy nhiên, đừng sử dụng SQL gốc cho các lệnh Ngôn ngữ định nghĩa dữ liệu (DDL). Bạn phải tiếp tục thực hiện các thay đổi ở cấp giản đồ bằng GraphQL để giữ cho phần phụ trợ và các SDK đã tạo được đồng bộ hoá.)
• Các tính năng dành riêng cho cơ sở dữ liệu: Sử dụng các hàm, toán tử hoặc kiểu dữ liệu dành riêng cho PostgreSQL.
• Tối ưu hoá hiệu suất: Điều chỉnh thủ công các câu lệnh SQL cho các đường dẫn quan trọng.

Các trường gốc SQL gốc

Để ghi các thao tác bằng SQL, hãy sử dụng một trong các trường gốc sau đây của các loại query hoặc mutation:

query trường

mutation trường

Lưu ý:

• Các thao tác được thực thi bằng các quyền được cấp cho tài khoản dịch vụ Data Connect.

• Nếu bạn đặt tên bảng một cách rõ ràng bằng cách sử dụng chỉ thị @table (@table(name: "ExampleTable")), bạn cũng phải đặt tên bảng trong dấu ngoặc kép trong câu lệnh SQL (SELECT field FROM "ExampleTable" ...).

  Nếu không có dấu ngoặc kép, Data Connect sẽ chuyển đổi tên bảng thành snake case (example_table).

Quy tắc và giới hạn về cú pháp

SQL gốc áp dụng các quy tắc phân tích cú pháp nghiêm ngặt để đảm bảo tính bảo mật và ngăn chặn việc chèn SQL. Hãy lưu ý những hạn chế sau:

• Nhận xét: Sử dụng nhận xét khối (/* ... */). Bạn không được sử dụng nhận xét dòng (--) vì chúng có thể cắt bớt các mệnh đề tiếp theo (chẳng hạn như bộ lọc bảo mật) trong quá trình nối truy vấn.
• Tham số: Sử dụng các tham số vị trí ($1, $2) khớp với thứ tự mảng params. Không hỗ trợ các tham số được đặt tên ($id, :name).
• Chuỗi: Các ký tự chuỗi mở rộng (E'...') và chuỗi có dấu đô la ($$...$$) được hỗ trợ. Không hỗ trợ ký tự thoát Unicode của PostgreSQL (U&'...').

Tham số trong phần nhận xét

Trình phân tích cú pháp bỏ qua mọi nội dung bên trong một khối chú thích. Nếu nhận xét một dòng chứa tham số (ví dụ: /* WHERE id = $1 */), bạn cũng phải xoá tham số đó khỏi danh sách params, nếu không, thao tác sẽ không thành công và gặp lỗi unused parameter: $1.

Ví dụ

Ví dụ 1: Câu lệnh SELECT cơ bản có bí danh trường

Bạn có thể đặt bí danh cho trường gốc (ví dụ: movies: _select) để phản hồi của máy khách rõ ràng hơn (data.movies thay vì 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]
  )
}

Sau khi chạy truy vấn bằng một SDK ứng dụng, kết quả sẽ nằm trong data.movies.

Ví dụ 2: Lệnh UPDATE cơ bản

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

Sau khi chạy thao tác sửa đổi bằng SDK máy khách, số lượng hàng bị ảnh hưởng sẽ nằm trong data._execute.

Ví dụ 3: Phương pháp tổng hợp cơ bản

queries.gql:

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

Sau khi chạy truy vấn bằng SDK ứng dụng, kết quả sẽ nằm trong data.stats.total_reviews.

Ví dụ 4: Tổng hợp nâng cao bằng hàm 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: []
  )
}

Sau khi chạy truy vấn bằng SDK ứng dụng, kết quả sẽ nằm trong data._select.

Ví dụ 5: Cập nhật bằng RETURNING và Ngữ cảnh uỷ quyền

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

Sau khi chạy thao tác sửa đổi bằng SDK máy khách, dữ liệu bài đăng đã cập nhật sẽ nằm trong data.updatedReview.

Ví dụ 6: CTE nâng cao với các thao tác chèn và cập nhật (lấy hoặc tạo nguyên tử)

Mẫu này rất hữu ích trong việc đảm bảo các bản ghi phụ thuộc (như Người dùng hoặc Phim) tồn tại trước khi chèn một bản ghi con (như Bài đánh giá), tất cả trong một giao dịch cơ sở dữ liệu duy nhất.

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

Ví dụ 7: Sử dụng các tiện ích Postgres

SQL gốc cho phép bạn sử dụng các tiện ích Postgres, chẳng hạn như PostGIS, mà không cần ánh xạ các loại hình học phức tạp vào giản đồ GraphQL hoặc thay đổi các bảng cơ bản.

Trong ví dụ này, giả sử ứng dụng nhà hàng của bạn có một bảng lưu trữ dữ liệu vị trí trong cột JSON siêu dữ liệu (ví dụ: {"latitude": 37.3688, "longitude": -122.0363}). Nếu đã bật tiện ích PostGIS, bạn có thể sử dụng các toán tử JSON Postgres tiêu chuẩn (->>) để trích xuất các giá trị này ngay lập tức và truyền chúng vào hàm 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]
  )
}

Sau khi chạy truy vấn bằng một SDK ứng dụng, kết quả sẽ nằm trong data.nearby.

Các phương pháp bảo mật hay nhất: SQL động và thủ tục được lưu trữ

Data Connect tham số hoá một cách an toàn tất cả các đầu vào tại ranh giới GraphQL đến cơ sở dữ liệu, bảo vệ hoàn toàn các truy vấn SQL chuẩn của bạn khỏi kỹ thuật chèn SQL bậc nhất. Tuy nhiên, nếu sử dụng SQL để gọi các hàm hoặc quy trình được lưu trữ Postgres tuỳ chỉnh thực thi SQL động, bạn phải đảm bảo mã PL/pgSQL nội bộ của mình xử lý các tham số này một cách an toàn.

Nếu quy trình lưu trữ của bạn nối trực tiếp dữ liệu đầu vào của người dùng vào một chuỗi EXECUTE, thì quy trình này sẽ bỏ qua việc tham số hoá và tạo ra lỗ hổng bảo mật SQL injection cấp hai:

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

Để tránh tình trạng này, hãy làm theo các phương pháp hay nhất sau:

• Sử dụng mệnh đề USING: Khi viết SQL động trong các quy trình được lưu trữ, hãy luôn sử dụng mệnh đề USING để liên kết các tham số dữ liệu một cách an toàn.
• Sử dụng format() cho giá trị nhận dạng: Sử dụng format() với cờ %I để chèn giá trị nhận dạng cơ sở dữ liệu an toàn (chẳng hạn như tên bảng).
• Chỉ cho phép giá trị nhận dạng: Không cho phép các ứng dụng khách tuỳ ý chọn giá trị nhận dạng cơ sở dữ liệu. Nếu quy trình của bạn yêu cầu các giá trị nhận dạng động, hãy xác thực dữ liệu đầu vào dựa trên danh sách cho phép được mã hoá cứng trong logic PL/pgSQL trước khi thực thi.

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