Interface DatabaseProvider

All Superinterfaces:
Serializable

public interface DatabaseProvider extends Serializable
Provider for database schema information and SQL query execution on behalf of an LLM. This interface enables AI-powered components to interact with application databases for features.

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 Type
    Method
    Description
    default int
    Returns the number of rows the given SQL query returns, for a component that loads rows lazily and needs the size of the result.
    Executes the given SQL query and returns the results.
    default List<Map<String,Object>>
    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

      List<Map<String,Object>> executeQuery(String sql)
      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 ToolException whose 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, not null
      Returns:
      the query results as a list of column-name-to-value maps, never null but may be empty
      Throws:
      NullPointerException - if the query is null
      ToolException - 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

      default List<Map<String,Object>> executeQuery(String sql, QueryPage page)
      Executes the given SQL query and returns one page of its rows, for a component that loads rows lazily. The default runs SELECT * FROM (sql) AS _t ORDER BY "column" ASC LIMIT limit OFFSET offset through executeQuery(String). Without sort orders, it runs the query followed by LIMIT limit OFFSET offset, or SELECT * FROM (sql) AS _limited LIMIT limit OFFSET offset when the query has a LIMIT, OFFSET, FETCH or SELECT TOP of 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, not null
      page - the page to return, not null
      Returns:
      the rows of the page, never null but may be empty
      Throws:
      NullPointerException - if the query or the page is null
      ToolException - 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

      default int count(String sql)
      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 runs SELECT COUNT(*) FROM (sql) AS _counted through executeQuery(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 is 0 when 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, not null
      Returns:
      the number of rows
      Throws:
      NullPointerException - if the query is null
      ToolException - 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