Assistant with text-to-SQL Performance

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.

@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?

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.

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.

@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

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

If you are using the Assistants API:

If not, you’ll need to review this:

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

@rigor08 You can explore
it is built with openai apis.