1"use strict";(self.webpackChunkdocs=self.webpackChunkdocs||[]).push([[31123],{4682:(e,n,s)=>{s.r(n),s.d(n,{assets:()=>l,contentTitle:()=>i,default:()=>h,frontMatter:()=>o,metadata:()=>r,toc:()=>c});const r=JSON.parse('{"id":"developers/use-cases/sql-qna","title":"SQL QnA","description":"Learn how to query structured data","source":"@site/docs/developers/use-cases/sql-qna.md","sourceDirName":"developers/use-cases","slug":"/developers/use-cases/sql-qna","permalink":"/docs/developers/use-cases/sql-qna","draft":false,"unlisted":false,"tags":[],"version":"current","frontMatter":{"description":"Learn how to query structured data"},"sidebar":"docs","previous":{"title":"Multiple Documents QnA","permalink":"/docs/developers/use-cases/multiple-documents-qna"},"next":{"title":"Upserting Data","permalink":"/docs/developers/use-cases/upserting-data"}}');var t=s(65723),a=s(8667);const o={description:"Learn how to query structured data"},i="SQL QnA",l={},c=[{value:"TL;DR",id:"tldr",level:2},{value:"1. SQL Database Schema + Example Rows",id:"1-sql-database-schema--example-rows",level:2},{value:"2. Return a SQL query with few shot prompting",id:"2-return-a-sql-query-with-few-shot-prompting",level:2},{value:"3. Validate the SQL query using If Else node",id:"3-validate-the-sql-query-using-if-else-node",level:2},{value:"4. Custom function to execute SQL query, and get the response",id:"4-custom-function-to-execute-sql-query-and-get-the-response",level:2},{value:"5. Return a natural response from the executed SQL response",id:"5-return-a-natural-response-from-the-executed-sql-response",level:2},{value:"Query",id:"query",level:2},{value:"Conclusion",id:"conclusion",level:2}];function d(e){const n={a:"a",admonition:"admonition",code:"code",em:"em",h1:"h1",h2:"h2",header:"header",hr:"hr",li:"li",ol:"ol",p:"p",pre:"pre",strong:"strong",ul:"ul",...(0,a.R)(),...e.components},{Details:s}=n;return s||function(e,n){throw new Error("Expected "+(n?"component":"object")+" `"+e+"` to be defined: you likely forgot to import, pass, or provide it.")}("Details",!0),(0,t.jsxs)(t.Fragment,{children:[(0,t.jsx)(n.header,{children:(0,t.jsx)(n.h1,{id:"sql-qna",children:"SQL QnA"})}),"\n",(0,t.jsx)(n.hr,{}),"\n",(0,t.jsxs)(n.p,{children:["Unlike previous examples like ",(0,t.jsx)(n.a,{href:"/docs/developers/use-cases/web-scrape-qna",children:"Web Scrape QnA"})," and ",(0,t.jsx)(n.a,{href:"/docs/developers/use-cases/multiple-documents-qna",children:"Multiple Documents QnA"}),", querying structured data does not require a vector database. At the high-level, this can be achieved with following steps:"]}),"\n",(0,t.jsxs)(n.ol,{children:["\n",(0,t.jsxs)(n.li,{children:["Providing the LLM:","\n",(0,t.jsxs)(n.ul,{children:["\n",(0,t.jsx)(n.li,{children:"overview of the SQL database schema"}),"\n",(0,t.jsx)(n.li,{children:"example rows data"}),"\n"]}),"\n"]}),"\n",(0,t.jsx)(n.li,{children:"Return a SQL query with few shot prompting"}),"\n",(0,t.jsxs)(n.li,{children:["Validate the SQL query using an ",(0,t.jsx)(n.a,{href:"/docs/sidekick-studio/chatflows/utilities/if-else",children:"If Else"})," node"]}),"\n",(0,t.jsx)(n.li,{children:"Create a custom function to execute the SQL query, and get the response"}),"\n",(0,t.jsx)(n.li,{children:"Return a natural response from the executed SQL response"}),"\n"]}),"\n",(0,t.jsxs)("figure",{children:[(0,t.jsx)("img",{src:"/.gitbook/assets/image (113).png",alt:""}),(0,t.jsx)("figcaption",{})]}),"\n",(0,t.jsx)(n.p,{children:"In this example, we are going to create a QnA chatbot that can interact with a SQL database stored in SingleStore"}),"\n",(0,t.jsxs)("figure",{children:[(0,t.jsx)("img",{src:"/.gitbook/assets/image (116).png",alt:""}),(0,t.jsx)("figcaption",{})]}),"\n",(0,t.jsx)(n.h2,{id:"tldr",children:"TL;DR"}),"\n",(0,t.jsx)(n.p,{children:"You can find the chatflow template:"}),"\n",(0,t.jsx)("a",{href:"/.gitbook/assets/SQL Chatflow.json",download:!0,children:"Download /.gitbook/assets/SQL Chatflow.json"}),"\n",(0,t.jsx)(n.h2,{id:"1-sql-database-schema--example-rows",children:"1. SQL Database Schema + Example Rows"}),"\n",(0,t.jsx)(n.p,{children:"Use a Custom JS Function node to connect to SingleStore, retrieve database schema and top 3 rows."}),"\n",(0,t.jsxs)(n.p,{children:["From the ",(0,t.jsx)(n.a,{href:"https://arxiv.org/abs/2204.00498",children:"research paper"}),", it is recommended to generate a prompt with following example format:"]}),"\n",(0,t.jsx)(n.pre,{children:(0,t.jsx)(n.code,{children:"CREATE TABLE samples (firstName varchar NOT NULL, lastName varchar)\r\nSELECT * FROM samples LIMIT 3\r\nfirstName lastName\r\nStephen Tyler\r\nJack McGinnis\r\nSteven Repici\n"})}),"\n",(0,t.jsxs)("figure",{children:[(0,t.jsx)("img",{src:"/.gitbook/assets/image (114).png",alt:""}),(0,t.jsx)("figcaption",{})]}),"\n",(0,t.jsxs)(s,{children:[(0,t.jsx)("summary",{children:"Full Javascript Co
1de"}),(0,t.jsx)(n.pre,{children:(0,t.jsx)(n.code,{className:"language-javascript",children:"const HOST = 'singlestore-host.com'\r\nconst USER = 'admin'\r\nconst PASSWORD = 'mypassword'\r\nconst DATABASE = 'mydb'\r\nconst TABLE = 'samples'\r\nconst mysql = require('mysql2/promise')\r\n\r\nlet sqlSchemaPrompt\r\n\r\nfunction getSQLPrompt() {\r\n return new Promise(async (resolve, reject) => {\r\n try {\r\n const singleStoreConnection = mysql.createPool({\r\n host: HOST,\r\n user: USER,\r\n password: PASSWORD,\r\n database: DATABASE\r\n })\r\n\r\n // Get schema info\r\n const [schemaInfo] = await singleStoreConnection.execute(\r\n `SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \"${TABLE}\"`\r\n )\r\n\r\n const createColumns = []\r\n const columnNames = []\r\n\r\n for (const schemaData of schemaInfo) {\r\n columnNames.push(`${schemaData['COLUMN_NAME']}`)\r\n createColumns.push(\r\n `${schemaData['COLUMN_NAME']} ${schemaData['COLUMN_TYPE']} ${schemaData['IS_NULLABLE'] === 'NO' ? 'NOT NULL' : ''}`\r\n )\r\n }\r\n\r\n const sqlCreateTableQuery = `CREATE TABLE samples (${createColumns.join(', ')})`\r\n const sqlSelectTableQuery = `SELECT * FROM samples LIMIT 3`\r\n\r\n // Get first 3 rows\r\n const [rows] = await singleStoreConnection.execute(sqlSelectTableQuery)\r\n\r\n const allValues = []\r\n for (const row of rows) {\r\n const rowValues = []\r\n for (const colName in row) {\r\n rowValues.push(row[colName])\r\n }\r\n allValues.push(rowValues.join(' '))\r\n }\r\n\r\n sqlSchemaPrompt = sqlCreateTableQuery + '\\n' + sqlSelectTableQuery + '\\n' + columnNames.join(' ') + '\\n' + allValues.join('\\n')\r\n\r\n resolve()\r\n } catch (e) {\r\n console.error(e)\r\n return reject(e)\r\n }\r\n })\r\n}\r\n\r\nasync function main() {\r\n await getSQLPrompt()\r\n}\r\n\r\nawait main()\r\n\r\nreturn sqlSchemaPrompt\n"})})]}),"\n",(0,t.jsx)(n.p,{children:"Once finished, click Execute:"}),"\n",(0,t.jsxs)("figure",{children:[(0,t.jsx)("img",{src:"/.gitbook/assets/image (117).png",alt:""}),(0,t.jsx)("figcaption",{})]}),"\n",(0,t.jsx)(n.p,{children:"We can now see the correct format has been generated. Next step is to bring this into Prompt Template."}),"\n",(0,t.jsx)(n.h2,{id:"2-return-a-sql-query-with-few-shot-prompting",children:"2. Return a SQL query with few shot prompting"}),"\n",(0,t.jsx)(n.p,{children:"Create a new Chat Model + Prompt Template + LLMChain"}),"\n",(0,t.jsxs)("figure",{children:[(0,t.jsx)("img",{src:"/.gitbook/assets/image (118).png",alt:""}),(0,t.jsx)("figcaption",{})]}),"\n",(0,t.jsx)(n.p,{children:"Specify the following prompt in the Prompt Template:"}),"\n",(0,t.jsx)(n.pre,{children:(0,t.jsx)(n.code,{children:"Based on the provided SQL table schema and question below, return a SQL SELECT ALL query that would answer the user's question. For example: SELECT * FROM table WHERE id = '1'.\r\n------------\r\nSCHEMA: \\{schema\\}\r\n------------\r\nQUESTION: \\{question\\}\r\n------------\r\nSQL QUERY:\n"})}),"\n",(0,t.jsxs)(n.p,{children:["Since we are using 2 variables: {schema} and {question}, specify their values in ",(0,t.jsx)(n.strong,{children:"Format Prompt Values"}),":"]}),"\n",(0,t.jsxs)("figure",{children:[(0,t.jsx)("img",{src:"/.gitbook/assets/image (122).png",alt:"",width:"563"}),(0,t.jsx)("figcaption",{})]}),"\n",(0,t.jsx)(n.admonition,{type:"info",children:(0,t.jsxs)(n.p,{children:["You can provide more examples to the prompt (i.e few-shot prompting) to let the LLM learns better. Or take reference from ",(0,t.jsx)(n.a,{href:"https://js.langchain.com/docs/use_cases/sql/prompting#dialect-specific-prompting",children:"dialect-specific prompting"})]})}),"\n",(0,t.jsxs)(n.h2,{id:"3-validate-the-sql-query-using-if-else-node",children:["3. Validate the SQL query using ",(0,t.jsx)(n.a,{href:"/docs/sidekick-studio/chatflows/utilities/if-else",children:"If Else"})," node"]}),"\n",(0,t.jsxs)(n.p,{children:["Sometimes the SQL query is invali
1d, and we do not want to waste resources the execute an invalid SQL query. For example, if a user is asking a general question that is irrelevant to the SQL database. We can use an ",(0,t.jsx)(n.code,{children:"If Else"})," node to route to different path."]}),"\n",(0,t.jsx)(n.p,{children:"For instance, we can perform a basic check to see if SELECT and WHERE are included in the SQL query given by the LLM."}),"\n",(0,t.jsxs)("figure",{children:[(0,t.jsx)("img",{src:"/.gitbook/assets/image (119).png",alt:"",width:"327"}),(0,t.jsx)("figcaption",{})]}),"\n",(0,t.jsx)(n.p,{children:"In the Else Function, we will route to a Prompt Template + LLMChain that basically tells LLM that it is unable to answer user query:"}),"\n",(0,t.jsxs)("figure",{children:[(0,t.jsx)("img",{src:"/.gitbook/assets/image (120).png",alt:""}),(0,t.jsx)("figcaption",{})]}),"\n",(0,t.jsx)(n.h2,{id:"4-custom-function-to-execute-sql-query-and-get-the-response",children:"4. Custom function to execute SQL query, and get the response"}),"\n",(0,t.jsxs)(n.p,{children:["If it is a valid SQL query, we need to execute the query. Connect the ",(0,t.jsx)(n.em,{children:(0,t.jsx)(n.strong,{children:"True"})})," output from ",(0,t.jsx)(n.strong,{children:"If Else"})," node to a ",(0,t.jsx)(n.strong,{children:"Custom JS Function"})," node:"]}),"\n",(0,t.jsxs)("figure",{children:[(0,t.jsx)("img",{src:"/.gitbook/assets/image (123).png",alt:"",width:"563"}),(0,t.jsx)("figcaption",{})]}),"\n",(0,t.jsxs)(s,{children:[(0,t.jsx)("summary",{children:"Full Javascript Code"}),(0,t.jsx)(n.pre,{children:(0,t.jsx)(n.code,{className:"language-javascript",children:"const HOST = 'singlestore-host.com'\r\nconst USER = 'admin'\r\nconst PASSWORD = 'mypassword'\r\nconst DATABASE = 'mydb'\r\nconst TABLE = 'samples'\r\nconst mysql = require('mysql2/promise')\r\n\r\nlet result\r\n\r\nfunction getSQLResult() {\r\n return new Promise(async (resolve, reject) => {\r\n try {\r\n const singleStoreConnection = mysql.createPool({\r\n host: HOST,\r\n user: USER,\r\n password: PASSWORD,\r\n database: DATABASE\r\n })\r\n\r\n const [rows] = await singleStoreConnection.execute($sqlQuery)\r\n\r\n result = JSON.stringify(rows)\r\n\r\n resolve()\r\n } catch (e) {\r\n console.error(e)\r\n return reject(e)\r\n }\r\n })\r\n}\r\n\r\nasync function main() {\r\n await getSQLResult()\r\n}\r\n\r\nawait main()\r\n\r\nreturn result\n"})})]}),"\n",(0,t.jsx)(n.h2,{id:"5-return-a-natural-response-from-the-executed-sql-response",children:"5. Return a natural response from the executed SQL response"}),"\n",(0,t.jsx)(n.p,{children:"Create a new Chat Model + Prompt Template + LLMChain"}),"\n",(0,t.jsxs)("figure",{children:[(0,t.jsx)("img",{src:"/.gitbook/assets/image (124).png",alt:""}),(0,t.jsx)("figcaption",{})]}),"\n",(0,t.jsx)(n.p,{children:"Write the following prompt in the Prompt Template:"}),"\n",(0,t.jsx)(n.pre,{children:(0,t.jsx)(n.code,{children:"Based on the question, and SQL response, write a natural language response, be details as possible:\r\n------------\r\nQUESTION: {question}\r\n------------\r\nSQL RESPONSE: {sqlResponse}\r\n------------\r\nNATURAL LANGUAGE RESPONSE:\n"})}),"\n",(0,t.jsxs)(n.p,{children:["Specify the variables in ",(0,t.jsx)(n.strong,{children:"Format Prompt Values"}),":"]}),"\n",(0,t.jsxs)("figure",{children:[(0,t.jsx)("img",{src:"/.gitbook/assets/image (125).png",alt:"",width:"563"}),(0,t.jsx)("figcaption",{})]}),"\n",(0,t.jsx)(n.p,{children:"Voila! Your SQL chatbot is now ready for testing!"}),"\n",(0,t.jsx)(n.h2,{id:"query",children:"Query"}),"\n",(0,t.jsx)(n.p,{children:"First, let's ask something related to the database."}),"\n",(0,t.jsxs)("figure",{children:[(0,t.jsx)("img",{src:"/.gitbook/assets/image (128).png",alt:"",width:"434"}),(0,t.jsx)("figcaption",{})]}),"\n",(0,t.jsx)(n.p,{children:"Looking at the logs, we can see the first LLMChain is able to give us a SQL query:"}),"\n",(0,t.jsx)(n.p,{children:(0,t.jsx)(n.strong,{children:"Input:"})}),"\n",(0,t.jsx)(n.pre,{children:(0,t.jsx)(n.code,{children:"Based on the provided SQL table schema and question below, return a SQL SELECT ALL query that would answer the user's question. For example: SELECT * FROM table WHERE id = '1'.\\n------------\\nSCHEMA: CREATE TABLE samples (id bigint(20) NOT NULL, firstName varchar(300) NOT NULL, lastName varchar(300) NOT NULL, userAddress varchar(300) NOT NULL, userState varchar(300) NOT NULL, userCode varchar(300) NOT NULL, userPostal varchar(300) NOT NULL, createdate timestamp(6) NOT NULL)\\nSELECT * FROM samples LIMIT 3\\nid f
1irstName lastName userAddress userState userCode userPostal createdate\\n1125899906842627 Steven Repici 14 Kingston St. Oregon NJ 5578 Thu Dec 14 2023 13:06:17 GMT+0800 (Singapore Standard Time)\\n1125899906842625 John Doe 120 jefferson st. Riverside NJ 8075 Thu Dec 14 2023 13:04:32 GMT+0800 (Singapore Standard Time)\\n1125899906842629 Bert Jet 9th, at Terrace plc Desert City CO 8576 Thu Dec 14 2023 13:07:11 GMT+0800 (Singapore Standard Time)\\n------------\\nQUESTION: what is the address of John\\n------------\\nSQL QUERY:\n"})}),"\n",(0,t.jsx)(n.p,{children:(0,t.jsx)(n.strong,{children:"Output"})}),"\n",(0,t.jsx)(n.pre,{children:(0,t.jsx)(n.code,{className:"language-sql",children:"SELECT userAddress FROM samples WHERE firstName = 'John'\n"})}),"\n",(0,t.jsx)(n.p,{children:"After executing the SQL query, the result is passed to the 2nd LLMChain:"}),"\n",(0,t.jsx)(n.p,{children:(0,t.jsx)(n.strong,{children:"Input"})}),"\n",(0,t.jsx)(n.pre,{children:(0,t.jsx)(n.code,{children:'Based on the question, and SQL response, write a natural language response, be details as possible:\\n------------\\nQUESTION: what is the address of John\\n------------\\nSQL RESPONSE: [{\\"userAddress\\":\\"120 jefferson st.\\"}]\\n------------\\nNATURAL LANGUAGE RESPONSE:\n'})}),"\n",(0,t.jsx)(n.p,{children:(0,t.jsx)(n.strong,{children:"Output"})}),"\n",(0,t.jsx)(n.pre,{children:(0,t.jsx)(n.code,{children:"The address of John is 120 Jefferson St.\n"})}),"\n",(0,t.jsx)(n.p,{children:"Now, we if ask something that is irrelevant to the SQL database, the Else route is taken."}),"\n",(0,t.jsxs)("figure",{children:[(0,t.jsx)("img",{src:"/.gitbook/assets/image (132).png",alt:"",width:"428"}),(0,t.jsx)("figcaption",{})]}),"\n",(0,t.jsx)(n.p,{children:"For first LLMChain, a SQL query is generated as below:"}),"\n",(0,t.jsx)(n.pre,{children:(0,t.jsx)(n.code,{className:"language-sql",children:"SELECT * FROM samples LIMIT 3\n"})}),"\n",(0,t.jsxs)(n.p,{children:["However, it fails the ",(0,t.jsx)(n.code,{children:"If Else"})," check because it doesn't contains both ",(0,t.jsx)(n.code,{children:"SELECT"})," and ",(0,t.jsx)(n.code,{children:"WHERE"}),", hence entering the Else route that has a prompt that says:"]}),"\n",(0,t.jsx)(n.pre,{children:(0,t.jsx)(n.code,{children:'Politely say "I\'m not able to answer query"\n'})}),"\n",(0,t.jsx)(n.p,{children:"And the final output is:"}),"\n",(0,t.jsx)(n.pre,{children:(0,t.jsx)(n.code,{children:"I apologize, but I'm not able to answer your query at the moment.\n"})}),"\n",(0,t.jsx)(n.h2,{id:"conclusion",children:"Conclusion"}),"\n",(0,t.jsx)(n.p,{children:"In this example, we have successfully created a SQL chatbot that can interact with your database, and is also able to handle questions that are irrelevant to database. Further improvement includes adding memory to provide conversation history."}),"\n",(0,t.jsx)(n.p,{children:"You can find the chatflow below:"}),"\n",(0,t.jsx)("a",{href:"/.gitbook/assets/SQL Chatflow.json",download:!0,children:"Download /.gitbook/assets/SQL Chatflow.json"})]})}function h(e={}){const{wrapper:n}={...(0,a.R)(),...e.components};return n?(0,t.jsx)(n,{...e,children:(0,t.jsx)(d,{...e})}):d(e)}},8667:(e,n,s)=>{s.d(n,{R:()=>o,x:()=>i});var r=s(22155);const t={},a=r.createContext(t);function o(e){const n=r.useContext(a);return r.useMemo(function(){return"function"==typeof e?e(n):{...n,...e}},[n,e])}function i(e){let n;return n=e.disableParentContext?"function"==typeof e.components?e.components(t):e.components||t:o(e.components),r.createElement(a.Provider,{value:n},e.children)}}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.