The inline confirmation appears; click Show Query to verify the table, columns, and values before confirming.
Best Practices for INSERT Operations  Copy Section
<AccordionGroup>
<Accordion title="Specify All Required Fields">
Mention all columns that don't have default values or aren't auto-generated.
Good: "Add a user with name, email, and status"
Avoid: "Add a user" (missing required fields)
</Accordion>
<Accordion title="Include Table Names for Clarity">
In databases with multiple schemas or similar table names, specify the full table path, e.g. "Add to test_schema.users".
</Accordion>
<Accordion title="Be Explicit with Data Types">
Provide values in the expected format for the column type.
Good: "Add order with date 2025-01-15"
Avoid: "Add order with date next Monday" (ambiguous)
</Accordion>
<Accordion title="Review Generated SQL">
Always check the generated INSERT statement in the confirmation prompt (via Show Query) to ensure the correct table is targeted, all required columns are included, and data types match expectations.
</Accordion>
</AccordionGroup>
UPDATE Operations - Modifying Records  Copy Chapter
Update existing records by describing what should change and which records to affect.

The AI generates:

Conditional and bulk updates work the same way: "Set all products in Electronics category to have discount 10" generates an UPDATE with the matching WHERE clause.
When the confirmation appears, pay special attention to the WHERE clause.
<Warning>
Missing WHERE clauses will update ALL records in the table. Always verify the WHERE condition before confirming.
</Warning>
After confirming, verify the "Action Executed" message, then run a follow-up SELECT to confirm the intended records changed.
Best Practices for UPDATE Operations  Copy Section
<AccordionGroup>
<Accordion title="Always Specify WHERE Conditions">
Be explicit about which records to update to avoid accidentally modifying all records.
Good: "Update user with id 5"
Dangerous: "Update all users" (only if you mean all records)
</Accordion>
<Accordion title="Use Unique Identifiers">
Reference records by primary keys or unique columns when possible.
Good: "Update user with id 123"
Risky: "Update user named John" (may match multiple records)
</Accordion>
<Accordion title="Test with SELECT First">
For complex updates, first run a SELECT with the same criteria to see which records will be affected.
Step 1: "Show me all inactive users"
Step 2 (after reviewing): "Set all inactive users to status deleted"
</Accordion>
<Accordion title="Avoid Updating Primary Keys">
Generally avoid updating primary key columns, as this can break foreign key relationships.
</Accordion>
</AccordionGroup>
DELETE Operations - Removing Records  Copy Chapter
Delete operations require the most caution as they permanently remove data. Like all modifications, DELETE requests go through the inline confirmation before anything executes.

The AI generates the statement and shows the inline confirmation:

Conditional deletions ("Delete all inactive users", "Remove all log entries older than 90 days") follow the same pattern. Click Show Query to review the DELETE statement and its WHERE clause, then Confirm to execute or Cancel to abort. Afterwards, run a follow-up SELECT to confirm the records are gone.
<Warning>
DELETE operations are permanent and cannot be undone through WhoDB. Only database backups can restore deleted data.
</Warning>
Best Practices for DELETE Operations  Copy Section
<AccordionGroup>
<Accordion title="Always Use WHERE Clauses">
Never delete without specifying which records to remove.
Good: "Delete user with id 5"
Extremely Dangerous: "Delete all users" (removes all records)
</Accordion>
<Accordion title="SELECT Before DELETE">
Run a SELECT query first to verify which records will be deleted.
Step 1: "Show me all users with status inactive"
Step 2 (after reviewing): "Delete all users with status inactive"
</Accordion>
<Accordion title="Understand Cascade Rules and Dependencies">
If your schema has CASCADE DELETE rules, deleting one record might remove related records in other tables. Conversely, foreign key constraints may block the deletion until referencing records are handled. Verify your schema's constraints before deleting.
</Accordion>
<Accordion title="Consider Soft Deletes">
For recoverable deletions, use UPDATE to set a deleted flag instead of DELETE: "Update user with id 5 to set deleted true".
</Accordion>
<Accordion title="Backup Before Bulk Deletes">
Before deleting thousands of records, verify your database backup is current and tested.
</Accordion>
</AccordionGroup>
Understanding Confirmation Prompts  Copy Chapter
The inline confirmation is your final checkpoint before data modification.
What the Confirmation Shows  Copy Section
<Steps>
<Step title="Operation Warning">
An inline alert warning that the operation will modify your database.
</Step>
<Step title="SQL Statement">
Click Show Query to see the exact SQL that will execute, including the target table, columns being affected, WHERE conditions, and values being set.
</Step>
<Step title="Action Buttons">
- Confirm: Proceeds with the operation
- Cancel: Aborts the operation
</Step>
</Steps>
Checklist Before Confirming  Copy Section
- [ ] The SQL targets the intended table and schema
- [ ] For UPDATE and DELETE, the WHERE clause targets the correct records
- [ ] For INSERT and UPDATE, values are correct and properly formatted
- [ ] The number of rows likely affected matches your expectation
- [ ] A WHERE clause exists unless you truly intend to affect all records
<Tip>
If anything in the confirmation looks unexpected, click Cancel and rephrase your request to the AI assistant.
</Tip>
Verifying Modifications  Copy Chapter
After executing a modification, verify the changes were applied correctly.
<Steps>
<Step title="Check Action Executed Message">
Look for the "Action Executed" confirmation in the chat. The success message shows which operation ran (for example, "DELETE" or "UPDATE").

</Step>
<Step title="Query the Modified Data">
Run a SELECT query to verify the changes:
After INSERT: "Show me the user with email [email protected]"
After UPDATE: "Show user with id 5"
After DELETE: "Count how many log entries are older than 90 days" (expect 0)
</Step>
</Steps>
Rollback Strategies  Copy Chapter
Plan recovery before making data modifications.
Prevention is Best  Copy Section
<CardGroup cols={2}>
<Card title="Test in Development" icon="flask">
Practice modifications in a development database first
</Card>
<Card title="Use Transactions" icon="rotate-left">
Run critical operations in transaction blocks (via Scratchpad)
</Card>
<Card title="Regular Backups" icon="clock-rotate-left">
Maintain frequent database backups for recovery
</Card>
<Card title="Soft Deletes" icon="eye-slash">
Use status flags instead of permanent deletion where possible
</Card>
</CardGroup>
If You Make a Mistake  Copy Section
<Steps>
<Step title="Stop and Assess">
Don't make additional changes. Note exactly what operation was executed, how many records were affected, and which tables.
</Step>
<Step title="Consider Manual Correction">
For small mistakes (wrong value in one record), correct it with another UPDATE:

</Step>
<Step title="Restore from Backup">
For significant data loss, restore the database from the most recent backup before the mistake.
<Warning>
Restoring from backup will lose any changes made after the backup was created.
</Warning>
</Step>
</Steps>
Using Transactions for Safety  Copy Section
For critical modifications, use the Scratchpad to wrap operations in transactions:

<Info>
The AI Chat Assistant executes operations immediately after confirmation. For transaction control, use the Scratchpad query interface where you can manually manage BEGIN, COMMIT, and ROLLBACK.
</Info>
Safety Practices for Production Data  Copy Chapter
The essentials when modifying production databases:
- Verify your connection — confirm you're on the correct database (production vs. development)
- SELECT before you modify — understand the current state and estimate the affected row count
- Check backups — ensure recent backups exist and have been tested
- Start small — for bulk operations, test with a single record first
- Verify each step — check results after each modification before proceeding
- Schedule bulk operations — perform large modifications during maintenance windows
For the full set of production guidelines, change management checklists, and audit considerations, see AI Chat Assistant Best Practices.
Common Modification Patterns  Copy Chapter
The core pattern for every safe modification is preview → modify → verify:
<Steps>
<Step title="Preview Target Records">

Review (and optionally count) the records that will be affected.
</Step>
<Step title="Perform the Modification">

Review the confirmation and confirm.
</Step>
<Step title="Verify the Result">

