MCPDatabasesSSE

🗄️ DataBaseSse Project (SSE Version)

This project is the SSE version of the original "stido" release available at https://github.com/alvnavraii/mcpDataBases. It includes improvements and adaptations for managing and migrating data between PostgreSQL and SQLite databases, with features exposed via Server-Sent Events (SSE).

📁 Main Structure

• main.py: 🖥️ MCP server with tools to query, insert, update, delete, and modify tables in the PostgreSQL database, now with SSE support.
• connection.py: 🔗 Manages the connection to the PostgreSQL database.
• pg_to_sqlite.py: 🔄 Script to migrate all tables and data from PostgreSQL to SQLite.
• ecommerce.db: 🗃️ SQLite database generated from the migration (optional).

⚙️ Features

• CRUD operations: ➕ Create, 🔎 Read, ✏️ Update, and ❌ Delete records in PostgreSQL database tables.
• Table management: 🏗️ Easily create, 🛠️ alter, and 🗑️ drop tables.
• Data migration: 🔄 Automatic transfer of structure and data between PostgreSQL and SQLite.
• Advanced queries: 🧮 Execute custom SQL queries for analysis or maintenance.
• SSE support: 📡 Real-time responses using Server-Sent Events for operations and notifications.

🚀 Usage Examples

1. MCP Server (main.py)

• Run the MCP server to expose database management tools with SSE:

  python3 main.py
  

• Available tools:
  • 🔎 query_db(query): Executes SELECT queries and returns results via SSE.
  • ➕ insert_db(query): Executes INSERT statements and notifies via SSE.
  • ✏️ update_db(query): Executes UPDATE statements and notifies via SSE.
  • ❌ delete_db(query): Executes DELETE statements and notifies via SSE.
  • 🏗️ create_table(query): Executes CREATE TABLE statements and notifies via SSE.
  • 🛠️ alter_table(query): Executes ALTER TABLE statements and notifies via SSE.
  • 🗑️ drop_table(query): Executes DROP TABLE statements and notifies via SSE.

2. Data migration between databases

• Run the migration script to copy all tables and data from PostgreSQL to SQLite:

  python3 pg_to_sqlite.py
  

• You can adapt the script to migrate data in both directions or between different schemas.

3. CRUD operation examples

• Create a record: 📝

  INSERT INTO users (email, password) VALUES ('[email protected]', 'secret');
  

• Read records: 🔍

  SELECT * FROM products WHERE is_active = TRUE;
  

• Update records: 🔄

  UPDATE categories SET name = 'New Technologies' WHERE id = 1;
  

• Delete records: 🚮

  DELETE FROM carts WHERE created_at < '2024-01-01';