Interface DatabaseProvider
- All Superinterfaces:
Serializable
Applications implement this interface with their own database connection. The provider exposes the schema so the LLM can generate valid SQL, and executes the resulting queries.
A component that loads rows lazily, such as a grid, asks for one page of a
query's rows at a time with executeQuery(String, QueryPage) and for
the size of the result with count(String). By default both extend
the query's SQL with ORDER BY, LIMIT and OFFSET, or
with COUNT(*), and run it through executeQuery(String).
Override them when your SQL dialect pages or counts differently, or when your
provider can do it more efficiently. A provider that delegates to another
provider should delegate these methods too, so that the other provider's own
paging is used.
Note: For security, the implementation should use a database account with read-only access limited to the relevant tables or views. This prevents the LLM from inadvertently modifying or deleting data.
public class MyDatabaseProvider implements DatabaseProvider {
private final DataSource readOnlyDataSource;
public MyDatabaseProvider(DataSource readOnlyDataSource) {
this.readOnlyDataSource = readOnlyDataSource;
}
@Override
public String getSchema() {
return "Tables: employees(id INT, name VARCHAR, dept VARCHAR), "
+ "departments(id INT, name VARCHAR). Dialect: PostgreSQL.";
}
@Override
public List<Map<String, Object>> executeQuery(String sql) {
try (var connection = readOnlyDataSource.getConnection();
var statement = connection.prepareStatement(sql);
var resultSet = statement.executeQuery()) {
// Convert to List<Map<String, Object>>
}
}
}
Dynamic schema retrieval: Hand-writing the schema string works for
small fixed schemas, but for larger schemas DatabaseMetaData
builds the description at runtime from a truly read-only connection and works
consistently across H2, MySQL, and PostgreSQL. Including primary and foreign
keys helps the LLM pick correct joins rather than guessing them from column
names:
@Override
public String getSchema() {
try (var connection = readOnlyDataSource.getConnection()) {
var meta = connection.getMetaData();
var catalog = connection.getCatalog();
var schemaName = connection.getSchema();
var schema = new StringBuilder();
try (var tables = meta.getTables(catalog, schemaName, "%",
new String[] { "TABLE" })) {
while (tables.next()) {
var table = tables.getString("TABLE_NAME");
// Append columns via meta.getColumns(catalog, schemaName,
// table, "%")
// Append primary keys via meta.getPrimaryKeys(catalog,
// schemaName, table)
// Append foreign keys via meta.getImportedKeys(catalog,
// schemaName, table)
}
}
return schema.toString();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
- Since:
- 25.3
- Author:
- Vaadin Ltd
-
Method Summary
Modifier and TypeMethodDescriptiondefault intReturns the number of rows the given SQL query returns, for a component that loads rows lazily and needs the size of the result.executeQuery(String sql) Executes the given SQL query and returns the results.executeQuery(String sql, QueryPage page) Executes the given SQL query and returns one page of its rows, for a component that loads rows lazily.Returns a text description of the database schema available to the LLM.
-
Method Details
-
getSchema
String getSchema()Returns a text description of the database schema available to the LLM. The description should include table names, column names with their types, and optionally the SQL dialect (e.g., PostgreSQL, MySQL). The LLM uses this information to generate valid SQL queries.See the class-level "Dynamic schema retrieval" section for a
DatabaseMetaData-based example that builds this description at runtime.- Returns:
- a text description of the database schema, never
null
-
executeQuery
Executes the given SQL query and returns the results. Each row is represented as a map from column name to column value.Implementations should ensure that only read-only queries are executed.
When a query fails (e.g. references an unknown column), throw a
ToolExceptionwhose message describes the problem in terms safe to expose — the message is passed to the LLM so it can correct the query instead of retrying it unchanged. Any other exception is replaced with a generic error message before reaching the LLM.- Parameters:
sql- the SQL query to execute, notnull- Returns:
- the query results as a list of column-name-to-value maps, never
nullbut may be empty - Throws:
NullPointerException- if the query isnullToolException- if the query fails and the implementation chooses to tell the LLM why; any other exception is reported to the LLM as a generic error
-
executeQuery
Executes the given SQL query and returns one page of its rows, for a component that loads rows lazily. The default runsSELECT * FROM (sql) AS _t ORDER BY "column" ASC LIMIT limit OFFSET offsetthroughexecuteQuery(String). Without sort orders, it runs the query followed byLIMIT limit OFFSET offset, orSELECT * FROM (sql) AS _limited LIMIT limit OFFSET offsetwhen the query has aLIMIT,OFFSET,FETCHorSELECT TOPof its own. The sort orders name result columns of the query, written as quoted identifiers. The query may end in a semicolon, which is left out, or in a line comment.Override this when your SQL dialect pages differently, for example with
OFFSET ... FETCH, or when your provider can page more efficiently.- Parameters:
sql- the SQL query, notnullpage- the page to return, notnull- Returns:
- the rows of the page, never
nullbut may be empty - Throws:
NullPointerException- if the query or the page isnullToolException- if the query fails and the implementation chooses to tell the LLM why. The message reaches the LLM, and any other exception is reported to it as a generic error, only when the page is requested during a tool call. An exception thrown while a component loads rows later is not reported to the LLM.- Since:
- 25.4
-
count
Returns the number of rows the given SQL query returns, for a component that loads rows lazily and needs the size of the result. The default runsSELECT COUNT(*) FROM (sql) AS _countedthroughexecuteQuery(String)and reads the first value of the first row. The query may end in a semicolon, which is left out, or in a line comment. The count is0when that query returns no row, a row without columns, or a value that is not a number.Override this when your SQL dialect counts differently, or when your provider can count more efficiently.
- Parameters:
sql- the SQL query, notnull- Returns:
- the number of rows
- Throws:
NullPointerException- if the query isnullToolException- if the query fails and the implementation chooses to tell the LLM why. The message reaches the LLM, and any other exception is reported to it as a generic error, only when the count is requested during a tool call. An exception thrown while a component loads rows later is not reported to the LLM.- Since:
- 25.4
-