Skip to main content

PostgreSQL Integration (QueryWeaver)

Connect PostgreSQL databases and query them using natural language.

Overview​

QueryWeaver allows your chatbot to:

  • Understand your database schema
  • Convert questions to SQL queries
  • Execute queries safely
  • Format results for users

How It Works​

  1. User asks a question in natural language
  2. WizChat understands the database schema
  3. AI generates appropriate SQL query
  4. Query is executed safely
  5. Results are formatted and presented

Example:

  • User: "How many orders were placed last month?"
  • SQL: SELECT COUNT(*) FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'
  • Response: "There were 1,247 orders placed last month."

Connecting a Database​

  1. Go to "Settings" > "Integrations"
  2. Click "PostgreSQL"
  3. Enter connection details:
    • Host
    • Port (default: 5432)
    • Database name
    • Username
    • Password
  4. Click "Test Connection"
  5. Click "Save"

Connection Options​

OptionDescription
SSL ModeRequire, prefer, or disable SSL
Connection TimeoutMax connection wait time
Query TimeoutMax query execution time
Read OnlyRestrict to SELECT queries

Schema Scanning​

After connecting, WizChat scans your schema:

  1. Tables and columns are discovered
  2. Data types are identified
  3. Relationships are mapped
  4. Descriptions can be added

Viewing Schema​

  1. Go to "Integrations" > "PostgreSQL"
  2. Click "Schema"
  3. Browse tables and columns

Adding Descriptions​

Help the AI understand your schema:

  1. Click on a table or column
  2. Add a description
  3. Click "Save"

Example:

  • Table cust: "Customer information table"
  • Column amt: "Order amount in USD"

Query Safety​

Read-Only Mode​

By default, only SELECT queries are allowed:

  • No INSERT, UPDATE, DELETE
  • No schema changes
  • Safe for production databases

Query Review​

Optionally review queries before execution:

  1. Go to "Settings" > "PostgreSQL"
  2. Enable "Review Queries"
  3. Queries show SQL before running

Row Limits​

Prevent large result sets:

  • Default: 100 rows
  • Configurable per database
  • Pagination for larger results

Example Queries​

Natural LanguageGenerated SQL
"Show me top 10 customers"SELECT * FROM customers ORDER BY total_purchases DESC LIMIT 10
"What's our monthly revenue?"SELECT DATE_TRUNC('month', order_date), SUM(amount) FROM orders GROUP BY 1
"Find orders over $1000"SELECT * FROM orders WHERE amount > 1000

Best Practices​

Database Setup​

  • Use a read-only database user
  • Grant minimal required permissions
  • Consider using a replica
  • Set appropriate timeouts

Schema Optimization​

  • Add descriptions to tables/columns
  • Use clear naming conventions
  • Document relationships
  • Exclude sensitive tables

Performance​

  • Add indexes for common queries
  • Set appropriate row limits
  • Monitor query performance
  • Use query timeouts

Security​

  • Credentials are encrypted
  • Connections use SSL when available
  • Queries are parameterized
  • Access can be revoked anytime
warning

Never connect databases containing sensitive data (passwords, PII) without proper security review.

Troubleshooting​

Connection Failed​

  • Verify host and port
  • Check firewall rules
  • Confirm credentials
  • Test SSL settings

Queries Not Working​

  • Check schema descriptions
  • Verify table permissions
  • Review generated SQL
  • Add more context to questions

Slow Queries​

  • Add appropriate indexes
  • Reduce row limits
  • Check database performance
  • Optimize complex queries