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

Hướng dẫn viết các thao tác Firebase SQL Connect bằng SQL thay vì GraphQL. page_type: guide announcement: > SQL gốc có sẵn dưới dạng một tính năng Xem trước. Điều này có nghĩa là tính năng này không tuân theo bất kỳ SLA hoặc chính sách về việc ngừng cung cấp nào và có thể thay đổi theo những cách không tương thích ngược. Nếu bạn sử dụng tính năng này với các hàm hoặc quy trình được lưu trữ thực thi SQL động, hãy làm theo các phương pháp hay nhất về bảo mật được giải thích ở cuối trang này.

Firebase SQL 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à SQL 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 tính 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 SQL 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 khả năng hỗ trợ công cụ và độ an toàn về kiểu đầ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 tệp .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 các thao tác không được GraphQL tiêu chuẩn hỗ trợ, 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 gõ mạnh.

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 dữ liệu đầy đủ và chỉ thị @view cung cấp kết quả được gõ 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 kết hợp, 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ụ 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ụ SQL Connect.

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 hành vi chèn SQL. Hãy lưu ý những hạn chế sau:

• Nhận xét: Sử dụng khối nhận xét (/* ... */). 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 được hỗ trợ các tham số được đặt tên ($id, :name).
• Chuỗi: Các chữ cố định chuỗi mở rộng (E'...') và chuỗi có dấu ngoặc kép bằng đô la ($$...$$) được hỗ trợ. Không hỗ trợ ký tự thoát Unicode của PostgreSQL (U&'...').

Tham số trong phần bình luận

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ới lỗi unused parameter: $1.

Quy ước đặt tên

Khi viết SQL gốc, bạn đang tương tác trực tiếp với cơ sở dữ liệu PostgreSQL, vì vậy, bạn phải sử dụng tên cơ sở dữ liệu thực cho các bảng và cột. Theo mặc định, SQL Connect sẽ tự động liên kết các tên trong giản đồ GraphQL với snake case trong cơ sở dữ liệu, trừ phi bạn tuỳ chỉnh một cách rõ ràng các giá trị nhận dạng Postgres bằng cách sử dụng các chỉ thị @table(name) và @col(name).

Nếu bạn xác định một loại không có chỉ thị, thì tên bảng và trường GraphQL sẽ ánh xạ đến các giá trị nhận dạng snake_case Postgres mặc định:

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

Theo mặc định, giá trị nhận dạng PostgreSQL không phân biệt chữ hoa chữ thường. Nếu sử dụng các chỉ thị như @table hoặc @col để chỉ định một tên chứa các chữ cái viết hoa hoặc chữ cái viết hoa và viết thường, bạn phải đặt giá trị nhận dạng đó trong dấu ngoặc kép trong câu lệnh SQL.

Trong ví dụ sau, bạn phải sử dụng "UserProfiles" cho tên bảng và "profileId" cho cột userId. Trường displayName tuân theo lượt chuyển đổi mặc định thành 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]
  )
}

Ví dụ về cách dùng

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

Bạn có thể đặt tên thay thế cho trường gốc (ví dụ: movies: _select) để phản hồi của ứng dụng 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 (chẳng hạn như Người dùng hoặc Phim) tồn tại trước khi chèn một bản ghi con (chẳng hạn 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!) @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 và _executeReturningFirst bao bọc truy vấn của bạn trong một CTE mẹ để định dạng đầu ra dưới dạng JSON. PostgreSQL không cho phép lồng một CTE sửa đổi dữ liệu bên trong một câu lệnh sửa đổi dữ liệu khác, khiến truy vấn không thành công.

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 sửa đổ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ữ

SQL 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 trực tiếp nối các 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;
$$;