如何使用PostgreSQL和Edge功能创建supabase的伪外国数据包装器-DEV365 开发者社区

在这篇博客文章中，我们将探讨如何使用PostgreSQL和Supabase的Edge功能为Supabase创建pseudo-mysql外国数据包装器。我们将讨论此包装器背后的动机，以及如何使开发人员从MySQL数据库获取数据。

在保险库中设置`service_role`键

o确保对我们的边缘功能的安全性和访问控制，我们需要将其限制为仅接受管理员/服务器请求。为此，我们将使用Vault安全地将Service_role密钥存储在数据库中。 Vault是管理秘密和保护敏感信息的流行工具。通过将Service_role键存储在保险库中，我们可以确保其机密性和完整性。

要在金库中设置Service_role键，请按照以下步骤：

打开supabase仪表板。
转到项目设置。
导航到保险库秘密配置：Supabase Vault Secrets
将Service_role密钥牢固地存储在保险库中。

创建MySQL包装器功能

现在，让我们深入了解代码，并了解包含MySQL包装器的每个功能的逻辑和功能。这些功能使开发人员能够通过Supabase Edge函数从MySQL数据库中检索数据。

我们将检查的第一个功能是http_post_with_auth。该功能充当PostgreSQL中HTTP扩展程序周围的方便包装器，从而使我们能够使用携带者令牌向Edge函数提出认证的请求。它将URL地址，发布数据和携带者令牌作为输入参数，并以表格式返回响应状态和内容。

这是http_post_with_auth函数的代码：

--
-- Function to make HTTP POST request with authentication
--
CREATE OR REPLACE FUNCTION public.http_post_with_auth(
    url_address text, 
    post_data text, 
    bearer text
)
 RETURNS TABLE(_status text, _content jsonb)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public', 'extensions'
AS $function$
DECLARE
  full_bearer TEXT := 'Bearer ' || bearer;
  response RECORD;
BEGIN
  -- Make the HTTP POST request with the given URL, data, and bearer token
  SELECT status::text, content::jsonb
  INTO response
  FROM http((
          'POST',
           url_address,
           ARRAY[http_header('Authorization', full_bearer), http_header('Content-Type', 'application/json')],
           'application/json',
           coalesce(post_data, '') -- Set content to an empty string if post_data is NULL
        )::http_request);

  -- Raise an exception if the response content is NULL
  IF response.content IS NULL THEN
    RAISE EXCEPTION 'Error: Edge Function returned NULL content. Status: %', response.status;
  END IF;

  -- Return the status and content of the response
  RETURN QUERY SELECT response.status, response.content;
END;
$function$;

边缘包装器

接下来，让我们讨论edge_wrapper函数。此函数将vault的service_role解密，并请求边缘函数，将mySQL查询作为参数传递。它从Vault检索API键，执行HTTP调用到边缘函数，并将响应返回为JSON对象。

这是edge_wrapper函数的代码：

--
-- Wrapper function for making queries to the Edge Function
--
CREATE OR REPLACE FUNCTION public.edge_wrapper(query text)
 RETURNS jsonb
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public', 'extensions', 'vault'
AS $function$
DECLARE
  api_key TEXT;
  response JSON;
  edge_function_url TEXT := 'https://wqazfpwdgwumetjycblf.supabase.co/functions/v1/mysql_wrapper';
BEGIN
  -- Get the API key from the vault
  SELECT decrypted_secret
  INTO api_key
  FROM vault.decrypted_secrets
  WHERE name = 'service_role';

  -- Make the HTTP call to the Edge Function
  SELECT _content::JSON
  INTO response
  FROM http_post_with_auth(
    edge_function_url,
    json_build_object('query', query)::TEXT,
    api_key
  );

  -- Return the JSON response
  RETURN response;
END;
$function$;

MySQL便利功能

mysql()函数基于提供的columns参数动态构建列表达式。它通过列数组循环，从JSON对象中提取相应的值，并分配与列名称匹配的别名。

--
-- Function to execute a MySQL query and return the specified columns
--
CREATE OR REPLACE FUNCTION mysql(query text, VARIADIC columns text[])
  RETURNS SETOF RECORD
  LANGUAGE plpgsql
