SQLGenius is an MCP-based Python server that provides a natural language to SQL interface for querying your BigQuery data. It leverages Vertex AI's Gemini Pro for natural language understanding, a Streamlit frontend for interactive exploration, and a set of MCP tools to execute queries, explore schemas, and validate SQL. The server exposes tools such as execute_nl_query to convert natural language questions into SQL, execute_sql_query to run raw SQL against your BigQuery dataset, list_tables to enumerate available tables, and get_table_schema to fetch table schemas. When you run the Streamlit application, the MCP server starts automatically in the background, enabling users to ask questions in plain English, compose SQL directly, and visually explore your database schema and sample data through the UI.

To use it, first ensure your environment is configured with the required credentials and project settings. The UI presents dedicated tabs for natural language queries, raw SQL execution, and database exploration. The natural language tab sends your question to Gemini Pro (via Vertex AI) and returns an SQL query, which you can review or refine before executing. The SQL tab lets you input and run ad-hoc queries, while the explorer tab shows tables, schemas, and sample data. Access control and safety features restricts queries to SELECT statements and validate inputs to prevent unsafe operations.

Prerequisites:

• Python 3.8+ installed on your system
• Git installed
• Access to Google Cloud with BigQuery and Vertex AI credentials

Step-by-step installation:

1. Clone the repository and navigate to the project directory: git clone https://github.com/pawankumar94-sql_mcp_server/sql_mcp_server.git cd sql_mcp_server

2. (Optional) Create and activate a virtual environment: python -m venv venv

   Windows

   venv\Scripts\activate

   macOS/Linux

   source venv/bin/activate

3. Install dependencies: pip install -r requirements.txt

4. Copy the example environment file and configure credentials: cp .env.example .env

   Edit .env to include your GCP project details and credentials, e.g.:

   PROJECT_ID=your-project-id

   DATASET_ID=your-dataset-id

   GOOGLE_APPLICATION_CREDENTIALS=path/to/your/service-account.json

   VERTEX_AI_LOCATION=us-central1

5. Start the application (which will also start the MCP server behind the scenes): streamlit run streamlit_app.py

Note: The MCP server is designed to work in conjunction with the Streamlit frontend. Ensure your Google Cloud credentials are correctly set and that the BigQuery dataset is accessible from your environment.

Tips and considerations:

• Ensure the following environment variables are set in .env: PROJECT_ID, DATASET_ID, GOOGLE_APPLICATION_CREDENTIALS, VERTEX_AI_LOCATION.
• The MCP tools exposed by the server include: execute_nl_query, execute_sql_query, list_tables, get_table_schema. You can extend tools by editing the sql_mcp_server.py and restarting the Streamlit app.
• Security: Only SELECT queries are permitted and query validation is performed to prevent dangerous operations.
• If you encounter API/auth issues, verify that GOOGLE_APPLICATION_CREDENTIALS points to a valid JSON service account with BigQuery and Vertex AI access.
• If the Streamlit app takes long to start, ensure your network can reach Vertex AI endpoints and that the Vertex AI location matches your configuration.