SQLAlchemyTableRetriever

Connect to any SQLAlchemy-supported database and execute SQL queries, returning results as a Pandas DataFrame and an optional Markdown-formatted table string.

Key Features

• Supports any SQLAlchemy-compatible database, including PostgreSQL, MySQL, SQLite, and MSSQL.
• Returns query results as a Pandas DataFrame and as a Markdown-formatted table string.
• Accepts an optional init_script to run setup SQL statements on first use.
• Initializes the database engine lazily via warm_up() on the first call.
• Handles errors gracefully, returning an empty DataFrame and an error message instead of crashing.

Configuration

1. Drag the SQLAlchemyTableRetriever component onto the canvas from the Component Library.
2. Click on the component to open the configuration panel.
3. On the General tab:
   • Set drivername to the SQLAlchemy driver, for example postgresql+psycopg2 or sqlite.
   • Set username and host for your database server.
   • Set password as a secret called DB_PASSWORD. For instructions, see Add Secrets.
   • Set database to the database name or path.
4. Optionally, set init_script on the Advanced tab with a list of SQL statements to run on initialization.

Connections

SQLAlchemyTableRetriever receives a query string at runtime. It outputs a dataframe (Pandas DataFrame) and a table (Markdown string). Connect its outputs to components that format or process the results, for example a PromptBuilder that includes the table in a prompt.

Source Code

To check this component's source code, open sqlalchemy_table_retriever.py in the Haystack Core Integrations repository.

Usage Examples

Basic Configuration

  SQLAlchemyTableRetriever:
    type: haystack_integrations.components.retrievers.sqlalchemy.sqlalchemy_table_retriever.SQLAlchemyTableRetriever
    init_parameters:
      drivername: postgresql+psycopg2
      username: myuser
      password:
        type: env_var
        env_vars:
        - DB_PASSWORD
        strict: false
      host: localhost
      port: 5432
      database: mydb

Using the Component in a Pipeline

# haystack-pipeline
components:
  retriever:
    type: haystack_integrations.components.retrievers.sqlalchemy.sqlalchemy_table_retriever.SQLAlchemyTableRetriever
    init_parameters:
      drivername: postgresql+psycopg2
      username: myuser
      password:
        type: env_var
        env_vars:
        - DB_PASSWORD
        strict: false
      host: db.example.com
      port: 5432
      database: mydb

  prompt_builder:
    type: haystack.components.builders.PromptBuilder
    init_parameters:
      template: "Answer the question based on the table:\n{{ table }}\n\nQuestion: {{ query }}"

connections:
- sender: retriever.table
  receiver: prompt_builder.table

inputs:
  query:
  - retriever.query
  - prompt_builder.query

outputs:
  replies: llm.replies

Parameters

Inputs

Parameter	Type	Description
query	str	The SQL query to execute.

Outputs

Parameter	Type	Description
dataframe	DataFrame	A Pandas DataFrame with the query results.
table	str	A Markdown-formatted string representation of the DataFrame.
error	str	An error message if the query failed, otherwise an empty string.

Init Parameters

These are the parameters you can configure in Pipeline Builder:

Parameter	Type	Default	Description
drivername	str		The SQLAlchemy driver name, for example sqlite, postgresql+psycopg2, or mysql+pymysql.
username	Optional[str]	None	Database username.
password	Optional[Secret]	None	Database password as a Haystack Secret.
host	Optional[str]	None	Database host.
port	Optional[int]	None	Database port.
database	Optional[str]	None	Database name or path. Use :memory: for an SQLite in-memory database.
init_script	Optional[List[str]]	None	Optional list of SQL statements executed once on warm_up(), for example to create tables or insert seed data. Each statement should be a separate string.

Run Method Parameters

These are the parameters you can configure for the component's run() method. This means you can pass these parameters at query time through the API, in Playground, or when running a job. For details, see Modify Pipeline Parameters at Query Time.

Parameter	Type	Default	Description
query	str		The SQL query to execute.

Related Information

• Add Secrets
• Pipeline Components