Mcpdatabasessse

🚀 Database SSE Project (SSE Version)

This project is the SSE version of the original "stido" project published at https://github.com/alvnavraii/mcpDataBases. It includes functional improvements and adaptations for managing data migrations between PostgreSQL and SQLite databases and exposes these functionalities through Server-Sent Events (SSE).

✨ Features

• CRUD Operations: ➕ Create, 🔎 Query, ✏️ Update, ❌ Delete records in PostgreSQL database tables.
• Table Management: 🏗️ Conveniently create, 🛠️ modify, and 🗑️ delete tables.
• Data Migration: 🔄 Automatically transfer structures and data between PostgreSQL and SQLite.
• Advanced Queries: 🧮 Execute custom SQL queries for analysis or maintenance.
• SSE Support: 📡 Use Server-Sent Events to implement real-time operation responses and notifications.

📦 Main Structure

• main.py: 🖥️ The MCP server, equipped with tools for querying, inserting, updating, deleting, and modifying PostgreSQL database tables, now supporting SSE.
• connection.py: 🔗 Manages the connection to the PostgreSQL database.
• pg_to_sqlite.py: 🔄 A script for migrating all tables and data from PostgreSQL to SQLite.
• ecommerce.db: 🗃️ An SQLite database generated by the migration (optional).

💻 Usage Examples

1. MCP Server (main.py)

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

  python3 main.py
  

• Available tools:
  • 🔎 query_db(query): Execute a SELECT query and return the results via SSE.
  • ➕ insert_db(query): Execute an INSERT statement and notify via SSE.
  • ✏️ update_db(query): Execute an UPDATE statement and notify via SSE.
  • ❌ delete_db(query): Execute a DELETE statement and notify via SSE.
  • 🏗️ create_table(query): Execute a CREATE TABLE statement and notify via SSE.
  • 🛠️ alter_table(query): Execute an ALTER TABLE statement and notify via SSE.
  • 🗑️ drop_table(query): Execute a DROP TABLE statement and notify via SSE.

2. Data Migration

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

  python3 pg_to_sqlite.py
  

• The script can be adjusted as needed to support two-way migration or data migration between different architectures.

3. CRUD Operation Examples

Basic Usage

• Create a Record: 📝

  INSERT INTO users (email, password) VALUES ('user@domain.com', 'secret');
  

• Query Records: 🔍

  SELECT * FROM products WHERE is_active = TRUE;
  

• Update a Record: 🔄

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

• Delete a Record: 🚮

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