Select ChatGPT From SQL? You Bet!

By Jessie Hobb On Feb 2, 2023

Prologue

I’m stating the obvious here. ChatGPT, released just eight weeks ago, has taken the whole world by storm. Microsoft is rumored to have invested $10B in it, and Sathya Nadella expects this to transform every Microsoft Product. Eventually, this should even come to SQL Server, the product I used to ship to Microsoft in the 90s. SQL itself is entering its 50th year and has continuously evolved to reign over all languages. Can SQL do ChatGPT? Let’s see. Examples here are from Couchbase. Couchbase has SQL for JSON, called N1QL, aka SQL++. Also, this article is different from the other 71 articles I’ve written here. You’ll see it if you read long enough!

“Every product of Microsoft will have the same AI capabilities to completely transform the product.” Sathya Nadella

Summary

Couchbase N1QL (Non-First Normal Form Query Language), aka SQL++, is a SQL-like language for querying JSON data stored in Couchbase. The CURL() function in Couchbase N1QL allows you to make HTTP requests to external services. It can be used to invoke ChatGPT by sending a request to its API endpoint. A JavaScript UDF (User-Defined Function) in Couchbase is a function written in JavaScript that can be executed as part of an N1QL query. It can also be used to invoke ChatGPT by making an HTTP request to its API endpoint. To connect ChatGPT with Couchbase N1QL, you need to know the API endpoint of ChatGPT and the authentication credentials required to access it. The syntax for invoking ChatGPT from Couchbase N1QL using the CURL() function may look like this:

SELECT CURL("https://api.openai.com/v1/engines/davinci/completions", {
    "request": "POST",
    "headers": [
        "Authorization: Bearer ChangeToYourKeyElseWontWork",
        "Content-Type: application/json"
        ],
    "data": '{
    "prompt": "Write a song on SQL using the style of Taylor Swift songs.",
        "max_tokens": 200
    }'
}) AS result ;

Here’s the response:

[
  {
    "result": {
      "choices": [
        {
          "finish_reason": "length",
          "index": 0,
          "logprobs": null,
          "text": " Where Swift sneakily commands, “…here you go / I wrote it on my phone so you could Google it,” you should write: “…here’s my query / I’ll drink my coffee and then will spool it.” Or, “…Here’s what I’m thinking / I pilfered my colleague’s project.” Only you can aspire to provide the official Taylor Swift SQL spoof song (don’t forget to play some chord tinkling).\n\n4. Invent an official buzzword for lambda expressions. Start a campaign to make “lambda-eme” or variants thereof the standard. Try to get lambda-me in all the common tools available (lamellar, lambmaster, lambator, lambda café, lambananas, and so on).\n\n5. Write about a process that took you too long in just 5 minutes. Make fun of"
        }
      ],
      "created": 1675103061,
      "id": "cmpl-6eT7FnvGFN9HViONQnDhv5uabM6AO",
      "model": "davinci",
      "object": "text_completion",
      "usage": {
        "completion_tokens": 200,
        "prompt_tokens": 13,
        "total_tokens": 213
      }
    }
  }
]

To pass parameters to ChatGPT, you can modify the data field in the above example. For example:

    "data": '{
    "prompt": "Write a song on SQL using the style of Taylor Swift songs.",
    "temperature": 0.5,
    "max_tokens": 200
    }'

Here Are the Questions the Rest of the Article Will Try to Answer:

What is Couchbase N1QL, and how does it work?
What is the CURL() function, and how is it used in Couchbase N1QL?
What is a JavaScript UDF in Couchbase, and how is it different from CURL()?
How to connect ChatGPT with Couchbase N1QL using the CURL() function and/or JavaScript UDF?
What is the syntax for invoking ChatGPT from Couchbase N1QL using CURL() function and/or JavaScript UDF?
How to pass parameters to ChatGPT from Couchbase N1QL using CURL() function and/or JavaScript UDF?
What are the best practices for error handling and debugging when invoking ChatGPT from Couchbase N1QL using CURL() function and/or JavaScript UDF?
What are the limitations and considerations when using CURL() function and/or JavaScript UDF to invoke ChatGPT from Couchbase N1QL?

