Connect to the OpenAI API from Supabase

Harness the power of AI in your apps from within your Supabase project, by creating a connection to the OpenAI API.

With another look at the Supabase http extension, we connect to the OpenAI API to use the Dall-e-3 image generation model. We will be making the API request via http, sending a prompt to the API and returning the url of our generated image that we can then use in our applications.

Here is the video and all of the code used is available below.

https://youtu.be/MX8qfc67zD0

Custom code for connecting to the OpenAI API

This is the function that connects to the API and returns an image based on your propmpt.

CREATE OR REPLACE FUNCTION create_image(description text) RETURNS JSONB SECURITY DEFINER AS $$
DECLARE
    request_data extensions.http_request; 
    response_data extensions.http_response;
    response_json jsonb;
    OPEN_AI_SECRET text;
    json_content jsonb;

BEGIN
    SELECT value::text INTO OPEN_AI_SECRET FROM private.keys WHERE key = 'OPEN_AI_SECRET';
    
    -- Construct JSON using jsonb_build_object
    json_content := jsonb_build_object(
        'model', 'dall-e-3',
        'prompt', description,
        'n', 1,
        'size', '1024x1024',
        'response_format','url'
    );

    -- Set the values for the http_request record
    request_data.method := 'POST';
    request_data.uri := 'https://api.openai.com/v1/images/generations';
    request_data.headers := ARRAY[
        ('Authorization', 'Bearer ' || OPEN_AI_SECRET)   
    ];
    request_data.content_type := 'application/json';
   
    request_data.content := json_content::text;

    -- Set timeout for HTTP request
    PERFORM set_config('http.timeout_msec', '45000', true);

    -- Making the HTTP POST request
    response_data := http(request_data)::extensions.http_response;

    -- Parsing the response JSON
    response_json := response_data.content::jsonb;

   

    -- Return the response JSON
    RETURN response_json;

END;
$$ LANGUAGE plpgsql;

This is the code we use to call the create_image function from within the SQL editor. Remember to change the prompt to the image you want to create.

SELECT create_image('a cabin in the woods');

This is the function to create your private schema and the table to store your API keys.

CREATE SCHEMA IF NOT EXISTS private;
CREATE TABLE IF NOT EXISTS private.keys (
    key text primary key not null,
    value text
);
REVOKE ALL ON TABLE private.keys FROM PUBLIC;