Automation Action: Lookup From A Database Using AI

The Lookup From A Database Using AI action can be used to query a database using natural language. A prompt is sent to an AI along with your database schema. The AI will convert the natural language query into a parameterized SQL SELECT statement. The SQL SELECT statement is then executed against your database. The returned rows can then be assigned to a variable.

Before you can use this action you must setup an AI Provider in the ThinkAutomation Server Settings - AI Providers section. See: AI Providers.

Select a Database Type to connect to from the list. You must specify a Connection String that ThinkAutomation will use to open the database. Click the ... button to build the connection string. Click the Test button to verify that ThinkAutomation can connect to the database.

In the Natural Language Query entry specify the query text (or a %variable%). This can by any text that describes the data you want to retrieve, for example:

I need a list of employees located in the United States hired within
the last 12 months. Show the list in date order.            

Specify the Max Rows to read. A value should be specified here, to prevent very large queries from executing.

From the Return As list, select the format to return the data:

• CSV Text
• Json Array
• Markdown Table

Database Schema

In the Database Schema entry, you must list the SQL CREATE statements for your database tables. Click the Load Schema button to automatically load the schema for all tables in the selected database. Each table and index will be added. It is recommended to add comments to each table that describes what the table does and how it links to other tables - this will help the AI in generating queries.

For example:

-- Employee table (Contains a record for each employee)
CREATE TABLE Employee(Id INTEGER,LastName VARCHAR,FirstName VARCHAR,Title VARCHAR,
BirthDate DATE,HireDate DATE,Address VARCHAR,City VARCHAR,Region VARCHAR,
PostalCode VARCHAR,Country VARCHAR,HomePhone VARCHAR,Notes VARCHAR);
CREATE UNIQUE INDEX PK_Employee ON Employee (Id);
-- EmployeeTerritory table (Contains a record for each territory) 
-- An employee can have multiple territories. 
-- The EmployeeId column links to the Id column on the Employee table)
CREATE TABLE EmployeeTerritory(Id VARCHAR,EmployeeId INTEGER,TerritoryName VARCHAR);
CREATE UNIQUE INDEX EmployeeTerritory_1 ON EmployeeTerritory (Id);            

The Prompt Text entry can be used to adjust the prompt that will be sent to the AI. In most cases you will not need to change this.

Click the Test button to test queries. For example: The above natural language query would generate the following SQL query:

SELECT LastName, FirstName, Title, HireDate FROM Employee
WHERE Country = @Country AND HireDate >= date('now', '-12 months')
ORDER BY HireDate LIMIT 100;
ParamName=@Country,ParamType=VARCHAR,ParamValue="United States"            

The SQL generated will be the the correct syntax according to the database type selected.

From the AI Provider list, select the AI provider to use.

Select a %variable% to receive the returned database rows from the Assign Response list.

How It Works

When the action is executed, ThinkAutomation sends the prompt to your selected AI provider. The prompt will include the database schema and user query. The prompt asks the AI to generate a SQL statement based on the schema and user query. The returned SQL statement is then executed against your database and the returned rows are then assigned to the selected variable in either CSV, Json or Markdown format.

The prompt directs the AI to use parameters, to ensure against SQL injection.

Use Cases

This action can be used to provide a simple front end for natural language database queries. Used in conjunction with the web chat message source - a user can ask a question and the database results can be returned to the chat in markdown format.

This action could also be used along with the Ask AI action - to provide database context as part of a larger AI workflow. The rows returned in CSV format can then be used to add context to a conversation - allowing the AI to further process database results.