How to Integrate Retool with PyCharm

Open PyCharm and create a new Python project (File → New Project). Choose your preferred framework — FastAPI for modern async APIs or Flask for simpler synchronous services. For a FastAPI project, open the integrated terminal (View → Tool Windows → Terminal) and install dependencies: pip install fastapi uvicorn pydantic. Create a file named main.py with your API service. Design your endpoints with Retool in mind — return flat JSON arrays for Table data, nested objects for detail views, and include pagination metadata (total, page, per_page) for large datasets. Retool binds to {{ query.data }} directly, so your JSON structure determines how easy the Retool wiring is. For a data endpoint that will power a Retool Table, return a JSON object with a 'results' array at the top level: Enable CORS in your Python service for local development (Retool Cloud proxies requests server-side, so CORS isn't needed in production, but may be helpful for local testing with Retool's development environment). Run the service in PyCharm using the Run button (Shift+F10) or the terminal: uvicorn main:app --reload for FastAPI. Your service will be available at http://localhost:8000. For Flask, the equivalent is: flask run (with FLASK_APP=main.py set) or python main.py. The service runs at http://localhost:5000.

1# FastAPI service example — designed for Retool consumption
2from fastapi import FastAPI, Query
3from fastapi.middleware.cors import CORSMiddleware
4from typing import Optional
5import pandas as pd
6from datetime import datetime
7
8app = FastAPI(title="Retool Data Service")
9
10# CORS for local development (Retool proxies in production)
11app.add_middleware(
12    CORSMiddleware,
13    allow_origins=["*"],
14    allow_methods=["*"],
15    allow_headers=["*"],
16)
17
18@app.get("/api/sales-summary")
19async def get_sales_summary(
20    start_date: str = Query(default="2024-01-01"),
21    end_date: str = Query(default="2024-12-31"),
22    region: Optional[str] = Query(default=None)
23):
24    # Example: load from database or file
25    # Replace with your actual data source
26    df = pd.read_sql("SELECT * FROM sales WHERE date >= %s AND date <= %s",
27                     con=get_db_connection(),
28                     params=[start_date, end_date])
29
30    if region:
31        df = df[df['region'] == region]
32
33    summary = df.groupby('product').agg(
34        total_revenue=('amount', 'sum'),
35        order_count=('order_id', 'count'),
36        avg_order=('amount', 'mean')
37    ).reset_index()
38
39    return {
40        "results": summary.to_dict(orient='records'),
41        "total": len(summary),
42        "generated_at": datetime.now().isoformat()
43    }

Before connecting your Python service to Retool, use PyCharm's built-in HTTP client to verify endpoint behavior. This catches response format issues before they become Retool debugging problems. Open the HTTP client in PyCharm: go to Tools → HTTP Client → Create Request or press Ctrl+Alt+Shift+Insert (macOS: Cmd+Option+Shift+Insert). A .http file opens in the editor where you write HTTP requests. Write test requests for each endpoint you plan to connect to Retool: PyCharm executes the request when you click the green Run button next to it and displays the response in the Run panel. You can see the full response JSON, HTTP status code, headers, and response time — exactly the information you need to verify the response structure Retool will receive. For endpoints that require authentication, add an Authorization header in the .http file. PyCharm's HTTP client supports environment files (.env.http) for storing API keys and base URLs that can be referenced as {{ENV_VARIABLE}} in requests — keeping credentials out of the test files. Verify your responses match what Retool expects: arrays of objects for Table data, flat key-value objects for stat displays, or time-series arrays for Chart data. If the response structure doesn't match, fix it in Python now rather than after wiring to Retool.

1### Test sales summary endpoint
2GET http://localhost:8000/api/sales-summary
3  ?start_date=2024-01-01
4  &end_date=2024-03-31
5  &region=West
6Content-Type: application/json
7Authorization: Bearer {{API_KEY}}
8
9###
10
11### Test endpoint that Retool will use for Table data
12GET http://localhost:8000/api/orders
13  ?status=pending
14  &page=1
15  &per_page=50
16Content-Type: application/json
17
18###
19
20### Test POST endpoint for Retool action buttons
21POST http://localhost:8000/api/orders/123/process
22Content-Type: application/json
23Authorization: Bearer {{API_KEY}}
24
25{
26  "action": "approve",
27  "notes": "Approved via Retool",
28  "operator": "ops-team@company.com"
29}

Since Retool proxies all API requests server-side, your Python service needs to authenticate Retool's server-side requests without requiring browser-based OAuth flows. API key authentication is the simplest and most appropriate pattern for Retool-consumed Python services. Implement API key authentication in FastAPI using a dependency: In Flask, implement authentication using a decorator or before_request hook. Generate a secure API key: open PyCharm's terminal and run: python -c "import secrets; print(secrets.token_urlsafe(32))". Store this key in your Python service's environment variables (use python-dotenv in development) and in Retool's configuration variables. For production deployments, never hardcode the API key in your Python source code. Use environment variables and load them with os.environ.get('RETOOL_API_KEY'). PyCharm supports .env file loading with the EnvFile plugin, making it easy to manage environment variables across development and production configurations. Once deployed, configure Retool to send the API key in a header: in the REST API Resource settings, add a default header with key = X-API-Key and value = {{ retoolContext.configVars.PYTHON_SERVICE_API_KEY }}.

