在Supabase 中使用 OpenAI

通过官网部署Supabase (opens new window)后，发现无法使用Ai Assistant并且在使用SQL Editor的时候会报Failed to generate title的错误

。

看了一下报错是没有设置OpenAi的key。由于我没有国外信用卡也不想办虚拟卡，所以一直用的中转。

这里通过在环境变量中增加BaseUrl实现将OpenAi的请求转发到中转(感谢OpenAI库提供的功能)然后增加Key即可。

修改docker-compose.yml中的studio服务，在environment中增加以下内容:

OPENAI_API_KEY: ${OPENAI_API_KEY}
OPENAI_BASE_URL: ${OPENAI_BASE_URL}

之后在.env文件中增加以下内容:

OPENAI_API_KEY=sk-
OPENAI_BASE_URL=https://api.openai.com/v1

重启服务即可。

但是这里只能解决一个问题，就是AiAssistant。如果在SQLEditor中执行SQL语句，还是会提示Failed to generate title。这里是因为用到了OpenAI的function功能，而Supabase目前使用的模型是gpt-3.5-turbo-0125现在不支持了，所以需要替换成gpt-4o-mini，虽然官方文档中还在支持列表，但是通过测试发现确实不行，而替换成gpt-4o-mini可以解决这个问题。

data = {
    "model": "gpt-4o-mini",
    "messages": [
      {
        "role": "user",
        "content": "select * from test"
      }
    ],
    "max_tokens": 1024,
    "temperature": 0,
    "tool_choice": {
      "type": "function",
      "function": {
        "name": "generateTitle"
      }
    },
    "tools": [
      {
        "type": "function",
        "function": {
          "name": "generateTitle",
          "description": "Generates a short title and summarized description for a Postgres SQL snippet.\nThe description should describe why this table was created (eg. \"Table to track todos\")",
          "parameters": {
            "type": "object",
            "additionalProperties": False,
            "properties": {
              "title": {
                "description": "The generated title for the SQL snippet (short and concise).\n- Omit these words: 'SQL', 'Postgres', 'Query', 'Database'",
                "type": "string"
              },
              "description": {
                "description": "The generated description for the SQL snippet.",
                "type": "string"
              }
            },
            "required": [
              "title",
              "description"
            ]
          }
        }
      }
    ],
    "stream": False
}

Response:

{
  "id": "chatcmpl-9rFePx8jM38j0aWuDmSqnOsaGQ6yI",
  "object": "chat.completion",
  "created": 1722478573,
  "model": "gpt-4o-mini-2024-07-18",
  "choices": [
    {
      "index": 0,
      "message": {
        "role": "assistant",
        "content": null,
        "tool_calls": [
          {
            "id": "call_jtPa4zpgqlhsgPEivNrVOxUW",
            "type": "function",
            "function": {
              "name": "generateTitle",
              "arguments": "{\"description\":\"Query to retrieve all records from the test table\",\"title\":\"Retrieve all records from test table\"}"
            }
          }
        ]
      },
      "logprobs": null,
      "finish_reason": "stop"
    }
  ],
  "usage": {
    "prompt_tokens": 127,
    "completion_tokens": 22,
    "total_tokens": 149
  },
  "system_fingerprint": "fp_0f03d4f0ee"
}

如果将gpt-4o-mini替换成gpt-3.5-turbo-0125，则无法触发function功能。

{
  "id": "chatcmpl-5Ld718d4erogtrp0e8ULpPVBERgaa",
  "object": "chat.completion",
  "created": 1722478667,
  "model": "gpt-3.5-turbo-0125",
  "choices": [
    {
      "index": 0,
      "message": {
        "role": "assistant",
        "content": "It looks like you're trying to execute a SQL command, specifically a query to select all records from a table named \"test.\" In SQL, the command `SELECT * FROM test;` retrieves all columns and rows from the specified table.\n\nIf you have a specific question about SQL, databases, or need help with a particular aspect of querying or managing data, feel free to ask! I can provide explanations, examples, or guidance on best practices."
      },
      "logprobs": null,
      "finish_reason": "stop"
    }
  ],
  "system_fingerprint": "fp_1b9fd620ce",
  "usage": {
    "completion_tokens": 0,
    "prompt_tokens": 0,
    "total_tokens": 0
  }
}

这里我选择通过FastAPI来写一个中转服务，将请求中的model替换成gpt-4o-mini，然后再转发请求到中转服务商。

from fastapi import FastAPI, Request
from fastapi.responses import JSONResponse, StreamingResponse
import json
import httpx
app = FastAPI()
base_url = "中转URL"
@app.api_route("/{full_path:path}", methods=["GET", "POST", "PUT", "DELETE"])
async def catch_all(request: Request, full_path: str):
    if "v1" not in full_path:
        return JSONResponse(content={'error': 'Invalid path'}, status_code=400)
    body = await request.body()
    url = base_url + full_path
    headers = dict(request.headers)
    data = json.loads(body.decode('utf-8'))
    data['model'] = "gpt-4o-mini"
    headers = {
        "Content-Type": "application/json",
        "Authorization": headers['authorization']
    }
    if data.get('stream', False):
        return StreamingResponse(fetch_data(url, data, headers))
    else:
        async with httpx.AsyncClient() as client:
            res = await client.post(url, json=data, headers=headers)
            if res.status_code == 200:
                print(1)
                return JSONResponse(content=res.json())
            else:
                return JSONResponse(content={'error': 'Invalid response from API', 'status_code': res.status_code})

async def fetch_data(url, data, headers):
    async with httpx.AsyncClient(timeout=60.0) as client:
        async with client.stream("POST", url, json=data, headers=headers) as response:
            if response.status_code != 200:
                yield b'Invalid response from API'
            else:
                try:
                    async for chunk in response.aiter_bytes():
                        yield chunk
                except httpx.StreamClosed:
                    yield b'Stream was closed unexpectedly.'

if __name__ == "__main__":
  import uvicorn
  uvicorn.run(app, host="0.0.0.0", port=8000)

之后修改.env替换OPENAI_BASE_URL为中转服务URL即可。

OPENAI_BASE_URL=http://localhost:8000/v1

然后功能就都正常了，但是即使生成了title，sql还是无法保存，刷新后还会消失。

function calling (opens new window): https://platform.openai.com/docs/guides/function-calling generate title code (opens new window): https://github.com/supabase/supabase/blob/master/packages/ai-commands/src/sql/functions.tsc/sql/functions.ts