AS $function$
DECLARE
  column_exprs text := '';
BEGIN
  -- Construct the column expressions dynamically based on the provided columns
  FOR i IN 1..array_length(columns, 1) LOOP
    IF i > 1 THEN
      column_exprs := column_exprs || ', ';
    END IF;
    column_exprs := column_exprs || format('(obj->>''%s'') AS %s', columns[i], columns[i]);
  END LOOP;

  -- Execute the dynamic query and return the result
  RETURN QUERY EXECUTE format('SELECT %s FROM jsonb_array_elements(edge_wrapper($1)) AS obj', column_exprs) USING query;
END;
$function$;

您可以通过在query参数之后传递作为单个参数来指定要从JSON数据中检索的列。

创建边缘功能

在我们深入了解实施细节之前，让我们使用Supabase CLI在DeNo中设置必要的连接秘密。这些秘密将允许我们的边缘功能与MySQL数据库建立连接。打开终端并运行以下命令

 supabase secrets set MYSQL_HOST=127.0.0.1
 supabase secrets set MYSQL_USER=db_user
 supabase secrets set MYSQL_DBNAME=database_name
 supabase secrets set MYSQL_PASSWORD=123

确保用实际主机，用户名，数据库名称和密码替换值（127.0.0.1，db_user，database_name，123）。

上面的秘密将在我们的边缘功能代码中访问以牢固地建立必要的连接参数。

边缘功能代码

下面的代码片段演示了负责执行查询并以JSON格式返回结果的边缘函数的实现。此外，它包含授权检查以确保仅处理授权请求。


import { serve } from "https://deno.land/std@0.192.0/http/server.ts";
import { Client } from "https://deno.land/x/mysql@v2.11.0/mod.ts";

// Serve the HTTP request
serve(async (req: Request) => {
  // Check if the request method is POST
  if (req.method !== "POST") {
    return new Response("Method not allowed", { status: 405 });
  }

  try {
    // Retrieve the service role key from the environment variables
    const serviceRole = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY");
    // Retrieve the token from the Authorization header
    const token = req.headers.get("Authorization")?.split(" ")[1];

    // Check if the token is missing or invalid
    if (!token) {
      return new Response("Missing authorization header", { status: 401 });
    }
    if (token !== serviceRole) {
      console.log(token + "\n" + serviceRole);
      return new Response("Not authorized", { status: 403 });
    }

    // Parse the request body and retrieve the query
    const requestBody = await req.json();
    const query = requestBody.query;

    // Check if the query is missing
    if (!query) {
      return new Response("Missing query parameter", { status: 400 });
    }

    // Retrieve the MySQL connection details from the environment variables
    const host = Deno.env.get("MYSQL_HOST");
    const user = Deno.env.get("MYSQL_USER");
    const db_name = Deno.env.get("MYSQL_DBNAME");
    const password = Deno.env.get("MYSQL_PASSWORD");

    // Connect to the MySQL database
    const client = await new Client().connect({
      hostname: host!,
      username: user!,
      db: db_name!,
      password: password!,
    });

    // Execute the query and store the response
    const response = await client.query(query);

    // Close the database connection
    await client.close();

    // Return the response as JSON
    return new Response(JSON.stringify(response), {
      headers: { "Content-Type": "application/json" },
    });
  } catch (error) {
    // Log the error and return an error response
    console.error(error);
    return new Response(JSON.stringify({ error: "An error occurred" }), {
      status: 500,
      headers: { "Content-Type": "application/json" },
    });
  }
});

上面的代码段说明了边缘函数的示例实现。它使用Deno的serve()函数设置了HTTP服务器，并聆听传入POST请求。该函数执行各种检查，例如确保请求方法已发布，验证授权标题并验证所需查询参数的存在。

检查通过后，该函数将从前设置的秘密中检索MySQL连接详细信息。它使用mysql模块的Client类建立了与MySQL数据库的连接。执行请求主体中指定的查询，并将响应发送回JSON字符串。

从MySQL获取数据

要使用supabase从MySQL数据库中检索数据，您有两个选项：将数据作为JSON获取或使用mysql()便利函数将其作为列获取。

将数据获取为JSON：要检索数据为JSON，您可以使用edge_wrapper函数。这是一个例子：

