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 dịch các thao tác GraphQL thành SQL. Đây là phương pháp tiêu chuẩn, cung cấp tính năng nhập mạnh mẽ và các cấu trúc được thực thi theo giản đồ. 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. Khi có thể, bạn nên sử dụng phương thức này để tận dụng tính năng an toàn về kiểu đầy đủ và hỗ trợ công cụ.
• Chỉ thị @view: Xác định một loại GraphQL trong schema.gql được hỗ trợ bởi câu lệnh SQL SELECT tuỳ chỉnh. Điều này hữu ích cho việc tạo các khung hiển thị chỉ đọc, được nhập mạnh mẽ dựa trên logic SQL phức tạp. Bạn có thể truy vấn các loại này như các loại thông thường. 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 .Tệp gql sử dụng các trường gốc đặc biệt. Điều này mang lại sự linh hoạt tối đa và khả năng kiểm soát trực tiếp, đặc biệt là đối với các 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. Không giống như GraphQL và chỉ thị @view, SQL gốc không cung cấp đầu ra được nhập mạnh mẽ.

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 tính năng an toàn về kiểu đầy đủ và chỉ thị @view cung cấp kết quả được nhập mạnh mẽ 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: Truy vấn và sử dụng trực tiếp 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 liên kết 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 nối, truy vấn con, 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à SDK đã tạo được đồng bộ.)
• 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 loại 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

Để viết 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 loại query hoặc mutation:

Trường query

Trường mutation

Lưu ý:

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

• Nếu bạn đặt tên bảng một cách rõ ràng bằng chỉ thị @tabledirective (@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 thực thi 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 (/* ... */). Không được phép 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 (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 được hỗ trợ các tham số được đặt tên ($id, :name).
• Chuỗi: Hỗ trợ các chuỗi ký tự mở rộng (E'...') và chuỗi được trích dẫn bằng dấu đô la ($$...$$). Không hỗ trợ ký tự thoát Unicode 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 nhận xét khối. Nếu bạn nhận xét một dòng có 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à báo lỗi unused parameter: $1.

Ví dụ

Ví dụ 1: 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) để làm cho phản hồi của ứng dụng gọn gà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 SDK ứng dụng, kết quả sẽ nằm trong data.movies.

Ví dụ 2: 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 thay đổi bằng SDK ứng dụng, số lượng hàng bị ảnh hưởng sẽ nằm trong data._execute.

Ví dụ 3: 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 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: UPDATE có RETURNING và Ngữ cảnh xác thực

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 thay đổi bằng SDK ứng dụng, dữ liệu bài đăng đã cập nhật sẽ nằm trong data.updatedReview.

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

Mẫu này hữu ích để đả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.

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 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 liên kết 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 một 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 SDK ứng dụng, kết quả sẽ nằm trong data.nearby.

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

Data Connect tham số hoá an toàn tất cả dữ liệu đầ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 tiêu chuẩn của bạn khỏi việc chèn SQL cấp mộ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 được lưu trữ của bạn trực tiếp nối 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 chèn SQL 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 điều 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() có cờ %I để chèn giá trị nhận dạng cơ sở dữ liệu an toàn (như tên bảng).
• Cho phép nghiêm ngặt giá trị nhận dạng: Đừng để ứ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 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 bên 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;
$$;