Couchbase N1QL and CURL()

Couchbase N1QL (Non-First Normal Form Query Language) is a SQL-like language for querying JSON data stored in Couchbase. It provides various functions for transforming and manipulating data, and one of these functions is the CURL() function. The CURL() function in Couchbase N1QL allows you to make HTTP requests to external services from within an N1QL query. This can be useful for integrating Couchbase with other systems, such as web APIs, or for fetching data from the web for further processing in Couchbase.

The syntax for using the CURL() function in Couchbase N1QL is as follows:

SELECT CURL(<url>, <options>);

SELECT RAW list FROM CURL("https://api.github.com/users/sitaramv/repos") AS list LIMIT 1;

In this example, the CURL() function is sending a GET request to the URL, which returns the repos for the user sitaramv. The result of the function is stored in the response variable list, which can be further processed using other N1QL functions.

The <options> JSON object can contain several properties, such as a method for specifying the HTTP method (GET, POST, PUT, etc.), headers for setting custom HTTP headers, and data for sending data in the body of the request.

Here is an example of how the CURL() function can be used to post data to an external

select imagetext 
from curl("https://vision.googleapis.com/v1/images:annotate?key=PUT YOUR KEY HERE", 
         {"request": "POST", 
          "header":"Content-Type: application/json", 
           "data": '{ "requests": [ { "image": { "source": { "imageUri": "http://www.couchbase.com/blog/wp-content/uploads/2018/01/Screen-Shot-2018-01-21-at-6.50.38-PM.png" } }, "features": [ { "type": "TEXT_DETECTION" } ] } ] }'}) AS imagetext
 

In this example, the CURL() function is sending a POST request to the URL with a JSON payload in the body of the request. The custom header Content-Type: application/jsonis set to indicate that the data in the request body is in JSON format.

The CURL() function in Couchbase N1QL provides a convenient way to connect Couchbase to external systems and fetch data from the web. With the ability to specify the HTTP method, headers, and request body, it offers a lot of flexibility and can be used in a variety of scenarios.

Couchbase N1QL (Non-First Normal Form Query Language) provides several functions for transforming and manipulating data, and one of these functions is the JavaScript User-Defined Function (UDF). A JavaScript UDF is a custom function written in JavaScript that can be used within an N1QL query to perform more complex operations.

The JavaScript UDF in Couchbase N1QL allows you to write custom logic in JavaScript, which can then be executed as part of an N1QL query. This provides a lot of flexibility, as you can write JavaScript code to perform complex operations that cannot be achieved using the built-in N1QL functions.

Here is an example of how a JavaScript UDF can be used in Couchbase N1QL:

curl -v -X POST \
http://localhost:8093/evaluator/v1/libraries/mysamples \
-u Administrator:password \
-H 'content-type: application/json' \
-d 'function square(val) {  \
return val * val;    \
}

CREATE FUNCTION square(val)
  LANGUAGE JAVASCRIPT AS "square" AT "mysamples";

SELECT square(5) as result;

In this example, a JavaScript UDF named square is created that takes a single argument val and returns its square. The square function is defined using the CREATE FUNCTION statement, and the JavaScript code for the function is enclosed in a pair of $$. The LANGUAGE javascript option specifies that the code for the function is written in JavaScript.

The JavaScript UDF can be used within an N1QL query by calling the function and passing the necessary arguments. In this example, the square function is called with argument value 5, and the result is stored in the result variable.

The JavaScript UDF can itself issue N1QL statements to operate on the data.

curl -v -X POST http://localhost:8093/evaluator/v1/libraries/p1 -u Administrator:password -H 'content-type: application/json' -d 'function ptc1(a, b) {
var qi0 = START TRANSACTION;
var acc = []; for (const row of qi0) { acc.push(row); }
    var qi1 = INSERT INTO b VALUES(UUID(), {"radius": $a, "area": $b}) RETURNING meta().id,* ;
    for (const row of qi1) { acc.push(row); }
        var a2 = a * a;  var b2 = b * b;
        var qi2 = INSERT INTO b VALUES(UUID(), {"radius": $a2, "area": $b2}) RETURNING meta().id,* ;
        for (const row of qi2) { acc.push(row); }
        var qi9 = COMMIT ;
        for (const row of qi9) { acc.push(row); }
        return acc;
  }'