Confirm the change was applied correctly.
</Step>
</Steps>
The same three-step shape applies to inserts (check the record doesn't exist, insert, confirm it was created), deletions (preview, delete, confirm records are gone), and cleanups. For records with dependencies — such as deleting a user who has orders — handle the related records first, then the parent record.
Troubleshooting  Copy Chapter
<AccordionGroup>
<Accordion title="Confirmation Doesn't Appear">
Symptom: You request a modification, but no confirmation prompt shows.
Possible Causes: The AI generated a read-only query instead, or the request wasn't recognized as a modification.
Solutions: Rephrase your request more explicitly ("Delete user with id 5"), check the AI response for error messages, or try a simpler, more direct modification request.
</Accordion>
<Accordion title="Foreign Key Constraint Error">
Symptom: "Foreign key constraint violation" error when deleting or updating.
Cause: Other tables have records referencing the record you're trying to modify.
Solutions: Delete or update referencing records first, check foreign key relationships, and consider CASCADE rules in your schema.
</Accordion>
<Accordion title="WHERE Clause Missing Warning">
Symptom: AI warns "This will affect all records in the table".
Solutions: Cancel the operation and rephrase with specific conditions ("Delete user with id 5" not "Delete user"). If you truly want to affect all records, explicitly confirm.
</Accordion>
<Accordion title="Wrong Records Modified">
Symptom: The operation modified different records than intended.
Solutions: Check what was actually modified with a SELECT query. If correctable, run a compensating UPDATE; if serious, restore from backup. In future, use SELECT first to verify target records.
</Accordion>
<Accordion title="Permission Denied Error">
Symptom: "Permission denied" or "Insufficient privileges" error.
Solutions: Verify your database user has INSERT/UPDATE/DELETE permissions, contact your database administrator, or check if the connection is read-only.
</Accordion>
<Accordion title="Data Type Mismatch">
Symptom: "Data type mismatch" or "Invalid input syntax" error.
Solutions: Check the table schema for column types. Format dates as 'YYYY-MM-DD', use numbers without quotes for numeric columns, and proper boolean values (true/false).
</Accordion>
<Accordion title="Large Modifications Are Slow">
Solutions: Break the operation into smaller batches, use the Scratchpad to run batched statements with transaction control, and schedule large modifications during low-traffic periods.
</Accordion>
</AccordionGroup>
Comparing AI Chat vs. Traditional Methods  Copy Chapter
| Scenario | AI Chat Assistant | Traditional UI | Scratchpad SQL |
|----------|-------------------|----------------|----------------|
| Quick single record change | Fast and convenient | Multiple clicks required | Overkill for simple changes |
| Bulk updates with conditions | Natural language, easy | Must use SQL | Most control and visibility |
| Complex multi-table operations | May require multiple steps | Not supported | Best option |
| Production-critical changes | Good with careful review | Limited capabilities | Recommended for control |
<Tip>
Use AI Chat for quick modifications and exploration. Use Scratchpad for complex operations requiring transaction control or multiple related statements.
</Tip>
Security Considerations  Copy Chapter
<Warning>
Your modification requests and table/column names may be sent to external AI providers. However, actual data values are not sent.
</Warning>
What's Sent to AI Providers: your natural language request, database schema (table and column names), and database type.
What's NOT Sent: actual data values, query results, or existing record contents.
For maximum privacy, use local models (Ollama, LM Studio) for complete data isolation, avoid mentioning sensitive values in your requests, and use Scratchpad for modifications involving sensitive data.
Audit and Compliance: Modifications are associated with the database user credentials used, so database-level logs can attribute changes. Enable your database's audit or query logging for comprehensive tracking.
Permission Management: Follow the principle of least privilege — use database users with only necessary permissions, read-only users for exploration, and separate credentials for production vs. development. See AI Chat Assistant Best Practices for read-only user setup.
Next Steps  Copy Chapter
<CardGroup cols={2}>
<Card title="Querying Data" icon="magnifying-glass" href="/ai/querying-data">
Learn how to retrieve and analyze data without modifications
</Card>
<Card title="Conversation Features" icon="comments" href="/ai/conversation-features">
Master multi-turn conversations for complex operations
</Card>
<Card title="Scratchpad Queries" icon="code" href="/query/writing-queries">
Use SQL directly for complex modifications requiring transactions
</Card>
<Card title="AI Best Practices" icon="star" href="/best-practices/ai-usage">
The canonical guide to safe and effective AI assistant usage
</Card>
</CardGroup>
<Check>
Every data modification and schema change (INSERT, UPDATE, DELETE, CREATE, ALTER, DROP) requires explicit confirmation, with full visibility into the SQL before it executes. Review carefully, verify with follow-up queries, and maintain backups for recovery.
</Check>
---
Ai/Querying Data  Copy Section
---
title: "Querying Data with AI Chat"
description: "Master the art of retrieving and analyzing database data using natural language queries"
---
Querying Data with AI Chat
WhoDB's AI Chat Assistant transforms database querying from a technical task into a natural conversation. Ask questions in plain English and get instant results with interactive tables, generated SQL, and intelligent error handling.
<Tip>
The AI assistant understands your database schema and generates optimized queries specific to your database type
</Tip>
<Warning>
AI querying requires an active provider and selected model. If the Chat page shows no available model, configure a provider first; prompts cannot be sent without a model.
</Warning>
Getting Started with Simple Queries  Copy Chapter
Traditional database querying requires knowledge of SQL syntax, table structures, and join relationships. The AI Chat Assistant eliminates these barriers by translating natural language into accurate SQL queries.
Asking Your First Question  Copy Section
<Steps>
<Step title="Navigate to Chat">
Click the Chat icon in the sidebar to access the AI assistant.
</Step>
<Step title="Type Your Question">
In the input field at the bottom, type a natural language question:

</Step>
<Step title="Press Enter">
Send your message. The AI's response streams in token-by-token as it is generated.
</Step>
<Step title="View Results">
The assistant will display the results in an interactive table.

</Step>
</Steps>
Simple Text Responses  Copy Section
For general questions or informational queries, the assistant provides text responses:
Example interactions:
- "Hello" - Gets a greeting and overview of capabilities
- "What tables are available?" - Lists your database tables
- "Explain what the users table contains" - Provides schema information
<Info>
Text responses are perfect for understanding your database structure before diving into queries
</Info>
Retrieving Data with SELECT Queries  Copy Chapter
The AI assistant excels at generating SELECT queries. Describe what you want — including columns, filters, sorting, and limits — and it produces the SQL:

Generated SQL:

You can phrase filtering, sorting, and limiting naturally: "the first 10 users", "products that cost more than $100", "orders with status 'completed'", "sorted by price descending".
<Tip>
Specify column names in your questions for more focused results
</Tip>
Viewing SQL Code  Copy Chapter
One of the most powerful features is the ability to toggle between table results and the generated SQL code.
SQL Code Toggle  Copy Section
When the assistant returns query results:
<Steps>
<Step title="Open the Actions Menu">
Hover over the results table and click the ellipsis (...) button that appears next to it.
</Step>
<Step title="Click Show Code">
Select Show Code from the dropdown menu to switch from table view to SQL code view.

</Step>
<Step title="Review the Query">
Examine the generated SQL to understand how your question was translated.
</Step>
<Step title="Toggle Back">
Open the menu again and select Show Table to return to the table view.
</Step>
</Steps>
Why View SQL Code?  Copy Section
<AccordionGroup>
<Accordion title="Learn SQL">
See how natural language maps to SQL syntax. Asking "Count users by email domain" and viewing the generated SQL teaches you string functions, aggregation, and grouping all at once.
</Accordion>
<Accordion title="Verify Query Logic">
For important queries, review the SQL to ensure it matches your intent: check JOIN conditions, WHERE clauses, GROUP BY logic, and ORDER BY direction.
</Accordion>
<Accordion title="Copy for Reuse">
Copy generated SQL to use in application code, save in the Scratchpad for refinement, or share with teammates.
</Accordion>
<Accordion title="Debug Issues">
If results aren't what you expected, viewing the SQL helps identify wrong table references, incorrect column names, missing filters, or unexpected joins.
</Accordion>
</AccordionGroup>
Complex Queries  Copy Chapter
The AI assistant handles sophisticated queries including aggregations, joins, and multi-step logic.
Aggregation Queries  Copy Section
Count, sum, average, and other statistical operations:

Generated SQL:

All the standard aggregation functions are available through natural phrasing: "How many users do we have?" (COUNT), "What's the total revenue?" (SUM), "Average order value" (AVG), "Highest and lowest prices" (MIN/MAX).
Multi-Table Queries (Joins)  Copy Section
Ask questions that span multiple tables:

Generated SQL:

<Info>
The AI assistant automatically determines the correct JOIN type and conditions based on your schema
</Info>
Dates, Patterns, and Multiple Conditions  Copy Section
The same natural phrasing works for time filters, pattern matching, and combined criteria:

The AI translates these into DATE_TRUNC/date-range filters, LIKE patterns, and combined WHERE conditions or subqueries as appropriate for your database type.
Understanding Query Results  Copy Chapter
When the AI assistant returns data, you get an interactive table with powerful features.
Interactive Results Table  Copy Section
Table Features:
- Column Headers: Show field names from your query
- Scrollable: Navigate large result sets
- Readable Formatting: Data types displayed appropriately
The assistant also provides context about your results, such as the number of rows returned and the columns included. NULL values are clearly distinguishable from empty strings.
Large Result Sets  Copy Section
Chat results are shown in a fixed-height scrollable table containing all returned rows. For queries returning many rows:
- Scroll within the table to view all data
- Consider adding LIMIT in your question
<Warning>
Very large result sets may take longer to load. Ask the assistant to limit results if you only need a sample
</Warning>
Handling Errors  Copy Chapter
The AI assistant provides clear, helpful error messages when queries fail.
Common Error Types  Copy Section
<AccordionGroup>
<Accordion title="Table or Column Not Found">
Error Example: ERROR: relation "test_schema.nonexistent_table" does not exist
The table or column name doesn't exist in your schema. Check your names, ask "What tables are available?", and verify spelling and schema name.
</Accordion>
<Accordion title="Permission Denied">
Error Example: ERROR: permission denied for table users
Your database user lacks permissions for this operation. Contact your database administrator or use a connection with appropriate permissions.
</Accordion>
<Accordion title="Syntax Error">
Error Example: ERROR: syntax error at or near "FROM"
The generated SQL has invalid syntax (rare but possible). Rephrase your question more clearly or break complex questions into simpler steps.
</Accordion>
<Accordion title="Type Mismatch">
Error Example: ERROR: column "price" is of type numeric but expression is of type text
Data type incompatibility in the query. Ask about the schema first, then rephrase with correct type expectations.
</Accordion>
</AccordionGroup>
Recovering from Errors  Copy Section
<Steps>
<Step title="Read the Error Message">
The error message contains clues about what went wrong.
</Step>
<Step title="Rephrase or Simplify">
Try asking the same thing in a different way, or break a complex question into steps:

</Step>
<Step title="Ask for Help">
The assistant can explain what went wrong:

</Step>
</Steps>
Multi-Turn Conversations  Copy Chapter
One of the AI assistant's most powerful features is maintaining conversation context for follow-up questions.
Building on Previous Queries  Copy Section
The assistant remembers your conversation, so each question can build on the last:

<Tip>
Use pronouns like "them," "those," "these" naturally—the assistant understands what you're referring to
</Tip>
Chat History Navigation  Copy Section
Navigate through your previously sent messages using the arrow keys: press <kbd>Up</kbd> in the input field to load your previous message, keep pressing to go further back, and press <kbd>Down</kbd> to move forward. Edit the loaded message and press <kbd>Enter</kbd> to resend.
Example Workflow: Data Exploration  Copy Chapter
Explore an unfamiliar database systematically:
<Steps>
<Step title="List Tables">

</Step>
<Step title="Examine Schema">

</Step>
<Step title="Sample Data">

</Step>
<Step title="Check Relationships">

</Step>
<Step title="Analyze Data">

</Step>
</Steps>
For a complete walkthrough of this approach, see AI-Powered Data Exploration.
Moving Queries to Scratchpad  Copy Chapter
When you generate a useful query, save it for future use:
<Steps>
<Step title="Find the Query">
Locate a query result with SQL you want to save.
</Step>
<Step title="Click Move to Scratchpad">
Hover over the result, click the ellipsis (...) button, and select Move to Scratchpad from the dropdown menu.
</Step>
<Step title="Choose Destination">
New Page: Creates a new Scratchpad page with this query
Existing Page: Adds to an existing Scratchpad page
Provide a page name if creating new.
</Step>
<Step title="Confirm">
Click Confirm to move the query.
</Step>
<Step title="Access in Scratchpad">
Navigate to Scratchpad to find your saved query, where you can edit and refine it, add comments, execute it multiple times, or copy important SQL into shared docs, runbooks, or your repository.
</Step>
</Steps>
<Info>
Queries moved to Scratchpad retain their original SQL exactly as generated by the AI
</Info>
For guidance on formulating effective questions, safety, and performance, see AI Chat Assistant Best Practices.
Keyboard Shortcuts  Copy Chapter
| Shortcut | Action |
|----------|--------|
| Up Arrow | Previous message in history |
| Down Arrow | Next message in history |
| Enter | Send message |
Arrow keys navigate through your previously sent messages, letting you quickly reload, edit, and resend earlier questions.
Troubleshooting  Copy Chapter
<AccordionGroup>
<Accordion title="AI Doesn't Understand My Question">
Symptom: The generated query doesn't match what you wanted.
Solutions:
1. Rephrase using different words
2. Be more specific about table and column names
3. Break complex questions into simpler parts
Try: "Show me users who signed up recently" instead of "Get the new people"
</Accordion>
<Accordion title="Results Are Different Than Expected">
Symptom: Query executes but returns unexpected data.
Solutions:
1. View the generated SQL to understand the logic
2. Check if filters are applied correctly and you're querying the right table
3. Add more specific conditions to your question
</Accordion>
<Accordion title="Query Takes Too Long">
Symptom: The assistant or query execution is very slow.
Solutions:
1. Add LIMIT to your question: "Show me the first 100 users"
2. Add specific filters or time ranges to reduce data volume
3. Break large operations into smaller queries
</Accordion>
<Accordion title="Can't Find My Previous Query">
Symptom: You want to re-run a query but can't find it.
Solutions:
1. Use Up arrow to navigate through message history
2. Scroll up in the chat to find the previous response
3. Check Scratchpad if you moved the query there
Next time: Move important queries to Scratchpad for permanent storage
</Accordion>
<Accordion title="AI Returns Text Instead of Data">
Symptom: You expected a table but got a text explanation.
Solutions: Make your question action-oriented using verbs like "show," "list," "display," "find".
Try: "Show me all users" instead of "Tell me about users"
</Accordion>
<Accordion title="Results Missing Expected Columns">
Symptom: The result table doesn't include columns you need.
Solutions: Explicitly mention the columns you want, or ask a follow-up: "Show me the same data but include [column name]".
</Accordion>
</AccordionGroup>
Limitations  Copy Chapter
Understanding limitations helps set appropriate expectations:
<AccordionGroup>
<Accordion title="Extremely Complex Logic">
Some extremely complex operations — recursive CTEs with deep nesting, dynamic pivots, complex window functions with multiple partitions — may require manual SQL in Scratchpad or breaking into multiple simpler queries.
</Accordion>
<Accordion title="Database-Specific Functions">
The AI adapts to your database type, but some advanced, database-specific functions or proprietary extensions may need clarification. Move to Scratchpad for advanced database-specific features.
</Accordion>
<Accordion title="Performance Optimization">
The AI generates correct queries but may not always produce the most optimized version for very large tables or complex join scenarios. Review and optimize generated SQL in Scratchpad for performance-critical queries.
</Accordion>
<Accordion title="Ambiguous Questions">
If your question could be interpreted multiple ways, the AI will make its best guess or ask clarifying questions. Be as specific as possible, especially with column names and conditions.
</Accordion>
</AccordionGroup>
Privacy and Security  Copy Chapter
<Warning>
For sensitive data, consider using local AI models (Ollama, LM Studio) instead of cloud providers
</Warning>
Sent to AI:
- Your question text
- Database schema (table and column names)
- Database type (PostgreSQL, MySQL, etc.)
Never Sent:
- Actual data from your tables
- Query results
- Connection credentials
- Passwords or sensitive configuration
For sensitive data: use local models for complete privacy, review your organization's policies on external AI services, avoid sensitive schema names with cloud providers, and use read-only connections for exploratory querying. See AI Chat Assistant Best Practices for full guidance.
Next Steps  Copy Chapter
<CardGroup cols={2}>
<Card title="Modifying Data" icon="pen-to-square" href="/ai/modifying-data">
Learn how to safely update, insert, and delete data using the AI assistant
</Card>
<Card title="Conversation Features" icon="comments" href="/ai/conversation-features">
Master advanced conversation features and context management
</Card>
<Card title="Scratchpad Integration" icon="code" href="/query/scratchpad-intro">
Move generated queries to Scratchpad for refinement and reuse
</Card>
<Card title="Best Practices" icon="star" href="/best-practices/ai-usage">
Discover optimal patterns for AI-assisted database work
</Card>
</CardGroup>
<Check>
You now have the skills to query databases naturally using AI, view and understand generated SQL, and handle complex data retrieval scenarios
</Check>
---
Ai/Setup Providers  Copy Section
---
title: "Setting Up AI Providers"
description: "Configure OpenAI, Anthropic, Gemini, Ollama, LM Studio, or custom AI providers for WhoDB's chat assistant"
---
Setting Up AI Providers
Before you can use WhoDB's AI Chat Assistant, you need to configure at least one AI provider. This guide walks you through setting up each supported provider and choosing the right model for your needs.
Accessing the AI Configuration  Copy Chapter
Navigate to the Chat page in WhoDB to access AI provider settings:
The provider configuration is located at the top of the Chat interface with two dropdowns:
1. AI Provider: Select your provider (OpenAI, Anthropic, Gemini, Ollama, LM Studio, etc.)
2. AI Model: Choose the specific model to use
<Warning>
Pick a provider and model before starting the query tutorials. The chat box becomes active once both are selected.
</Warning>
Choosing Your AI Provider  Copy Chapter
Different providers offer different trade-offs between accuracy, speed, cost, and privacy:
<CardGroup cols={2}>
<Card title="OpenAI" icon="openai">
Best for: Most users, general-purpose queries
- Industry-leading accuracy
- Fast response times
- Pay-per-use pricing
</Card>
<Card title="Anthropic" icon="anthropic">
Best for: Complex reasoning, large contexts
- Excellent with sophisticated queries
- Very large context windows
- Strong safety features
</Card>
<Card title="Gemini" icon="google">
Best for: Google Cloud users, general-purpose queries
- Strong reasoning and SQL generation
- Competitive pricing
- Pay-per-use pricing
</Card>
<Card title="Ollama / LM Studio" icon="server">
Best for: Privacy, zero API costs
- Complete data privacy
- No internet required
- Free to use
</Card>
<Card title="Custom" icon="plug">
Best for: Enterprise deployments
- Self-hosted models
- Organization-specific configurations
- Complete control
</Card>
</CardGroup>
Setting Up OpenAI  Copy Chapter
OpenAI provides GPT models that offer excellent SQL generation capabilities with fast response times.
Prerequisites  Copy Section
Before configuring OpenAI:
1. Create an OpenAI account at https://platform.openai.com/
2. Add payment method to your account
3. Generate an API key from https://platform.openai.com/api-keys
<Warning>
Keep your OpenAI API key secure. Never share it or commit it to version control
</Warning>
Configuration Steps  Copy Section
<Steps>
<Step title="Access Provider Configuration">
On the Chat page, click the AI Provider dropdown.

</Step>
<Step title="Add New Provider">
Click "Add a provider" at the bottom of the dropdown menu.
A sheet will slide in from the right side of the screen.
</Step>
<Step title="Select OpenAI">
In the Model Type dropdown, select OpenAI.
The dropdown shows icons for each provider to help identify them quickly.
</Step>
<Step title="Enter API Key">
Paste your OpenAI API key in the Token field.

<Info>
API keys starting with sk-proj- are project-specific keys recommended by OpenAI
</Info>
</Step>
<Step title="Submit Configuration">
Click the Submit button to test the connection and save your configuration.
WhoDB will:
- Verify the API key is valid
- Fetch available models
- Save the configuration for future use
</Step>
<Step title="Select Model">
Once configured, the AI Model dropdown populates with the models fetched live from your OpenAI account. Pick a model based on your accuracy, speed, and cost needs.

</Step>
</Steps>
Cost Considerations  Copy Section
OpenAI charges based on tokens used. Costs vary by model, schema size, and conversation length. See OpenAI's pricing page for current rates and monitor usage in your OpenAI dashboard.
Setting Up Anthropic (Claude)  Copy Chapter
Anthropic's Claude models excel at complex reasoning and handle large database schemas exceptionally well.
Prerequisites  Copy Section
1. Create an Anthropic account at https://console.anthropic.com/
2. Add payment method
3. Generate an API key from the console
Configuration Steps  Copy Section
<Steps>
<Step title="Open Provider Configuration">
Click the AI Provider dropdown and select "Add a provider".
</Step>
<Step title="Select Anthropic">
Choose Anthropic from the Model Type dropdown.
</Step>
<Step title="Enter API Key">
Paste your Anthropic API key in the Token field.

</Step>
<Step title="Submit and Select Model">
Click Submit to verify and save the configuration.
Available Claude models are fetched live from the Anthropic API and appear in the Model dropdown. See Anthropic's pricing page for current models and rates.
</Step>
</Steps>
When to Use Claude  Copy Section
Claude excels in these scenarios:
- Large Schemas: Handles databases with hundreds of tables
- Complex Joins: Better at understanding multi-table relationships
- Contextual Queries: Superior at maintaining long conversation contexts
- Ambiguous Requests: Better at asking clarifying questions
Setting Up Google Gemini  Copy Chapter
Gemini models offer strong SQL generation with competitive pricing.
Prerequisites  Copy Section
1. Create a Google AI Studio account at https://aistudio.google.com/
2. Generate an API key from the AI Studio console
Configuration Steps  Copy Section
<Steps>
<Step title="Open Provider Configuration">
Click the AI Provider dropdown and select "Add a provider".
</Step>
<Step title="Select Gemini">
Choose Gemini from the Model Type dropdown.
</Step>
<Step title="Enter API Key">
Paste your Gemini API key in the Token field.
</Step>
<Step title="Submit and Select Model">
Click Submit to verify and save the configuration.
Available Gemini models are fetched live from the Gemini API and appear in the Model dropdown. See Google's Gemini pricing page for current models and rates.
</Step>
</Steps>
<Note>
To route Gemini requests through a custom endpoint (e.g., a proxy), set the WHODB_GEMINI_ENDPOINT environment variable.
</Note>
Setting Up Ollama (Local Models)  Copy Chapter
Ollama allows you to run AI models locally on your machine, providing complete privacy with zero API costs.
Prerequisites  Copy Section
<Steps>
<Step title="Install Ollama">
Download and install Ollama from https://ollama.com
Supported Platforms:
- macOS (Apple Silicon and Intel)
- Linux (x86_64, ARM64)
- Windows (via WSL2)
</Step>
<Step title="Download a Model">
Open a terminal and download your preferred model:

<Info>
The first ollama run command downloads the model (several GB). Subsequent runs use the cached model
</Info>
</Step>
<Step title="Verify Ollama is Running">
Check that Ollama is accessible:

You should see a JSON response with available models.
</Step>
</Steps>
Configuring Ollama in WhoDB  Copy Section
<Steps>
<Step title="Access Chat Page">
Navigate to the Chat page. Ollama is always listed in the AI Provider dropdown, whether or not it is currently running. Models only load when the Ollama service is reachable.
</Step>
<Step title="Select Ollama">
Choose Ollama from the provider dropdown.
<Note>
No API key required—WhoDB connects to Ollama at localhost:11434 by default. If Ollama runs on a different host or port, set the WHODB_OLLAMA_HOST and WHODB_OLLAMA_PORT environment variables.
</Note>
</Step>
<Step title="Choose Model">
Select your downloaded model from the AI Model dropdown.
Available models are those you've downloaded via ollama run.
</Step>
</Steps>
Recommended Ollama Models for SQL  Copy Section
| Model | Size | RAM Required | Best For | Download Command |
|-------|------|-------------|----------|------------------|
| Llama 3.1 (8B) | 4.7 GB | 8 GB | General use, good balance | ollama run llama3.1 |
| CodeLlama (7B) | 3.8 GB | 8 GB | Code/SQL generation | ollama run codellama |
| Mistral (7B) | 4.1 GB | 8 GB | Fast responses | ollama run mistral |
| Llama 3.1 (70B) | 40 GB | 64 GB | Maximum accuracy | ollama run llama3.1:70b |
<Tip>
Start with Llama 3.1 (8B) for the best balance of performance and resource usage
</Tip>
Ollama Performance Optimization  Copy Section
<AccordionGroup>
<Accordion title="Hardware Requirements">
Minimum:
- CPU: 4 cores
- RAM: 8 GB
- Disk: 10 GB free
Recommended:
- CPU: 8+ cores
- RAM: 16 GB+
- GPU: NVIDIA GPU with 8GB+ VRAM (optional but faster)
- Disk: 50 GB free for multiple models
</Accordion>
<Accordion title="GPU Acceleration">
Ollama automatically uses GPU if available:
NVIDIA GPU:
- Requires CUDA drivers
- Dramatically faster inference
- Supports larger models with less RAM
Apple Silicon (M1/M2/M3):
- Native Metal acceleration
- Excellent performance
- No additional configuration needed
Check GPU usage:

</Accordion>
<Accordion title="Model Performance">
Adjust model size based on your hardware:
If queries are slow:
- Use smaller models (7B instead of 70B)
- Close other applications
- Enable GPU acceleration
- Consider using cloud providers for complex queries
If accuracy is poor:
- Upgrade to larger models (70B)
- Provide more specific queries
- Use cloud providers (OpenAI/Anthropic) for critical tasks
</Accordion>
</AccordionGroup>
Ollama Privacy Benefits  Copy Section
<CardGroup cols={2}>
<Card title="Complete Data Privacy" icon="lock">
Your database schema never leaves your machine
</Card>
<Card title="No Internet Required" icon="wifi-slash">
Works in air-gapped or offline environments
</Card>
<Card title="Zero Ongoing Costs" icon="dollar-sign">
No API charges regardless of usage
</Card>
<Card title="Full Control" icon="sliders">
Choose models, control updates, customize behavior
</Card>
</CardGroup>
<Check>
Ollama is ideal for regulated industries, sensitive data, or organizations requiring complete data sovereignty
</Check>
Setting Up LM Studio (Local Models)  Copy Chapter
LM Studio is another local option with a desktop app for downloading and managing models.
<Steps>
<Step title="Install LM Studio">
Download LM Studio from https://lmstudio.ai and load a model.
</Step>
<Step title="Start the Local Server">
Enable LM Studio's local server (default: http://localhost:1234).
</Step>
<Step title="Select LM Studio in WhoDB">
Like Ollama, LM Studio is always listed in the AI Provider dropdown. Select it and choose one of your loaded models.
</Step>
</Steps>
<Note>
If LM Studio runs on a non-default endpoint, set WHODB_LMSTUDIO_BASE_URL. Use WHODB_LMSTUDIO_API_KEY if your server requires a key, and WHODB_LMSTUDIO_NAME to change the display name.
</Note>
Advanced Configuration  Copy Chapter
Multiple Providers  Copy Section
You can configure multiple AI providers and switch between them:
<Steps>
<Step title="Add Multiple Providers">
Add OpenAI, Anthropic, and Ollama providers using the "Add a provider" option.
</Step>
<Step title="Switch Between Providers">
Use the AI Provider dropdown to switch between configured providers at any time.
</Step>
<Step title="Use Case-Specific Providers">
Strategy:
- Use a local model (Ollama, LM Studio) for exploration and learning
- Use a fast, low-cost cloud model for quick production queries
- Use your provider's most capable model for complex analytics
This optimizes both cost and performance.
</Step>
</Steps>
Removing Providers  Copy Section
<Steps>
<Step title="Access Provider Management">
On the Chat page, select a manually added provider and click the Delete Provider button.

</Step>
<Step title="Confirm Deletion">
A confirmation dialog appears. Click Delete to remove the current provider configuration.
<Warning>
This removes the API key and disconnects the provider. You'll need to reconfigure to use it again
</Warning>
</Step>
</Steps>
<Note>
Providers configured by environment variables are managed through deployment configuration. Remove or change those providers by updating the deployment environment. As a security measure, a server-configured provider API key is never combined with a client-supplied endpoint — if a request overrides the endpoint, WhoDB uses the server-configured endpoint with the server key instead.
</Note>
Built-In Providers via Environment Variables  Copy Section
You can preconfigure the built-in providers on the server so users don't need to enter API keys in the UI:
| Variable | Description |
|----------|-------------|
| WHODB_OPENAI_API_KEY | OpenAI API key |
| WHODB_OPENAI_ENDPOINT | Custom OpenAI-compatible endpoint (optional) |
| WHODB_OPENAI_NAME | Display name override (optional) |
| WHODB_ANTHROPIC_API_KEY | Anthropic API key |
| WHODB_ANTHROPIC_ENDPOINT | Custom Anthropic endpoint (optional) |
| WHODB_ANTHROPIC_NAME | Display name override (optional) |
| WHODB_GEMINI_ENDPOINT | Custom Gemini endpoint (optional) |
| WHODB_OLLAMA_HOST / WHODB_OLLAMA_PORT | Ollama host and port (default: localhost:11434) |
| WHODB_OLLAMA_NAME | Display name override (optional) |
| WHODB_LMSTUDIO_BASE_URL | LM Studio server URL (default: http://localhost:1234) |
| WHODB_LMSTUDIO_API_KEY | LM Studio API key, if required (optional) |
| WHODB_LMSTUDIO_NAME | Display name override (optional) |
| WHODB_BLOCK_INTERNAL_AI_ENDPOINTS | Set to true to block AI requests to internal/private network endpoints (SSRF protection for hosted deployments) |
Custom Providers via Environment Variables  Copy Section
You can connect any OpenAI-compatible AI provider (LM Studio, OpenRouter, vLLM, etc.) using WHODB_AI_GENERIC_<ID>_* environment variables. This is useful for Docker deployments, self-hosted models, or providers not built into the UI.
Each provider needs a unique <ID> (e.g., LMSTUDIO, OPENROUTER) and up to six variables:
| Variable | Required | Default | Description |
|----------|----------|---------|-------------|
| WHODB_AI_GENERIC_<ID>_NAME | No | <ID> | Display name shown in the provider dropdown |
| WHODB_AI_GENERIC_<ID>_TYPE | No | openai-generic | Client type (leave default for OpenAI-compatible APIs) |
| WHODB_AI_GENERIC_<ID>_BASE_URL | Yes | | API base URL (e.g., http://localhost:1234/v1) |
| WHODB_AI_GENERIC_<ID>_API_KEY | No | | API key if required by the provider |
| WHODB_AI_GENERIC_<ID>_MODELS | Yes | | Comma-separated list of model names |
| WHODB_AI_GENERIC_<ID>_ICON | No | | URL of an icon to show in the provider dropdown |
<CodeGroup>

</CodeGroup>
<Info>
You can configure multiple generic providers at the same time — each <ID> creates a separate entry in the provider dropdown. The BASE_URL and MODELS variables are required; all others are optional.
</Info>
Provider Comparison  Copy Chapter
Choose the right provider for your needs:
| Feature | OpenAI | Anthropic | Ollama |
|---------|--------|-----------|--------|
| Setup Complexity | Easy | Easy | Moderate |
| Cost | Pay per use | Pay per use | Free |
| Privacy | External | External | Complete |
| Speed | Fast | Medium | Varies |
| Accuracy | Excellent | Excellent | Good |
| Internet Required | Yes | Yes | No |
| Best For | General use | Complex queries | Privacy/Cost |
Troubleshooting  Copy Chapter
<AccordionGroup>
<Accordion title="OpenAI API Key Invalid">
Symptom: "Invalid API key" error when adding OpenAI provider
Solutions:
1. Verify key is copied correctly (no extra spaces)
2. Check key hasn't been revoked in OpenAI dashboard
3. Ensure billing is set up on your OpenAI account
4. Try generating a new API key
5. Verify you're using a valid key format (starts with sk-)
</Accordion>
<Accordion title="Anthropic Connection Failed">
Symptom: Cannot connect to Anthropic after entering API key
Solutions:
1. Verify API key from https://console.anthropic.com/
2. Check your account has available credits
3. Ensure no network/firewall blocking claude.ai
4. Try a new API key
5. Check Anthropic service status
</Accordion>
<Accordion title="Ollama Not Responding">
Symptom: Ollama is selected but no models load or queries fail
Solutions:
1. Verify Ollama is running: curl http://localhost:11434/api/tags
2. Restart Ollama service
3. Check Ollama is accessible on port 11434 (or set WHODB_OLLAMA_HOST/WHODB_OLLAMA_PORT for a custom endpoint)
4. Ensure no firewall blocking localhost connections
5. Verify at least one model is downloaded
Test Ollama:

</Accordion>
<Accordion title="No Models Available">
Symptom: Model dropdown is empty after adding provider
Solutions:
1. Wait a few seconds for models to load
2. Refresh the page
3. Verify API key has proper permissions
4. Check provider dashboard for account status
5. Try removing and re-adding the provider
For Ollama:

</Accordion>
<Accordion title="Slow Ollama Response">
Symptom: Ollama queries take very long to respond
Solutions:
1. Close resource-intensive applications
2. Use smaller models (7B instead of 70B)
3. Verify GPU acceleration is working: ollama ps
4. Increase system RAM allocation
5. Consider cloud providers for time-sensitive queries
Check resource usage:

</Accordion>
<Accordion title="Rate Limit Errors">
Symptom: "Rate limit exceeded" from OpenAI or Anthropic
Solutions:
1. Wait before retrying (limits reset after time window)
2. Upgrade your API plan for higher limits
3. Reduce query frequency
4. Check usage in provider dashboard
5. Consider switching to Ollama for unlimited queries
</Accordion>
</AccordionGroup>
Security Best Practices  Copy Chapter
<Steps>
<Step title="Secure API Keys">
- Never share API keys publicly
- Don't commit keys to git repositories
- Use environment variables in production
- Rotate keys regularly (every 90 days)
- Monitor usage for unauthorized access
</Step>
<Step title="Choose Provider Based on Data Sensitivity">
Highly Sensitive Data:
- Use Ollama exclusively
- Never send to external providers
Moderately Sensitive Data:
- Review provider terms of service
- Verify data handling policies
- Consider data residency requirements
Public or Non-Sensitive Data:
- Any provider acceptable
</Step>
<Step title="Monitor Provider Usage">
OpenAI: https://platform.openai.com/usage
Anthropic: https://console.anthropic.com/settings/usage
Set up billing alerts to prevent unexpected charges.
</Step>
<Step title="Use Least Privilege">
When creating API keys:
- Limit permissions to only what's needed
- Create separate keys for development/production
- Set spending limits where available
- Enable key restrictions (IP allowlists, etc.)
</Step>
</Steps>
Next Steps  Copy Chapter
<CardGroup cols={2}>
<Card title="Querying Data" icon="magnifying-glass" href="/ai/querying-data">
Learn how to ask questions and retrieve data using your configured provider
</Card>
<Card title="Your First AI Query" icon="rocket" href="/guides/tutorials/ai-first-query">
Step-by-step tutorial to get started with the AI assistant
</Card>
<Card title="Conversation Features" icon="comments" href="/ai/conversation-features">
Master multi-turn conversations and context management
</Card>
<Card title="Best Practices" icon="star" href="/best-practices/ai-usage">
Learn optimal patterns for using the AI assistant
</Card>
</CardGroup>
<Info>
With your AI provider configured, you're ready to start querying your database using natural language
</Info>
---
Best Practices/Access Control  Copy Section
---
title: Database Access Control
description: Best practices for database access control, role-based access, read-only users, and security policies with WhoDB
---
Database Access Control
Proper access control is fundamental to database security. This guide covers implementing role-based access, managing read-only users, establishing connection profiles, and enforcing security policies to protect your databases while enabling productive work.
Access Control Fundamentals  Copy Chapter
Principle of Least Privilege  Copy Section
Grant users only the minimum permissions necessary to perform their job functions.
Least Privilege Benefits:
- Reduces blast radius of compromised credentials
- Minimizes accidental data damage
- Simplifies security audits
- Improves compliance posture
- Enables precise accountability tracking
Implementation Process:
1. Document each user's actual job requirements
2. Grant read-only access initially
3. Expand permissions only when justified
4. Review periodically and remove unused access
5. Audit changes regularly
Dangerous Anti-Patterns:
- Sharing credentials between team members
- Using administrative accounts for routine work
- Granting broad permissions "just in case"
- Not reviewing access after role changes
- Maintaining access after team transitions
Database User Categories  Copy Section
Design your access control strategy around user categories.
<AccordionGroup>
<Accordion title="Read-Only Analysts">
Purpose: Data exploration, reporting, analysis
Permissions:
- SELECT on specific schemas
- View creation (read-only views)
- Query execution only
Restrictions:
- No INSERT, UPDATE, DELETE
- No DDL (CREATE, ALTER, DROP)
- No administrative functions
- Limited to development/reporting data
Example (PostgreSQL):

</Accordion>
<Accordion title="Application Developers">
Purpose: Read and write application data in development/staging
Permissions:
- SELECT, INSERT, UPDATE on specific tables
- View creation
- Stored procedure execution
- Limited schema modifications in dev only
Restrictions:
- No production write access
- No administrative operations
- No user management
- No backup/restore operations
Example (PostgreSQL):

</Accordion>
<Accordion title="Data Engineers">
Purpose: ETL operations, data transformations, pipeline management
Permissions:
- Full access to staging schemas
- SELECT on source tables
- Full access to transformation tables
- Scheduled job execution
- Read-only access to production source data
Restrictions:
- No direct production data modifications
- No user management
- No security configuration changes
- No backup operations
Example (PostgreSQL):

</Accordion>
<Accordion title="Database Administrators">
Purpose: System administration, maintenance, emergency operations
Permissions:
- Full database access
- User and role management
- Backup and recovery operations
- Configuration management
- Performance tuning and monitoring
Restrictions:
- MFA required
- Limited to administrative tasks only
- Comprehensive audit logging mandatory
- Change approval process required
- Time-limited elevated sessions
</Accordion>
<Accordion title="Service/Application Accounts">
Purpose: Automated processes, scheduled jobs, application runtime
Permissions:
- Only necessary for application function
- Environment-specific (dev, staging, prod)
- Limited to required tables and operations
Restrictions:
- No interactive use
- No shared with developers
- Rotated regularly
- Monitored for suspicious activity
- No elevated privileges
</Accordion>
</AccordionGroup>
Role-Based Access Control (RBAC)  Copy Chapter
Designing Your RBAC Schema  Copy Section
Establish a systematic approach to managing roles and permissions.
RBAC Structure Example:

Naming Conventions:

Role Hierarchy  Copy Section
Organize roles hierarchically to simplify administration.
Hierarchy Example:

Benefits:
- Inheriting permissions reduces duplication
- Changes to parent roles propagate automatically
- Clear permission hierarchy understood by teams
- Easier onboarding and offboarding
Implementing RBAC  Copy Section
<AccordionGroup>
<Accordion title="PostgreSQL RBAC">
Step 1: Create roles

Step 2: Define permissions

Step 3: Create users and assign roles

Step 4: Set default permissions

</Accordion>
<Accordion title="MySQL RBAC">
Step 1: Create users with roles

Step 2: Grant role-based permissions

Step 3: Apply changes

Step 4: Set default role

</Accordion>
<Accordion title="MongoDB RBAC">
Step 1: Create roles with specific privileges

Step 2: Create users with role assignment

Step 3: Verify role permissions

</Accordion>
</AccordionGroup>
Read-Only Access Management  Copy Chapter
Creating Safe Read-Only Users  Copy Section
Read-only users are essential for non-destructive database access.
Read-Only User Best Practices:
- Use for analysts, consultants, auditors
- Default to read-only, expand only when justified
- Combine with IP restrictions and time-based limits
- Monitor for suspicious activity patterns
- Rotate credentials regularly
Read-Only Verification  Copy Section
<Warning>
Verify that read-only access truly prevents modifications. Some database configurations can accidentally grant write access through views or functions.
</Warning>
Verification Queries:

View-Based Read-Only Access  Copy Section
Use views to provide controlled, read-only access to specific data.
Sensitive Data Masking View:

Time-Series Data View:

Connection Profile Management  Copy Chapter
Establishing Connection Profiles  Copy Section
WhoDB connection profiles securely store database credentials and settings.
Connection Profile Best Practices:
- Use environment-specific profiles (dev, staging, prod)
- Never hardcode credentials in configuration files
- Rotate credentials regularly
- Use least privilege database users
- Enable SSL/TLS for all connections
- Document connection purpose and usage
Organizing Connection Profiles  Copy Section
<AccordionGroup>
<Accordion title="Naming Convention">
Use consistent naming for easy identification:
Format: [environment]-[database-type]-[region]-[purpose]
Examples:
- dev-postgres-us-east-analytics
- prod-mysql-us-west-app
- staging-postgres-eu-reporting
- dev-mongodb-local-testing
</Accordion>
<Accordion title="Environment Separation">
Keep profiles organized by environment:

</Accordion>
<Accordion title="Clear Profile Naming for Safety">
Use unambiguous profile names to prevent accidental production operations:
- Prefix names with the environment: prod-, staging-, dev-
- Include the access level where relevant: prod-postgres-readonly
- Always verify the profile name before executing modifications
- Train the team on the naming convention
</Accordion>
<Accordion title="Connection Parameters">
Configure security parameters in each profile:

</Accordion>
</AccordionGroup>
Credential Rotation  Copy Section
<Warning>
Regular credential rotation is essential for security. Credentials should be rotated at least quarterly, immediately after employee departure, and after any suspected breach.
</Warning>
Credential Rotation Process:

Rotation Schedule:

Security Policies  Copy Chapter
Data Classification  Copy Section
Classify data by sensitivity to guide access control decisions.
Data Classification Levels:
| Level | Examples | Access | Encryption | Auditing |
|-------|----------|--------|------------|----------|
| Public | Product catalog, public documentation | Everyone | Optional | Optional |
| Internal | Sales reports, team info | Employees | Recommended | Recommended |
| Sensitive | Customer data, health records | Department specific | Required | Required |
| Restricted | Passwords, API keys, PII | Minimal, need-based | Required | Required |
Classification Process:
1. Audit all tables and columns
2. Document sensitivity level
3. Define access restrictions
4. Implement technical controls
5. Review and update annually
Example Classification:

Implementing Access Policies  Copy Section
<AccordionGroup>
<Accordion title="Column-Level Security">
Restrict access to sensitive columns:

</Accordion>
<Accordion title="Row-Level Security">
PostgreSQL Row-Level Security (RLS):

</Accordion>
<Accordion title="Time-Based Access">
Restrict access by time:

</Accordion>
<Accordion title="IP Restriction">
Restrict connections by IP:

</Accordion>
</AccordionGroup>
Audit Logging  Copy Section
<Warning>
Enable comprehensive audit logging for all database access. Audit logs are critical for security investigations, compliance audits, and incident response.
</Warning>
What to Log:
- User login/logout events
- Query execution (SELECT, INSERT, UPDATE, DELETE)
- Schema modifications
- Security policy changes
- Failed authentication attempts
- Administrative operations
PostgreSQL Audit Configuration:

Audit Log Review Process:

Managing Access Lifecycle  Copy Chapter
User Onboarding  Copy Section
<AccordionGroup>
<Accordion title="Day 1: Account Creation">
- [ ] Database account created
- [ ] Appropriate role assigned
- [ ] Temporary password issued securely
- [ ] Connection profile provided
- [ ] Security policy overview provided
- [ ] WhoDB access granted
</Accordion>
<Accordion title="Day 7: Verification">
- [ ] User confirmed connection works
- [ ] Permissions verified correct
- [ ] Training completed on security policies
- [ ] First queries documented
- [ ] Questions answered
</Accordion>
<Accordion title="Day 30: Review">
- [ ] Permissions still appropriate for role
- [ ] Access usage review
- [ ] Feedback on onboarding process
- [ ] Additional training needs identified
- [ ] Permanent password set
</Accordion>
</AccordionGroup>
Access Reviews  Copy Section
<AccordionGroup>
<Accordion title="Quarterly User Access Review">
Process:
1. Generate user list with assigned roles
2. Send to department managers for verification
3. Collect feedback on access appropriateness
4. Identify and remove unnecessary access
5. Document review and approvals
6. Archive for compliance
Template:

</Accordion>
<Accordion title="Role Change Review">
When user changes roles:
- [ ] Remove old role permissions
- [ ] Document date of change
- [ ] Assign new role permissions
- [ ] Verify appropriate access
- [ ] Document business justification
- [ ] Notify security team
</Accordion>
<Accordion title="Dormant Account Review">
Inactive accounts should be disabled:

</Accordion>
</AccordionGroup>
User Offboarding  Copy Section
<Warning>
Prompt offboarding is critical when employees leave. Delayed credential removal represents a significant security risk.
</Warning>
Offboarding Checklist:

Access Control Checklist  Copy Chapter
Initial Setup:
- [ ] Database roles defined for each user type
- [ ] Least privilege principle implemented
- [ ] Read-only users created for analysts
- [ ] Connection profiles configured securely
- [ ] SSL/TLS enabled for all connections
Role Management:
- [ ] RBAC hierarchy established
- [ ] Default permissions set
- [ ] New table permissions automated
- [ ] Role documentation complete
- [ ] Service accounts use least privilege
Security Policies:
- [ ] Data classified by sensitivity
- [ ] Column-level security implemented
- [ ] Row-level security configured
- [ ] IP restrictions enforced
- [ ] Audit logging enabled
Ongoing Maintenance:
- [ ] Quarterly access reviews scheduled
- [ ] Credential rotation calendar maintained
- [ ] Dormant accounts identified monthly
- [ ] Audit logs reviewed regularly
- [ ] Policy violations investigated
Incident Response:
- [ ] Breach response procedures documented
- [ ] Escalation path defined
- [ ] Audit log preservation process established
- [ ] Forensic analysis capabilities in place
- [ ] Communication template prepared
Summary  Copy Chapter
Robust access control requires careful planning, consistent implementation, and ongoing maintenance. Use database roles, dedicated credentials, network controls, and database-native audit logs to protect your data while keeping WhoDB useful for daily inspection and query workflows. Access control is not a one-time setup; review it as team members, environments, and threats change.
---
Best Practices/Ai Usage  Copy Section
---
title: AI Chat Assistant Best Practices
description: Comprehensive guidance for optimal and safe use of WhoDB's AI Chat Assistant
---
AI Chat Assistant Best Practices
The AI Chat Assistant transforms database interaction from technical SQL writing to natural conversation. This guide is the canonical reference for using the AI assistant effectively, safely, and efficiently in production environments.
<Tip>
Effective AI assistant usage combines clear communication, security awareness, and strategic provider selection
</Tip>
Understanding AI-Powered Database Interaction  Copy Chapter
The AI Chat Assistant is fundamentally different from traditional database tools. Rather than writing SQL directly, you describe what you want in natural language, and the AI generates appropriate queries based on your database schema.
How AI Assistants Work  Copy Section
<Steps>
<Step title="Schema Analysis">
The AI assistant analyzes your complete database schema, including tables, columns, data types, and relationships.
</Step>
<Step title="Natural Language Processing">
Your question is processed to understand intent, entities, conditions, and desired operations.
</Step>
<Step title="Query Generation">
Based on schema and intent, the AI generates database-specific SQL optimized for your database type.
</Step>
<Step title="Execution and Feedback">
WhoDB executes the query and presents results, which become part of conversation context.
</Step>
</Steps>
Key Differences from Traditional SQL  Copy Section
| Aspect | Traditional SQL | AI Assistant |
|--------|----------------|-------------|
| Input Method | Write exact syntax | Describe desired outcome |
| Schema Knowledge | Must memorize or reference | Automatically aware |
| Error Handling | Syntax errors require fixes | Rephrase in natural language |
| Learning Curve | Steep for beginners | Accessible immediately |
| Precision | Exact control | Interpretation required |
| Speed | Fast for experts | Fast for everyone |
Query Formulation Best Practices  Copy Chapter
Effective communication with the AI assistant follows specific patterns that produce accurate, efficient results.
Be Specific and Explicit  Copy Section
Vague questions produce unreliable results. Specificity ensures the AI understands your exact intent.
<AccordionGroup>
<Accordion title="Specify Table Names">
Good Examples:

Avoid:

When table names might be ambiguous, include schema names: test_schema.users
</Accordion>
<Accordion title="Name Columns Explicitly">
Good Examples:

Avoid:

Explicit column names help the AI generate precise SELECT statements.
</Accordion>
<Accordion title="Specify Time Ranges Clearly">
Good Examples:

Avoid:

Use specific dates or clear relative ranges (last 7 days, this month, last year).
</Accordion>
<Accordion title="Define Conditions Precisely">
Good Examples:

Avoid:

Explicitly state field names, comparison operators, and values.
</Accordion>
</AccordionGroup>
Provide Context  Copy Section
Context helps the AI understand your intent and generate more accurate queries.
Include Business Context:

Mention Expected Results:

Expected results help you quickly identify when queries return unexpected data.
Use Proper Database Terminology  Copy Section
Use terminology appropriate to your database type: tables, rows, columns, JOINs, and WHERE clauses for SQL databases; collections, documents, fields, and aggregation pipelines for MongoDB; keys, values, sets, and hashes for Redis.
SQL Example:

MongoDB Example:

Start Simple, Then Refine  Copy Section
Build complex queries through iterative refinement rather than trying to get everything perfect in one question:

The AI understands each question refines the previous one. This iterative approach is faster and more reliable than trying to construct complex queries in a single request.
Safety and Security Best Practices  Copy Chapter
Using AI assistants safely requires understanding what data is shared, potential risks, and protective measures.
Understand Data Sharing  Copy Section
Different AI providers have different data handling policies.
<Warning>
Your database schema structure and query text are sent to AI providers. However, actual data values and query results are not transmitted.
</Warning>
What Gets Sent to AI Providers:
- Your natural language questions
- Database table names and schemas
- Column names and data types
- Database type (PostgreSQL, MySQL, etc.)
- Previous conversation context
What Does NOT Get Sent:
- Actual row data from your database
- Query result contents
- Stored data values
- Connection credentials
For Maximum Privacy:
- Use local models (Ollama, LM Studio) for complete data isolation
- Avoid mentioning sensitive values in questions
- Use generic terms instead of revealing schema names
Verify Before Modifying Data  Copy Section
Always review and verify before confirming data modification operations.
<AccordionGroup>
<Accordion title="Review SQL in the Confirmation Prompt">
Before clicking Confirm on any INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operation, click Show Query and check:
- [ ] Correct table is targeted
- [ ] WHERE clause is present and accurate
- [ ] Values are correct and properly formatted
- [ ] No unintended side effects
</Accordion>
<Accordion title="Use SELECT Before DELETE or UPDATE">
Always verify which records will be affected before modifying them.
Step 1 - Verify:

Review the results carefully. Count the records. Verify these are the records you want to modify.
Step 2 - Modify:

This two-step approach prevents accidental data loss.
</Accordion>
<Accordion title="Test in Development First">
For critical operations, test the query in a development environment before running in production:
1. Connect to development database
2. Ask the AI assistant to generate the query
3. Review the generated SQL, execute, and verify results
4. Copy the verified SQL to production (via Scratchpad)
5. Execute in production during an appropriate window
</Accordion>
</AccordionGroup>
Use Read-Only Users When Possible  Copy Section
For data exploration and analysis tasks, connect with read-only database credentials.
PostgreSQL Read-Only User:

MySQL Read-Only User:

<Note>
Confirmation prompts are triggered by the SQL operation type, not by database permissions. With a read-only user you still see the confirmation for a modification request; the database rejects the statement if you confirm it.
</Note>
<Tip>
Use read-only credentials for most of your database work. Only use write credentials when actually modifying data.
</Tip>
Backup Before Bulk Operations  Copy Section
Before executing bulk modifications, ensure a recent, tested backup exists that includes all affected tables, and that the restoration procedure is documented.
Quick Backup Commands:
PostgreSQL:

MySQL:

Review Generated SQL  Copy Section
The AI assistant shows generated SQL before execution. Use this visibility to verify correctness:
- SELECT: correct tables, appropriate JOIN conditions, WHERE filters match intent, no expensive functions on large tables
- UPDATE: WHERE clause present and correct, SET values appropriate
- DELETE: WHERE clause present (unless intentionally deleting all), correct table, backup exists
- INSERT: all required columns included, values match column data types
Provider Selection Strategy  Copy Chapter
Choosing the right AI provider for each situation optimizes cost, performance, privacy, and accuracy.
When to Use Each Provider  Copy Section
Model lists are fetched live from each provider when you add it, so pick from your provider's current lineup. For current model options and rates, check your provider's pricing page.
<AccordionGroup>
<Accordion title="OpenAI (GPT Models)">
Best For:
- General-purpose queries across all database types
- Fast response requirements
- Users without local AI infrastructure
Cost Optimization:
- Use a smaller, cheaper model for simple queries
- Reserve the most capable model for complex multi-table operations
- See OpenAI's pricing page for current rates
</Accordion>
<Accordion title="Anthropic (Claude Models)">
Best For:
- Complex analytical queries
- Large database schemas (100+ tables)
- Long conversation contexts
- Sophisticated reasoning requirements
Model Selection:
Choose from the models fetched from the Anthropic API — smaller models for speed and cost, larger models for the most complex scenarios. See Anthropic's pricing page for current models and rates.
</Accordion>
<Accordion title="Gemini">
Best For:
- Google Cloud users
- General-purpose queries with competitive pricing
Model Selection:
Choose from the models fetched from the Gemini API. See Google's Gemini pricing page for current models and rates.
</Accordion>
<Accordion title="Ollama and LM Studio (Local Models)">
Best For:
- Privacy-sensitive environments and regulated industries
- Air-gapped or offline deployments
- Unlimited usage without API costs
Model Selection:
Use whatever models you have downloaded locally. Larger models are more accurate but need more RAM; smaller models respond faster on modest hardware.
Trade-offs:
- Typically slower than cloud providers
- Smaller local models may be less accurate than the top cloud models
- Complete privacy and zero ongoing costs
</Accordion>
</AccordionGroup>
Cost Optimization Strategies  Copy Section
<CardGroup cols={2}>
<Card title="Use Cheaper Models for Simple Tasks" icon="dollar-sign">
Your provider's smaller models handle simple queries well at a fraction of the cost
</Card>
<Card title="Start New Conversations" icon="rotate">
Long conversations consume more tokens. Start fresh when switching topics
</Card>
<Card title="Be Concise" icon="compress">
Shorter questions and responses reduce token usage and costs
</Card>
<Card title="Local for High Volume" icon="server">
Local models (Ollama, LM Studio) eliminate per-query costs for high-volume usage
</Card>
</CardGroup>
Per-token pricing changes frequently — compare current rates on your providers' pricing pages rather than relying on fixed estimates.
Privacy Considerations  Copy Section
Choose providers based on data sensitivity and organizational policies.
<Steps>
<Step title="Assess Data Sensitivity">
Highly Sensitive (PHI, financial records, trade secrets, PII): use local models exclusively.
Moderately Sensitive (internal business data, non-PII customer information): review provider terms of service, consider local models or ensure provider compliance.
Public or Non-Sensitive (public datasets, demonstration databases): any provider acceptable.
</Step>
<Step title="Review Organizational Policies">
Consult your information security, data governance, legal/compliance, and privacy teams. Verify alignment with data residency requirements, third-party data processing policies, and regulations such as GDPR, HIPAA, and PCI DSS.
</Step>
<Step title="Implement Provider Controls">
For external providers: document approved use cases, train users on privacy boundaries, and establish escalation procedures. For local models: document installation and configuration, maintain model versions, and plan capacity for user growth.
</Step>
</Steps>
<Warning>
Schema names and table names are sent to external AI providers. Avoid using sensitive or revealing names if privacy is critical.
</Warning>
Performance Optimization  Copy Chapter
Optimize AI assistant performance through efficient query patterns and conversation management.
Efficient Query Patterns  Copy Section
<AccordionGroup>
<Accordion title="Aggregate Before Retrieving">
Request aggregated data rather than retrieving all rows.
Efficient:

Inefficient:

Let the database perform aggregations rather than retrieving large datasets.
</Accordion>
<Accordion title="Limit Large Result Sets">
Request only necessary data for large tables.
Efficient:

Inefficient:

Use LIMIT clauses for exploratory queries on large tables.
</Accordion>
<Accordion title="Use Indexes Effectively">
Structure queries to leverage existing indexes.
Index-Friendly:

Index-Inefficient:

Ask about indexed columns: "What indexes exist on the orders table?"
</Accordion>
<Accordion title="Avoid Cartesian Products">
Ensure JOINs have proper conditions.
Efficient:

The AI generally avoids this, but verify JOIN conditions in the generated SQL.
</Accordion>
</AccordionGroup>
Managing Conversation Context  Copy Section
Long conversations accumulate context that slows response times and increases costs. Short conversations (1-10 messages) are fastest and cheapest; conversations beyond 30-50 messages become noticeably slower and more expensive.
When to Start New Conversations:
<Steps>
<Step title="Topic Changes">
When switching to a completely different database area or analysis focus, click "New Chat" and start fresh.
</Step>
<Step title="Performance Degradation">
When queries take noticeably longer to respond, save important queries to Scratchpad, then start a new chat.
</Step>
<Step title="Context Confusion">
When the AI references incorrect previous context or misunderstands follow-up questions, start a new conversation with clear, explicit questions.
</Step>
<Step title="Reaching Provider Limits">
Every model has a context limit, and quality degrades as you approach it. Limits vary widely by model — check your provider's documentation, and start fresh proactively.
</Step>
</Steps>
<CardGroup cols={2}>
<Card title="Save Before Clearing" icon="floppy-disk">
Move important queries to Scratchpad before starting a new chat
</Card>
<Card title="Use Concise Follow-Ups" icon="message">
"Show top 10" instead of "Can you please show me the top 10 results"
</Card>
</CardGroup>
Collaboration and Documentation  Copy Chapter
Preserve valuable queries and build shared knowledge with a lightweight process.
When a query is worth reusing — regular reports, complex analysis you'll repeat, or queries that revealed useful insights — hover over the result, click the ellipsis (...) button, and select Move to Scratchpad. Choose or create an appropriately named page, then add a comment explaining what the query does, when to use it, and any caveats.
Organize Scratchpad pages by purpose (reporting, analysis templates, data quality checks, maintenance) so queries are easy to find later. For team sharing, copy important SQL into your normal shared docs, runbooks, or repository.
Finally, well-documented schemas help the AI generate more accurate queries. Add database-level comments to tables and non-obvious columns:

Error Handling and Recovery  Copy Chapter
Understand common mistakes and recovery strategies.
Common Mistakes to Avoid  Copy Section
<AccordionGroup>
<Accordion title="Modifying Production Without Testing">
Mistake: Executing a bulk DELETE directly in production without verification.
Prevention:
1. Test in development database first
2. Use SELECT to verify before DELETE
3. Ensure backup exists
Recovery: If executed accidentally, restore from the most recent backup.
</Accordion>
<Accordion title="Missing WHERE Clauses">
Mistake: "Update all users to set role to admin" — no WHERE clause, so all records are affected.
Prevention:
- Always specify which records to modify
- Review the confirmation prompt carefully before confirming
- Use SELECT first to verify target records
Recovery: If a backup exists, restore the affected table. Otherwise, manually identify and correct affected records.
</Accordion>
<Accordion title="Ambiguous Questions">
Mistake: "Show me the data" — too vague, the AI must guess intent.
Better:

</Accordion>
<Accordion title="Ignoring Generated SQL">
Mistake: Clicking Confirm without reviewing the SQL in the confirmation prompt.
Prevention: Always read the generated SQL before confirming. Verify table names, WHERE clauses, and values. Cancel and rephrase if anything looks wrong.
</Accordion>
<Accordion title="Relying on Context Too Long">
Mistake: Continuing conversations for 50+ messages, assuming the AI remembers early context accurately.
Prevention: Start new conversations when switching topics, be explicit in follow-up questions, and watch for signs of context confusion.
</Accordion>
</AccordionGroup>
Troubleshooting Approach  Copy Section
When queries don't work as expected, follow a systematic troubleshooting process.
<Steps>
<Step title="Identify the Issue">
What did you ask for? What did you actually receive? What error message appeared? Does the generated SQL match your intent?
</Step>
<Step title="Review Generated SQL">
Open the result's ellipsis (...) menu and select Show Code to see the generated SQL. Check table and column names, WHERE conditions, JOIN conditions, and aggregations against your intent.
</Step>
<Step title="Simplify the Question">
If the query is complex, break it into smaller parts:

Incremental refinement is more reliable than complex single questions.
</Step>
<Step title="Verify Data and Schema">
Ensure the data you're querying actually exists and names match your schema:

Empty result sets might indicate an empty table, overly restrictive filters, a wrong table, or data in a different schema.
</Step>
<Step title="Rephrase and Retry">
If the AI misunderstood, rephrase with more explicit details:
Vague: "Show user data"
Explicit: "Show user_id, email, first_name, last_name, and created_at from the users table for all active users"
</Step>
</Steps>
Recovery Strategies  Copy Section
<CardGroup cols={2}>
<Card title="Compensating Updates" icon="rotate-left">
For incorrect UPDATE operations, run a compensating query to restore values
</Card>
<Card title="Restore from Backup" icon="database">
For significant data loss, restore affected tables from a recent backup
</Card>
<Card title="Transaction Rollback" icon="ban">
If using Scratchpad with transactions, ROLLBACK before COMMIT
</Card>
<Card title="Manual Correction" icon="pen">
For small-scale errors, manually correct affected records
</Card>
</CardGroup>
Production Environment Guidelines  Copy Chapter
Using AI assistants in production requires additional discipline and procedures.
Testing Queries  Copy Section
Never execute untested queries directly in production.
<Steps>
<Step title="Develop in Development Environment">
Connect to a development or staging database first. Generate and test queries in the non-production environment.
</Step>
<Step title="Verify Query Logic">
Execute the generated query and verify it returns expected data, performance is acceptable, and there are no unintended side effects.
</Step>
<Step title="Review for Production Impact">
Assess how many rows will be affected, whether this locks tables, and what the rollback plan is.
</Step>
<Step title="Transfer to Production">
Copy the verified SQL from Scratchpad, execute during an appropriate window, monitor execution, and verify results immediately.
</Step>
</Steps>
Change Management  Copy Section
Follow established change management procedures for data modifications.
Pre-Change Checklist:
- [ ] Change request documented and approved
- [ ] Testing completed in non-production
- [ ] Backup verified and accessible
- [ ] Rollback procedure documented
- [ ] Team members notified
During and After:
- [ ] Execute during scheduled window and monitor progress
- [ ] Verify results match expectations
- [ ] Update documentation and notify stakeholders
Audit Requirements  Copy Section
Maintain audit trails for compliance and troubleshooting.
Audit Logging in WhoDB Community Edition:
Use database-level audit logging to record executed queries. Per-query audit trails with user attribution are available in the Enterprise Edition.
Additional Audit Measures:
- Enable database-level query logging
- Review audit logs regularly and retain them per compliance requirements
- Consider regulatory obligations: GDPR (personal data access), HIPAA (PHI access), SOX (financial data), PCI DSS (cardholder data)
Learning and Improvement  Copy Chapter
The AI assistant is also an effective SQL tutor. Ask it to explain generated queries ("Why did you use LEFT JOIN instead of INNER JOIN?"), request alternative approaches ("Is there a more efficient way to write this query?"), and explore features in context ("How do window functions work in PostgreSQL?").
Build skills progressively: start with simple SELECT queries, then add WHERE conditions, aggregations, JOINs, and eventually subqueries and window functions. Study the SQL the AI generates — its JOIN structure, aliasing, and date-filtering patterns — rather than using queries blindly. Comparing AI-generated SQL against queries you write manually in Scratchpad is a fast way to discover techniques you might not have considered.
Next Steps  Copy Chapter
<CardGroup cols={2}>
<Card title="AI Introduction" icon="brain" href="/ai/introduction">
Review AI Chat Assistant capabilities and features
</Card>
<Card title="Setup Providers" icon="gear" href="/ai/setup-providers">
Configure OpenAI, Anthropic, Gemini, Ollama, or LM Studio for your needs
</Card>
<Card title="Querying Data" icon="magnifying-glass" href="/ai/querying-data">
Learn effective techniques for data retrieval
</Card>
<Card title="Modifying Data" icon="pen-to-square" href="/ai/modifying-data">
Understand safe data modification with AI assistance
</Card>
<Card title="Conversation Features" icon="comments" href="/ai/conversation-features">
Master multi-turn conversations and context management
</Card>
<Card title="Security Best Practices" icon="shield" href="/best-practices/security">
Comprehensive security practices for database management
</Card>
</CardGroup>
<Check>
Combine AI assistance with human judgment for optimal database management—the AI generates queries efficiently, and you verify they're correct before execution
</Check>
---
Best Practices/Collaboration  Copy Section
---
title: Team Collaboration
description: Practical patterns for using WhoDB as a team - shared deployments, per-user database accounts, version-controlled queries, and data exports
---
Team Collaboration
In WhoDB, collaboration happens through a shared deployment, database-side access control, version control for SQL, and exports. This guide covers the patterns that work.
Run One Shared Deployment  Copy Chapter
Deploy a single WhoDB instance for the team instead of everyone running their own copy. Everyone gets the same version, the same connection profiles, and one place to secure and monitor.
Define the team's connections as environment-defined profiles on the deployment so they appear on the login page for everyone:

This keeps connection details in deployment config (one source of truth) rather than in chat messages and wikis. See the Team Setup Guide for full deployment instructions and Database Connectivity for the profile format.
Use Per-User Database Accounts  Copy Chapter
Access control in WhoDB is the database's access control. Give each person their own database account scoped to what they actually need, rather than sharing one credential:

Why per-user accounts matter for collaboration:
- Least privilege: analysts get SELECT, developers get read-write on dev, only operators get production write access
- Attribution: database-native audit and statement logs show who ran what
- Clean offboarding: disable one account instead of rotating a shared password
See Access Control for detailed role setups.
Share Queries Through Version Control  Copy Chapter
WhoDB's Scratchpad state is stored per-browser — pages and cells are not visible to teammates. Use Scratchpad to build and test queries, then share the SQL itself through your normal channels:
1. Develop the query in Scratchpad against a development or staging database
2. Document it with a short header comment (purpose, dependencies, expected runtime)
3. Review it in a pull request like any other code — especially UPDATE/DELETE statements and anything touching production
4. Store it in a shared repository:

Anyone who needs the query pastes it back into their own Scratchpad. Cell history in Scratchpad helps you recover your own recent SQL, but the repository is the team's memory.
<Tip>
Treat destructive queries like code changes: peer review before they run against production, and keep the reviewed version in the repository so the next person doesn't rewrite it from scratch.
</Tip>
Share Data Through Exports  Copy Chapter
For sharing results rather than queries, use WhoDB's export options:
- CSV or Excel from the data grid or query results — attach to tickets, drop into Slack, or feed into spreadsheets
- JSON Lines for records from sources like MongoDB, where supported
- Apply filters before exporting so the snapshot contains only what the discussion needs
When you share an export, include the SQL that produced it and when it was run — a CSV without provenance goes stale silently.
Collaboration Checklist  Copy Chapter
- [ ] One shared WhoDB deployment, secured per the Team Setup Guide
- [ ] Connections defined as environment profiles, secrets in a password manager
- [ ] Per-user database accounts with least-privilege grants
- [ ] Team query repository in version control, with review for destructive SQL
- [ ] Exports shared with the query and timestamp that produced them
- [ ] Accounts disabled and shared credentials rotated when someone leaves
Summary  Copy Chapter
Use WhoDB for inspection, querying, and repeatable exports; use database accounts for access control; and use your normal source control and review practices for long-lived team knowledge. The Scratchpad is each person's workbench — version control is the team's shared surface.
---
Best Practices/Data Management  Copy Section
---
title: Data Management Best Practices
description: Safe and effective data management techniques for WhoDB users
---
Data Management Best Practices
Effective data management balances operational efficiency with data safety. This guide covers essential practices for managing data safely and effectively using WhoDB, from routine operations to complex data transformations.
Data Safety Principles  Copy Chapter
Always Backup Before Changes  Copy Section
The most important rule of data management is simple: always have a backup before making changes.
Types of Changes Requiring Backups:
- Bulk updates or deletes
- Schema modifications
- Data migrations
- Testing new queries on production data
- Running unfamiliar scripts
- Major application updates
Backup Strategies:
- Full database backup for major changes
- Table-level backup for isolated changes
- Row-level backup for small, targeted changes
- Transaction savepoints for multi-step operations
Creating Backups:
PostgreSQL:

MySQL:

MongoDB:

Verify Backups  Copy Section
Backups are only useful if they can be restored successfully.
Backup Verification Process:
1. Create test database or schema
2. Restore backup to test location
3. Verify data integrity
4. Test critical queries
5. Document verification date
6. Automate verification where possible
Regular Testing Schedule:
- Test restore procedures monthly
- Verify backup completeness
- Measure restoration time
- Update recovery documentation
- Train team members on restoration
Use Transactions Appropriately  Copy Section
Transactions ensure data consistency by treating multiple operations as a single unit of work.
Transaction Basics:

When to Use Transactions:
- Multiple related updates
- Data migrations
- Batch operations
- Testing complex queries
- Any operation that must be atomic
Transaction Best Practices:
- Keep transactions short
- Avoid user interaction during transactions
- Don't hold transactions during long operations
- Use appropriate isolation levels
- Monitor for deadlocks
Safe Data Modification  Copy Chapter
Test Queries Before Execution  Copy Section
Always test data modification queries before running them on production data.
Safe Testing Workflow:
1. Select Before Update/Delete:

2. Use Transactions for Testing:

3. Test on Subset First:

Use WHERE Clauses Carefully  Copy Section
Missing or incorrect WHERE clauses cause some of the most devastating data loss incidents.
Dangerous Patterns:

Safety Measures:
- Always write WHERE clause first
- Use SELECT to verify WHERE logic
- Double-check column names and values
- Use transactions for reversibility
- Limit rows affected during testing
Implement Row-Level Verification  Copy Section
For critical updates, verify each affected row.
Verification Query Pattern:

Bulk Operations  Copy Chapter
Planning Bulk Operations  Copy Section
Bulk operations require careful planning to avoid impacting system performance.
Pre-Operation Checklist:
- [ ] Backup created and verified
- [ ] Operation tested on subset
- [ ] Maintenance window scheduled
- [ ] Rollback plan documented
- [ ] Monitoring in place
- [ ] Stakeholders notified
- [ ] Resource requirements assessed
Batch Processing  Copy Section
Process large datasets in batches to avoid locking tables and consuming excessive resources.
Batch Update Pattern:

Benefits of Batch Processing:
- Reduces lock contention
- Allows concurrent operations
- Easier to monitor progress
- Can be paused and resumed
- Lower memory usage
Handling Large Deletes  Copy Section
Large delete operations can cause performance issues and transaction log growth.
Incremental Delete Strategy:

Truncate for Full Table Deletion:

Data Validation  Copy Chapter
Input Validation  Copy Section
Validate data before insertion or update to maintain data quality.
Validation Checks:
Data Type Validation:

Format Validation:

Business Rule Validation:

Constraint Management  Copy Section
Use database constraints to enforce data integrity automatically.
Essential Constraints:
Primary Keys:

Foreign Keys:

Unique Constraints:

Check Constraints:

Not Null Constraints:

Data Quality Monitoring  Copy Section
Continuously monitor data quality to detect issues early.
Quality Metrics:
- Null value percentages
- Duplicate record counts
- Constraint violation attempts
- Data distribution anomalies
- Referential integrity breaks
Quality Monitoring Queries:

Data Migration  Copy Chapter
Planning Migrations  Copy Section
Data migrations require thorough planning and testing.
Migration Planning Checklist:
- [ ] Source and target schemas documented
- [ ] Data transformation logic defined
- [ ] Data volume and duration estimated
- [ ] Dependencies identified
- [ ] Testing strategy created
- [ ] Rollback procedure documented
- [ ] Validation queries prepared
Migration Testing  Copy Section
Test migrations in non-production environment before running in production.
Testing Phases:
1. Unit Testing:
   - Test individual transformation functions
   - Verify edge cases
   - Validate error handling
2. Integration Testing:
   - Test complete migration process
   - Verify referential integrity
   - Check constraint compliance
3. Performance Testing:
   - Measure migration duration
   - Assess system impact
   - Optimize batch sizes
4. Data Validation:
   - Compare row counts
   - Verify data accuracy
   - Check completeness
Validation Query Examples:

Rollback Procedures  Copy Section
Every migration needs a documented rollback procedure.
Rollback Strategy:
1. Keep original data until migration validated
2. Document reverse transformation logic
3. Test rollback procedure
4. Define rollback decision criteria
5. Assign rollback authority
Example Rollback Process:

Data Archival  Copy Chapter
Archival Strategy  Copy Section
Archive old data to maintain system performance while preserving historical information.
When to Archive:
- Data no longer actively used
- Regulatory retention requirements met
- Table size impacting performance
- Historical reference needed
Archival Approaches:
Separate Archive Tables:

Partitioning:

Archive Storage  Copy Section
Choose appropriate storage for archived data.
Storage Options:
- Separate database for archives
- Compressed backup files
- Cloud object storage (S3, Azure Blob)
- Tape backup for long-term storage
Archive Access:
- Read-only access when needed
- Separate connection credentials
- Lower priority query execution
- Documented retrieval process
Data Safety Checklist  Copy Chapter
Before executing any data modification operation:
Pre-Operation:
- [ ] Backup created and verified
- [ ] Query tested with SELECT
- [ ] WHERE clause verified
- [ ] Transaction started (if appropriate)
- [ ] Row count estimated
- [ ] Operation documented
During Operation:
- [ ] Progress monitored
- [ ] Performance impact assessed
- [ ] Errors logged
- [ ] Can pause if needed
Post-Operation:
- [ ] Changes verified
- [ ] Transaction committed
- [ ] Documentation updated
- [ ] Stakeholders notified
- [ ] Backup retained until validated
Rollback Ready:
- [ ] Rollback procedure documented
- [ ] Rollback tested (if critical)
- [ ] Rollback authority designated
- [ ] Decision criteria defined
Summary  Copy Chapter
Safe data management requires discipline, planning, and robust procedures. Always backup before changes, test operations thoroughly, use transactions appropriately, and validate results carefully. By following these best practices, you can confidently manage data using WhoDB while minimizing risk of data loss or corruption. Remember that the time invested in proper planning and testing is always less than the time required to recover from data disasters.
---
Best Practices/Documentation  Copy Section
---
title: Database Documentation
description: Practical ways to document your databases using WhoDB - database comments, schema diagram exports, and version-controlled SQL
---
Database Documentation
Good database documentation lives as close to the data as possible. This guide covers the documentation practices that work well alongside WhoDB: database-side comments, exported schema diagrams, and version-controlled SQL.
Use Database Comments  Copy Chapter
The most durable place to document a table or column is the database itself. Comments stored in the catalog travel with the schema, survive tool changes, and are always one query away — run them from WhoDB's Scratchpad whenever you need to read or update them:

What to capture in comments:
- Business purpose of the table and who owns it
- Valid values and defaults for enum-like columns
- Known data quality issues ("nulls possible in pre-2016 rows")
- Sensitivity flags for PII columns
<Tip>
Keep comments short and factual. Longer context — retention policies, runbooks, incident history — belongs in your team's regular documentation system, linked from a short pointer in the comment if needed.
</Tip>
Export Schema Diagrams from the Graph View  Copy Chapter
WhoDB's graph view renders your schema as an interactive diagram of tables and foreign-key relationships. Use the download/export action in the lower-right graph controls to save the diagram as an image for your documentation.
Good uses for exported diagrams:
- Architecture and onboarding docs — a current picture of the schema beats prose
- Design review artifacts before and after a migration
- Spotting undocumented or missing relationships while you write schema docs
Re-export after schema changes so diagrams in your docs don't drift from reality.
Share SQL Through Version Control  Copy Chapter
WhoDB's Scratchpad is where you build and refine queries, but Scratchpad pages are stored per-browser — they are not shared between team members or machines. Treat Scratchpad as a workbench, and store the finished SQL in version control.
A simple repository layout works well:

Give each committed query a short header so the next reader has context:

The workflow:
1. Build and test the query in Scratchpad against a development database
2. Copy the final SQL into your repository with a header comment
3. Review it like any other code change (pull request)
4. Paste it back into Scratchpad whenever someone needs to run it
Document Connections with Environment-Defined Profiles  Copy Chapter
Connection details are documentation too. Instead of a wiki page of hosts and ports that goes stale, define connection profiles as environment variables on your WhoDB deployment — they appear on the login page for everyone, and the deployment config becomes the single source of truth:

See Database Connectivity for the full profile format, and keep the actual secrets in your secret manager rather than committed files.
Keep It Current  Copy Chapter
<Warning>
Outdated documentation is worse than no documentation. Update comments and re-export diagrams as part of the same change that alters the schema, not as a separate cleanup task.
</Warning>
A lightweight cadence is enough:
- With every schema migration: update affected table/column comments
- After notable schema changes: re-export the graph diagram used in docs
- Quarterly: prune queries in the repository that are no longer used
Summary  Copy Chapter
Documentation that lives in the database (comments), in version control (SQL files), and in exported diagrams stays useful because it sits in the same workflow as the changes themselves. WhoDB produces the diagrams from the graph view and gives you the Scratchpad to develop and read the SQL you commit.
---
Best Practices/Mongodb  Copy Section
---
title: "Using WhoDB with MongoDB"
description: "How to connect, browse, and query MongoDB databases in WhoDB"
---
Using WhoDB with MongoDB
This page covers what is specific to MongoDB when you use it through WhoDB: connecting, browsing collections, and running shell-style commands in the Scratchpad.
Connecting  Copy Chapter
Select MongoDB on the login page. The form asks for:
- Hostname (required) — you can also paste a full mongodb:// or mongodb+srv:// connection URL and WhoDB parses it into the fields.
- Username, Password, and Database (optional, depending on your server's auth setup).
Advanced options include:
- Port — defaults to 27017.
- URL Params — extra connection-string parameters such as ?authSource=admin.
- DNS Enabled (false) — set to true for SRV (mongodb+srv) connections such as MongoDB Atlas.
- SSL mode — Disabled, Enabled (TLS with certificate verification), or Insecure (TLS without certificate verification).
<Tip>
For production databases, connect with a read-only user. WhoDB edits documents directly from the grid, so browsing with a read-only user prevents accidental changes.
</Tip>
How WhoDB Presents MongoDB  Copy Chapter
- Collections are the storage units, scoped to the selected database; system.* collections are hidden. Views appear read-only, and indexes are listed as metadata.
- Documents can be browsed as a grid or as JSON, and edited, inserted, or deleted from the UI.
- Because documents are schema-less, field and type information is sampled from existing documents rather than read from a fixed schema.
WhoDB Features with MongoDB  Copy Chapter
- Scratchpad — runs shell-style commands like db.users.find({age: {$gt: 30}}). Supported methods: find, findOne, insertOne, insertMany, updateOne, updateMany, deleteOne, deleteMany, countDocuments, aggregate, distinct, createIndex, drop, and db.dropDatabase(). find results are capped at 1,000 documents, and aggregation pipelines may not use $where, $function, $accumulator, $out, or $merge. See Scratchpad.
- Where conditions — the filter builder offers MongoDB query operators such as eq, ne, gt, lt, in, exists, regex, and elemMatch. See Where Conditions.
- Graph view — relationships between collections are inferred from naming conventions (for example, a user_id field pointing at users), since MongoDB has no declared foreign keys. See Graph View.
- Mock data generation — generates documents for collections. See Mock Data.
- Export — CSV, Excel, and additionally NDJSON (JSON Lines), which preserves nested document structure. See Export Options.
Tips  Copy Chapter
- Prefer NDJSON export when documents contain nested objects or arrays; flat CSV cells stringify them.
- Use a filter in find or the where-condition builder before browsing very large collections.
- Sampled field lists may miss fields that only appear in rare documents — check the JSON view of individual documents when in doubt.
Compatible Databases  Copy Chapter
FerretDB and Amazon DocumentDB speak the MongoDB wire protocol and appear as their own entries on the login page, with the same collection-based experience in WhoDB.
Further Reading  Copy Chapter
For index design, replica sets, sharding, and backup guidance, see the official MongoDB documentation.
Related Pages  Copy Chapter
<CardGroup cols={2}>
<Card title="Database Connectivity" icon="plug" href="/features/database-connectivity">
Connection details for all supported databases.
</Card>
<Card title="Export Options" icon="download" href="/advanced/export-options">
CSV, Excel, and NDJSON export.
</Card>
</CardGroup>
---
Best Practices/Mysql  Copy Section
---
title: "Using WhoDB with MySQL"
description: "How to connect, browse, and query MySQL and MariaDB databases in WhoDB"
---
Using WhoDB with MySQL
This page covers what is specific to MySQL and MariaDB when you use them through WhoDB: connecting, database switching, and which WhoDB features apply.
Connecting  Copy Chapter
Select MySQL or MariaDB on the login page. The form asks for:
- Hostname, Username, Password, and Database (all required).
Advanced options include:
- Port — defaults to 3306.
- Parse Time (True) and Loc (UTC) — control how the driver handles date/time values.
- Allow clear text passwords (0).
- SSL mode — DISABLED, PREFERRED, REQUIRED, VERIFY_CA, or VERIFY_IDENTITY.
<Tip>
For production databases, connect with a read-only account. WhoDB edits rows directly from the grid, so browsing with a read-only user prevents accidental changes.
</Tip>
How WhoDB Presents MySQL  Copy Chapter
- MySQL has no schema layer inside a database, so instead of a schema dropdown the sidebar offers database switching: pick a different database on the same server without logging in again. System databases (information_schema, mysql, performance_schema, sys) are hidden.
- Tables and views appear as storage units. Tables support row viewing, inserts, updates, deletes, and data import; views are read-only.
- Column types, primary keys, and foreign keys are read from the catalog, so the grid and graph reflect your actual schema.
WhoDB Features with MySQL  Copy Chapter
- Scratchpad — run MySQL SQL, including multiple statements in one cell. See Scratchpad.
- Graph view — visualizes foreign-key relationships between tables in the current database. See Graph View.
- Where conditions — the filter builder offers MySQL operators such as =, LIKE, BETWEEN, IN, and IS NULL. See Where Conditions.
- Mock data generation — generates rows for tables and follows foreign-key dependencies so parent rows are created first. See Mock Data.
- Export — CSV and Excel from the grid or Scratchpad results. See Export Options.
Tips  Copy Chapter
- Add a LIMIT to exploratory Scratchpad queries on large tables; unbounded SELECT statements pull every row.
- Foreign keys drive both the graph view and mock data ordering — tables without declared foreign keys show no relationships.
- Filter with the where-condition builder before exporting so the file contains only the rows you need.
Compatible Databases  Copy Chapter
TiDB is MySQL-compatible and appears as its own entry on the login page (default port 4000) with the same MySQL-style experience in WhoDB.
Further Reading  Copy Chapter
For server tuning, storage engines, replication, and backup guidance, see the official MySQL documentation or the MariaDB documentation.
Related Pages  Copy Chapter
<CardGroup cols={2}>
<Card title="Database Connectivity" icon="plug" href="/features/database-connectivity">
Connection details for all supported databases.
</Card>
<Card title="Scratchpad" icon="terminal" href="/query/scratchpad-intro">
Write and run SQL against your connection.
</Card>
</CardGroup>
---
Best Practices/Performance  Copy Section
---
title: Performance Optimization
description: Techniques for optimizing database query performance in WhoDB
---
Performance Optimization
Database performance directly impacts application responsiveness and user experience. This guide covers proven techniques for optimizing query performance and managing database resources effectively in WhoDB.
Understanding Query Performance  Copy Chapter
Query Execution Fundamentals  Copy Section
Before optimizing queries, understand how databases execute them:
Query Processing Stages:
1. Parsing: SQL syntax validation and query tree construction
2. Planning: Query optimizer determines execution strategy
3. Optimization: Query plan refinement based on statistics
4. Execution: Actual data retrieval and processing
5. Result Return: Data formatting and transmission
Performance Factors:
- Table size and row count
- Index availability and quality
- Data distribution and statistics
- Query complexity and joins
- Hardware resources (CPU, memory, I/O)
- Concurrent user load
Performance Metrics  Copy Section
Track these key metrics to identify performance issues:
Query-Level Metrics:
- Execution time (total and breakdown)
- Rows examined vs. rows returned
- Index usage
- Temporary table creation
- Sort operations
System-Level Metrics:
- Query throughput (queries per second)
- Connection pool utilization
- Cache hit ratios
- I/O wait times
- CPU and memory usage
Query Optimization Strategies  Copy Chapter
Use EXPLAIN to Analyze Queries  Copy Section
The EXPLAIN command reveals how the database executes your query. WhoDB makes it easy to analyze query plans.
PostgreSQL EXPLAIN:

Key Information to Review:
- Seq Scan: Full table scan (potentially slow)
- Index Scan: Using an index (generally fast)
- Nested Loop: Join method for small result sets
- Hash Join: Join method for larger result sets
- Sort: Explicit sorting operation
- Cost: Estimated query cost
- Actual Time: Real execution time
MySQL EXPLAIN:

Warning Signs:
- Type: ALL (full table scan)
- Extra: Using filesort (disk-based sorting)
- Extra: Using temporary (temporary table creation)
- Rows: Large numbers indicating inefficiency
Optimize WHERE Clauses  Copy Section
WHERE clause optimization is fundamental to query performance.
Use Indexed Columns:

Avoid Leading Wildcards:

Use Appropriate Data Types:

Optimize JOIN Operations  Copy Section
Joins are common performance bottlenecks in complex queries.
Join Order Matters:
Join smaller tables first to reduce intermediate result sizes:

Use Appropriate Join Types:
- INNER JOIN: Only matching rows (most efficient)
- LEFT JOIN: All left table rows (use when necessary)
- RIGHT JOIN: All right table rows (consider reversing to LEFT)
- FULL OUTER JOIN: All rows from both tables (least efficient)
Index Foreign Keys:
Always create indexes on foreign key columns:

Limit Result Sets  Copy Section
Retrieving unnecessary data wastes resources.
Use LIMIT for Large Tables:

Select Specific Columns:

Use Pagination:
For displaying data in pages, use offset and limit:

Note: For large offsets, consider keyset pagination for better performance.
Indexing Strategies  Copy Chapter
Index Basics  Copy Section
Indexes dramatically improve query performance but require careful planning.
When to Create Indexes:
- Columns frequently used in WHERE clauses
- Columns used in JOIN conditions
- Columns used in ORDER BY clauses
- Columns used in GROUP BY clauses
- Foreign key columns
Index Types:
- B-tree: Default, good for equality and range queries
- Hash: Fast equality comparisons, no range support
- GIN/GiST: Full-text search and array operations
- Bitmap: Multiple index combination
Composite Indexes  Copy Section
Composite indexes cover multiple columns and are powerful optimization tools.
Column Order Matters:

General Rule:
Place the most selective (filters to fewer rows) column first, and columns used together frequently.
Covering Indexes  Copy Section
Covering indexes include all columns needed for a query, eliminating table lookups.
Example:

Index Maintenance  Copy Section
Indexes require maintenance to remain effective.
PostgreSQL Index Maintenance:

MySQL Index Maintenance:

When to Rebuild:
- After large bulk operations
- When query performance degrades
- Regular maintenance schedule (monthly/quarterly)
- After significant data distribution changes
Managing Large Result Sets  Copy Chapter
Cursor-Based Pagination  Copy Section
For large result sets, cursor-based pagination is more efficient than offset-based.
Keyset Pagination:

This approach maintains consistent performance regardless of page depth.
Streaming Results  Copy Section
For very large exports, stream results instead of loading all into memory:
Batch Processing:

Process each batch, update $last_processed_id, and repeat.
Aggregation Optimization  Copy Section
Optimize aggregation queries for large datasets.
Use Materialized Views:

Use Incremental Aggregation:
Instead of recalculating everything, maintain running totals:

Connection Pooling  Copy Chapter
Understanding Connection Pools  Copy Section
Database connections are expensive to create. Connection pooling reuses connections for better performance.
Benefits:
- Reduced connection overhead
- Better resource utilization
- Improved scalability
- Consistent performance under load
Connection Pool Configuration  Copy Section
Key Parameters:
- Minimum Pool Size: Connections kept open during idle periods
- Maximum Pool Size: Upper limit on concurrent connections
- Connection Timeout: Maximum wait time for available connection
- Idle Timeout: Time before idle connection is closed
- Max Lifetime: Maximum connection age before refresh
Recommended Starting Values:

Adjust based on:
- Application concurrent user count
- Query execution time
- Database server capacity
- Network latency
Connection Pool Monitoring  Copy Section
Monitor these metrics:
- Active connections
- Idle connections
- Connection wait times
- Connection creation rate
- Connection errors
Warning Signs:
- Frequent connection timeouts
- High connection wait times
- Maximum pool size consistently reached
- Many short-lived connections
Query Caching  Copy Chapter
Application-Level Caching  Copy Section
Cache query results at the application level for frequently accessed data.
Good Candidates for Caching:
- Reference data (countries, categories)
- User session data
- Dashboard aggregations
- Recently viewed items
Cache Invalidation Strategies:
- Time-based expiration (TTL)
- Event-based invalidation
- Versioned cache keys
- Least Recently Used (LRU) eviction
Database Query Cache  Copy Section
Some databases provide built-in query caching.
MySQL Query Cache (Deprecated in 8.0):
Modern MySQL versions don't include query cache. Use application-level caching instead.
PostgreSQL Shared Buffers:
PostgreSQL caches frequently accessed data pages in shared buffers. Configure appropriately: