AI as interface to Database

💬 Queries and Resources
2

Hey there @davblo,

I think this is definitely doable.

I remember watching @kent's demo having something exactly like you're saying, and he provided the workflow and the app here. His demo and explanation can be found here https://youtu.be/e7Bs5qiDqpk?si=pYoMYsm4G2MiFBc6&t=1562

So basically you would create a couple of tools:

  • text to sql, which would do what you mention here:

It should then be able to accept a common language enquiry from a user, and generate the corresponding response from the available data.

  • This tool is definitely a workflow, where you pass yourdatabase schema within the window context, and potentially some examples of the data (although I still need to understand what are best practices for prompt engineering for examples, is it better in the system context, or within the prompt, or something else entirely?)

As an example, if you're using Postgres you can query

SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_schema, table_name, ordinal_position;

to get the schema, and

SELECT
  tc.table_name AS source_table,
  kcu.column_name AS source_column,
  ccu.table_name AS target_table,
  ccu.column_name AS target_column,
  rc.update_rule,
  rc.delete_rule
FROM 
  information_schema.table_constraints AS tc
JOIN 
  information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
  AND tc.table_schema = kcu.table_schema
JOIN 
  information_schema.referential_constraints AS rc
  ON tc.constraint_name = rc.constraint_name
  AND tc.table_schema = rc.constraint_schema
JOIN 
  information_schema.constraint_column_usage AS ccu
  ON rc.unique_constraint_name = ccu.constraint_name
  AND rc.unique_constraint_schema = ccu.constraint_schema
WHERE 
  tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'public'
ORDER BY 
  source_table, source_column;

to get foreign keys relationships.

This should provide a good starting point to the llm to draft an sql query to pull the results you're looking for. The challenge will be in providing enough context around your table's columns so that it can really infer from natural language what is it that the user is searching.

I think that with Retool's evals and data sets you can further improve this. Or maybe not?? @kent, sorry if I double tag you, but are evals related only to question - > tool, or can it go deeper? I.e. question -> expected output?

Thanks!