create or replace function ptc1(x, y) language javascript as "ptc1" at "p1" ;

execute function ptc1(4, 16);

The JavaScript UDF in Couchbase N1QL is different from the CURL() function in several ways. The CURL() function is used to make HTTP requests to external services and retrieve data from the web, whereas the JavaScript UDF allows you to write custom logic in JavaScript and execute it as part of an N1QL query. JavaScript itself can invoke CURL() function. In the example below, we write a sample JavaScript() function called ChatGPT(), which invokes the ChatGPT API.

Additionally, the CURL() function returns the data from the external service, whereas the JavaScript UDF can return any value that can be expressed in JavaScript, such as numbers, strings, objects, arrays, etc.

In conclusion, the JavaScript UDF in Couchbase N1QL provides a powerful way to extend the capabilities of N1QL by writing custom logic in JavaScript. It offers a lot of flexibility and can be used to perform complex operations that cannot be achieved using the built-in N1QL functions.

Invoking OpenAI’s ChatGPT from Couchbase N1QL can be achieved using either the CURL() function or a JavaScript User-Defined Function (UDF). Both methods have their own syntax and are briefly discussed below.

Using the CURL() function:

The CURL() function can be used to make HTTP requests to external services, such as OpenAI’s API for ChatGPT. The syntax for invoking ChatGPT from Couchbase N1QL using the CURL() function is as follows:

SELECT CURL("https://api.openai.com/v1/engines/davinci/completions", {
    "request": "POST",
    "headers": [
        "Authorization: Bearer ChangeThisToYourKey",
        "Content-Type: application/json"
        ],
    "data": '{
    "prompt": "Hello. How are you doing today?",
       "temperature": 0.9,
        "max_tokens": 200
    }'
}) AS result ;

In this example, the CURL() function is used to make a POST request to the OpenAI API endpoint for ChatGPT. The headers option is used to set the content type as application/json, and the data option is used to specify the request payload, which includes the prompt for ChatGPT, the temperature, and the maximum number of tokens to generate. The response from the API is stored in the response variable.

Using a JavaScript UDF:

Another way to invoke ChatGPT from Couchbase N1QL is by using a JavaScript UDF. The syntax for invoking ChatGPT from Couchbase N1QL using a JavaScript UDF is as follows:

curl -v -X POST http://localhost:8093/evaluator/v1/libraries/cglib -u Administrator:password -d 'function chatGPT3(prompt) {
  var chaturl = "https://api.openai.com/v1/engines/davinci/completions"
  dstr = "{\"prompt\": \"" + prompt +  "\",\"temperature\": 0.5 , \"max_tokens\": 200}"
 var options = {
"request" : "POST",
  headers: [
 "Authorization: Bearer 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",
 "Content-Type: application/json"
   ],
   "data": dstr
  };
  var query = SELECT CURL($chaturl, $options);
  var acc = [];
  for (const row of query)
    acc.push(row);

  return acc;
}'

In the cbq shell, do the following:

DROP FUNCTION chatGPT3;
CREATE FUNCTION chatGPT3(prompt) language javascript as "chatGPT3" at "cglib";

select chatGPT3("Write an essay on Lincoln.");

Here’s the result of that query:

cbq> select chatGPT3("Write an essay on Lincoln.");
{
    "requestID": "6acb9a20-93f0-41c2-bdc4-fe28107d85a9",
    "signature": {
        "$1": "json"
    },
    "results": [
    {
        "$1": [
            {
                "$1": {
                    "choices": [
                        {
                            "finish_reason": "length",
                            "index": 0,
                            "logprobs": null,
                            "text": "\n\n“Lincoln,” wrote Carl Sandburg, “was a self-made man in the best sense of the word.”\n\nWhat does Sandburg mean by this statement?\n\nWhat advantages did Lincoln have in his youth?\n\nWhat disadvantages did he have?\n\nWhat events from his youth helped to make him a self-made man?\n\nWhat events from his youth helped to make him a self-made man?\n\nWhat events from his youth helped to make him a self-made man?\n\nWhat events from his youth helped to make him a self-made man?\n\nWhat events from his youth helped to make him a self-made man?\n\nWhat events from his youth helped to make him a self-made man?\n\nWhat events from his youth helped to make him a self-made man?\n\nWhat events from his youth helped to make him a self-made man?\n\n"
                        }
                    ],
                    "created": 1675310533,
                    "id": "cmpl-6fL5Zu1MGjNorxBOwyg3H1ODzzjSD",
                    "model": "davinci",
                    "object": "text_completion",
                    "usage": {
                        "completion_tokens": 200,
                        "prompt_tokens": 6,
                        "total_tokens": 206
                    }
                }
            }
        ]
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "7.179456323s",
        "executionTime": "7.179347355s",
        "resultCount": 1,
        "resultSize": 1537,
        "serviceLoad": 1
    }
}
cbq> 

The actual essay chatGPT wrote was (don’t ask me to explain this…!)

"text": "\n\n“Lincoln,” wrote Carl Sandburg, “was a self-made man in the best sense of the word.”
      What does Sandburg mean by this statement?
      What advantages did Lincoln have in his youth?
      What disadvantages did he have?
      What events from his youth helped to make him a self-made man?
      What events from his youth helped to make him a self-made man?
      What events from his youth helped to make him a self-made man?
      What events from his youth helped to make him a self-made man?

In this example, a JavaScript UDF named chatGPTe is created that takes prompt as the argument. The JavaScript code for the UDF uses the request library to make a POST request to the OpenAI API endpoint for ChatGPT. The options for the request include the URL, the method, the headers, and the request payload.

Once the API call is made, the response from the API is stored in the response variable. The UDF then returns the response, which can be used in an N1QL query by calling the chatGPT function and passing the necessary arguments.

In conclusion, you can use either the CURL() function directly or via JavaScript function.

What are the limitations and considerations when using CURL() function and/or JavaScript UDF to invoke ChatGPT from Couchbase N1QL?

Invoking ChatGPT from Couchbase N1QL using CURL() function and JavaScript UDF can offer a lot of benefits for your application. However, it is important to be aware of the limitations and considerations in order to ensure a smooth and successful integration. Here are some of the most important things to keep in mind when using these methods:

CURL() Function Limitations:
- The CURL() function in N1QL is limited to making GET and POST requests only. If you need to make other types of requests, such as PUT or DELETE, you will need to use a JavaScript UDF.
- The CURL() function is synchronous and will block the N1QL query until the response is received. This can impact performance, especially if the response is large or slow.
Data Size Limitations:
- Both the CURL() function and JavaScript UDF have limitations on the amount of data that can be passed or received. You may need to chunk your data into smaller pieces and make multiple requests if the data is very large.
- The response from ChatGPT may also be large, so it is important to consider this when planning your integration. You may need to process the response in smaller chunks and store the results in Couchbase rather than trying to load the entire response into memory.
Security Considerations:
- When using the CURL() function or JavaScript UDF to make requests to ChatGPT, you will be passing sensitive information, such as API keys and other credentials, over the network. It is important to consider the security of this information and take steps to encrypt or protect it as necessary.
- The response from ChatGPT may also contain sensitive information, so it is important to secure the data stored in Couchbase and protect it from unauthorized access.

By keeping these limitations and considerations in mind, you can ensure a smooth and successful integration of ChatGPT with Couchbase N1QL using CURL() function and JavaScript UDF. If you encounter any issues or challenges, it is always a good idea to seek assistance from a knowledgeable professional or the Couchbase community for guidance.

Epilogue

Unlike my other 71 articles on DZone, I just wrote the prologue and the epilogue of this article. The rest of the article was written by ChatGPT. ChatGPT’s ability to write human-consumable content is pretty impressive. I did the edits, wrote correct examples, and tested them. ChatGPT’s code generation still needs improvement. It’s a whole new world.