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 data 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:

• Markdown table
• CSV Text
• Json Array

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.

Most of the time the AI can figure out what a column is based on its name (IE: It will know that the ‘CompanyName’ column is used for the company name). However for vague column names or where the value represents a specific thing – you should add comments to the schema text. For example:

 -- Movie Reviews table (a record for each movie review) -- The score column is between 1 and 100 - with 100 being the highest rated. -- The country column is the 2 character ISO country code (in upper case) where the movie was made. -- The orig_lang is the language that the movie is in (eg: 'English') -- The crew column lists the actors and the parts they played. eg: 'Tom Hanks, Otto Anderson, Mariana Treviño, Marisol' means: Tom Hanks played Otto Anderson and Mariana Treviño played Marisol. CREATE TABLE [Reviews] ( [name] TEXT, [date_x] DATETIME, [score] REAL, [genre] TEXT, [overview] TEXT, [crew] TEXT, [orig_title] TEXT, [orig_lang] TEXT, [budget_x] REAL, [revenue] REAL, [country] TEXT );

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

You can optionally specify a Conversation Id. This is useful if multiple AI requests will be made and you want to include previous prompts/responses for context. For example, if the Lookup From A Database Using AI action is used within a general AI Conversation workflow. The previous question/responses will be added to the prompt. This will provide better results when follow-on questions are asked.

The Conversation Id can be any text. For example, setting it to %Msg_FromEmail% will link any requests for the same incoming email address. The built-in variable %Msg_ConversationId% can be used for the Conversation Id. This is a hash of the from/to addresses and subject.

Select a %variable% to receive the returned database rows from the Assign Response list. You can also optionally assign the generated SQL statement to a variable selected from the Assign Generated SQL To list.

<h5id=”how-it-works”>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 Markdown, CSV, Json format.

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

<h5id=”use-cases”>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 Markdown format can then be used to add context to a conversation – allowing the AI to further process database results.

See Also: Using AI To Chat With Your Own Databases – Example