SELECT * FROM edge_wrapper('SELECT * FROM wp_comments;');

此查询将以JSON数组的形式从wp_comments表返回数据。

将数据获取为列：如果您希望将数据作为单独的列获取，则可以使用mysql()函数。这是一个例子：

SELECT * FROM mysql(
    'SELECT * FROM wp_comments;', 
    'user_id', 
    'comment_date', 
    'comment_content'
    ) 
-- Note that we need to specify the output format to get the table
AS (user_id text, comment_date text, comment_content text);

在此示例中，我们从wp_comments表中获取user_id，comment_date和comment_content列。通过在AS子句中指定输出格式和列类型，我们可以将结果构造为具有所需列名称和数据类型的表。

您可以通过在mysql()函数中扩展列名列表来包含其他输出列，如下所示：

SELECT * FROM mysql(
    'SELECT * FROM wp_comments;', 
    'user_id', 
    'comment_date', 
    'comment_content',
    'comment_agent'
) AS (
    user_id text, 
    comment_date text, 
    comment_content text, 
    comment_agent text
);

在这种情况下，我们在结果中添加了comment_agent列。

计数行：您还可以使用mysql()函数来执行行计数。这是一个例子：

SELECT * FROM mysql(
    'SELECT COUNT(*) AS post_count FROM wp_posts;',
    'post_count'
) AS (post_count text);

此查询从wp_posts表中检索行计数，并将其分配给post_count列。

通过以这些方式使用mysql()函数，您可以从Supabase中的MySQL数据库中获取数据。在下一部分中，我们提出一些更高级的示例。

查询WordPress的高级示例

SELECT * FROM mysql(
    'SELECT post_title, display_name FROM wp_posts 
    INNER JOIN wp_users ON wp_posts.post_author = wp_users.ID 
    WHERE post_type = "post";',
    'post_title',
    'display_name'
) AS (post_title text, author_name text);

在此示例中，我们从WordPress数据库中获取帖子标题和相应的作者名称。查询分别在post_author和ID列上加入wp_posts和wp_users表。我们还包括一个条件（WHERE post_type = "post"）以检索帖子。结果将包括列post_title和author_name。

计数每个帖子的评论

SELECT * FROM mysql(
    'SELECT p.ID, p.post_title, COUNT(c.comment_ID) AS comment_count 
    FROM wp_posts p 
    LEFT JOIN wp_comments c ON p.ID = c.comment_post_ID 
    WHERE p.post_type = "post" 
    GROUP BY p.ID;',
    'ID',
    'post_title',
    'comment_count'
) AS (id text, post_title text, comment_count text);

在此示例中，我们检索WordPress数据库中每个帖子的帖子ID，帖子标题和评论计数。该查询分别基于wp_posts和wp_comments表之间的左连接，分别基于ID和comment_post_ID列。我们应用条件仅选择帖子（WHERE koude42_type = "post"），然后使用GROUP BY子句来汇总每个帖子的注释计数。结果包括post_id，post_title和comment_count的列。

提取用户角色

SELECT * FROM mysql(
    'SELECT user_login, meta_value AS role 
    FROM wp_users 
    INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id 
    WHERE meta_key = "wp_capabilities";',
    'user_login',
    'role'
) AS (username text, role text);

在此示例中，我们检索了WordPress数据库中用户的用户名和相应角色。查询分别在ID和user_id列上加入wp_users和wp_usermeta表。我们包括一个条件（WHERE meta_key = "wp_capabilities"），仅选择用户角色。结果包括username和role列。

结论

在这篇博客文章中，我们深入研究了使用PostgreSQL和Supabase的Edge函数为Supabase创建伪外的数据包装器的过程。通过利用Supabase和PostgreSQL的功能，开发人员可以将其MySQL数据无缝集成到其Postgres数据库中。本文中提供的代码示例是在您的项目中实施此功能的起点，为使用Supabase和PostgreSQL提供了令人兴奋的可能性。

我们鼓励您探索并尝试此处讨论的概念和技术，以适应您的特定需求和用例。通过利用Supabase和PostgresQL的合并能力，您可以解锁新途径，以在应用程序中有效的数据管理和集成。

在保险库中设置service_role键