My Database

🚀 MCP Universal Database Connection Protocol SQLAlchemy Server

The MCP Universal Database Connection Protocol SQLAlchemy Server is a SQLAlchemy-based general database connection server that supports multiple database systems.

🚀 Quick Start

✨ Features

• Supported Database Systems: MySQL, PostgreSQL, SQLite, MSSQL, Oracle.
• Characteristics:
  • Support for multiple connection protocols.
  • Automatic database reconnection.
  • Formatting of query results (JSON, JSONL, Markdown tables).
  • Support for stored procedure calls (only for specific databases).

📦 Installation

pip install mcp-sqlalchemy-server

Configuration

Edit the configuration file config.json:

{
  "port": 5001,
  "databases": {
    "mysql": {
      "driver": "mysql+pymysql",
      "host": "localhost",
      "database": "mydb",
      "user": "root",
      "password": "password"
    },
    "postgresql": {
      "driver": "postgresql+psycopg2",
      "host": "localhost",
      "database": "mydb",
      "user": "postgres",
      "password": "password"
    }
  }
}

Startup

python -m mcp_sqlalchemy_server --config config.json

💻 Usage Examples

Basic Usage

const MCP = require('mcp-sqlalchemy-client');

// Connect to the MySQL database
const mysqlConn = new MCP.Connection({
  database: 'mysql',
  user: 'root',
  password: 'password'
});

// Execute a query
mysqlConn.query('SELECT * FROM users').then(rows => {
  console.log(rows);
}).catch(err => {
  console.error(err);
});

// Connect to the PostgreSQL database
const postgresConn = new MCP.Connection({
  database: 'postgresql',
  user: 'postgres',
  password: 'password'
});

// Execute a query
postgresConn.query('SELECT * FROM users').then(rows => {
  console.log(rows);
}).catch(err => {
  console.error(err);
});

Advanced Usage

Execute SQL Queries

const query = 'SELECT * FROM users WHERE id = 1';
conn.query(query).then(rows => {
  console.log(rows);
}).catch(err => {
  console.error(err);
});

Return Formats

• JSON: A standard JSON array, suitable for most cases.
• JSONL: One JSON object per line, suitable for large data volume output.
• Markdown Table: Returns tabular data in Markdown format, convenient for document display.

Special Features (Virtuoso Only)

Execute SPASQL Queries

const query = 'EXEC Demo.demo.execute_spasql_query';
conn.spasql(query).then(rows => {
  console.log(rows);
}).catch(err => {
  console.error(err);
});

Execute SPARQL Queries

const query = 'SELECT * { <http://example.org> <p:age> ?age}';
conn.sparql(query).then(rows => {
  console.log(rows);
}).catch(err => {
  console.error(err);
});

Call the Virtuoso AI Assistant

const prompt = 'Tell me some suggestions for database optimization.';
conn.virtuosoSupportAI(prompt).then(response => {
  console.log(response);
}).catch(err => {
  console.error(err);
});

📚 Documentation

Troubleshooting

To facilitate troubleshooting:

1. Install the MCP inspection tool:

   npm install -g @modelcontextprotocol/inspector
   

2. Start the inspection tool:

   npx @modelcontextprotocol/inspector uv --directory /path/to/mcp-sqlalchemy-server run mcp-sqlalchemy-server
   

Access the provided URL to troubleshoot server interaction issues.