SQLite Database Server With Google OAuth

A simple demonstration MCP (Model Context Protocol) Server in Python that allows an AI assistant to perform CRUD operations on a SQLite database. All MCP tools are protected using Google OAuth 2.0.

Overview

The server manages a single employees table in a SQLite database (company.db). Before executing any CRUD operation, it authenticates the user via the Google OAuth 2.0 Authorization Code flow. The server opens a browser window, completes authentication, and stores the user's email in-memory for the duration of the session.

Folder Structure

.
├── server.py         # Main entry point, sets up FastMCP and registers tools
├── auth.py           # Google OAuth 2.0 logic and local callback server
├── database.py       # SQLAlchemy setup, session management, and sample data injection
├── models.py         # SQLAlchemy model definitions
├── tools.py          # CRUD operations exposed as MCP tools
├── config.py         # Loads environment variables
├── .env              # Environment variables (Credentials)
├── requirements.txt  # Python dependencies
└── README.md         # This documentation

Installing Dependencies

Make sure you have Python 3.12+ installed, then run:

pip install -r requirements.txt

Setting up Google OAuth Credentials

1. Go to the Google Cloud Console.
2. Create a new project or select an existing one.
3. Navigate to APIs & Services > Credentials.
4. Click Create Credentials > OAuth client ID.
5. Select Web application as the application type.
6. Under Authorized redirect URIs, add exactly what is in your .env file (e.g., http://localhost:8080/callback).
7. Click Create and copy your Client ID and Client Secret.

Configuring .env

Edit the .env file and replace the placeholder values with your actual Google OAuth credentials:

GOOGLE_CLIENT_ID=your-client-id-here
GOOGLE_CLIENT_SECRET=your-client-secret-here
REDIRECT_URI=http://localhost:8080/callback

Note: You can change the port in REDIRECT_URI if port 8080 is already in use. The auth.py script automatically parses this URL to determine which port to listen on.

Running the MCP Server

You can run the server in development mode using the FastMCP CLI or via an MCP inspector:

fastmcp dev server.py

Alternatively, configure your AI Assistant (like Claude Desktop) to run the server script directly via stdio.

Authenticating with Google

When the AI Assistant attempts to call an MCP tool for the first time:

1. The server will detect that you are not authenticated.
2. It will automatically open your default web browser to the Google Sign-In page.
3. Once you sign in and grant permission, Google will redirect you to http://localhost:8080/callback.
4. A local HTTP server intercepts the authorization code, exchanges it for an access token, and fetches your email.
5. The browser will display an "Authentication successful!" message, and the original tool call will proceed.
6. The authentication state is kept in memory for the lifecycle of the Python process.

Example MCP Tool Calls

Once authenticated, your AI Assistant can use the following tools:

• get_all_employees(): Returns a list of all employees.
• get_employee_by_id(employee_id=1): Returns the details of a specific employee.
• add_employee(name="Jane Doe", email="[email protected]", department="IT", salary=100000): Inserts a new employee.
• update_employee(employee_id=1, salary=130000): Updates specific fields for an employee.
• delete_employee(employee_id=3): Deletes an employee from the database.