Database Query Interface Overview
Purpose
The Database Query Interface module provides developers with a centralized, secure dashboard tailored for executing read-only SQL queries against relational databases or Firestore documents. This tool empowers developers to interact with their data sources without requiring direct database access, streamlining the querying process and enhancing security by limiting operations to read-only actions.
Key Benefits
- Streamlined Query Execution: Offers an intuitive interface for submitting SQL or Firestore queries, eliminating the need for command-line tools or direct database connections.
- Real-Time Results: Delivers instant query results in a user-friendly format, aiding in quick data analysis and troubleshooting.
- Cross-Platform Compatibility: Supports multiple database systems (MySQL, PostgreSQL, etc.) and integrates seamlessly with Firestore, providing versatile querying capabilities.
- Enhanced Collaboration: Enables sharing of queries and results among team members through export features, fostering teamwork and knowledge sharing.
- Audit Logging: Tracks query history for compliance and debugging purposes, offering transparency and accountability.
- Improved Security: Restricts access to read-only operations, minimizing the risk of accidental data alteration or loss.
Usage Scenarios
- Ad-Hoc Data Exploration: Quickly test hypotheses or gather insights by running impromptu queries without direct database access.
- Query Debugging: Identify and resolve issues in SQL statements or Firestore document retrievals efficiently.
- Performance Monitoring: Analyze query performance metrics to optimize database efficiency and troubleshoot bottlenecks.
- Data Analysis: Extract and analyze data for business intelligence, market trends, or user behavior studies.
- Backup and Reporting: Generate comprehensive reports or export data for backups, ensuring data integrity and availability.
- Cross-Tool Integration: Facilitate integration with ETL processes or other tools by exporting query results in various formats.
This module is an essential tool for developers seeking to manage and analyze their database environments efficiently and securely.
Database Query Interface Module Documentation
1. Role-Based Access Control (RBAC)
- Description: Ensures only authorized admins can access the dashboard, preventing unauthorized data exposure.
2. SQL & Firestore Query Support
- Description: Enables execution of read-only SQL queries against relational databases and Firestore queries for NoSQL operations.
3. Query Editor with Syntax Highlighting
- Description: Provides a user-friendly interface for writing queries with syntax highlighting to enhance readability and reduce errors.
4. Real-Time Query Execution
- Description: Allows immediate execution of queries, returning results in real-time for quick feedback and debugging.
5. Result Pagination
- Description: Manages large datasets by displaying results in pages, improving performance and usability.
6. Query History & Export
- Description: Logs past queries and allows exporting results as CSV or Excel files for offline analysis.
7. Data Visualization
- Description: Converts query results into visual charts and graphs, aiding in data interpretation and decision-making.
8. Query Scheduling
- Description: Enables scheduling of queries to run at specific times, useful for automated reporting and monitoring.
9. Request Validation & Sanitization
- Description: Prevents SQL injection attacks by validating and sanitizing inputs before execution.
10. Result Masking & Redaction
- Description: Obscures sensitive data in query results, ensuring compliance with security policies.
11. Audit Logging
- Description: Tracks query executions with detailed logs, including user ID, timestamp, and query details for accountability.
12. API Integration
- Description: Offers programmatic access to query execution via APIs, enabling automation and integration with other tools.
13. Query Cache
- Description: Stores frequently executed queries to improve performance by returning cached results when possible.
This documentation provides a comprehensive overview of the Database Query Interface module, highlighting its features for developers and admins.
### FastAPI Endpoint
This endpoint handles both SQL and Firestore queries securely.
```python
from fastapi import APIRouter, Depends, HTTPException
from typing import Optional
from pydantic import BaseModel
import firebase_admin.firestore as firestore
router = APIRouter(prefix="/admin/query", tags=["query"])
class QueryRequest(BaseModel):
query_type: str # "sql" or "firestore"
query_str: str
admin_token: Optional[str] = None
class SQLResponse(BaseModel):
columns: list
rows: list
class FirestoreResponse(BaseModel):
documents: list
@router.post("/", dependencies=[Depends(admin_auth)])
async def execute_query(query_request: QueryRequest) -> dict:
try:
if query_request.admin_token != "admin_token":
raise HTTPException(status_code=403, detail="Unauthorized")
db_type = query_request.query_type
query = query_request.query_str
if db_type == "sql":
# Execute SQL query
result = await execute_sql_query(query)
return {"status": "success", "sql_data": result}
elif db_type == "firestore":
# Fetch Firestore documents
result = await fetch_firestore_documents(query)
return {"status": "success", "firestore_data": result}
else:
raise HTTPException(status_code=400, detail="Invalid query type")
except Exception as e:
print(f"Error executing query: {e}")
raise HTTPException(status_code=500, detail=str(e))
React UI Snippet
A simple dashboard for executing queries.
import React, { useState } from "react";
const QueryDashboard = () => {
const [query, setQuery] = useState("");
const [dbType, setDbType] = useState("sql");
const [results, setResults] = useState([]);
const [error, setError] = useState("");
const handleSubmit = async (e) => {
e.preventDefault();
try {
const response = await fetch("/admin/query/", {
method: "POST",
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify({
query_type: dbType,
query_str: query,
admin_token: "admin_token"
})
});
if (!response.ok) {
throw new Error("Query failed");
}
const data = await response.json();
setResults(data.result);
setError("");
} catch (err) {
setError(err.message);
setResults([]);
}
};
return (
<div>
<h1>Database Query Interface</h1>
<form onSubmit={handleSubmit}>
<input
type="text"
value={query}
onChange={(e) => setQuery(e.target.value)}
placeholder="Enter your query here..."
/>
<select value={dbType} onChange={(e) => setDbType(e.target.value)}>
<option value="sql">SQL Query</option>
<option value="firestore">Firestore Query</option>
</select>
<button type="submit">Execute Query</button>
</form>
{error && <p style={{ color: "red" }}>{error}</p>}
{results.length > 0 && (
<div>
<h3>Results:</h3>
<pre>{JSON.stringify(results, null, 2)}</pre>
</div>
)}
</div>
);
};
export default QueryDashboard;
Data Schema (Pydantic)
Define response models for query results.
from pydantic import BaseModel
class SQLData(BaseModel):
columns: list[str]
rows: list[list]
class FirestoreDocument(BaseModel):
id: str
fields: dict
class FirestoreData(BaseModel):
documents: list[FirestoreDocument]
class QueryResponse(BaseModel):
status: str
message: Optional[str] = None
sql_data: Optional[SQLData] = None
firestore_data: Optional[FirestoreData] = None
This documentation provides a comprehensive overview of the Database Query Interface module, including code examples for FastAPI, React, and Pydantic. It’s designed to help developers understand how to interact with the system securely and efficiently.
```markdown
# Database Query Interface Module Documentation
## Summary
The **Database Query Interface** is an admin-only dashboard designed for executing read-only SQL or Firestore queries. This module provides developers with a centralized interface to query, analyze, and visualize data from their database systems.
---
## Related Modules
- **Query Builder**: A module that helps construct complex SQL or Firestore queries without direct code access.
- **Data Viewer**: A tool for visualizing and exploring query results in various formats (tables, charts).
- **Activity Log**: Tracks all executed queries for auditing and monitoring purposes.
- **Backup Module**: Manages database backups and recovery operations.
---
## Use Cases
1. **Execute Ad-Hoc Queries**: Developers can run one-time SQL or Firestore queries to retrieve specific data insights.
2. **Query Firestore Collections**: Access and analyze data stored in Firestore collections directly from the dashboard.
3. **Performance Testing**: Test query performance without impacting production databases.
4. **Data Analysis**: Use read-only access to analyze historical data trends and patterns.
---
## Integration Tips
- **API Integration**: The module provides RESTful APIs for programmatic query execution and result retrieval.
- **Security**: Integrate with existing authentication mechanisms (e.g., OAuth, IAM) to ensure admin-only access.
- **Database Compatibility**: Ensure compatibility with your database systems (SQL or Firestore) by configuring connection details.
---
## Configuration Options
Below is a table of configuration options for the Database Query Interface module:
| **Option** | **Description** | **Data Type** | **Default Value** |
|---------------------------|---------------------------------------------------------------------------------|---------------|-------------------|
| `enable_sql_queries` | Enables or disables SQL query execution. | Boolean | true |
| `enable_firestore_access` | Enables or disables access to Firestore collections. | Boolean | false |
| `allowed_ip_ranges` | IP ranges allowed to access the dashboard (in CIDR notation). | String Array | [] |
| `query_timeout` | Maximum time allowed for query execution in seconds. | Integer | 30 |
| `enable_query_caching` | Enables caching of frequently executed queries to improve performance. | Boolean | false |
---
## Conclusion
The Database Query Interface module is a powerful tool for developers to execute and analyze read-only database queries. By integrating with related modules and configuring settings appropriately, this interface can be tailored to meet specific organizational needs.