The ai assistant of supabase sql editor is not working in the local development.

[devhkr]

When using AI ASSISTANT in a local environment, the following error occurs

Looking at the logs, I see the following error

2024-02-27 22:32:52 OpenAIError: The OPENAI_API_KEY environment variable is missing or empty; either provide it, or instantiate the OpenAI client with an apiKey option, like new OpenAI({ apiKey: 'My API Key' }). 2024-02-27 22:32:52 at new OpenAI (file:///app/node_modules/openai/index.mjs:26:19) 2024-02-27 22:32:52 at /app/apps/studio/.next/server/pages/api/ai/sql/edit.js:1:1710

Doesn't AI ASSISTANT work in the local environment by default? Or is there something wrong with my settings?
If the settings are wrong, I'm wondering how I can fix them. :)

[charislam]

Hey @devhkr! The clue is in this part of the error message: The OPENAI_API_KEY environment variable is missing or empty

You'll need to provide your own OPENAI_KEY in your env file.
(Note Open AI is expecting the default OPENAI_API_KEY, but in our code we've renamed it OPENAI_KEY, so that's what you'll have to name yours as well.)

[devhkr]

I've uploaded the screenshots for clarity for those of you who are aware of this situation.
I removed the sensitive information. :)

I followed the following documentation to set up my development environment.
https://supabase.com/docs/guides/cli/local-development

And as shown above, I wrote both OPENAI_API_KEY and OPENAI_KEY in the env.local file.
I also wrote them to the .env file just in case.
Next, I restarted supabase and got the same error.

Am I doing something wrong?

[charislam]

Hey @devhkr! Thanks for your patience!

Apparently I misunderstood your original question: I thought you were doing local development on studio, but if I understand correctly, you're running Supabase locally with the CLI to do local development of your own app. 🤦🏼‍♀️ Sorry, I should've looked at your original screenshots a bit more carefully.

In that case, I believe the solution is to just pass the environment variable when running supabase start (see this PR that added the feature). Let me know if that works for you!

[devhkr]

Hi @charislam
I apologize for any confusion I may have caused by not explaining the situation in enough detail. 😔
I installed the CLI with homebrew and the latest version is 1.145.4.
That PR seems to have been applied in v1.147.3.
I think that's why the env file didn't work.
So it looks like the supabase team will need to update the supabase cli package for homebrew.

[devhkr]

Hi @charislam
I upgraded the supabase package in homebrew and it works fine with no errors.
brew upgrade supabase
Thanks for all your help. :)

[charislam]

Awesome, glad to hear it worked!

[mrcaidev]

In a nutshell, put OPENAI_API_KEY=... in your-project/.env.

A few catches that confused me for quite some time:

1. .env.local doesn't work in the time of writing. Only .env is supported.
2. Put .env in the root of your project, not in supabase/.

[HoekWax]

You did manage to make it work ? I did what you wrote, i then run supabase start.
But the AI assistant doesn't work locally, when I log the error using docker logs supabase_studio_lvlup-supabase, I get this error

