Assistant with text-to-SQL Performance

API
, , ,
21

Well, actions are not materially different in that they would also involve an API request to the database. For the Assistant you would simply use the function call to achieve the same.

22

@jr.2509 got you man. But did you get what i am trying to achieve. and if what i asked can be achieved through actions (function call) the how because firstly i need to be authenticated to access the database then to perform query over that database how?

23

It depends on if you’re using a SQL or NoSql database. If you’re using a Sql database it maybe simpler to upload a file containing the schema of the database and in the prompt, as mentioned above clearly define and explain the schema to the assistant. Then you can do the rest of the assistant configuration as normal. I wouldn’t know how to do it for a NoSql database as mongodb however. Also, if you change your structure of your database make sure to also change it in the file upload.

24

Here is a super scaled down version of my text to sql code:

  // Send $systemMessage and $question to the model
  $response = $this->solraiService->solrai_generateSQL($systemMessage, $question);
  
  // Get the $sql statement from the response.
  $sql = $response['generated_text'];	

  if ($this->isSafeSQL($sql)) {
    // proceed with executing the SQL query
    $isSafe = 'Y';
  } else {
    // log an error and halt the execution
    $logger->error('Unsafe SQL query detected: @query', ['@query' => $sql]);
    $isSafe = 'N';
  }

  // Check if the SQL is not an error message
  if ($isSafe === 'Y' && $sql !== null && (substr($sql, 0, 6) !== "Error:" && substr($sql, 0, 14) !== "Error occurred")) {
    // Hopefully, we have SQL here
    // So, we execute the $sql command and get the results
    $results = $this->solraiService->dbConNew_readonly($sql);
    
    // We convert the results to HTML for display.
    $output = $this->solraiService->convertToHTML($results);
  	
    // Alternately, I can also send this $output to model to render a semantic response	
    if ($analyze === 'Y') {
  	$response01 = $this->analyzeResults($sqlQueryOutput, $analyzeQuery, $sqlAnalyzeMessage, $modelSql);
  		$generated_text = $response01['generated_text'];
  	$sqlQueryOutput = $sqlQueryOutput . '<strong>Analysis: </strong>' . $analyzeQuery . '<br>' . $generated_text;
    }

I run my queries inside of a Drupal CMS, so I can directly access my database without use of API – which appears to be what you wish to do.

Note that I can output to the end user (your Step #4) an HTML formatted table and/or a semantic (natural language) response.

25

@SomebodySysop @omerk203 what i am actually asking is that how the assistant is supposed to get access to my database i.e Open Flow let’s say its kind of an access layer over mongodb. I will provide my DB schema to the assistant via instructions but how will Assistant access the DB. The database requires authentication and how will it run query over that database

26

I may be wrong here, but I don’t think giving it access to your database is such a good idea. If im understand correctly you want it to be able to extract a sql from natural language and bring back that data? If so, here’s the process I would assume. First the user gives the natural language to the assistant, assistant analyzes prompt and builds a SQL query accordingly, from there you can have a function that takes that sql query as a string and runs it on your database. This way you have that layer of abstraction. But again this is for a SQL server, I wouldn’t know how to do it for a db like Mongo. Best of luck!

1 Like
27

If you are using the Assistants API: https://platform.openai.com/docs/guides/function-calling

If not, you’ll need to review this:

https://platform.openai.com/docs/assistants/how-it-works/runs-and-run-steps

I don’t use Assistants. I use Chat Completion API. So, the way I do it is exactly as I demonstrated here: Assistant with text-to-SQL Performance - #24 by SomebodySysop

29

Whoa … no no no. That’s to much finger crossing hoping it understands what you need it to do. you still got a lot of prep work to do.

  1. You need to document every singe table and put this in a well documented/laid out markdown file.

  2. You need to pull all the relationships from the DB, get AI to analyse that and put those results in a markdown file as well.

  3. Based on those 2 files, you need to then hand them back to the AI to analyse and create a detailed Mermaid file showing all the relationships as a mermaid flowchart.

  4. Next generate a doc with AI to document how to use these files to work out a search.

  5. Next with the help of AI, create as many relevant views as possible and create a tool call for each 1.

This should work, I know because I have done it.

This is an old post, was tool calling around back then?

1 Like
30

Just a follow-up. I’m still using this methodology with chat completion: https://platform.openai.com/docs/examples/default-sql-translate

But, dude, using the new o3-mini model, it’s working like a dream. I went from around 60% success to over 90% success.

I think @Sim2K just did a good job of outlining the steps: Assistant with text-to-SQL Performance - #29 by Sim2K

The only difference is that I don’t use functions or tools – I do straight chat completions with all the descriptions in the system and user text, then programmatically execute the returned SQL code.

The key is to really, really document your tables very well. Do that right, and o3-mini will take care of the rest.