MCP Server Starter

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= The project Directory =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= mcp-server-starter/ ├─ package.json ├─ tsconfig.json ├─ .gitignore ├─ .env # optional (future auth, db path, etc.) └─ src/ ├─ server/ │ └─ stdio.ts # minimal MCP server (stdio transport) ├─ tools/ │ ├─ sql/ │ │ ├─ index.ts # "run_named_query" tool (registers with server) │ │ └─ templates.ts # allow-listed SQL templates │ └─ index.ts # (barrel) export all tools ├─ db/ │ ├─ seed.ts # create/seed SQLite │ └─ sqlite.ts # tiny DB helper └─ client/ └─ devClient.ts # simple TS client to test the server =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Initialize the project & install dependencies =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= cd mcp-server-starter # 1) Initialize Node project npm init -y # 2) Install runtime deps npm i @modelcontextprotocol/sdk zod better-sqlite3 dotenv # 3) Dev dependencies (TypeScript + runner) npm i -D typescript tsx @types/node # 4) Create a TS config npx tsc --init # 5) Folders mkdir -p src/{server,tools/sql,db,client} =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= For testing the connectivity, we can test by running this in the cmd: ``` npm run db:ping ``` This connection will return means that it is connected fine and can proceed with the connection to the server itself. Make sure to comment other connection first before testing with the particular connection. =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= RUN ONE DATABASE CONTAINER AT A TIME: 1. PostgreSQL: Create: docker run -d --name pg -e POSTGRES_PASSWORD=postgres -e POSTGRES_USER=postgres -e POSTGRES_DB=appdb -p 5432:5432 postgres:16 ``` # 2) PostgreSQL DB_PROVIDER=postgres DATABASE_URL=postgres://postgres:postgres@127.0.0.1:5432/appdb ``` 2. MySQL: Create: docker run -d --name mysql -e MYSQL_ROOT_PASSWORD=rootpass -e MYSQL_DATABASE=appdb -p 3306:3306 mysql:8 ``` # 3) MySQL / MariaDB # DB_PROVIDER=mysql # DATABASE_URL=mysql://root:rootpass@127.0.0.1:3306/appdb ``` 3. SQL Server: Create: docker run -d --name mssql -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Passw0rd!" -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest ``` # 4) SQL Server / Azure SQL # DB_PROVIDER=mssql # DATABASE_URL=Server=localhost,1433;Database=appdb;User Id=sa;Password=Passw0rd!;Encrypt=true;TrustServerCertificate=true # Example (keep commented): # Server=HOST,1433;Database=DB;User Id=USER;Password=PASS;Encrypt=true ``` 4. Create: docker run -d --name oracle -e ORACLE_PASSWORD=oracle -p 1521:1521 gvenzl/oracle-xe:21-slim ``` # 5) Oracle # DB_PROVIDER=oracle # DATABASE_URL=system/oracle@127.0.0.1:1521/XEPDB1 ``` =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= WHAT TO TEST IN THE POSTMAN =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= RUN LOCALLY: npm run dev:http BASIC: List all tables: 1. PostgreSQL (Public Schema only) { "db": "pg", "sql": SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE' ORDER BY table_name } 2. MySQL / MariaDB (Current Database Only) { "db": "mysql", "sql": "SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME" } 3. SQL Server (current database) { "db": "mssql", "sql": "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME" } 4. Oracle (current user's tables; internal filtered separatelty) { "db": "oracle", "sql": SELECT DISTINCT table_name FROM user_tab_columns WHERE table_name NOT LIKE 'ROLLING$%' AND table_name NOT LIKE 'SCHEDULER_%' -- AND UPPER(table_name) NOT IN (<your excludedOracleTables uppercased>) ORDER BY table_name; } 5. SQLite { "db": "sqlite", "sql": "SELECT name AS table_name FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name" } 1. SQLite: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= List all user tables (MSSQL) { "sql": "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME" } List all views (optional) { "sql": "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS ORDER BY TABLE_NAME" } List columns for a specific table { "sql": "SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Users' ORDER BY ORDINAL_POSITION" } Primary key columns for a table { "sql": "SELECT c.COLUMN_NAME AS PrimaryKeyColumn FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ON t.CONSTRAINT_NAME = c.CONSTRAINT_NAME AND t.TABLE_NAME = c.TABLE_NAME WHERE t.TABLE_NAME = 'Users' AND t.CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY c.ORDINAL_POSITION" } Foreign keys (table → referenced table) { "sql": "SELECT fk.name AS FK_Name, tp.name AS FromTable, cp.name AS FromColumn, tr.name AS ToTable, cr.name AS ToColumn FROM sys.foreign_keys fk JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id JOIN sys.tables tp ON fkc.parent_object_id = tp.object_id JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id JOIN sys.tables tr ON fkc.referenced_object_id = tr.object_id JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id ORDER BY tp.name, fk.name, fkc.constraint_column_id" } List everything in a table (e.g., Users) { "sql": "SELECT * FROM Users ORDER BY UserID", "rowLimit": 1000 } List everything in another table (e.g., Products) { "sql": "SELECT * FROM Products ORDER BY ProductID", "rowLimit": 1000 } Row counts per table (overview) { "sql": "SELECT t.name AS TableName, SUM(p.rows) AS RowCount FROM sys.tables t JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0,1) GROUP BY t.name ORDER BY t.name" } Sample N rows from each table (advanced) { "sql": "DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql = STRING_AGG(CONCAT('SELECT ''', QUOTENAME(s.name), '.', QUOTENAME(t.name), ''' AS TableName, (SELECT TOP (3) * FROM ', QUOTENAME(s.name), '.', QUOTENAME(t.name), ' FOR JSON PATH, INCLUDE_NULL_VALUES) AS JsonRows'), ' UNION ALL ') WITHIN GROUP (ORDER BY t.name) FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id; EXEC sp_executesql @sql;", "readOnly": false } Filter by equality (by name) { "sql": "SELECT * FROM Users WHERE UserName = :name ORDER BY UserID", "params": { "name": "Alice" } } Filter with LIKE (contains) { "sql": "SELECT * FROM Users WHERE UserName LIKE '%' + :substr + '%' ORDER BY UserID", "params": { "substr": "li" } } IN list { "sql": "SELECT * FROM Products WHERE ProductID IN (:id1, :id2, :id3) ORDER BY ProductID", "params": { "id1": 1, "id2": 2, "id3": 3 } } Date range { "sql": "SELECT * FROM Orders WHERE OrderDate BETWEEN :start AND :end ORDER BY OrderDate DESC", "params": { "start": "2025-09-01", "end": "2025-09-30" } } Numeric comparison { "sql": "SELECT * FROM Products WHERE Price > :minPrice ORDER BY Price DESC", "params": { "minPrice": 500 } } Pagination (page 2, size 10) { "sql": "SELECT * FROM Users ORDER BY UserID OFFSET :offset ROWS FETCH NEXT :pageSize ROWS ONLY", "params": { "offset": 10, "pageSize": 10 } } Join (orders with product lines for a given user) { "sql": "SELECT o.OrderID, o.OrderDate, p.ProductName, oi.Quantity, p.Price, (oi.Quantity * p.Price) AS LineTotal FROM Orders o JOIN OrderItems oi ON oi.OrderID = o.OrderID JOIN Products p ON p.ProductID = oi.ProductID WHERE o.UserID = :userId ORDER BY o.OrderDate DESC, o.OrderID", "params": { "userId": 1 } } Aggregation (total spend per user, top N) { "sql": "SELECT u.UserID, u.UserName, SUM(oi.Quantity * p.Price) AS TotalSpend FROM Users u JOIN Orders o ON o.UserID = u.UserID JOIN OrderItems oi ON oi.OrderID = o.OrderID JOIN Products p ON p.ProductID = oi.ProductID GROUP BY u.UserID, u.UserName HAVING SUM(oi.Quantity * p.Price) > :minSpend ORDER BY TotalSpend DESC", "params": { "minSpend": 500 } } Find users with no orders (LEFT JOIN anti-pattern) { "sql": "SELECT u.* FROM Users u LEFT JOIN Orders o ON o.UserID = u.UserID WHERE o.OrderID IS NULL ORDER BY u.UserID" } Null-safe filter { "sql": "SELECT * FROM Users WHERE (Email IS NULL OR Email = :email)", "params": { "email": "alice@example.com" } } =-=-=-=-=-=-=-=-=-=-=-=-=-=-= 2.PostgreSQL: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 3. MySQL / MariaDB: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 4. SQL Server / Azure SQL: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 5. Oracle =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= NOTES: - When we deploy, we just need to define the database secret in the platform environment. What’s the difference between your STDIO and HTTP today? A) src/server/stdio.ts — MCP server (real MCP) over STDIO - Creates an McpServer and registers your SQL tools (one namespace per DB alias). - Uses STDIO transport (the MCP client launches the server as a subprocess and exchanges JSON‑RPC over stdin/stdout). - This is a real MCP server—MCP clients can discover tools with tools/list and invoke them with tools/call. - STDIO is a standard transport in MCP, alongside Streamable HTTP. B) src/server/http.ts — Custom REST API (not MCP) - Starts an Express server with routes like: - GET /dbs (list aliases), - POST /sql/query (executes SQL under an alias, read‑only by default). - It does not implement MCP methods (tools/list, tools/call), so Copilot Studio or MCP clients can’t auto‑discover your tools from it. It’s just regular REST you can test in Postman. - MCP is a protocol (JSON‑RPC schema + transports) for agents to list+invoke tools in a standard way; REST routes like /sql/query are outside that protocol.