⨯ OpenAIError: The OPENAI_API_KEY environment variable is missing or empty; either provide it, or instantiate the OpenAI client with an apiKey option, like new OpenAI({ apiKey: 'My API Key' }).
    at new OpenAI (file:///app/node_modules/openai/index.mjs:26:19)
    at /app/apps/studio/.next/server/pages/api/ai/sql/edit.js:1:1872

When i execute docker exec -it supabase_studio_lvlup-supabase env to check the env, I see that OPENAI_KEY exists in the env, it has the value from the key OPENAI_API_KEY that I set in my .env

It seems like the studio wants to use OPENAI_API_KEY but the value set in the env of the container is OPENAI_KEY, event if in my .env at the root folder I only set the key OPENAI_API_KEY and not the key OPENAI_KEY

Does anyone have the same issue ? Or know what's going on ?

[mkungla]

Inconsistency and Issues with Environment Configuration

I am encountering several persistent issues that significantly impact the development workflow, specifically around environment configuration and error handling. I would appreciate any help or clarification on these matters:

1. Inconsistent Naming for Environment Variables

There's an ongoing inconsistency in how environment variables are named (OPENAI_API_KEY vs. OPENAI_KEY). This can lead to confusion and potential errors in configuration.

2. Loading .env Files in a Monorepo

The logic for loading .env files in a monorepo setup is unclear, especially when dealing with nested directories. For instance:

• .env is not recognized when placed in the root (.) or directly in the service directory (./services/datastore/supabase).
• It is instead only recognized when placed in ./services/datastore/.env.

3. Local Environment Error

Despite the .env files loading correctly, attempts to execute requests result in an error. Here is the error message received:

{
  "error": "There was an unknown error editing the SQL snippet. Please try again."
}

4. Concern About Closed Related Issues

I've noticed that previous bug reports related to these issues were closed without a clear resolution. Can someone explain the rationale behind these decisions? It would be beneficial to reopen these discussions to address and resolve the concerns effectively.

[rngadam]

Facing the same issues. First, error messsage in frontend talks about OPENAI_KEY, but what makes it work seems to be OPENAI_API_KEY:

after setting OPENAI_API_KEY in the root .env, I get a different, cryptic error message:

found out after looking up the logs from supabase/studio container that it is actually caused by exceeding quota:

$ docker logs e0559b085f10
  ▲ Next.js 14.2.0
  - Local:        http://localhost:3000
  - Network:      http://0.0.0.0:3000

 ✓ Starting...
 ✓ Ready in 1868ms
AI SQL generation failed: 429 You exceeded your current quota, please check your plan and billing details. For more information on this error, read the docs: https://platform.openai.com/docs/guides/error-codes/api-errors.
AI SQL generation failed: 429 You exceeded your current quota, please check your plan and billing details. For more information on this error, read the docs: https://platform.openai.com/docs/guides/error-codes/api-errors.

[hgup]

$ docker logs e0559b085f10
  ▲ Next.js 14.2.0
  - Local:        http://localhost:3000
  - Network:      http://0.0.0.0:3000

 ✓ Starting...
 ✓ Ready in 1868ms
AI SQL generation failed: 429 You exceeded your current quota, please check your plan and billing details. For more information on this error, read the docs: https://platform.openai.com/docs/guides/error-codes/api-errors.
AI SQL generation failed: 429 You exceeded your current quota, please check your plan and billing details. For more information on this error, read the docs: https://platform.openai.com/docs/guides/error-codes/api-errors.

+1 It exceeds rate limit. I don't know what limit it is referring to since I never used my OPEN AI API keys before

[HoekWax]

It's because you didn't set a credit card in you open ai account, you have to buy like 10 dollars or more of credits so you can use the api

[hgup]

Didn't know that... silly me... thanks

[devhkr]

I put OPENAI_API_KEY in the .env file and it worked fine.
But after upgrading the supabase CLI library, the error occurs again.
So I'm reopening the discussion. 🥲

OpenAIError: The OPENAI_API_KEY environment variable is missing or empty; either provide it, or instantiate the OpenAI client with an apiKey option, like new OpenAI({ apiKey: 'My API Key' }).

[HoekWax]

Hello @charislam, can you check if everything is alright with that ? It seems like a naming issue for the OpenAi api key still exists, I've described it on another post in this discussion

[devhkr]

I upgraded the supabase cli to 1.169.8 using homebrew, and it doesn't cause any errors, but the UI has changed like a service in the cloud, but the AI assistant doesn't work properly.
I thought it would work over time, so I decided not to use it for now.

[HoekWax]

I had another issue and they fixed it in beta release for now

So i tried starting the studio using the beta cli release by running npx supabase@beta start and it works !

[devhkr]

It hasn't been applied to the homebrew database yet, so I'll have to try it when it is.

[adamlamaa]

I had this issue and upgraded from supabase 1.167.4 -> 1.172.2 which resolved the issue and the AI assistante now works in my studio.

I have a .env with the OPENAI_API_KEY key in the root of my repo.

[ifallah]

i add

OPENAI_API_KEY=yourkey
OPENAI_KEY=yourkey

in (.env) & (docker-compose.yml)

and copye .env to root folders supabase

and its work now

[Rocke1001feller]

1、summary

-- before 2024/04/25 or before v1.24.04,

add OPENAI_KEY to .env or docker-compose.yml, any setting is OK .. and most important executedocker compose up --force-recreate

-- after 2024/04/25 or after v1.24.04(include),

use OPENAI_API_KEY to .env or docker-compose.yml, any setting is OK ..and most important execute docker compose up --force-recreate

2、below are details

[adao]

why do i need an openai_api_key at all? any option to turn this off?

[Monu18765]

Yup good question