1# FastAPI API key authentication dependency
2from fastapi import Security, HTTPException, status
3from fastapi.security.api_key import APIKeyHeader
4import os
5
6API_KEY = os.environ.get('RETOOL_API_KEY')
7api_key_header = APIKeyHeader(name='X-API-Key', auto_error=False)
8
9async def get_api_key(api_key: str = Security(api_key_header)):
10    if not api_key or api_key != API_KEY:
11        raise HTTPException(
12            status_code=status.HTTP_401_UNAUTHORIZED,
13            detail='Invalid or missing API key'
14        )
15    return api_key
16
17# Apply to protected endpoints
18@app.get("/api/sales-summary")
19async def get_sales_summary(
20    start_date: str = Query(default="2024-01-01"),
21    end_date: str = Query(default="2024-12-31"),
22    _: str = Security(get_api_key)  # Requires valid API key
23):
24    # ... endpoint logic

Once your Python service is tested locally via PyCharm's HTTP client, deploy it to a server Retool Cloud can reach. Choose a deployment platform based on your infrastructure: - **Railway or Render**: easiest for small services, just push to Git and Railway/Render builds and deploys automatically. Good for Flask/FastAPI services with moderate traffic. - **Google Cloud Run**: serverless containers, excellent for FastAPI services that have bursty traffic and should scale to zero when idle. - **AWS EC2 with nginx**: full control, appropriate for services that need specific networking, VPC placement, or performance requirements. For Railway deployment, create a Procfile in your PyCharm project root: web: uvicorn main:app --host 0.0.0.0 --port $PORT. Push to GitHub and connect to Railway — it detects Python and deploys automatically. Set environment variables (RETOOL_API_KEY) in Railway's environment settings panel. After deployment, note your service's public URL (e.g., https://my-service.railway.app). Go to Retool's Resources tab → Add Resource → REST API. Name it 'Python Data Service'. Set Base URL to your deployed service's URL. Add the API key header: key = X-API-Key, value = {{ retoolContext.configVars.PYTHON_SERVICE_API_KEY }}. Create your first Retool query targeting this resource — set Method to GET, Path to /api/sales-summary, and add URL parameters matching your endpoint's query parameters. Click Run to verify the response and ensure Retool can reach your deployed Python service. For complex data pipelines involving multiple Python services with shared authentication, RapidDev's team can help design the right Retool resource architecture for multi-service backends.

1# Procfile for Railway/Render deployment
2web: uvicorn main:app --host 0.0.0.0 --port $PORT
3
4# requirements.txt
5fastapi==0.109.0
6uvicorn[standard]==0.27.0
7pandas==2.2.0
8pydantic==2.6.0
9python-dotenv==1.0.0
10sqlalchemy==2.0.25

With the Retool resource configured, build queries for each endpoint and bind them to Retool components. For a sales summary dashboard, create a query named getSalesSummary, set Method to GET, Path to /api/sales-summary, and add URL parameters that map Retool component values to your endpoint's query parameters: - start_date: {{ dateRange.start }} - end_date: {{ dateRange.end }} - region: {{ dropdown_region.value || '' }} Bind the query to a Table component: Data = {{ getSalesSummary.data.results }}. Bind a Stat component to show total records: {{ getSalesSummary.data.total }}. For debugging issues between PyCharm-developed code and Retool, use a two-track approach. In PyCharm: attach the debugger (Shift+F9) to your running FastAPI process and set breakpoints in the endpoint function to inspect what data is being computed and returned. In Retool: use the query's Response tab to inspect the raw JSON Retool receives and compare it to PyCharm's debugger output. For request/response mismatches — Retool sending different parameter formats than your Python endpoint expects — add request logging in your FastAPI service to see exactly what Retool sends: This combination of PyCharm debugging and Retool's query inspector covers the full request lifecycle, making it straightforward to trace issues from the Retool UI component through the query parameter, into the Python endpoint, and back to the Retool response binding.

1# Add request logging middleware to FastAPI for debugging Retool integration
2import logging
3from fastapi import Request
4import time
5
6logging.basicConfig(level=logging.INFO)
7logger = logging.getLogger(__name__)
8
9@app.middleware("http")
10async def log_requests(request: Request, call_next):
11    start_time = time.time()
12    logger.info(f"Incoming: {request.method} {request.url}")
13    logger.info(f"Headers: {dict(request.headers)}")
14
15    # Log query params (useful for debugging Retool URL parameter issues)
16    if request.query_params:
17        logger.info(f"Query params: {dict(request.query_params)}")
18
19    response = await call_next(request)
20    process_time = time.time() - start_time
21    logger.info(f"Response: {response.status_code} in {process_time:.3f}s")
22    return response