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

Multiple Schema Support​

You can select one or more schemas per PostgreSQL connection. This is useful when your database organizes data across schemas (e.g., public, sales, analytics).

  1. Go to your PostgreSQL MCP server settings
  2. In the "Schemas" field, select the schemas you want to expose
  3. Click "Save"
  4. Re-scan to discover tables from all selected schemas

WizChat will automatically prefix table names with the schema name when multiple schemas are selected, so the AI can distinguish between them.

tip

If different schemas contain tables with the same name, WizChat handles this automatically — no manual disambiguation needed.

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

Metrics Dashboard​

WizChat can automatically detect key metrics from your PostgreSQL data and display them in a visual dashboard.

How It Works​

  • WizChat analyzes your schema and suggests metrics (counts, averages, totals)
  • Metrics are displayed as charts and summary cards
  • You can also define custom metrics with your own SQL

Using Metrics​

  1. Open your chatbot
  2. Go to the "MCP" tab
  3. Select a PostgreSQL server
  4. Click the "Metrics" tab
  5. View auto-detected metrics or add your own

Multiple Servers​

If you have more than one PostgreSQL connection, each server gets its own metrics tab. Server names are shown clearly even when multiple databases share the same host.

Custom Metrics​

To add a custom metric:

  1. Click "Add Custom Metric"
  2. Enter a name and SQL query
  3. Select the chart type
  4. Click "Save"

Metric Access Control​

Metric visibility is controlled in two layers:

  1. Server access — controls who can see the PostgreSQL integration at all
  2. Metric access — additional filtering within the server's user group

Both layers must pass for a user to see a metric. If the server is restricted to specific users, only those users can see any metrics — even ones marked "Public." A public metric means "visible to all server users," not "visible to everyone."

Managing per-metric access​

By default, all metrics are visible to every user who has access to the server. You can restrict individual metrics further so only specific users or groups can see them.

  1. Go to the "Metrics" tab for your PostgreSQL server
  2. Click the globe icon next to the metric you want to restrict
  3. Choose "Restricted" and add authorized users or groups
  4. Click "Save Changes"
IconMeaning
Globe (blue)Visible to all server users
Lock (amber)Restricted to specific users or groups
tip

You can use Email Groups to manage access for multiple users at once. Create groups in Settings > Email Groups, then add them to any metric.

info

When the server itself is restricted, the admin UI shows a purple banner reminding you that server-level access applies first. The "Public" option in the metric access dialog means "all server users" rather than "all chatbot users."

How it works for end users:

  • Users who don't have server access won't see any metrics from that server
  • Within an accessible server, users only see metrics they are authorized for
  • Anonymous (unauthenticated) users only see public metrics on public servers
  • No error messages or "access denied" notices are shown — restricted metrics are silently hidden

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