Package dev.langchain4j.experimental.rag.content.retriever.sql

Class SqlDatabaseContentRetriever

java.lang.Object

dev.langchain4j.experimental.rag.content.retriever.sql.SqlDatabaseContentRetriever

All Implemented Interfaces:

ContentRetriever

public class SqlDatabaseContentRetriever
extends Object
implements ContentRetriever

WARNING! Although fun and exciting, this class is dangerous to use! Do not ever use this in production! The database user must have very limited READ-ONLY permissions! Although the generated SQL is somewhat validated (to ensure that the SQL is a SELECT statement) using JSqlParser, this class does not guarantee that the SQL will be harmless. Use it at your own risk!

Using the DataSource and the ChatLanguageModel, this ContentRetriever attempts to generate and execute SQL queries for given natural language queries.
Optionally, sqlDialect, databaseStructure, promptTemplate, and maxRetries can be specified to customize the behavior. See the javadoc of the constructor for more details. Most methods can be overridden to customize the behavior further.
The default prompt template is not highly optimized, so it is advised to experiment with it and see what works best for your use case.

Constructor Summary

Constructors

Constructor	Description
SqlDatabaseContentRetriever(DataSource dataSource, String sqlDialect, String databaseStructure, PromptTemplate promptTemplate, ChatLanguageModel chatLanguageModel, Integer maxRetries)	Creates an instance of a SqlDatabaseContentRetriever.

Method Summary

Modifier and Type	Method	Description
protected String	clean(String sqlQuery)
protected Prompt	createSystemPrompt()
protected String	execute(String sqlQuery, Statement statement)
protected String	generateSqlQuery(Query naturalLanguageQuery, String previousSqlQuery, String previousErrorMessage)
static String	getSqlDialect(DataSource dataSource)
protected boolean	isSelect(String sqlQuery)
List<Content>	retrieve(Query naturalLanguageQuery)	Retrieves relevant Contents using a given Query.
protected void	validate(String sqlQuery)

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Details

SqlDatabaseContentRetriever

public SqlDatabaseContentRetriever(DataSource dataSource,
 String sqlDialect,
 String databaseStructure,
 PromptTemplate promptTemplate,
 ChatLanguageModel chatLanguageModel,
 Integer maxRetries)

Creates an instance of a SqlDatabaseContentRetriever.

Parameters:

dataSource - The DataSource to be used for executing SQL queries. This is a mandatory parameter. WARNING! The database user must have very limited READ-ONLY permissions!

sqlDialect - The SQL dialect, which will be provided to the LLM in the SystemMessage. The LLM should know the specific SQL dialect in order to generate valid SQL queries. Example: "MySQL", "PostgreSQL", etc. This is an optional parameter. If not specified, it will be determined from the DataSource.

databaseStructure - The structure of the database, which will be provided to the LLM in the SystemMessage. The LLM should be familiar with available tables, columns, relationships, etc. in order to generate valid SQL queries. It is best to specify the complete "CREATE TABLE ..." DDL statement for each table. Example (shortened): "CREATE TABLE customers(\n id INT PRIMARY KEY,\n name VARCHAR(50), ...);\n CREATE TABLE products(...);\n ..." This is an optional parameter. If not specified, it will be generated from the DataSource. WARNING! In this case, all tables will be visible to the LLM!

promptTemplate - The PromptTemplate to be used for creating a SystemMessage. This is an optional parameter. Default: DEFAULT_PROMPT_TEMPLATE.

chatLanguageModel - The ChatLanguageModel to be used for generating SQL queries. This is a mandatory parameter.

maxRetries - The maximum number of retries to perform if the database cannot execute the generated SQL query. An error message will be sent back to the LLM to try correcting the query. This is an optional parameter. Default: 1.

Method Details

getSqlDialect

public static String getSqlDialect(DataSource dataSource)

retrieve

public List<Content> retrieve(Query naturalLanguageQuery)

Description copied from interface: ContentRetriever

Retrieves relevant Contents using a given Query. The Contents are sorted by relevance, with the most relevant Contents appearing at the beginning of the returned List<Content>.

Specified by:

retrieve in interface ContentRetriever

Parameters:

naturalLanguageQuery - The Query to use for retrieval.

Returns:

A list of retrieved Contents.

generateSqlQuery

protected String generateSqlQuery(Query naturalLanguageQuery,
 String previousSqlQuery,
 String previousErrorMessage)

createSystemPrompt

protected Prompt createSystemPrompt()

clean

protected String clean(String sqlQuery)

validate

protected void validate(String sqlQuery)

isSelect

protected boolean isSelect(String sqlQuery)

execute

protected String execute(String sqlQuery,
 Statement statement)
                  throws SQLException

Throws:

SQLException