This is page 2 of 8. Use http://codebase.md/bytebase/dbhub?lines=true&page={x} to view the full context.
# Directory Structure
```
├── .dockerignore
├── .env.example
├── .github
│ ├── CODEOWNERS
│ ├── copilot-instructions.md
│ └── workflows
│ ├── docker-publish.yml
│ ├── npm-publish.yml
│ └── run-tests.yml
├── .gitignore
├── .husky
│ └── pre-commit
├── .npmrc
├── .prettierrc.json
├── bun.lock
├── CLAUDE.md
├── Dockerfile
├── LICENSE
├── llms-full.txt
├── package.json
├── pnpm-lock.yaml
├── pnpm-workspace.yaml
├── README.md
├── resources
│ ├── employee-sqlite
│ │ ├── employee.sql
│ │ ├── load_department.sql
│ │ ├── load_dept_emp.sql
│ │ ├── load_dept_manager.sql
│ │ ├── load_employee.sql
│ │ ├── load_salary1.sql
│ │ ├── load_title.sql
│ │ ├── object.sql
│ │ ├── show_elapsed.sql
│ │ └── test_employee_md5.sql
│ └── images
│ ├── claude-desktop.webp
│ ├── cursor.webp
│ ├── logo-full.svg
│ ├── logo-full.webp
│ ├── logo-icon-only.svg
│ ├── logo-text-only.svg
│ └── mcp-inspector.webp
├── scripts
│ └── setup-husky.sh
├── src
│ ├── __tests__
│ │ └── json-rpc-integration.test.ts
│ ├── config
│ │ ├── __tests__
│ │ │ ├── env.test.ts
│ │ │ └── ssh-config-integration.test.ts
│ │ ├── demo-loader.ts
│ │ └── env.ts
│ ├── connectors
│ │ ├── __tests__
│ │ │ ├── mariadb.integration.test.ts
│ │ │ ├── mysql.integration.test.ts
│ │ │ ├── postgres-ssh.integration.test.ts
│ │ │ ├── postgres.integration.test.ts
│ │ │ ├── shared
│ │ │ │ └── integration-test-base.ts
│ │ │ ├── sqlite.integration.test.ts
│ │ │ └── sqlserver.integration.test.ts
│ │ ├── interface.ts
│ │ ├── manager.ts
│ │ ├── mariadb
│ │ │ └── index.ts
│ │ ├── mysql
│ │ │ └── index.ts
│ │ ├── postgres
│ │ │ └── index.ts
│ │ ├── sqlite
│ │ │ └── index.ts
│ │ └── sqlserver
│ │ └── index.ts
│ ├── index.ts
│ ├── prompts
│ │ ├── db-explainer.ts
│ │ ├── index.ts
│ │ └── sql-generator.ts
│ ├── resources
│ │ ├── index.ts
│ │ ├── indexes.ts
│ │ ├── procedures.ts
│ │ ├── schema.ts
│ │ ├── schemas.ts
│ │ └── tables.ts
│ ├── server.ts
│ ├── tools
│ │ ├── __tests__
│ │ │ └── execute-sql.test.ts
│ │ ├── execute-sql.ts
│ │ └── index.ts
│ ├── types
│ │ ├── sql.ts
│ │ └── ssh.ts
│ └── utils
│ ├── __tests__
│ │ ├── safe-url.test.ts
│ │ ├── ssh-config-parser.test.ts
│ │ └── ssh-tunnel.test.ts
│ ├── allowed-keywords.ts
│ ├── dsn-obfuscate.ts
│ ├── response-formatter.ts
│ ├── safe-url.ts
│ ├── sql-row-limiter.ts
│ ├── ssh-config-parser.ts
│ └── ssh-tunnel.ts
├── tsconfig.json
├── tsup.config.ts
└── vitest.config.ts
```
# Files
--------------------------------------------------------------------------------
/resources/images/logo-text-only.svg:
--------------------------------------------------------------------------------
```
1 | <svg width="4233" height="987" viewBox="0 0 4233 987" fill="none" xmlns="http://www.w3.org/2000/svg">
2 | <path d="M229.9 982.9L45.1 966.4L85.8 210.7C66.7333 215.1 49.1333 220.233 33 226.1L0 39.1C38.8667 31.0333 86.9 22.6 144.1 13.7999C201.3 4.99998 260.333 0.599988 321.2 0.599988C368.133 0.599988 415.8 5.73332 464.2 16C512.6 25.5333 558.8 41.6666 602.8 64.4C647.533 86.4 687.133 116.1 721.6 153.5C756.8 190.9 784.3 236.733 804.1 291C824.633 345.267 834.9 409.433 834.9 483.5C834.9 587.633 818.033 676.733 784.3 750.8C751.3 824.133 702.9 880.233 639.1 919.1C576.033 957.967 499.767 977.4 410.3 977.4C350.9 977.4 292.233 966.4 234.3 944.4C232.833 959.067 231.367 971.9 229.9 982.9ZM313.5 185.4C303.233 185.4 292.6 185.4 281.6 185.4C274.267 321.8 267.667 438.033 261.8 534.1C255.933 629.433 250.8 709 246.4 772.8C268.4 780.133 292.6 786.733 319 792.6C346.133 797.733 374.367 800.3 403.7 800.3C463.1 800.3 510.767 785.267 546.7 755.2C583.367 725.133 609.767 685.533 625.9 636.4C642.033 586.533 650.1 532.633 650.1 474.7C650.1 428.5 643.133 389.267 629.2 357C615.267 324.733 596.933 297.967 574.2 276.7C552.2 255.433 528 238.933 501.6 227.2C475.2 214.733 449.167 205.567 423.5 199.7C398.567 193.833 376.2 190.167 356.4 188.7C336.6 186.5 322.3 185.4 313.5 185.4ZM971.163 971.9C970.429 963.833 970.063 955.033 970.063 945.5C970.063 935.967 970.063 924.967 970.063 912.5C970.063 880.967 970.796 843.567 972.263 800.3C973.729 756.3 975.929 709.733 978.863 660.6C981.796 610.733 984.729 560.867 987.663 511C990.596 460.4 993.529 412 996.463 365.8C1000.13 319.6 1003.43 278.167 1006.36 241.5C990.229 246.633 975.929 251.4 963.463 255.8L934.863 70.9999C1017.73 47.5333 1094.73 29.9333 1165.86 18.2C1237.73 6.46665 1302.63 0.599988 1360.56 0.599988C1478.63 0.599988 1568.83 22.9667 1631.16 67.7C1693.5 112.433 1724.66 177.333 1724.66 262.4C1724.66 310.8 1714.03 351.5 1692.76 384.5C1671.5 416.767 1641.43 443.533 1602.56 464.8C1697.9 513.2 1745.56 592.033 1745.56 701.3C1745.56 749.7 1736.4 790.4 1718.06 823.4C1700.46 855.667 1677.73 881.7 1649.86 901.5C1622 921.3 1592.3 935.967 1560.76 945.5C1529.96 955.767 1500.63 962.367 1472.76 965.3C1445.63 968.967 1424.36 970.8 1408.96 970.8C1370.83 970.8 1330.13 967.133 1286.86 959.8C1243.6 952.467 1201.43 941.467 1160.36 926.8C1160.36 941.467 1160.36 955.4 1160.36 968.6L971.163 971.9ZM1364.96 184.3C1338.56 184.3 1310.7 185.767 1281.36 188.7C1252.76 190.9 1224.16 194.567 1195.56 199.7C1191.16 253.233 1186.76 315.567 1182.36 386.7C1191.9 387.433 1201.43 387.8 1210.96 387.8C1221.23 387.8 1231.5 387.8 1241.76 387.8C1435.36 387.8 1532.16 348.2 1532.16 269C1532.16 242.6 1517.13 222.067 1487.06 207.4C1457.73 192 1417.03 184.3 1364.96 184.3ZM1267.06 561.6C1251.66 561.6 1235.9 561.6 1219.76 561.6C1204.36 561.6 1188.6 561.967 1172.46 562.7C1171 594.967 1169.53 627.233 1168.06 659.5C1167.33 691.767 1166.23 723.667 1164.76 755.2C1205.83 767.667 1246.16 776.833 1285.76 782.7C1325.36 787.833 1359.46 790.4 1388.06 790.4C1448.93 790.4 1492.56 783.067 1518.96 768.4C1545.36 753 1558.56 728.067 1558.56 693.6C1558.56 651.067 1533.26 618.433 1482.66 595.7C1432.8 572.967 1360.93 561.6 1267.06 561.6ZM1889.67 975.2C1883.07 934.133 1878.3 883.9 1875.37 824.5C1872.44 765.1 1870.97 700.933 1870.97 632C1870.97 560.867 1872.07 487.9 1874.27 413.1C1877.2 338.3 1880.5 266.433 1884.17 197.5C1888.57 127.833 1893.34 66.2333 1898.47 12.7L2089.87 16C2081.8 67.3333 2075.2 124.533 2070.07 187.6C2065.67 249.933 2062 314.833 2059.07 382.3C2111.14 374.967 2167.6 369.467 2228.47 365.8C2289.34 362.133 2348 360.3 2404.47 360.3C2431.6 360.3 2457.64 361.033 2482.57 362.5C2481.84 307.5 2480.74 256.167 2479.27 208.5C2477.8 160.833 2475.97 120.5 2473.77 87.5C2471.57 54.5 2469 31.7666 2466.07 19.2999L2655.27 7.19997C2661.87 68.8 2667 142.5 2670.67 228.3C2675.07 314.1 2677.27 405.033 2677.27 501.1C2677.27 578.833 2675.8 658.033 2672.87 738.7C2669.94 818.633 2664.8 895.633 2657.47 969.7H2473.77C2476.7 946.233 2478.9 914.333 2480.37 874C2481.84 832.933 2482.94 786.733 2483.67 735.4C2484.4 683.333 2484.77 629.067 2484.77 572.6C2484.77 562.333 2484.77 551.7 2484.77 540.7C2470.84 540.7 2456.54 540.7 2441.87 540.7C2427.94 539.967 2413.64 539.6 2398.97 539.6C2341.04 539.6 2282.37 541.8 2222.97 546.2C2164.3 549.867 2108.2 555.367 2054.67 562.7C2054.67 586.167 2054.67 609.633 2054.67 633.1C2054.67 697.633 2055.04 758.133 2055.77 814.6C2057.24 871.067 2059.44 922.4 2062.37 968.6L1889.67 975.2ZM3052.37 986.2C3005.44 986.2 2966.57 977.767 2935.77 960.9C2905.7 944.767 2881.5 922.4 2863.17 893.8C2845.57 865.2 2832.37 833.3 2823.57 798.1C2815.5 762.167 2810 725.133 2807.07 687C2804.87 648.133 2803.77 610.733 2803.77 574.8C2803.77 533.733 2804.87 491.933 2807.07 449.4C2810 406.867 2812.94 360.3 2815.87 309.7L3017.17 297.6C3014.97 313 3011.67 336.1 3007.27 366.9C3002.87 397.7 2998.84 432.9 2995.17 472.5C2991.5 512.1 2989.67 553.533 2989.67 596.8C2989.67 667.933 2995.9 720.733 3008.37 755.2C3020.84 788.933 3039.54 805.8 3064.47 805.8C3114.34 805.8 3151 769.5 3174.47 696.9C3197.94 624.3 3209.67 509.533 3209.67 352.6V302L3303.17 299.8L3416.47 297.6C3406.2 366.533 3398.5 429.967 3393.37 487.9C3388.24 545.833 3384.94 594.6 3383.47 634.2C3382 673.067 3381.27 699.1 3381.27 712.3C3381.27 746.033 3384.2 770.233 3390.07 784.9C3395.94 798.833 3406.2 805.8 3420.87 805.8C3428.2 805.8 3436.27 804.333 3445.07 801.4C3453.87 798.467 3464.14 793.333 3475.87 786L3468.17 975.2C3440.3 979.6 3416.1 981.8 3395.57 981.8C3360.37 981.8 3331.4 975.2 3308.67 962C3286.67 948.8 3269.07 931.2 3255.87 909.2C3233.14 933.4 3204.9 952.1 3171.17 965.3C3138.17 979.233 3098.57 986.2 3052.37 986.2ZM3576.47 975.2C3576.47 915.067 3577.57 850.533 3579.77 781.6C3581.97 712.667 3584.9 642.633 3588.57 571.5C3592.24 500.367 3595.9 430.7 3599.57 362.5C3603.97 294.3 3608.37 230.133 3612.77 170C3617.17 109.133 3620.84 55.2333 3623.77 8.29995L3807.47 10.4999C3803.07 52.3 3798.67 99.2333 3794.27 151.3C3790.6 203.367 3786.94 258.733 3783.27 317.4C3804.54 308.6 3827.27 302 3851.47 297.6C3876.4 292.467 3903.17 289.9 3931.77 289.9C3970.64 289.9 4007.67 296.5 4042.87 309.7C4078.8 322.167 4111.07 341.6 4139.67 368C4168.27 394.4 4190.64 428.5 4206.77 470.3C4223.64 512.1 4232.07 562.333 4232.07 621C4232.07 683.333 4223.27 737.233 4205.67 782.7C4188.07 828.167 4164.24 865.567 4134.17 894.9C4104.1 924.233 4070 945.867 4031.87 959.8C3994.47 974.467 3955.97 981.8 3916.37 981.8C3886.3 981.8 3858.07 977.767 3831.67 969.7C3806 961.633 3782.17 951 3760.17 937.8C3760.17 949.533 3760.17 961.267 3760.17 973L3576.47 975.2ZM3906.47 474.7C3851.47 474.7 3806.74 490.833 3772.27 523.1C3769.34 590.567 3766.77 656.567 3764.57 721.1C3800.5 768.767 3850.74 792.6 3915.27 792.6C3936.54 792.6 3957.8 787.833 3979.07 778.3C4001.07 768.033 4019.4 750.8 4034.07 726.6C4048.74 702.4 4056.07 668.667 4056.07 625.4C4056.07 576.267 4043.24 538.867 4017.57 513.2C3992.64 487.533 3955.6 474.7 3906.47 474.7Z" fill="black"/>
3 | </svg>
4 |
```
--------------------------------------------------------------------------------
/src/server.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
2 | import { StreamableHTTPServerTransport } from "@modelcontextprotocol/sdk/server/streamableHttp.js";
3 | import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
4 | import express from "express";
5 | import path from "path";
6 | import { readFileSync } from "fs";
7 | import { fileURLToPath } from "url";
8 |
9 | import { ConnectorManager } from "./connectors/manager.js";
10 | import { ConnectorRegistry } from "./connectors/interface.js";
11 | import { resolveDSN, resolveTransport, resolvePort, isDemoMode, redactDSN, isReadOnlyMode, resolveId } from "./config/env.js";
12 | import { getSqliteInMemorySetupSql } from "./config/demo-loader.js";
13 | import { registerResources } from "./resources/index.js";
14 | import { registerTools } from "./tools/index.js";
15 | import { registerPrompts } from "./prompts/index.js";
16 |
17 | // Create __dirname equivalent for ES modules
18 | const __filename = fileURLToPath(import.meta.url);
19 | const __dirname = path.dirname(__filename);
20 |
21 | // Load package.json to get version
22 | const packageJsonPath = path.join(__dirname, "..", "package.json");
23 | const packageJson = JSON.parse(readFileSync(packageJsonPath, "utf8"));
24 |
25 | // Server info
26 | export const SERVER_NAME = "DBHub MCP Server";
27 | export const SERVER_VERSION = packageJson.version;
28 |
29 | /**
30 | * Generate ASCII art banner with version information
31 | */
32 | export function generateBanner(version: string, modes: string[] = []): string {
33 | // Create a mode string that includes all active modes
34 | const modeText = modes.length > 0 ? ` [${modes.join(' | ')}]` : '';
35 |
36 | return `
37 | _____ ____ _ _ _
38 | | __ \\| _ \\| | | | | |
39 | | | | | |_) | |_| |_ _| |__
40 | | | | | _ <| _ | | | | '_ \\
41 | | |__| | |_) | | | | |_| | |_) |
42 | |_____/|____/|_| |_|\\__,_|_.__/
43 |
44 | v${version}${modeText} - Universal Database MCP Server
45 | `;
46 | }
47 |
48 | /**
49 | * Initialize and start the DBHub server
50 | */
51 | export async function main(): Promise<void> {
52 | try {
53 | // Resolve ID from command line args (for Cursor multi-instance support)
54 | const idData = resolveId();
55 | const id = idData?.id;
56 |
57 | // Resolve DSN from command line args, environment variables, or .env files
58 | const dsnData = resolveDSN();
59 |
60 | if (!dsnData) {
61 | const samples = ConnectorRegistry.getAllSampleDSNs();
62 | const sampleFormats = Object.entries(samples)
63 | .map(([id, dsn]) => ` - ${id}: ${dsn}`)
64 | .join("\n");
65 |
66 | console.error(`
67 | ERROR: Database connection string (DSN) is required.
68 | Please provide the DSN in one of these ways (in order of priority):
69 |
70 | 1. Use demo mode: --demo (uses in-memory SQLite with sample employee database)
71 | 2. Command line argument: --dsn="your-connection-string"
72 | 3. Environment variable: export DSN="your-connection-string"
73 | 4. .env file: DSN=your-connection-string
74 |
75 | Example formats:
76 | ${sampleFormats}
77 |
78 | See documentation for more details on configuring database connections.
79 | `);
80 | process.exit(1);
81 | }
82 |
83 | // Create MCP server factory function for HTTP transport
84 | const createServer = () => {
85 | const server = new McpServer({
86 | name: SERVER_NAME,
87 | version: SERVER_VERSION,
88 | });
89 |
90 | // Register resources, tools, and prompts
91 | registerResources(server);
92 | registerTools(server, id);
93 | registerPrompts(server);
94 |
95 | return server;
96 | };
97 |
98 | // Create server factory function (will be used for both STDIO and HTTP transports)
99 |
100 | // Create connector manager and connect to database
101 | const connectorManager = new ConnectorManager();
102 | console.error(`Connecting with DSN: ${redactDSN(dsnData.dsn)}`);
103 | console.error(`DSN source: ${dsnData.source}`);
104 | if (idData) {
105 | console.error(`ID: ${idData.id} (from ${idData.source})`);
106 | }
107 |
108 | // If in demo mode, load the employee database
109 | if (dsnData.isDemo) {
110 | const initScript = getSqliteInMemorySetupSql();
111 | await connectorManager.connectWithDSN(dsnData.dsn, initScript);
112 | } else {
113 | await connectorManager.connectWithDSN(dsnData.dsn);
114 | }
115 |
116 | // Resolve transport type
117 | const transportData = resolveTransport();
118 | console.error(`Using transport: ${transportData.type}`);
119 | console.error(`Transport source: ${transportData.source}`);
120 |
121 | // Print ASCII art banner with version and slogan
122 | const readonly = isReadOnlyMode();
123 |
124 | // Collect active modes
125 | const activeModes: string[] = [];
126 | const modeDescriptions: string[] = [];
127 |
128 | if (dsnData.isDemo) {
129 | activeModes.push("DEMO");
130 | modeDescriptions.push("using sample employee database");
131 | }
132 |
133 | if (readonly) {
134 | activeModes.push("READ-ONLY");
135 | modeDescriptions.push("only read only queries allowed");
136 | }
137 |
138 | // Output mode information
139 | if (activeModes.length > 0) {
140 | console.error(`Running in ${activeModes.join(' and ')} mode - ${modeDescriptions.join(', ')}`);
141 | }
142 |
143 | console.error(generateBanner(SERVER_VERSION, activeModes));
144 |
145 | // Set up transport based on type
146 | if (transportData.type === "http") {
147 | // Set up Express server for Streamable HTTP transport
148 | const app = express();
149 |
150 | // Enable JSON parsing
151 | app.use(express.json());
152 |
153 | // Handle CORS and security headers
154 | app.use((req, res, next) => {
155 | // Validate Origin header to prevent DNS rebinding attacks
156 | const origin = req.headers.origin;
157 | if (origin && !origin.startsWith('http://localhost') && !origin.startsWith('https://localhost')) {
158 | return res.status(403).json({ error: 'Forbidden origin' });
159 | }
160 |
161 | res.header('Access-Control-Allow-Origin', origin || 'http://localhost');
162 | res.header('Access-Control-Allow-Methods', 'GET, POST, OPTIONS');
163 | res.header('Access-Control-Allow-Headers', 'Content-Type, Mcp-Session-Id');
164 | res.header('Access-Control-Allow-Credentials', 'true');
165 |
166 | if (req.method === 'OPTIONS') {
167 | return res.sendStatus(200);
168 | }
169 | next();
170 | });
171 |
172 | // Health check endpoint
173 | app.get("/healthz", (req, res) => {
174 | res.status(200).send("OK");
175 | });
176 |
177 | // Main endpoint for streamable HTTP transport
178 | app.post("/message", async (req, res) => {
179 | try {
180 | // In stateless mode, create a new instance of transport and server for each request
181 | // to ensure complete isolation. A single instance would cause request ID collisions
182 | // when multiple clients connect concurrently.
183 | const transport = new StreamableHTTPServerTransport({
184 | sessionIdGenerator: undefined, // Disable session management for stateless mode
185 | enableJsonResponse: false // Use SSE streaming
186 | });
187 | const server = createServer();
188 |
189 | await server.connect(transport);
190 | await transport.handleRequest(req, res, req.body);
191 | } catch (error) {
192 | console.error("Error handling request:", error);
193 | if (!res.headersSent) {
194 | res.status(500).json({ error: 'Internal server error' });
195 | }
196 | }
197 | });
198 |
199 |
200 | // Start the HTTP server
201 | const portData = resolvePort();
202 | const port = portData.port;
203 | console.error(`Port source: ${portData.source}`);
204 | app.listen(port, '0.0.0.0', () => {
205 | console.error(`DBHub server listening at http://0.0.0.0:${port}`);
206 | console.error(`Connect to MCP server at http://0.0.0.0:${port}/message`);
207 | });
208 | } else {
209 | // Set up STDIO transport
210 | const server = createServer();
211 | const transport = new StdioServerTransport();
212 | console.error("Starting with STDIO transport");
213 | await server.connect(transport);
214 |
215 | // Listen for SIGINT to gracefully shut down
216 | process.on("SIGINT", async () => {
217 | console.error("Shutting down...");
218 | await transport.close();
219 | process.exit(0);
220 | });
221 | }
222 | } catch (err) {
223 | console.error("Fatal error:", err);
224 | process.exit(1);
225 | }
226 | }
227 |
```
--------------------------------------------------------------------------------
/src/prompts/sql-generator.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { z } from "zod";
2 | import { ConnectorManager } from "../connectors/manager.js";
3 | import {
4 | formatPromptSuccessResponse,
5 | formatPromptErrorResponse,
6 | } from "../utils/response-formatter.js";
7 | import { SQLDialect } from "../types/sql.js";
8 |
9 | // Schema for SQL generator prompt
10 | export const sqlGeneratorSchema = {
11 | description: z.string().describe("Natural language description of the SQL query to generate"),
12 | schema: z.string().optional().describe("Optional database schema to use"),
13 | };
14 |
15 | /**
16 | * SQL Generator Prompt Handler
17 | * Generates SQL queries from natural language descriptions
18 | */
19 | export async function sqlGeneratorPromptHandler(
20 | {
21 | description,
22 | schema,
23 | }: {
24 | description: string;
25 | schema?: string;
26 | },
27 | _extra: any
28 | ) {
29 | try {
30 | // Get current connector to determine dialect
31 | const connector = ConnectorManager.getCurrentConnector();
32 |
33 | // Determine SQL dialect from connector automatically
34 | let sqlDialect: SQLDialect;
35 | switch (connector.id) {
36 | case "postgres":
37 | sqlDialect = "postgres";
38 | break;
39 | case "sqlite":
40 | sqlDialect = "sqlite";
41 | break;
42 | case "mysql":
43 | sqlDialect = "mysql";
44 | break;
45 | case "sqlserver":
46 | sqlDialect = "mssql";
47 | break;
48 | default:
49 | sqlDialect = "ansi"; // Default to standard SQL if connector type is unknown
50 | }
51 |
52 | // Verify schema exists if provided
53 | if (schema) {
54 | const availableSchemas = await connector.getSchemas();
55 | if (!availableSchemas.includes(schema)) {
56 | return formatPromptErrorResponse(
57 | `Schema '${schema}' does not exist or cannot be accessed. Available schemas: ${availableSchemas.join(", ")}`,
58 | "SCHEMA_NOT_FOUND"
59 | );
60 | }
61 | }
62 |
63 | // Get schema information to help with table/column references
64 | try {
65 | const tables = await connector.getTables(schema);
66 |
67 | if (tables.length === 0) {
68 | const schemaInfo = schema ? `in schema '${schema}'` : "in the database";
69 | return formatPromptErrorResponse(
70 | `No tables found ${schemaInfo}. Please check your database connection or schema name.`,
71 | "NO_TABLES_FOUND"
72 | );
73 | }
74 |
75 | const tableSchemas = await Promise.all(
76 | tables.map(async (table) => {
77 | try {
78 | const columns = await connector.getTableSchema(table, schema);
79 | return {
80 | table,
81 | columns: columns.map((col) => ({
82 | name: col.column_name,
83 | type: col.data_type,
84 | })),
85 | };
86 | } catch (error) {
87 | // Skip tables we can't access
88 | return null;
89 | }
90 | })
91 | );
92 |
93 | // Filter out null entries (tables we couldn't access)
94 | const accessibleSchemas = tableSchemas.filter((schema) => schema !== null);
95 |
96 | if (accessibleSchemas.length === 0) {
97 | return formatPromptErrorResponse(
98 | `No accessible tables found. You may not have sufficient permissions to access table schemas.`,
99 | "NO_ACCESSIBLE_TABLES"
100 | );
101 | }
102 |
103 | // Generate a schema description for context
104 | const schemaContext =
105 | accessibleSchemas.length > 0
106 | ? `Available tables and their columns:\n${accessibleSchemas
107 | .map(
108 | (schema) =>
109 | `- ${schema!.table}: ${schema!.columns
110 | .map((col) => `${col.name} (${col.type})`)
111 | .join(", ")}`
112 | )
113 | .join("\n")}`
114 | : "No schema information available.";
115 |
116 | // Example queries for the given dialect to use as reference
117 | const dialectExamples: Record<SQLDialect, string[]> = {
118 | postgres: [
119 | "SELECT * FROM users WHERE created_at > NOW() - INTERVAL '1 day'",
120 | "SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.name HAVING COUNT(o.id) > 5",
121 | "SELECT product_name, price FROM products WHERE price > (SELECT AVG(price) FROM products)",
122 | ],
123 | sqlite: [
124 | "SELECT * FROM users WHERE created_at > datetime('now', '-1 day')",
125 | "SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.name HAVING COUNT(o.id) > 5",
126 | "SELECT product_name, price FROM products WHERE price > (SELECT AVG(price) FROM products)",
127 | ],
128 | mysql: [
129 | "SELECT * FROM users WHERE created_at > NOW() - INTERVAL 1 DAY",
130 | "SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.name HAVING COUNT(o.id) > 5",
131 | "SELECT product_name, price FROM products WHERE price > (SELECT AVG(price) FROM products)",
132 | ],
133 | mssql: [
134 | "SELECT * FROM users WHERE created_at > DATEADD(day, -1, GETDATE())",
135 | "SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.name HAVING COUNT(o.id) > 5",
136 | "SELECT product_name, price FROM products WHERE price > (SELECT AVG(price) FROM products)",
137 | ],
138 | ansi: [
139 | "SELECT * FROM users WHERE created_at > CURRENT_TIMESTAMP - INTERVAL '1' DAY",
140 | "SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.name HAVING COUNT(o.id) > 5",
141 | "SELECT product_name, price FROM products WHERE price > (SELECT AVG(price) FROM products)",
142 | ],
143 | };
144 |
145 | // Build a prompt that would help generate the SQL
146 | // In a real implementation, this would call an AI model
147 | const schemaInfo = schema ? `in schema '${schema}'` : "across all schemas";
148 | const prompt = `
149 | Generate a ${sqlDialect} SQL query based on this description: "${description}"
150 |
151 | ${schemaContext}
152 | Working ${schemaInfo}
153 |
154 | The query should:
155 | 1. Be written for ${sqlDialect} dialect
156 | 2. Use only the available tables and columns
157 | 3. Prioritize readability
158 | 4. Include appropriate comments
159 | 5. Be compatible with ${sqlDialect} syntax
160 | `;
161 |
162 | // In a real implementation, this would be the result from an AI model call
163 | // For this demo, we'll generate a simple SQL query based on the description
164 | let generatedSQL: string;
165 |
166 | // Very simple pattern matching for demo purposes
167 | // In a real implementation, this would use a language model
168 | if (description.toLowerCase().includes("count")) {
169 | const schemaPrefix = schema ? `-- Schema: ${schema}\n` : "";
170 | generatedSQL = `${schemaPrefix}-- Count query generated from: "${description}"
171 | SELECT COUNT(*) AS count
172 | FROM ${accessibleSchemas.length > 0 ? accessibleSchemas[0]!.table : "table_name"};`;
173 | } else if (
174 | description.toLowerCase().includes("average") ||
175 | description.toLowerCase().includes("avg")
176 | ) {
177 | const table = accessibleSchemas.length > 0 ? accessibleSchemas[0]!.table : "table_name";
178 | const numericColumn =
179 | accessibleSchemas.length > 0
180 | ? accessibleSchemas[0]!.columns.find((col) =>
181 | ["int", "numeric", "decimal", "float", "real", "double"].some((t) =>
182 | col.type.includes(t)
183 | )
184 | )?.name || "numeric_column"
185 | : "numeric_column";
186 |
187 | const schemaPrefix = schema ? `-- Schema: ${schema}\n` : "";
188 | generatedSQL = `${schemaPrefix}-- Average query generated from: "${description}"
189 | SELECT AVG(${numericColumn}) AS average
190 | FROM ${table};`;
191 | } else if (description.toLowerCase().includes("join")) {
192 | const schemaPrefix = schema ? `-- Schema: ${schema}\n` : "";
193 | generatedSQL = `${schemaPrefix}-- Join query generated from: "${description}"
194 | SELECT t1.*, t2.*
195 | FROM ${accessibleSchemas.length > 0 ? accessibleSchemas[0]?.table : "table1"} t1
196 | JOIN ${accessibleSchemas.length > 1 ? accessibleSchemas[1]?.table : "table2"} t2
197 | ON t1.id = t2.${accessibleSchemas.length > 0 ? accessibleSchemas[0]?.table : "table1"}_id;`;
198 | } else {
199 | // Default to a simple SELECT
200 | const table = accessibleSchemas.length > 0 ? accessibleSchemas[0]!.table : "table_name";
201 | const schemaPrefix = schema ? `-- Schema: ${schema}\n` : "";
202 | generatedSQL = `${schemaPrefix}-- Query generated from: "${description}"
203 | SELECT *
204 | FROM ${table}
205 | LIMIT 10;`;
206 | }
207 |
208 | // Return the generated SQL with explanations
209 | return formatPromptSuccessResponse(
210 | generatedSQL,
211 | // Add references to example queries that could help
212 | dialectExamples[sqlDialect]
213 | );
214 | } catch (error) {
215 | return formatPromptErrorResponse(
216 | `Error generating SQL query schema information: ${(error as Error).message}`,
217 | "SCHEMA_RETRIEVAL_ERROR"
218 | );
219 | }
220 | } catch (error) {
221 | return formatPromptErrorResponse(
222 | `Failed to generate SQL: ${(error as Error).message}`,
223 | "SQL_GENERATION_ERROR"
224 | );
225 | }
226 | }
227 |
```
--------------------------------------------------------------------------------
/src/connectors/__tests__/shared/integration-test-base.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { describe, it, expect, beforeAll, afterAll } from 'vitest';
2 | import type { Connector } from '../../interface.js';
3 |
4 | export interface DatabaseTestConfig {
5 | expectedSchemas: string[];
6 | expectedTables: string[];
7 | expectedTestSchemaTable?: string;
8 | testSchema?: string;
9 | supportsStoredProcedures?: boolean;
10 | expectedStoredProcedures?: string[];
11 | }
12 |
13 | export interface TestContainer {
14 | getConnectionUri(): string;
15 | stop(): Promise<void>;
16 | }
17 |
18 | /**
19 | * Base class for database integration tests that provides common test patterns
20 | */
21 | export abstract class IntegrationTestBase<TContainer extends TestContainer> {
22 | protected container!: TContainer;
23 | public connector!: Connector;
24 | protected connectionString!: string;
25 | public config: DatabaseTestConfig;
26 |
27 | constructor(config: DatabaseTestConfig) {
28 | this.config = config;
29 | }
30 |
31 | /**
32 | * Abstract methods that must be implemented by specific database test classes
33 | */
34 | abstract createContainer(): Promise<TContainer>;
35 | abstract createConnector(): Connector;
36 | abstract setupTestData(connector: Connector): Promise<void>;
37 |
38 | /**
39 | * Setup method to be called in beforeAll
40 | */
41 | async setup(): Promise<void> {
42 | console.log('Starting database container...');
43 |
44 | this.container = await this.createContainer();
45 | console.log('Container started, getting connection details...');
46 |
47 | this.connectionString = this.container.getConnectionUri();
48 | console.log('Connection URI:', this.connectionString);
49 |
50 | this.connector = this.createConnector();
51 | await this.connector.connect(this.connectionString);
52 | console.log('Connected to database');
53 |
54 | await this.setupTestData(this.connector);
55 | console.log('Test data setup complete');
56 | }
57 |
58 | /**
59 | * Cleanup method to be called in afterAll
60 | */
61 | async cleanup(): Promise<void> {
62 | if (this.connector) {
63 | await this.connector.disconnect();
64 | }
65 | if (this.container) {
66 | await this.container.stop();
67 | }
68 | }
69 |
70 | /**
71 | * Common test suite that can be reused across different database types
72 | */
73 | createTestSuite(suiteName: string): void {
74 | describe(suiteName, () => {
75 | beforeAll(async () => {
76 | await this.setup();
77 | }, 120000);
78 |
79 | afterAll(async () => {
80 | await this.cleanup();
81 | });
82 |
83 | this.createConnectionTests();
84 | this.createSchemaTests();
85 | this.createTableTests();
86 | this.createSQLExecutionTests();
87 |
88 | if (this.config.supportsStoredProcedures) {
89 | this.createStoredProcedureTests();
90 | }
91 |
92 | this.createErrorHandlingTests();
93 | });
94 | }
95 |
96 | createConnectionTests(): void {
97 | describe('Connection', () => {
98 | it('should connect successfully to database container', async () => {
99 | expect(this.connector).toBeDefined();
100 | });
101 |
102 | it('should parse DSN correctly', async () => {
103 | const sampleDSN = this.connector.dsnParser.getSampleDSN();
104 | expect(sampleDSN).toContain('://');
105 | expect(this.connector.dsnParser.isValidDSN(sampleDSN)).toBe(true);
106 | });
107 |
108 | it('should validate DSN format', () => {
109 | const sampleDSN = this.connector.dsnParser.getSampleDSN();
110 | expect(this.connector.dsnParser.isValidDSN(sampleDSN)).toBe(true);
111 | expect(this.connector.dsnParser.isValidDSN('invalid-dsn')).toBe(false);
112 | });
113 | });
114 | }
115 |
116 | createSchemaTests(): void {
117 | describe('Schema Operations', () => {
118 | it('should list schemas', async () => {
119 | const schemas = await this.connector.getSchemas();
120 | this.config.expectedSchemas.forEach(expectedSchema => {
121 | expect(schemas).toContain(expectedSchema);
122 | });
123 | });
124 |
125 | it('should list tables in default schema', async () => {
126 | const tables = await this.connector.getTables();
127 | this.config.expectedTables.forEach(expectedTable => {
128 | expect(tables).toContain(expectedTable);
129 | });
130 | });
131 |
132 | if (this.config.testSchema && this.config.expectedTestSchemaTable) {
133 | it('should list tables in specific schema', async () => {
134 | const tables = await this.connector.getTables(this.config.testSchema);
135 | expect(tables).toContain(this.config.expectedTestSchemaTable);
136 | });
137 | }
138 |
139 | it('should check if table exists', async () => {
140 | const firstTable = this.config.expectedTables[0];
141 | expect(await this.connector.tableExists(firstTable)).toBe(true);
142 | expect(await this.connector.tableExists('nonexistent_table')).toBe(false);
143 |
144 | if (this.config.testSchema && this.config.expectedTestSchemaTable) {
145 | expect(await this.connector.tableExists(this.config.expectedTestSchemaTable, this.config.testSchema)).toBe(true);
146 | expect(await this.connector.tableExists(this.config.expectedTestSchemaTable, 'public')).toBe(false);
147 | }
148 | });
149 | });
150 | }
151 |
152 | createTableTests(): void {
153 | describe('Table Schema Operations', () => {
154 | it('should get table schema for users table', async () => {
155 | const schema = await this.connector.getTableSchema('users');
156 | expect(schema.length).toBeGreaterThan(0);
157 |
158 | const idColumn = schema.find(col => col.column_name === 'id');
159 | expect(idColumn).toBeDefined();
160 | expect(idColumn?.is_nullable).toBe('NO');
161 |
162 | const nameColumn = schema.find(col => col.column_name === 'name');
163 | expect(nameColumn).toBeDefined();
164 | });
165 |
166 | it('should get table indexes', async () => {
167 | const indexes = await this.connector.getTableIndexes('users');
168 | expect(indexes.length).toBeGreaterThan(0);
169 |
170 | const primaryIndex = indexes.find(idx => idx.is_primary);
171 | expect(primaryIndex).toBeDefined();
172 | expect(primaryIndex?.column_names).toContain('id');
173 |
174 | // Some databases automatically create unique indexes, others handle unique constraints differently
175 | // We'll just verify we got at least the primary key index
176 | expect(indexes.length).toBeGreaterThanOrEqual(1);
177 | });
178 | });
179 | }
180 |
181 | createSQLExecutionTests(): void {
182 | describe('SQL Execution', () => {
183 | it('should execute simple SELECT query', async () => {
184 | const result = await this.connector.executeSQL('SELECT COUNT(*) as count FROM users', {});
185 | expect(result.rows).toHaveLength(1);
186 | expect(Number(result.rows[0].count)).toBeGreaterThanOrEqual(3);
187 | });
188 |
189 | it('should execute INSERT and SELECT', async () => {
190 | const insertResult = await this.connector.executeSQL(
191 | "INSERT INTO users (name, email, age) VALUES ('Test User', '[email protected]', 25)", {}
192 | );
193 | expect(insertResult).toBeDefined();
194 |
195 | const selectResult = await this.connector.executeSQL(
196 | "SELECT * FROM users WHERE email = '[email protected]'", {}
197 | );
198 | expect(selectResult.rows).toHaveLength(1);
199 | expect(selectResult.rows[0].name).toBe('Test User');
200 | expect(Number(selectResult.rows[0].age)).toBe(25);
201 | });
202 |
203 | it('should handle complex queries with joins', async () => {
204 | const result = await this.connector.executeSQL(`
205 | SELECT u.name, COUNT(o.id) as order_count
206 | FROM users u
207 | LEFT JOIN orders o ON u.id = o.user_id
208 | GROUP BY u.id, u.name
209 | HAVING COUNT(o.id) > 0
210 | ORDER BY order_count DESC
211 | `, {});
212 |
213 | expect(result.rows.length).toBeGreaterThan(0);
214 | expect(result.rows[0]).toHaveProperty('name');
215 | expect(result.rows[0]).toHaveProperty('order_count');
216 | });
217 | });
218 | }
219 |
220 | createStoredProcedureTests(): void {
221 | describe('Stored Procedures', () => {
222 | it('should list stored procedures', async () => {
223 | const procedures = await this.connector.getStoredProcedures();
224 | if (this.config.expectedStoredProcedures) {
225 | this.config.expectedStoredProcedures.forEach(expectedProc => {
226 | expect(procedures).toContain(expectedProc);
227 | });
228 | }
229 | });
230 |
231 | if (this.config.expectedStoredProcedures?.length) {
232 | it('should get stored procedure details', async () => {
233 | const procedureName = this.config.expectedStoredProcedures[0];
234 | const procedure = await this.connector.getStoredProcedureDetail(procedureName);
235 | expect(procedure.procedure_name).toBe(procedureName);
236 | expect(procedure.procedure_type).toMatch(/function|procedure/);
237 | });
238 | }
239 | });
240 | }
241 |
242 | createErrorHandlingTests(): void {
243 | describe('Error Handling', () => {
244 | it('should handle invalid SQL gracefully', async () => {
245 | await expect(
246 | this.connector.executeSQL('SELECT * FROM nonexistent_table', {})
247 | ).rejects.toThrow();
248 | });
249 |
250 | it('should handle connection errors', async () => {
251 | const newConnector = this.createConnector();
252 | await expect(
253 | newConnector.executeSQL('SELECT 1', {})
254 | ).rejects.toThrow(/Not connected to.*database/);
255 | });
256 |
257 | it('should handle invalid table schema requests', async () => {
258 | const result = await this.connector.getTableSchema('nonexistent_table');
259 | expect(Array.isArray(result)).toBe(true);
260 | expect(result.length).toBe(0);
261 | });
262 | });
263 | }
264 | }
```
--------------------------------------------------------------------------------
/src/connectors/__tests__/postgres-ssh.integration.test.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { describe, it, expect, beforeAll, afterAll, vi } from 'vitest';
2 | import { PostgreSqlContainer, StartedPostgreSqlContainer } from '@testcontainers/postgresql';
3 | import { PostgresConnector } from '../postgres/index.js';
4 | import { ConnectorManager } from '../manager.js';
5 | import { ConnectorRegistry } from '../interface.js';
6 | import { SSHTunnel } from '../../utils/ssh-tunnel.js';
7 | import type { SSHTunnelConfig } from '../../types/ssh.js';
8 | import * as sshConfigParser from '../../utils/ssh-config-parser.js';
9 |
10 | describe('PostgreSQL SSH Tunnel Simple Integration Tests', () => {
11 | let postgresContainer: StartedPostgreSqlContainer;
12 |
13 | beforeAll(async () => {
14 | // Register PostgreSQL connector
15 | ConnectorRegistry.register(new PostgresConnector());
16 |
17 | // Start PostgreSQL container
18 | postgresContainer = await new PostgreSqlContainer('postgres:15-alpine')
19 | .withDatabase('testdb')
20 | .withUsername('testuser')
21 | .withPassword('testpass')
22 | .start();
23 | }, 60000); // 1 minute timeout for container startup
24 |
25 | afterAll(async () => {
26 | await postgresContainer?.stop();
27 | });
28 |
29 | describe('SSH Tunnel Basic Functionality', () => {
30 | it('should establish SSH tunnel and connect to local port', async () => {
31 | // For this test, we'll create a mock SSH tunnel that just forwards to the same port
32 | // This tests the tunnel establishment logic without needing a real SSH server
33 | const tunnel = new SSHTunnel();
34 |
35 | // Test that the tunnel correctly reports its state
36 | expect(tunnel.getIsConnected()).toBe(false);
37 | expect(tunnel.getTunnelInfo()).toBeNull();
38 | });
39 |
40 | it('should parse DSN correctly when SSH tunnel is configured', async () => {
41 | const manager = new ConnectorManager();
42 |
43 | // Test DSN parsing with getDefaultPort
44 | const testCases = [
45 | { dsn: 'postgres://user:pass@host:5432/db', expectedPort: 5432 },
46 | { dsn: 'mysql://user:pass@host:3306/db', expectedPort: 3306 },
47 | { dsn: 'mariadb://user:pass@host:3306/db', expectedPort: 3306 },
48 | { dsn: 'sqlserver://user:pass@host:1433/db', expectedPort: 1433 },
49 | ];
50 |
51 | for (const testCase of testCases) {
52 | // Access private method through reflection for testing
53 | const port = (manager as any).getDefaultPort(testCase.dsn);
54 | expect(port).toBe(testCase.expectedPort);
55 | }
56 | });
57 |
58 | it('should handle connection without SSH tunnel', async () => {
59 | const manager = new ConnectorManager();
60 |
61 | // Make sure no SSH config is set
62 | delete process.env.SSH_HOST;
63 |
64 | const dsn = postgresContainer.getConnectionUri();
65 |
66 | await manager.connectWithDSN(dsn);
67 |
68 | // Test that connection works
69 | const connector = manager.getConnector();
70 | const result = await connector.executeSQL('SELECT 1 as test', {});
71 | expect(result.rows).toHaveLength(1);
72 | expect(result.rows[0].test).toBe(1);
73 |
74 | await manager.disconnect();
75 | });
76 |
77 | it('should fail gracefully when SSH config is invalid', async () => {
78 | const manager = new ConnectorManager();
79 |
80 | // Set invalid SSH config (missing required fields)
81 | process.env.SSH_HOST = 'example.com';
82 | // Missing SSH_USER
83 |
84 | try {
85 | const dsn = postgresContainer.getConnectionUri();
86 | await expect(manager.connectWithDSN(dsn)).rejects.toThrow(/SSH tunnel configuration requires/);
87 | } finally {
88 | delete process.env.SSH_HOST;
89 | }
90 | });
91 |
92 | it('should validate SSH authentication method', async () => {
93 | const manager = new ConnectorManager();
94 |
95 | // Set SSH config without authentication method
96 | process.env.SSH_HOST = 'example.com';
97 | process.env.SSH_USER = 'testuser';
98 | // Missing both SSH_PASSWORD and SSH_KEY
99 |
100 | try {
101 | const dsn = postgresContainer.getConnectionUri();
102 | await expect(manager.connectWithDSN(dsn)).rejects.toThrow(/SSH tunnel configuration requires either/);
103 | } finally {
104 | delete process.env.SSH_HOST;
105 | delete process.env.SSH_USER;
106 | }
107 | });
108 |
109 | it('should handle SSH config file resolution', async () => {
110 | const manager = new ConnectorManager();
111 |
112 | // Mock the SSH config parser functions
113 | const mockParseSSHConfig = vi.spyOn(sshConfigParser, 'parseSSHConfig');
114 | const mockLooksLikeSSHAlias = vi.spyOn(sshConfigParser, 'looksLikeSSHAlias');
115 |
116 | // Spy on the SSH tunnel establish method to verify the config values
117 | const mockSSHTunnelEstablish = vi.spyOn(SSHTunnel.prototype, 'establish');
118 |
119 | try {
120 | // Configure mocks to simulate SSH config file lookup with specific values
121 | mockLooksLikeSSHAlias.mockReturnValue(true);
122 | mockParseSSHConfig.mockReturnValue({
123 | host: 'bastion.example.com',
124 | username: 'sshuser',
125 | port: 2222,
126 | privateKey: '/home/user/.ssh/id_rsa'
127 | });
128 |
129 | // Mock SSH tunnel establish to capture the config and prevent actual connection
130 | mockSSHTunnelEstablish.mockRejectedValue(new Error('SSH connection failed (expected in test)'));
131 |
132 | // Set SSH host alias (would normally come from command line)
133 | process.env.SSH_HOST = 'mybastion';
134 |
135 | const dsn = postgresContainer.getConnectionUri();
136 |
137 | // This should fail during SSH connection (expected), but we can verify the config parsing
138 | await expect(manager.connectWithDSN(dsn)).rejects.toThrow();
139 |
140 | // Verify that SSH config parsing functions were called correctly
141 | expect(mockLooksLikeSSHAlias).toHaveBeenCalledWith('mybastion');
142 | expect(mockParseSSHConfig).toHaveBeenCalledWith('mybastion', expect.stringContaining('.ssh/config'));
143 |
144 | // Verify that SSH tunnel was attempted with the correct config values from SSH config
145 | expect(mockSSHTunnelEstablish).toHaveBeenCalledTimes(1);
146 | const sshTunnelCall = mockSSHTunnelEstablish.mock.calls[0];
147 | const [sshConfig, tunnelOptions] = sshTunnelCall;
148 |
149 | // Debug: Log the actual values being passed (for verification)
150 | // SSH Config should contain the values from our mocked SSH config file
151 | // Tunnel Options should contain database connection details from the container DSN
152 |
153 | // Verify SSH config values were properly resolved from the SSH config file
154 | expect(sshConfig).toMatchObject({
155 | host: 'bastion.example.com', // Should use HostName from SSH config
156 | username: 'sshuser', // Should use User from SSH config
157 | port: 2222, // Should use Port from SSH config
158 | privateKey: '/home/user/.ssh/id_rsa' // Should use IdentityFile from SSH config
159 | });
160 |
161 | // Verify tunnel options are correctly set up for the database connection
162 | expect(tunnelOptions).toMatchObject({
163 | targetHost: expect.any(String), // Database host from DSN
164 | targetPort: expect.any(Number) // Database port from DSN
165 | });
166 |
167 | // The localPort might be undefined for dynamic allocation, so check separately if it exists
168 | if (tunnelOptions.localPort !== undefined) {
169 | expect(typeof tunnelOptions.localPort).toBe('number');
170 | }
171 |
172 | // Verify that the target database details from the DSN are preserved
173 | const originalDsnUrl = new URL(dsn);
174 | expect(tunnelOptions.targetHost).toBe(originalDsnUrl.hostname);
175 | expect(tunnelOptions.targetPort).toBe(parseInt(originalDsnUrl.port));
176 |
177 | } finally {
178 | // Clean up
179 | delete process.env.SSH_HOST;
180 | mockParseSSHConfig.mockRestore();
181 | mockLooksLikeSSHAlias.mockRestore();
182 | mockSSHTunnelEstablish.mockRestore();
183 | }
184 | });
185 |
186 | it('should skip SSH config lookup for direct hostnames', async () => {
187 | const manager = new ConnectorManager();
188 |
189 | // Mock the SSH config parser functions
190 | const mockParseSSHConfig = vi.spyOn(sshConfigParser, 'parseSSHConfig');
191 | const mockLooksLikeSSHAlias = vi.spyOn(sshConfigParser, 'looksLikeSSHAlias');
192 |
193 | try {
194 | // Configure mocks - direct hostname should not trigger SSH config lookup
195 | mockLooksLikeSSHAlias.mockReturnValue(false);
196 |
197 | // Set a direct hostname with required SSH credentials
198 | process.env.SSH_HOST = 'ssh.example.com';
199 | process.env.SSH_USER = 'sshuser';
200 | process.env.SSH_PASSWORD = 'sshpass';
201 |
202 | const dsn = postgresContainer.getConnectionUri();
203 |
204 | // This should fail during actual SSH connection, but we can verify the parsing behavior
205 | await expect(manager.connectWithDSN(dsn)).rejects.toThrow();
206 |
207 | // Verify that SSH config parsing was checked but not executed
208 | expect(mockLooksLikeSSHAlias).toHaveBeenCalledWith('ssh.example.com');
209 | expect(mockParseSSHConfig).not.toHaveBeenCalled();
210 |
211 | } finally {
212 | // Clean up
213 | delete process.env.SSH_HOST;
214 | delete process.env.SSH_USER;
215 | delete process.env.SSH_PASSWORD;
216 | mockParseSSHConfig.mockRestore();
217 | mockLooksLikeSSHAlias.mockRestore();
218 | }
219 | });
220 | });
221 | });
```
--------------------------------------------------------------------------------
/src/tools/__tests__/execute-sql.test.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { describe, it, expect, vi, beforeEach, afterEach } from 'vitest';
2 | import { executeSqlToolHandler } from '../execute-sql.js';
3 | import { ConnectorManager } from '../../connectors/manager.js';
4 | import { isReadOnlyMode } from '../../config/env.js';
5 | import type { Connector, ConnectorType, SQLResult } from '../../connectors/interface.js';
6 |
7 | // Mock dependencies
8 | vi.mock('../../connectors/manager.js');
9 | vi.mock('../../config/env.js');
10 |
11 | // Mock connector for testing
12 | const createMockConnector = (id: ConnectorType = 'sqlite'): Connector => ({
13 | id,
14 | name: 'Mock Connector',
15 | dsnParser: {} as any,
16 | connect: vi.fn(),
17 | disconnect: vi.fn(),
18 | getSchemas: vi.fn(),
19 | getTables: vi.fn(),
20 | tableExists: vi.fn(),
21 | getTableSchema: vi.fn(),
22 | getTableIndexes: vi.fn(),
23 | getStoredProcedures: vi.fn(),
24 | getStoredProcedureDetail: vi.fn(),
25 | executeSQL: vi.fn(),
26 | });
27 |
28 | // Helper function to parse tool response
29 | const parseToolResponse = (response: any) => {
30 | return JSON.parse(response.content[0].text);
31 | };
32 |
33 | describe('execute-sql tool', () => {
34 | let mockConnector: Connector;
35 | const mockGetCurrentConnector = vi.mocked(ConnectorManager.getCurrentConnector);
36 | const mockGetCurrentExecuteOptions = vi.mocked(ConnectorManager.getCurrentExecuteOptions);
37 | const mockIsReadOnlyMode = vi.mocked(isReadOnlyMode);
38 |
39 | beforeEach(() => {
40 | mockConnector = createMockConnector('sqlite');
41 | mockGetCurrentConnector.mockReturnValue(mockConnector);
42 | mockGetCurrentExecuteOptions.mockReturnValue({});
43 | mockIsReadOnlyMode.mockReturnValue(false);
44 | });
45 |
46 | afterEach(() => {
47 | vi.clearAllMocks();
48 | });
49 |
50 | describe('single statement execution', () => {
51 | it('should execute a single SELECT statement successfully', async () => {
52 | const mockResult: SQLResult = { rows: [{ id: 1, name: 'test' }] };
53 | vi.mocked(mockConnector.executeSQL).mockResolvedValue(mockResult);
54 |
55 | const result = await executeSqlToolHandler({ sql: 'SELECT * FROM users' }, null);
56 | const parsedResult = parseToolResponse(result);
57 |
58 | expect(parsedResult.success).toBe(true);
59 | expect(parsedResult.data.rows).toEqual([{ id: 1, name: 'test' }]);
60 | expect(parsedResult.data.count).toBe(1);
61 | expect(mockConnector.executeSQL).toHaveBeenCalledWith('SELECT * FROM users', {});
62 | });
63 |
64 | it('should handle execution errors', async () => {
65 | vi.mocked(mockConnector.executeSQL).mockRejectedValue(new Error('Database error'));
66 |
67 | const result = await executeSqlToolHandler({ sql: 'SELECT * FROM invalid_table' }, null);
68 |
69 | expect(result.isError).toBe(true);
70 | const parsedResult = parseToolResponse(result);
71 | expect(parsedResult.success).toBe(false);
72 | expect(parsedResult.error).toBe('Database error');
73 | expect(parsedResult.code).toBe('EXECUTION_ERROR');
74 | });
75 | });
76 |
77 | describe('multi-statement execution', () => {
78 | it('should pass multi-statement SQL directly to connector', async () => {
79 | const mockResult: SQLResult = { rows: [{ id: 1 }] };
80 | vi.mocked(mockConnector.executeSQL).mockResolvedValue(mockResult);
81 |
82 | const sql = 'SELECT * FROM users; SELECT * FROM roles;';
83 | const result = await executeSqlToolHandler({ sql }, null);
84 | const parsedResult = parseToolResponse(result);
85 |
86 | expect(parsedResult.success).toBe(true);
87 | expect(mockConnector.executeSQL).toHaveBeenCalledWith(sql, {});
88 | });
89 | });
90 |
91 | describe('read-only mode validation', () => {
92 | beforeEach(() => {
93 | mockIsReadOnlyMode.mockReturnValue(true);
94 | });
95 |
96 | it('should allow single SELECT statement in read-only mode', async () => {
97 | const mockResult: SQLResult = { rows: [{ id: 1 }] };
98 | vi.mocked(mockConnector.executeSQL).mockResolvedValue(mockResult);
99 |
100 | const result = await executeSqlToolHandler({ sql: 'SELECT * FROM users' }, null);
101 | const parsedResult = parseToolResponse(result);
102 |
103 | expect(parsedResult.success).toBe(true);
104 | expect(mockConnector.executeSQL).toHaveBeenCalled();
105 | });
106 |
107 | it('should allow multiple read-only statements in read-only mode', async () => {
108 | const mockResult: SQLResult = { rows: [] };
109 | vi.mocked(mockConnector.executeSQL).mockResolvedValue(mockResult);
110 |
111 | const sql = 'SELECT * FROM users; SELECT * FROM roles;';
112 | const result = await executeSqlToolHandler({ sql }, null);
113 | const parsedResult = parseToolResponse(result);
114 |
115 | expect(parsedResult.success).toBe(true);
116 | expect(mockConnector.executeSQL).toHaveBeenCalledWith(sql, {});
117 | });
118 |
119 | it('should reject single INSERT statement in read-only mode', async () => {
120 | const result = await executeSqlToolHandler({ sql: "INSERT INTO users (name) VALUES ('test')" }, null);
121 |
122 | expect(result.isError).toBe(true);
123 | const parsedResult = parseToolResponse(result);
124 | expect(parsedResult.success).toBe(false);
125 | expect(parsedResult.error).toContain('Read-only mode is enabled');
126 | expect(parsedResult.code).toBe('READONLY_VIOLATION');
127 | expect(mockConnector.executeSQL).not.toHaveBeenCalled();
128 | });
129 |
130 | it('should reject multi-statement with any write operation in read-only mode', async () => {
131 | const sql = "SELECT * FROM users; INSERT INTO users (name) VALUES ('test'); SELECT COUNT(*) FROM users;";
132 | const result = await executeSqlToolHandler({ sql }, null);
133 |
134 | expect(result.isError).toBe(true);
135 | const parsedResult = parseToolResponse(result);
136 | expect(parsedResult.success).toBe(false);
137 | expect(parsedResult.error).toContain('Read-only mode is enabled');
138 | expect(parsedResult.code).toBe('READONLY_VIOLATION');
139 | expect(mockConnector.executeSQL).not.toHaveBeenCalled();
140 | });
141 |
142 | });
143 |
144 | describe('SQL comments handling', () => {
145 | it('should allow SELECT with single-line comment in read-only mode', async () => {
146 | mockIsReadOnlyMode.mockReturnValue(true);
147 | const mockResult: SQLResult = { rows: [{ id: 1 }] };
148 | vi.mocked(mockConnector.executeSQL).mockResolvedValue(mockResult);
149 |
150 | const sql = '-- Fetch active users\nSELECT * FROM users WHERE active = TRUE';
151 | const result = await executeSqlToolHandler({ sql }, null);
152 | const parsedResult = parseToolResponse(result);
153 |
154 | expect(parsedResult.success).toBe(true);
155 | expect(mockConnector.executeSQL).toHaveBeenCalledWith(sql, {});
156 | });
157 |
158 | it('should allow SELECT with multi-line comment in read-only mode', async () => {
159 | mockIsReadOnlyMode.mockReturnValue(true);
160 | const mockResult: SQLResult = { rows: [] };
161 | vi.mocked(mockConnector.executeSQL).mockResolvedValue(mockResult);
162 |
163 | const sql = '/* This query fetches\n all products */\nSELECT * FROM products';
164 | const result = await executeSqlToolHandler({ sql }, null);
165 | const parsedResult = parseToolResponse(result);
166 |
167 | expect(parsedResult.success).toBe(true);
168 | expect(mockConnector.executeSQL).toHaveBeenCalledWith(sql, {});
169 | });
170 |
171 | it('should handle multiple statements with comments in read-only mode', async () => {
172 | mockIsReadOnlyMode.mockReturnValue(true);
173 | const mockResult: SQLResult = { rows: [] };
174 | vi.mocked(mockConnector.executeSQL).mockResolvedValue(mockResult);
175 |
176 | const sql = '-- First query\nSELECT * FROM users;\n/* Second query */\nSELECT * FROM roles;';
177 | const result = await executeSqlToolHandler({ sql }, null);
178 | const parsedResult = parseToolResponse(result);
179 |
180 | expect(parsedResult.success).toBe(true);
181 | expect(mockConnector.executeSQL).toHaveBeenCalledWith(sql, {});
182 | });
183 |
184 | it('should reject INSERT with comment in read-only mode', async () => {
185 | mockIsReadOnlyMode.mockReturnValue(true);
186 |
187 | const sql = '-- Insert new user\nINSERT INTO users (name) VALUES (\'test\')';
188 | const result = await executeSqlToolHandler({ sql }, null);
189 |
190 | expect(result.isError).toBe(true);
191 | const parsedResult = parseToolResponse(result);
192 | expect(parsedResult.success).toBe(false);
193 | expect(parsedResult.code).toBe('READONLY_VIOLATION');
194 | expect(mockConnector.executeSQL).not.toHaveBeenCalled();
195 | });
196 |
197 | it('should handle query that is only comments as read-only', async () => {
198 | mockIsReadOnlyMode.mockReturnValue(true);
199 | const mockResult: SQLResult = { rows: [] };
200 | vi.mocked(mockConnector.executeSQL).mockResolvedValue(mockResult);
201 |
202 | const sql = '-- Just a comment\n/* Another comment */';
203 | const result = await executeSqlToolHandler({ sql }, null);
204 | const parsedResult = parseToolResponse(result);
205 |
206 | expect(parsedResult.success).toBe(true);
207 | expect(mockConnector.executeSQL).toHaveBeenCalledWith(sql, {});
208 | });
209 |
210 | it('should handle inline comments correctly', async () => {
211 | mockIsReadOnlyMode.mockReturnValue(true);
212 | const mockResult: SQLResult = { rows: [] };
213 | vi.mocked(mockConnector.executeSQL).mockResolvedValue(mockResult);
214 |
215 | const sql = 'SELECT id, -- user id\n name -- user name\nFROM users';
216 | const result = await executeSqlToolHandler({ sql }, null);
217 | const parsedResult = parseToolResponse(result);
218 |
219 | expect(parsedResult.success).toBe(true);
220 | expect(mockConnector.executeSQL).toHaveBeenCalledWith(sql, {});
221 | });
222 | });
223 |
224 |
225 | describe('edge cases', () => {
226 | it('should handle empty SQL string', async () => {
227 | const mockResult: SQLResult = { rows: [] };
228 | vi.mocked(mockConnector.executeSQL).mockResolvedValue(mockResult);
229 |
230 | const result = await executeSqlToolHandler({ sql: '' }, null);
231 | const parsedResult = parseToolResponse(result);
232 |
233 | expect(parsedResult.success).toBe(true);
234 | expect(mockConnector.executeSQL).toHaveBeenCalledWith('', {});
235 | });
236 |
237 | it('should handle SQL with only semicolons and whitespace', async () => {
238 | const mockResult: SQLResult = { rows: [] };
239 | vi.mocked(mockConnector.executeSQL).mockResolvedValue(mockResult);
240 |
241 | const result = await executeSqlToolHandler({ sql: ' ; ; ; ' }, null);
242 | const parsedResult = parseToolResponse(result);
243 |
244 | expect(parsedResult.success).toBe(true);
245 | expect(mockConnector.executeSQL).toHaveBeenCalledWith(' ; ; ; ', {});
246 | });
247 | });
248 | });
```
--------------------------------------------------------------------------------
/src/config/__tests__/env.test.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { describe, it, expect, beforeEach, afterEach } from 'vitest';
2 | import { buildDSNFromEnvParams, resolveDSN, resolveId } from '../env.js';
3 |
4 | describe('Environment Configuration Tests', () => {
5 | // Store original env values to restore after tests
6 | const originalEnv = { ...process.env };
7 |
8 | beforeEach(() => {
9 | // Clear relevant environment variables before each test
10 | delete process.env.DB_TYPE;
11 | delete process.env.DB_HOST;
12 | delete process.env.DB_PORT;
13 | delete process.env.DB_USER;
14 | delete process.env.DB_PASSWORD;
15 | delete process.env.DB_NAME;
16 | delete process.env.DSN;
17 | delete process.env.ID;
18 | });
19 |
20 | afterEach(() => {
21 | // Restore original environment
22 | process.env = { ...originalEnv };
23 | });
24 |
25 | describe('buildDSNFromEnvParams', () => {
26 | it('should build PostgreSQL DSN with all parameters', () => {
27 | process.env.DB_TYPE = 'postgres';
28 | process.env.DB_HOST = 'localhost';
29 | process.env.DB_PORT = '5432';
30 | process.env.DB_USER = 'testuser';
31 | process.env.DB_PASSWORD = 'testpass';
32 | process.env.DB_NAME = 'testdb';
33 |
34 | const result = buildDSNFromEnvParams();
35 |
36 | expect(result).toEqual({
37 | dsn: 'postgres://testuser:testpass@localhost:5432/testdb',
38 | source: 'individual environment variables'
39 | });
40 | });
41 |
42 | it('should build MySQL DSN with default port when port not specified', () => {
43 | process.env.DB_TYPE = 'mysql';
44 | process.env.DB_HOST = 'mysql.example.com';
45 | process.env.DB_USER = 'admin';
46 | process.env.DB_PASSWORD = 'secret';
47 | process.env.DB_NAME = 'myapp';
48 |
49 | const result = buildDSNFromEnvParams();
50 |
51 | expect(result).toEqual({
52 | dsn: 'mysql://admin:[email protected]:3306/myapp',
53 | source: 'individual environment variables'
54 | });
55 | });
56 |
57 | it('should build MariaDB DSN with default port', () => {
58 | process.env.DB_TYPE = 'mariadb';
59 | process.env.DB_HOST = 'mariadb.example.com';
60 | process.env.DB_USER = 'user';
61 | process.env.DB_PASSWORD = 'pass';
62 | process.env.DB_NAME = 'database';
63 |
64 | const result = buildDSNFromEnvParams();
65 |
66 | expect(result).toEqual({
67 | dsn: 'mariadb://user:[email protected]:3306/database',
68 | source: 'individual environment variables'
69 | });
70 | });
71 |
72 | it('should build SQL Server DSN with default port', () => {
73 | process.env.DB_TYPE = 'sqlserver';
74 | process.env.DB_HOST = 'sqlserver.example.com';
75 | process.env.DB_USER = 'sa';
76 | process.env.DB_PASSWORD = 'strongpass';
77 | process.env.DB_NAME = 'master';
78 |
79 | const result = buildDSNFromEnvParams();
80 |
81 | expect(result).toEqual({
82 | dsn: 'sqlserver://sa:[email protected]:1433/master',
83 | source: 'individual environment variables'
84 | });
85 | });
86 |
87 | it('should build SQLite DSN with only DB_TYPE and DB_NAME', () => {
88 | process.env.DB_TYPE = 'sqlite';
89 | process.env.DB_NAME = '/path/to/database.db';
90 |
91 | const result = buildDSNFromEnvParams();
92 |
93 | expect(result).toEqual({
94 | dsn: 'sqlite:////path/to/database.db',
95 | source: 'individual environment variables'
96 | });
97 | });
98 |
99 | it('should handle postgresql type and normalize to postgres protocol', () => {
100 | process.env.DB_TYPE = 'postgresql';
101 | process.env.DB_HOST = 'localhost';
102 | process.env.DB_USER = 'user';
103 | process.env.DB_PASSWORD = 'pass';
104 | process.env.DB_NAME = 'db';
105 |
106 | const result = buildDSNFromEnvParams();
107 |
108 | expect(result?.dsn).toBe('postgres://user:pass@localhost:5432/db');
109 | });
110 |
111 | it('should properly encode special characters in password', () => {
112 | process.env.DB_TYPE = 'postgres';
113 | process.env.DB_HOST = 'localhost';
114 | process.env.DB_USER = 'user';
115 | process.env.DB_PASSWORD = 'test@pass:with/special#chars&more=special';
116 | process.env.DB_NAME = 'db';
117 |
118 | const result = buildDSNFromEnvParams();
119 |
120 | expect(result?.dsn).toBe(
121 | 'postgres://user:test%40pass%3Awith%2Fspecial%23chars%26more%3Dspecial@localhost:5432/db'
122 | );
123 | });
124 |
125 | it('should properly encode special characters in username', () => {
126 | process.env.DB_TYPE = 'postgres';
127 | process.env.DB_HOST = 'localhost';
128 | process.env.DB_USER = '[email protected]';
129 | process.env.DB_PASSWORD = 'pass';
130 | process.env.DB_NAME = 'db';
131 |
132 | const result = buildDSNFromEnvParams();
133 |
134 | expect(result?.dsn).toBe(
135 | 'postgres://user%40domain.com:pass@localhost:5432/db'
136 | );
137 | });
138 |
139 | it('should properly encode special characters in database name', () => {
140 | process.env.DB_TYPE = 'postgres';
141 | process.env.DB_HOST = 'localhost';
142 | process.env.DB_USER = 'user';
143 | process.env.DB_PASSWORD = 'pass';
144 | process.env.DB_NAME = 'my-db@test';
145 |
146 | const result = buildDSNFromEnvParams();
147 |
148 | expect(result?.dsn).toBe(
149 | 'postgres://user:pass@localhost:5432/my-db%40test'
150 | );
151 | });
152 |
153 | it('should handle SQLite with special characters in file path', () => {
154 | process.env.DB_TYPE = 'sqlite';
155 | process.env.DB_NAME = '/tmp/test_db@#$.db';
156 |
157 | const result = buildDSNFromEnvParams();
158 |
159 | expect(result).toEqual({
160 | dsn: 'sqlite:////tmp/test_db@#$.db',
161 | source: 'individual environment variables'
162 | });
163 | });
164 |
165 | it('should return null when required parameters are missing for non-SQLite databases', () => {
166 | process.env.DB_TYPE = 'postgres';
167 | process.env.DB_HOST = 'localhost';
168 | // Missing DB_USER, DB_PASSWORD, DB_NAME
169 |
170 | const result = buildDSNFromEnvParams();
171 |
172 | expect(result).toBeNull();
173 | });
174 |
175 | it('should return null when DB_TYPE is missing', () => {
176 | process.env.DB_HOST = 'localhost';
177 | process.env.DB_USER = 'user';
178 | process.env.DB_PASSWORD = 'pass';
179 | process.env.DB_NAME = 'db';
180 |
181 | const result = buildDSNFromEnvParams();
182 |
183 | expect(result).toBeNull();
184 | });
185 |
186 | it('should return null when SQLite is missing DB_NAME', () => {
187 | process.env.DB_TYPE = 'sqlite';
188 | // Missing DB_NAME
189 |
190 | const result = buildDSNFromEnvParams();
191 |
192 | expect(result).toBeNull();
193 | });
194 |
195 | it('should throw error for unsupported database type', () => {
196 | process.env.DB_TYPE = 'oracle';
197 | process.env.DB_HOST = 'localhost';
198 | process.env.DB_USER = 'user';
199 | process.env.DB_PASSWORD = 'pass';
200 | process.env.DB_NAME = 'db';
201 |
202 | expect(() => buildDSNFromEnvParams()).toThrow(
203 | 'Unsupported DB_TYPE: oracle. Supported types: postgres, postgresql, mysql, mariadb, sqlserver, sqlite'
204 | );
205 | });
206 |
207 | it('should use custom port when provided', () => {
208 | process.env.DB_TYPE = 'postgres';
209 | process.env.DB_HOST = 'localhost';
210 | process.env.DB_PORT = '9999';
211 | process.env.DB_USER = 'user';
212 | process.env.DB_PASSWORD = 'pass';
213 | process.env.DB_NAME = 'db';
214 |
215 | const result = buildDSNFromEnvParams();
216 |
217 | expect(result?.dsn).toBe('postgres://user:pass@localhost:9999/db');
218 | });
219 |
220 | it('should return null for empty password (required field)', () => {
221 | process.env.DB_TYPE = 'postgres';
222 | process.env.DB_HOST = 'localhost';
223 | process.env.DB_USER = 'user';
224 | process.env.DB_PASSWORD = '';
225 | process.env.DB_NAME = 'db';
226 |
227 | const result = buildDSNFromEnvParams();
228 |
229 | expect(result).toBeNull();
230 | });
231 | });
232 |
233 | describe('resolveDSN integration with individual parameters', () => {
234 | it('should use DSN when both DSN and individual parameters are provided', () => {
235 | process.env.DSN = 'postgres://direct:dsn@localhost:5432/directdb';
236 | process.env.DB_TYPE = 'mysql';
237 | process.env.DB_HOST = 'localhost';
238 | process.env.DB_USER = 'user';
239 | process.env.DB_PASSWORD = 'pass';
240 | process.env.DB_NAME = 'db';
241 |
242 | const result = resolveDSN();
243 |
244 | expect(result).toEqual({
245 | dsn: 'postgres://direct:dsn@localhost:5432/directdb',
246 | source: 'environment variable'
247 | });
248 | });
249 |
250 | it('should fall back to individual parameters when DSN is not provided', () => {
251 | process.env.DB_TYPE = 'postgres';
252 | process.env.DB_HOST = 'localhost';
253 | process.env.DB_USER = 'user';
254 | process.env.DB_PASSWORD = 'pass';
255 | process.env.DB_NAME = 'db';
256 |
257 | const result = resolveDSN();
258 |
259 | expect(result).toEqual({
260 | dsn: 'postgres://user:pass@localhost:5432/db',
261 | source: 'individual environment variables'
262 | });
263 | });
264 |
265 | it('should return null when neither DSN nor complete individual parameters are provided', () => {
266 | process.env.DB_TYPE = 'postgres';
267 | process.env.DB_HOST = 'localhost';
268 | // Missing required parameters
269 |
270 | const result = resolveDSN();
271 |
272 | expect(result).toBeNull();
273 | });
274 |
275 | it('should handle SQLite individual parameters correctly', () => {
276 | process.env.DB_TYPE = 'sqlite';
277 | process.env.DB_NAME = ':memory:';
278 |
279 | const result = resolveDSN();
280 |
281 | expect(result).toEqual({
282 | dsn: 'sqlite:///:memory:',
283 | source: 'individual environment variables'
284 | });
285 | });
286 | });
287 |
288 | describe('edge cases and complex scenarios', () => {
289 | it('should handle password with all special URL characters', () => {
290 | process.env.DB_TYPE = 'postgres';
291 | process.env.DB_HOST = 'localhost';
292 | process.env.DB_USER = 'user';
293 | process.env.DB_PASSWORD = '!@#$%^&*()+={}[]|\\:";\'<>?,./~`';
294 | process.env.DB_NAME = 'db';
295 |
296 | const result = buildDSNFromEnvParams();
297 |
298 | // Verify it builds without error and contains encoded characters
299 | expect(result).toBeTruthy();
300 | // Note: encodeURIComponent doesn't encode ! so it remains as !
301 | expect(result?.dsn).toContain('!'); // ! is not encoded
302 | expect(result?.dsn).toContain('%40'); // @
303 | expect(result?.dsn).toContain('%23'); // #
304 | expect(result?.dsn).toContain('%24'); // $
305 | expect(result?.dsn).toContain('%25'); // %
306 | });
307 |
308 | it('should handle database names with Unicode characters', () => {
309 | process.env.DB_TYPE = 'postgres';
310 | process.env.DB_HOST = 'localhost';
311 | process.env.DB_USER = 'user';
312 | process.env.DB_PASSWORD = 'pass';
313 | process.env.DB_NAME = 'тест_база_данных'; // Cyrillic characters
314 |
315 | const result = buildDSNFromEnvParams();
316 |
317 | expect(result).toBeTruthy();
318 | expect(result?.dsn).toContain('%D1%82%D0%B5%D1%81%D1%82'); // Encoded Cyrillic
319 | });
320 |
321 | it('should be case insensitive for database type', () => {
322 | process.env.DB_TYPE = 'POSTGRES';
323 | process.env.DB_HOST = 'localhost';
324 | process.env.DB_USER = 'user';
325 | process.env.DB_PASSWORD = 'pass';
326 | process.env.DB_NAME = 'db';
327 |
328 | const result = buildDSNFromEnvParams();
329 |
330 | expect(result?.dsn).toBe('postgres://user:pass@localhost:5432/db');
331 | });
332 | });
333 |
334 | describe('resolveId', () => {
335 | it('should return null when ID is not provided', () => {
336 | const result = resolveId();
337 |
338 | expect(result).toBeNull();
339 | });
340 |
341 | it('should resolve ID from environment variable', () => {
342 | process.env.ID = 'prod';
343 |
344 | const result = resolveId();
345 |
346 | expect(result).toEqual({
347 | id: 'prod',
348 | source: 'environment variable'
349 | });
350 | });
351 |
352 | it('should handle different ID formats', () => {
353 | process.env.ID = 'staging-db-01';
354 |
355 | const result = resolveId();
356 |
357 | expect(result).toEqual({
358 | id: 'staging-db-01',
359 | source: 'environment variable'
360 | });
361 | });
362 |
363 | it('should handle numeric IDs as strings', () => {
364 | process.env.ID = '123';
365 |
366 | const result = resolveId();
367 |
368 | expect(result).toEqual({
369 | id: '123',
370 | source: 'environment variable'
371 | });
372 | });
373 | });
374 | });
```
--------------------------------------------------------------------------------
/src/__tests__/json-rpc-integration.test.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { describe, it, expect, beforeAll, afterAll } from 'vitest';
2 | import { spawn, ChildProcess } from 'child_process';
3 | import fs from 'fs';
4 | import path from 'path';
5 | import os from 'os';
6 |
7 | describe('JSON RPC Integration Tests', () => {
8 | let serverProcess: ChildProcess | null = null;
9 | let testDbPath: string;
10 | let baseUrl: string;
11 | const testPort = 3001;
12 |
13 | beforeAll(async () => {
14 | // Create a temporary SQLite database file
15 | const tempDir = os.tmpdir();
16 | testDbPath = path.join(tempDir, `json_rpc_test_${Date.now()}_${Math.random().toString(36).substr(2, 9)}.db`);
17 |
18 | baseUrl = `http://localhost:${testPort}`;
19 |
20 | // Start the server as a child process
21 | serverProcess = spawn('pnpm', ['dev'], {
22 | env: {
23 | ...process.env,
24 | DSN: `sqlite://${testDbPath}`,
25 | TRANSPORT: 'http',
26 | PORT: testPort.toString(),
27 | NODE_ENV: 'test'
28 | },
29 | stdio: 'pipe'
30 | });
31 |
32 | // Handle server output
33 | serverProcess.stdout?.on('data', (data) => {
34 | console.log(`Server stdout: ${data}`);
35 | });
36 |
37 | serverProcess.stderr?.on('data', (data) => {
38 | console.error(`Server stderr: ${data}`);
39 | });
40 |
41 | // Wait for server to start up
42 | let serverReady = false;
43 | for (let i = 0; i < 20; i++) {
44 | try {
45 | await new Promise(resolve => setTimeout(resolve, 1000));
46 | const response = await fetch(`${baseUrl}/message`, {
47 | method: 'POST',
48 | headers: {
49 | 'Content-Type': 'application/json',
50 | 'Accept': 'application/json, text/event-stream'
51 | },
52 | body: JSON.stringify({
53 | jsonrpc: '2.0',
54 | id: 'health-check',
55 | method: 'notifications/initialized'
56 | })
57 | });
58 | if (response.status < 500) {
59 | serverReady = true;
60 | break;
61 | }
62 | } catch (e) {
63 | // Server not ready yet, continue waiting
64 | }
65 | }
66 |
67 | if (!serverReady) {
68 | throw new Error('Server did not start within expected time');
69 | }
70 |
71 | // Create test tables and data via HTTP request
72 | await makeJsonRpcCall('execute_sql', {
73 | sql: `
74 | CREATE TABLE IF NOT EXISTS users (
75 | id INTEGER PRIMARY KEY AUTOINCREMENT,
76 | name VARCHAR(100) NOT NULL,
77 | email VARCHAR(100) UNIQUE NOT NULL,
78 | age INTEGER
79 | );
80 |
81 | CREATE TABLE IF NOT EXISTS orders (
82 | id INTEGER PRIMARY KEY AUTOINCREMENT,
83 | user_id INTEGER REFERENCES users(id),
84 | total DECIMAL(10,2),
85 | created_at DATETIME DEFAULT CURRENT_TIMESTAMP
86 | );
87 |
88 | INSERT INTO users (name, email, age) VALUES
89 | ('John Doe', '[email protected]', 30),
90 | ('Jane Smith', '[email protected]', 25),
91 | ('Bob Johnson', '[email protected]', 35);
92 |
93 | INSERT INTO orders (user_id, total) VALUES
94 | (1, 99.99),
95 | (1, 149.50),
96 | (2, 75.25);
97 | `
98 | });
99 | }, 30000);
100 |
101 | afterAll(async () => {
102 | // Kill the server process if it's still running
103 | if (serverProcess) {
104 | serverProcess.kill('SIGTERM');
105 |
106 | // Wait for process to exit
107 | await new Promise((resolve) => {
108 | if (serverProcess) {
109 | serverProcess.on('exit', resolve);
110 | setTimeout(() => {
111 | if (serverProcess && !serverProcess.killed) {
112 | serverProcess.kill('SIGKILL');
113 | }
114 | resolve(void 0);
115 | }, 5000);
116 | } else {
117 | resolve(void 0);
118 | }
119 | });
120 | }
121 |
122 | // Clean up the test database file
123 | if (testDbPath && fs.existsSync(testDbPath)) {
124 | fs.unlinkSync(testDbPath);
125 | }
126 | });
127 |
128 | async function makeJsonRpcCall(method: string, params: any): Promise<any> {
129 | const response = await fetch(`${baseUrl}/message`, {
130 | method: 'POST',
131 | headers: {
132 | 'Content-Type': 'application/json',
133 | 'Accept': 'application/json, text/event-stream',
134 | },
135 | body: JSON.stringify({
136 | jsonrpc: '2.0',
137 | id: Math.random().toString(36).substr(2, 9),
138 | method: 'tools/call',
139 | params: {
140 | name: method,
141 | arguments: params
142 | }
143 | })
144 | });
145 |
146 | if (!response.ok) {
147 | throw new Error(`HTTP ${response.status}: ${response.statusText}`);
148 | }
149 |
150 | // Handle different response types
151 | const contentType = response.headers.get('content-type');
152 | if (contentType?.includes('text/event-stream')) {
153 | // Handle SSE response
154 | const text = await response.text();
155 | const lines = text.split('\n').filter(line => line.startsWith('data: '));
156 | if (lines.length > 0) {
157 | return JSON.parse(lines[0].substring(6)); // Remove 'data: ' prefix
158 | }
159 | throw new Error('No data in SSE response');
160 | } else {
161 | return await response.json();
162 | }
163 | }
164 |
165 | describe('execute_sql JSON RPC calls', () => {
166 | it('should execute a simple SELECT query successfully', async () => {
167 | const response = await makeJsonRpcCall('execute_sql', {
168 | sql: 'SELECT * FROM users WHERE age > 25 ORDER BY age'
169 | });
170 |
171 | expect(response).toHaveProperty('result');
172 | expect(response.result).toHaveProperty('content');
173 | expect(Array.isArray(response.result.content)).toBe(true);
174 |
175 | const content = JSON.parse(response.result.content[0].text);
176 | expect(content.success).toBe(true);
177 | expect(content.data).toHaveProperty('rows');
178 | expect(content.data).toHaveProperty('count');
179 | expect(content.data.rows).toHaveLength(2);
180 | expect(content.data.rows[0].name).toBe('John Doe');
181 | expect(content.data.rows[1].name).toBe('Bob Johnson');
182 | });
183 |
184 | it('should execute a JOIN query successfully', async () => {
185 | const response = await makeJsonRpcCall('execute_sql', {
186 | sql: `
187 | SELECT u.name, u.email, o.total
188 | FROM users u
189 | JOIN orders o ON u.id = o.user_id
190 | WHERE u.age >= 30
191 | ORDER BY o.total DESC
192 | `
193 | });
194 |
195 | expect(response).toHaveProperty('result');
196 | const content = JSON.parse(response.result.content[0].text);
197 | expect(content.success).toBe(true);
198 | expect(content.data.rows).toHaveLength(2);
199 | expect(content.data.rows[0].total).toBe(149.50);
200 | expect(content.data.rows[1].total).toBe(99.99);
201 | });
202 |
203 | it('should execute aggregate queries successfully', async () => {
204 | const response = await makeJsonRpcCall('execute_sql', {
205 | sql: `
206 | SELECT
207 | COUNT(*) as user_count,
208 | AVG(age) as avg_age,
209 | MIN(age) as min_age,
210 | MAX(age) as max_age
211 | FROM users
212 | `
213 | });
214 |
215 | expect(response).toHaveProperty('result');
216 | const content = JSON.parse(response.result.content[0].text);
217 | expect(content.success).toBe(true);
218 | expect(content.data.rows).toHaveLength(1);
219 | expect(content.data.rows[0].user_count).toBe(3);
220 | expect(content.data.rows[0].avg_age).toBe(30);
221 | expect(content.data.rows[0].min_age).toBe(25);
222 | expect(content.data.rows[0].max_age).toBe(35);
223 | });
224 |
225 | it('should handle multiple statements in a single call', async () => {
226 | const response = await makeJsonRpcCall('execute_sql', {
227 | sql: `
228 | INSERT INTO users (name, email, age) VALUES ('Test User', '[email protected]', 28);
229 | SELECT COUNT(*) as total_users FROM users;
230 | `
231 | });
232 |
233 | expect(response).toHaveProperty('result');
234 | const content = JSON.parse(response.result.content[0].text);
235 | expect(content.success).toBe(true);
236 | expect(content.data.rows).toHaveLength(1);
237 | expect(content.data.rows[0].total_users).toBe(4);
238 | });
239 |
240 | it('should handle SQLite-specific functions', async () => {
241 | const response = await makeJsonRpcCall('execute_sql', {
242 | sql: `
243 | SELECT
244 | sqlite_version() as version,
245 | datetime('now') as current_time,
246 | upper('hello world') as uppercase,
247 | length('test string') as str_length
248 | `
249 | });
250 |
251 | expect(response).toHaveProperty('result');
252 | const content = JSON.parse(response.result.content[0].text);
253 | expect(content.success).toBe(true);
254 | expect(content.data.rows).toHaveLength(1);
255 | expect(content.data.rows[0].version).toBeDefined();
256 | expect(content.data.rows[0].uppercase).toBe('HELLO WORLD');
257 | expect(content.data.rows[0].str_length).toBe(11);
258 | });
259 |
260 | it('should return error for invalid SQL', async () => {
261 | const response = await makeJsonRpcCall('execute_sql', {
262 | sql: 'SELECT * FROM non_existent_table'
263 | });
264 |
265 | expect(response).toHaveProperty('result');
266 | const content = JSON.parse(response.result.content[0].text);
267 | expect(content.success).toBe(false);
268 | expect(content.error).toContain('no such table');
269 | expect(content.code).toBe('EXECUTION_ERROR');
270 | });
271 |
272 | it('should handle empty result sets', async () => {
273 | const response = await makeJsonRpcCall('execute_sql', {
274 | sql: 'SELECT * FROM users WHERE age > 100'
275 | });
276 |
277 | expect(response).toHaveProperty('result');
278 | const content = JSON.parse(response.result.content[0].text);
279 | expect(content.success).toBe(true);
280 | expect(content.data.rows).toHaveLength(0);
281 | expect(content.data.count).toBe(0);
282 | });
283 |
284 | it('should work with SQLite transactions', async () => {
285 | const response = await makeJsonRpcCall('execute_sql', {
286 | sql: `
287 | BEGIN TRANSACTION;
288 | INSERT INTO users (name, email, age) VALUES ('Transaction User', '[email protected]', 40);
289 | COMMIT;
290 | SELECT * FROM users WHERE email = '[email protected]';
291 | `
292 | });
293 |
294 | expect(response).toHaveProperty('result');
295 | const content = JSON.parse(response.result.content[0].text);
296 | expect(content.success).toBe(true);
297 | expect(content.data.rows).toHaveLength(1);
298 | expect(content.data.rows[0].name).toBe('Transaction User');
299 | expect(content.data.rows[0].age).toBe(40);
300 | });
301 |
302 | it('should handle PRAGMA statements', async () => {
303 | const response = await makeJsonRpcCall('execute_sql', {
304 | sql: 'PRAGMA table_info(users)'
305 | });
306 |
307 | expect(response).toHaveProperty('result');
308 | const content = JSON.parse(response.result.content[0].text);
309 | expect(content.success).toBe(true);
310 | expect(content.data.rows.length).toBeGreaterThan(0);
311 | expect(content.data.rows.some((row: any) => row.name === 'id')).toBe(true);
312 | expect(content.data.rows.some((row: any) => row.name === 'name')).toBe(true);
313 | });
314 | });
315 |
316 | describe('JSON RPC protocol compliance', () => {
317 | it('should return proper JSON RPC response structure', async () => {
318 | const requestId = Math.random().toString(36).substr(2, 9);
319 | const response = await makeJsonRpcCall('execute_sql', {
320 | sql: 'SELECT 1 as test'
321 | });
322 |
323 | expect(response).toHaveProperty('jsonrpc', '2.0');
324 | expect(response).toHaveProperty('id');
325 | expect(response).toHaveProperty('result');
326 | expect(response.result).toHaveProperty('content');
327 | });
328 |
329 | it('should handle malformed requests gracefully', async () => {
330 | const response = await fetch(`${baseUrl}/message`, {
331 | method: 'POST',
332 | headers: {
333 | 'Content-Type': 'application/json',
334 | 'Accept': 'application/json, text/event-stream',
335 | },
336 | body: JSON.stringify({
337 | // Missing required jsonrpc field
338 | id: 'test',
339 | method: 'tools/call',
340 | params: {
341 | name: 'execute_sql',
342 | arguments: { sql: 'SELECT 1' }
343 | }
344 | })
345 | });
346 |
347 | // The server should still respond, but with an error
348 | expect(response.status).toBeLessThan(500);
349 | });
350 | });
351 | });
```
--------------------------------------------------------------------------------
/src/prompts/db-explainer.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { z } from "zod";
2 | import { ConnectorManager } from "../connectors/manager.js";
3 | import {
4 | formatPromptSuccessResponse,
5 | formatPromptErrorResponse,
6 | } from "../utils/response-formatter.js";
7 |
8 | // Schema for database explainer prompt
9 | export const dbExplainerSchema = {
10 | schema: z.string().optional().describe("Optional database schema to use"),
11 | table: z.string().optional().describe("Optional specific table to explain"),
12 | };
13 |
14 | /**
15 | * Database Explainer Prompt Handler
16 | * Provides explanations about database elements
17 | */
18 | export async function dbExplainerPromptHandler(
19 | {
20 | schema,
21 | table,
22 | }: {
23 | schema?: string;
24 | table?: string;
25 | },
26 | _extra: any
27 | ): Promise<{
28 | messages: {
29 | role: "assistant" | "user";
30 | content: {
31 | type: "text";
32 | text: string;
33 | };
34 | }[];
35 | references?: string[];
36 | error?: string;
37 | code?: string;
38 | _meta?: Record<string, unknown>;
39 | [key: string]: unknown;
40 | }> {
41 | try {
42 | const connector = ConnectorManager.getCurrentConnector();
43 |
44 | // Verify schema exists if provided
45 | if (schema) {
46 | const availableSchemas = await connector.getSchemas();
47 | if (!availableSchemas.includes(schema)) {
48 | return formatPromptErrorResponse(
49 | `Schema '${schema}' does not exist or cannot be accessed. Available schemas: ${availableSchemas.join(", ")}`,
50 | "SCHEMA_NOT_FOUND"
51 | );
52 | }
53 | }
54 |
55 | // Get list of available tables in the specified schema
56 | const tables = await connector.getTables(schema);
57 |
58 | // Process the table parameter if provided
59 | const normalizedTable = table?.toLowerCase() || "";
60 |
61 | // Check if table parameter matches a table in the database
62 | const matchingTable = tables.find((t) => t.toLowerCase() === normalizedTable);
63 | if (matchingTable && table) {
64 | try {
65 | // Explain the table
66 | const columns = await connector.getTableSchema(matchingTable, schema);
67 |
68 | if (columns.length === 0) {
69 | return formatPromptErrorResponse(
70 | `Table '${matchingTable}' exists but has no columns or cannot be accessed.`,
71 | "EMPTY_TABLE_SCHEMA"
72 | );
73 | }
74 |
75 | // Create a table structure description
76 | const schemaInfo = schema ? ` in schema '${schema}'` : "";
77 | const tableDescription = `Table: ${matchingTable}${schemaInfo}
78 |
79 | Structure:
80 | ${columns.map((col) => `- ${col.column_name} (${col.data_type})${col.is_nullable === "YES" ? ", nullable" : ""}${col.column_default ? `, default: ${col.column_default}` : ""}`).join("\n")}
81 |
82 | Purpose:
83 | This table appears to store ${determineTablePurpose(matchingTable, columns)}
84 |
85 | Relationships:
86 | ${determineRelationships(matchingTable, columns)}`;
87 |
88 | return formatPromptSuccessResponse(tableDescription);
89 | } catch (error) {
90 | return formatPromptErrorResponse(
91 | `Error retrieving schema for table '${matchingTable}': ${(error as Error).message}`,
92 | "TABLE_SCHEMA_ERROR"
93 | );
94 | }
95 | }
96 |
97 | // Check if table parameter has a table.column format
98 | if (table && table.includes(".")) {
99 | const [tableName, columnName] = table.split(".");
100 | const tableExists = tables.find((t) => t.toLowerCase() === tableName.toLowerCase());
101 |
102 | if (!tableExists) {
103 | // Table part of table.column doesn't exist
104 | return formatPromptErrorResponse(
105 | `Table '${tableName}' does not exist in schema '${schema || "default"}'. Available tables: ${tables.slice(0, 10).join(", ")}${tables.length > 10 ? "..." : ""}`,
106 | "TABLE_NOT_FOUND"
107 | );
108 | }
109 |
110 | try {
111 | // Get column info
112 | const columns = await connector.getTableSchema(tableName, schema);
113 | const column = columns.find(
114 | (c) => c.column_name.toLowerCase() === columnName.toLowerCase()
115 | );
116 |
117 | if (column) {
118 | const columnDescription = `Column: ${tableName}.${column.column_name}
119 |
120 | Type: ${column.data_type}
121 | Nullable: ${column.is_nullable === "YES" ? "Yes" : "No"}
122 | Default: ${column.column_default || "None"}
123 |
124 | Purpose:
125 | ${determineColumnPurpose(column.column_name, column.data_type)}`;
126 |
127 | return formatPromptSuccessResponse(columnDescription);
128 | } else {
129 | // Column doesn't exist in the table
130 | return formatPromptErrorResponse(
131 | `Column '${columnName}' does not exist in table '${tableName}'. Available columns: ${columns.map((c) => c.column_name).join(", ")}`,
132 | "COLUMN_NOT_FOUND"
133 | );
134 | }
135 | } catch (error) {
136 | return formatPromptErrorResponse(
137 | `Error accessing table schema: ${(error as Error).message}`,
138 | "SCHEMA_ACCESS_ERROR"
139 | );
140 | }
141 | }
142 |
143 | // If no specific table was provided or the table refers to the database itself,
144 | // provide a database overview
145 | // This will trigger if:
146 | // 1. No table parameter was provided
147 | // 2. The table parameter is a database overview keyword
148 | if (!table || ["database", "db", "schema", "overview", "all"].includes(normalizedTable)) {
149 | const schemaInfo = schema ? `in schema '${schema}'` : "across all schemas";
150 |
151 | let dbOverview = `Database Overview ${schemaInfo}
152 |
153 | Tables: ${tables.length}
154 | ${tables.map((t) => `- ${t}`).join("\n")}
155 |
156 | This database ${describeDatabasePurpose(tables)}`;
157 |
158 | return formatPromptSuccessResponse(dbOverview);
159 | }
160 |
161 | // If we get here and table was provided but not found,
162 | // check for partial matches to suggest alternatives
163 | if (table && !normalizedTable.includes(".")) {
164 | // Search for partial matches
165 | const possibleTableMatches = tables.filter(
166 | (t) =>
167 | t.toLowerCase().includes(normalizedTable) || normalizedTable.includes(t.toLowerCase())
168 | );
169 |
170 | if (possibleTableMatches.length > 0) {
171 | // Found partial matches, suggest them to the user
172 | return formatPromptSuccessResponse(
173 | `Table "${table}" not found. Did you mean one of these tables?\n\n${possibleTableMatches.join("\n")}`
174 | );
175 | } else {
176 | // No matches at all, return a clear error with available tables
177 | const schemaInfo = schema ? `in schema '${schema}'` : "in the database";
178 | return formatPromptErrorResponse(
179 | `Table "${table}" does not exist ${schemaInfo}. Available tables: ${tables.slice(0, 10).join(", ")}${tables.length > 10 ? "..." : ""}`,
180 | "TABLE_NOT_FOUND"
181 | );
182 | }
183 | }
184 | } catch (error) {
185 | return formatPromptErrorResponse(
186 | `Error explaining database: ${(error as Error).message}`,
187 | "EXPLANATION_ERROR"
188 | );
189 | }
190 |
191 | // If no condition was met and no other return was triggered
192 | return formatPromptErrorResponse(
193 | `Unable to process request for schema: ${schema}, table: ${table}`,
194 | "UNHANDLED_REQUEST"
195 | );
196 | }
197 |
198 | /**
199 | * Helper function to make an educated guess about the purpose of a table
200 | * based on its name and columns
201 | */
202 | function determineTablePurpose(tableName: string, columns: any[]): string {
203 | const lowerTableName = tableName.toLowerCase();
204 | const columnNames = columns.map((c) => c.column_name.toLowerCase());
205 |
206 | // Check for common patterns
207 | if (
208 | lowerTableName.includes("user") ||
209 | columnNames.includes("username") ||
210 | columnNames.includes("email")
211 | ) {
212 | return "user information and profiles";
213 | }
214 |
215 | if (lowerTableName.includes("order") || lowerTableName.includes("purchase")) {
216 | return "order or purchase transactions";
217 | }
218 |
219 | if (lowerTableName.includes("product") || lowerTableName.includes("item")) {
220 | return "product or item information";
221 | }
222 |
223 | if (lowerTableName.includes("log") || columnNames.includes("timestamp")) {
224 | return "event or activity logs";
225 | }
226 |
227 | if (columnNames.includes("created_at") && columnNames.includes("updated_at")) {
228 | return "tracking timestamped data records";
229 | }
230 |
231 | // Default
232 | return "data related to " + tableName;
233 | }
234 |
235 | /**
236 | * Helper function to determine potential relationships based on column names
237 | */
238 | function determineRelationships(tableName: string, columns: any[]): string {
239 | const potentialRelationships = [];
240 |
241 | // Look for _id columns which often indicate foreign keys
242 | const idColumns = columns.filter(
243 | (c) =>
244 | c.column_name.toLowerCase().endsWith("_id") &&
245 | !c.column_name.toLowerCase().startsWith(tableName.toLowerCase())
246 | );
247 |
248 | if (idColumns.length > 0) {
249 | idColumns.forEach((col) => {
250 | const referencedTable = col.column_name.toLowerCase().replace("_id", "");
251 | potentialRelationships.push(
252 | `May have a relationship with the "${referencedTable}" table (via ${col.column_name})`
253 | );
254 | });
255 | }
256 |
257 | // Check if the table itself might be referenced by others
258 | if (columns.some((c) => c.column_name.toLowerCase() === "id")) {
259 | potentialRelationships.push(
260 | `May be referenced by other tables as "${tableName.toLowerCase()}_id"`
261 | );
262 | }
263 |
264 | return potentialRelationships.length > 0
265 | ? potentialRelationships.join("\n")
266 | : "No obvious relationships identified based on column names";
267 | }
268 |
269 | /**
270 | * Helper function to determine the purpose of a column based on naming patterns
271 | */
272 | function determineColumnPurpose(columnName: string, dataType: string): string {
273 | const lowerColumnName = columnName.toLowerCase();
274 |
275 | if (lowerColumnName === "id") {
276 | return "Primary identifier for records in this table";
277 | }
278 |
279 | if (lowerColumnName.endsWith("_id")) {
280 | const referencedTable = lowerColumnName.replace("_id", "");
281 | return `Foreign key reference to the "${referencedTable}" table`;
282 | }
283 |
284 | if (lowerColumnName.includes("name")) {
285 | return "Stores name information";
286 | }
287 |
288 | if (lowerColumnName.includes("email")) {
289 | return "Stores email address information";
290 | }
291 |
292 | if (lowerColumnName.includes("password") || lowerColumnName.includes("hash")) {
293 | return "Stores security credential information (likely hashed)";
294 | }
295 |
296 | if (lowerColumnName === "created_at" || lowerColumnName === "created_on") {
297 | return "Timestamp for when the record was created";
298 | }
299 |
300 | if (lowerColumnName === "updated_at" || lowerColumnName === "modified_at") {
301 | return "Timestamp for when the record was last updated";
302 | }
303 |
304 | if (lowerColumnName.includes("date") || lowerColumnName.includes("time")) {
305 | return "Stores date or time information";
306 | }
307 |
308 | if (
309 | lowerColumnName.includes("price") ||
310 | lowerColumnName.includes("cost") ||
311 | lowerColumnName.includes("amount")
312 | ) {
313 | return "Stores monetary value information";
314 | }
315 |
316 | // Data type specific purposes
317 | if (dataType.includes("boolean")) {
318 | return "Stores a true/false flag";
319 | }
320 |
321 | if (dataType.includes("json")) {
322 | return "Stores structured JSON data";
323 | }
324 |
325 | if (dataType.includes("text") || dataType.includes("varchar") || dataType.includes("char")) {
326 | return "Stores text information";
327 | }
328 |
329 | // Default
330 | return `Stores ${dataType} data`;
331 | }
332 |
333 | /**
334 | * Helper function to describe the overall database purpose based on tables
335 | */
336 | function describeDatabasePurpose(tables: string[]): string {
337 | const tableNames = tables.map((t) => t.toLowerCase());
338 |
339 | if (tableNames.some((t) => t.includes("user")) && tableNames.some((t) => t.includes("order"))) {
340 | return "appears to be an e-commerce or customer order management system";
341 | }
342 |
343 | if (
344 | tableNames.some((t) => t.includes("patient")) ||
345 | tableNames.some((t) => t.includes("medical"))
346 | ) {
347 | return "appears to be related to healthcare or medical record management";
348 | }
349 |
350 | if (
351 | tableNames.some((t) => t.includes("student")) ||
352 | tableNames.some((t) => t.includes("course"))
353 | ) {
354 | return "appears to be related to education or student management";
355 | }
356 |
357 | if (
358 | tableNames.some((t) => t.includes("employee")) ||
359 | tableNames.some((t) => t.includes("payroll"))
360 | ) {
361 | return "appears to be related to HR or employee management";
362 | }
363 |
364 | if (
365 | tableNames.some((t) => t.includes("inventory")) ||
366 | tableNames.some((t) => t.includes("stock"))
367 | ) {
368 | return "appears to be related to inventory or stock management";
369 | }
370 |
371 | // Default
372 | return "contains multiple tables that store related information";
373 | }
374 |
```
--------------------------------------------------------------------------------
/src/connectors/postgres/index.ts:
--------------------------------------------------------------------------------
```typescript
1 | import pg from "pg";
2 | const { Pool } = pg;
3 | import {
4 | Connector,
5 | ConnectorType,
6 | ConnectorRegistry,
7 | DSNParser,
8 | SQLResult,
9 | TableColumn,
10 | TableIndex,
11 | StoredProcedure,
12 | ExecuteOptions,
13 | } from "../interface.js";
14 | import { SafeURL } from "../../utils/safe-url.js";
15 | import { obfuscateDSNPassword } from "../../utils/dsn-obfuscate.js";
16 | import { SQLRowLimiter } from "../../utils/sql-row-limiter.js";
17 |
18 | /**
19 | * PostgreSQL DSN Parser
20 | * Handles DSN strings like: postgres://user:password@localhost:5432/dbname?sslmode=disable
21 | * Supported SSL modes:
22 | * - sslmode=disable: No SSL
23 | * - sslmode=require: SSL connection without certificate verification
24 | * - Any other value: SSL with certificate verification
25 | */
26 | class PostgresDSNParser implements DSNParser {
27 | async parse(dsn: string): Promise<pg.PoolConfig> {
28 | // Basic validation
29 | if (!this.isValidDSN(dsn)) {
30 | const obfuscatedDSN = obfuscateDSNPassword(dsn);
31 | const expectedFormat = this.getSampleDSN();
32 | throw new Error(
33 | `Invalid PostgreSQL DSN format.\nProvided: ${obfuscatedDSN}\nExpected: ${expectedFormat}`
34 | );
35 | }
36 |
37 | try {
38 | // Use the SafeURL helper instead of the built-in URL
39 | // This will handle special characters in passwords, etc.
40 | const url = new SafeURL(dsn);
41 |
42 | const config: pg.PoolConfig = {
43 | host: url.hostname,
44 | port: url.port ? parseInt(url.port) : 5432,
45 | database: url.pathname ? url.pathname.substring(1) : '', // Remove leading '/' if exists
46 | user: url.username,
47 | password: url.password,
48 | };
49 |
50 | // Handle query parameters (like sslmode, etc.)
51 | url.forEachSearchParam((value, key) => {
52 | if (key === "sslmode") {
53 | if (value === "disable") {
54 | config.ssl = false;
55 | } else if (value === "require") {
56 | config.ssl = { rejectUnauthorized: false };
57 | } else {
58 | config.ssl = true;
59 | }
60 | }
61 | // Add other parameters as needed
62 | });
63 |
64 | return config;
65 | } catch (error) {
66 | throw new Error(
67 | `Failed to parse PostgreSQL DSN: ${error instanceof Error ? error.message : String(error)}`
68 | );
69 | }
70 | }
71 |
72 | getSampleDSN(): string {
73 | return "postgres://postgres:password@localhost:5432/postgres?sslmode=require";
74 | }
75 |
76 | isValidDSN(dsn: string): boolean {
77 | try {
78 | return dsn.startsWith('postgres://') || dsn.startsWith('postgresql://');
79 | } catch (error) {
80 | return false;
81 | }
82 | }
83 | }
84 |
85 | /**
86 | * PostgreSQL Connector Implementation
87 | */
88 | export class PostgresConnector implements Connector {
89 | id: ConnectorType = "postgres";
90 | name = "PostgreSQL";
91 | dsnParser = new PostgresDSNParser();
92 |
93 | private pool: pg.Pool | null = null;
94 |
95 | async connect(dsn: string): Promise<void> {
96 | try {
97 | const config = await this.dsnParser.parse(dsn);
98 | this.pool = new Pool(config);
99 |
100 | // Test the connection
101 | const client = await this.pool.connect();
102 | console.error("Successfully connected to PostgreSQL database");
103 | client.release();
104 | } catch (err) {
105 | console.error("Failed to connect to PostgreSQL database:", err);
106 | throw err;
107 | }
108 | }
109 |
110 | async disconnect(): Promise<void> {
111 | if (this.pool) {
112 | await this.pool.end();
113 | this.pool = null;
114 | }
115 | }
116 |
117 | async getSchemas(): Promise<string[]> {
118 | if (!this.pool) {
119 | throw new Error("Not connected to database");
120 | }
121 |
122 | const client = await this.pool.connect();
123 | try {
124 | const result = await client.query(`
125 | SELECT schema_name
126 | FROM information_schema.schemata
127 | WHERE schema_name NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
128 | ORDER BY schema_name
129 | `);
130 |
131 | return result.rows.map((row) => row.schema_name);
132 | } finally {
133 | client.release();
134 | }
135 | }
136 |
137 | async getTables(schema?: string): Promise<string[]> {
138 | if (!this.pool) {
139 | throw new Error("Not connected to database");
140 | }
141 |
142 | const client = await this.pool.connect();
143 | try {
144 | // In PostgreSQL, use 'public' as the default schema if none specified
145 | // 'public' is the standard default schema in PostgreSQL databases
146 | const schemaToUse = schema || "public";
147 |
148 | const result = await client.query(
149 | `
150 | SELECT table_name
151 | FROM information_schema.tables
152 | WHERE table_schema = $1
153 | ORDER BY table_name
154 | `,
155 | [schemaToUse]
156 | );
157 |
158 | return result.rows.map((row) => row.table_name);
159 | } finally {
160 | client.release();
161 | }
162 | }
163 |
164 | async tableExists(tableName: string, schema?: string): Promise<boolean> {
165 | if (!this.pool) {
166 | throw new Error("Not connected to database");
167 | }
168 |
169 | const client = await this.pool.connect();
170 | try {
171 | // In PostgreSQL, use 'public' as the default schema if none specified
172 | const schemaToUse = schema || "public";
173 |
174 | const result = await client.query(
175 | `
176 | SELECT EXISTS (
177 | SELECT FROM information_schema.tables
178 | WHERE table_schema = $1
179 | AND table_name = $2
180 | )
181 | `,
182 | [schemaToUse, tableName]
183 | );
184 |
185 | return result.rows[0].exists;
186 | } finally {
187 | client.release();
188 | }
189 | }
190 |
191 | async getTableIndexes(tableName: string, schema?: string): Promise<TableIndex[]> {
192 | if (!this.pool) {
193 | throw new Error("Not connected to database");
194 | }
195 |
196 | const client = await this.pool.connect();
197 | try {
198 | // In PostgreSQL, use 'public' as the default schema if none specified
199 | const schemaToUse = schema || "public";
200 |
201 | // Query to get all indexes for the table
202 | const result = await client.query(
203 | `
204 | SELECT
205 | i.relname as index_name,
206 | array_agg(a.attname) as column_names,
207 | ix.indisunique as is_unique,
208 | ix.indisprimary as is_primary
209 | FROM
210 | pg_class t,
211 | pg_class i,
212 | pg_index ix,
213 | pg_attribute a,
214 | pg_namespace ns
215 | WHERE
216 | t.oid = ix.indrelid
217 | AND i.oid = ix.indexrelid
218 | AND a.attrelid = t.oid
219 | AND a.attnum = ANY(ix.indkey)
220 | AND t.relkind = 'r'
221 | AND t.relname = $1
222 | AND ns.oid = t.relnamespace
223 | AND ns.nspname = $2
224 | GROUP BY
225 | i.relname,
226 | ix.indisunique,
227 | ix.indisprimary
228 | ORDER BY
229 | i.relname
230 | `,
231 | [tableName, schemaToUse]
232 | );
233 |
234 | return result.rows.map((row) => ({
235 | index_name: row.index_name,
236 | column_names: row.column_names,
237 | is_unique: row.is_unique,
238 | is_primary: row.is_primary,
239 | }));
240 | } finally {
241 | client.release();
242 | }
243 | }
244 |
245 | async getTableSchema(tableName: string, schema?: string): Promise<TableColumn[]> {
246 | if (!this.pool) {
247 | throw new Error("Not connected to database");
248 | }
249 |
250 | const client = await this.pool.connect();
251 | try {
252 | // In PostgreSQL, use 'public' as the default schema if none specified
253 | // Tables are created in the 'public' schema by default unless otherwise specified
254 | const schemaToUse = schema || "public";
255 |
256 | // Get table columns
257 | const result = await client.query(
258 | `
259 | SELECT
260 | column_name,
261 | data_type,
262 | is_nullable,
263 | column_default
264 | FROM information_schema.columns
265 | WHERE table_schema = $1
266 | AND table_name = $2
267 | ORDER BY ordinal_position
268 | `,
269 | [schemaToUse, tableName]
270 | );
271 |
272 | return result.rows;
273 | } finally {
274 | client.release();
275 | }
276 | }
277 |
278 | async getStoredProcedures(schema?: string): Promise<string[]> {
279 | if (!this.pool) {
280 | throw new Error("Not connected to database");
281 | }
282 |
283 | const client = await this.pool.connect();
284 | try {
285 | // In PostgreSQL, use 'public' as the default schema if none specified
286 | const schemaToUse = schema || "public";
287 |
288 | // Get stored procedures and functions from PostgreSQL
289 | const result = await client.query(
290 | `
291 | SELECT
292 | routine_name
293 | FROM information_schema.routines
294 | WHERE routine_schema = $1
295 | ORDER BY routine_name
296 | `,
297 | [schemaToUse]
298 | );
299 |
300 | return result.rows.map((row) => row.routine_name);
301 | } finally {
302 | client.release();
303 | }
304 | }
305 |
306 | async getStoredProcedureDetail(procedureName: string, schema?: string): Promise<StoredProcedure> {
307 | if (!this.pool) {
308 | throw new Error("Not connected to database");
309 | }
310 |
311 | const client = await this.pool.connect();
312 | try {
313 | // In PostgreSQL, use 'public' as the default schema if none specified
314 | const schemaToUse = schema || "public";
315 |
316 | // Get stored procedure details from PostgreSQL
317 | const result = await client.query(
318 | `
319 | SELECT
320 | routine_name as procedure_name,
321 | routine_type,
322 | CASE WHEN routine_type = 'PROCEDURE' THEN 'procedure' ELSE 'function' END as procedure_type,
323 | external_language as language,
324 | data_type as return_type,
325 | routine_definition as definition,
326 | (
327 | SELECT string_agg(
328 | parameter_name || ' ' ||
329 | parameter_mode || ' ' ||
330 | data_type,
331 | ', '
332 | )
333 | FROM information_schema.parameters
334 | WHERE specific_schema = $1
335 | AND specific_name = $2
336 | AND parameter_name IS NOT NULL
337 | ) as parameter_list
338 | FROM information_schema.routines
339 | WHERE routine_schema = $1
340 | AND routine_name = $2
341 | `,
342 | [schemaToUse, procedureName]
343 | );
344 |
345 | if (result.rows.length === 0) {
346 | throw new Error(`Stored procedure '${procedureName}' not found in schema '${schemaToUse}'`);
347 | }
348 |
349 | const procedure = result.rows[0];
350 |
351 | // If routine_definition is NULL, try to get the procedure body with pg_get_functiondef
352 | let definition = procedure.definition;
353 |
354 | try {
355 | // Get the OID for the procedure/function
356 | const oidResult = await client.query(
357 | `
358 | SELECT p.oid, p.prosrc
359 | FROM pg_proc p
360 | JOIN pg_namespace n ON p.pronamespace = n.oid
361 | WHERE p.proname = $1
362 | AND n.nspname = $2
363 | `,
364 | [procedureName, schemaToUse]
365 | );
366 |
367 | if (oidResult.rows.length > 0) {
368 | // If definition is still null, get the full definition
369 | if (!definition) {
370 | const oid = oidResult.rows[0].oid;
371 | const defResult = await client.query(`SELECT pg_get_functiondef($1)`, [oid]);
372 | if (defResult.rows.length > 0) {
373 | definition = defResult.rows[0].pg_get_functiondef;
374 | } else {
375 | // Fall back to prosrc if pg_get_functiondef fails
376 | definition = oidResult.rows[0].prosrc;
377 | }
378 | }
379 | }
380 | } catch (err) {
381 | // Ignore errors trying to get definition - it's optional
382 | console.error(`Error getting procedure definition: ${err}`);
383 | }
384 |
385 | return {
386 | procedure_name: procedure.procedure_name,
387 | procedure_type: procedure.procedure_type,
388 | language: procedure.language || "sql",
389 | parameter_list: procedure.parameter_list || "",
390 | return_type: procedure.return_type !== "void" ? procedure.return_type : undefined,
391 | definition: definition || undefined,
392 | };
393 | } finally {
394 | client.release();
395 | }
396 | }
397 |
398 |
399 | async executeSQL(sql: string, options: ExecuteOptions): Promise<SQLResult> {
400 | if (!this.pool) {
401 | throw new Error("Not connected to database");
402 | }
403 |
404 | const client = await this.pool.connect();
405 | try {
406 | // Check if this is a multi-statement query
407 | const statements = sql.split(';')
408 | .map(statement => statement.trim())
409 | .filter(statement => statement.length > 0);
410 |
411 | if (statements.length === 1) {
412 | // Single statement - apply maxRows if applicable
413 | const processedStatement = SQLRowLimiter.applyMaxRows(statements[0], options.maxRows);
414 |
415 | return await client.query(processedStatement);
416 | } else {
417 | // Multiple statements - execute all in same session for transaction consistency
418 | let allRows: any[] = [];
419 |
420 | // Execute within a transaction to ensure session consistency
421 | await client.query('BEGIN');
422 | try {
423 | for (let statement of statements) {
424 | // Apply maxRows limit to SELECT queries if specified
425 | const processedStatement = SQLRowLimiter.applyMaxRows(statement, options.maxRows);
426 |
427 | const result = await client.query(processedStatement);
428 | // Collect rows from SELECT/WITH/EXPLAIN statements
429 | if (result.rows && result.rows.length > 0) {
430 | allRows.push(...result.rows);
431 | }
432 | }
433 | await client.query('COMMIT');
434 | } catch (error) {
435 | await client.query('ROLLBACK');
436 | throw error;
437 | }
438 |
439 | return { rows: allRows };
440 | }
441 | } finally {
442 | client.release();
443 | }
444 | }
445 | }
446 |
447 | // Create and register the connector
448 | const postgresConnector = new PostgresConnector();
449 | ConnectorRegistry.register(postgresConnector);
450 |
```
--------------------------------------------------------------------------------
/resources/images/logo-icon-only.svg:
--------------------------------------------------------------------------------
```
1 | <?xml version="1.0" encoding="utf-8" ?>
2 | <svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="1726" height="1726">
3 | <path transform="scale(3.37017 3.37017)" d="M254.218 57.5893C271.394 55.6769 272.54 75.2483 275.511 87.1378L295.294 165.462C299.884 183.301 307.629 197.039 328.502 199.183C334.057 199.754 339.715 198.929 345.246 198.378L412.049 191.183C425.378 189.601 438.767 187.757 452.145 186.658C468.778 185.292 477.924 203.888 465.058 215.324C461.545 218.446 456.446 220.607 452.34 222.893L433.42 233.654C409.633 247.433 383.743 265.504 362.153 282.306C335.092 303.364 338.726 310.498 351.273 342.507C360.435 365.88 370.358 388.992 380.434 411.991C385.123 422.693 395.262 438.162 385.686 449.038C372.682 463.808 357.348 444.473 348.315 436.069C330.537 419.527 278.769 366.218 257.218 367.308C243.678 361.753 220.536 385.323 211.706 392.946C192.763 409.301 174.154 426.001 155.998 443.222C152.993 446.072 149.925 449.376 146.372 451.53C135.603 458.059 120.717 450.01 122.345 436.978C122.781 433.48 124.842 429.998 126.198 426.784L143.092 386.939C148.482 374.162 153.896 361.423 159.017 348.53L165.816 331.022C171.383 316.316 173.417 305.043 161.809 291.308C147.414 274.274 77.2887 232.452 57.6713 221.362C50.2817 217.184 41.9927 214.05 40.663 204.506C39.4719 195.956 45.9445 187.566 54.5122 186.467C59.8027 185.789 73.9458 188.172 80.0187 188.887L158.966 197.829C181 200.286 199.279 201.349 212.689 178.85C216.312 172.771 217.358 166.288 218.867 159.511L235.986 86.0643C238.769 74.094 239.159 59.403 254.218 57.5893Z"/>
4 | <path fill="#FD8D31" transform="scale(3.37017 3.37017)" d="M128.679 438.854C129.843 434.72 132.026 430.714 133.69 426.744L151.444 385.252C159.251 366.672 168.271 345.946 174.752 326.903C179.159 313.954 176.797 300.279 168.956 289.184C156.929 272.165 89.5353 231.76 71.2351 221.298C66.6739 218.69 50.3012 210.101 48.5148 206.191C46.3001 201.344 48.6188 194.222 54.3268 193.23C58.3095 192.538 75.2131 195.312 80.2394 195.943L159.41 205.008C183.62 207.554 205.326 206.532 219.076 181.689C223.022 174.56 224.443 166.481 226.263 158.63L230.959 138.031C234.847 120.83 238.661 103.575 242.918 86.464C245.024 78.0039 245.663 59.2654 260.185 65.3291C266.746 69.5161 266.869 80.971 268.774 88.3972L287.296 162.02C289.244 169.794 290.747 177.7 294.78 184.737C306.318 204.874 324.863 207.248 345.284 205.09L435.232 195.685C440.299 195.173 453.658 192.907 457.726 193.917C463.485 195.348 465.613 202.997 462.601 207.725C460.243 211.426 444.677 219.051 440.304 221.589C422.184 232.106 353.665 274.442 341.87 291.207C328.523 310.18 338.108 327.74 345.485 346.332C354.151 368.169 363.222 389.841 372.623 411.367L379.697 427.504C382.7 434.386 387.272 445.297 374.948 447.426C369.533 448.362 362.597 439.929 359.11 436.705L327.814 407.663C312.391 393.306 296.527 378.648 278.702 367.308C263.115 359.278 248.666 355.809 233.635 366.23C222.889 373.68 210.985 384.81 200.988 393.604C187.077 405.84 173.076 417.862 159.555 430.535C152.284 437.349 135.402 458.902 128.679 438.854Z"/>
5 | <path fill="#F55E24" transform="scale(3.37017 3.37017)" d="M128.679 438.854C129.843 434.72 132.026 430.714 133.69 426.744L151.444 385.252C159.251 366.672 168.271 345.946 174.752 326.903C179.159 313.954 176.797 300.279 168.956 289.184C156.929 272.165 89.5353 231.76 71.2351 221.298C66.6739 218.69 50.3012 210.101 48.5148 206.191C46.3001 201.344 48.6188 194.222 54.3268 193.23C58.3095 192.538 75.2131 195.312 80.2394 195.943L159.41 205.008C183.62 207.554 205.326 206.532 219.076 181.689C223.022 174.56 224.443 166.481 226.263 158.63L230.959 138.031C234.847 120.83 238.661 103.575 242.918 86.464C245.024 78.0039 245.663 59.2654 260.185 65.3291C258.694 72.6523 255.427 87.0523 254.961 94.1515C245.625 97.2213 244.814 106.899 251.781 112.618C250.683 115.05 247.36 126.823 248.272 128.937C242.859 133.294 241.835 138.071 243.938 144.466C242.729 146.635 237.711 172.197 236.832 176.25C233.107 193.426 230.169 208.706 211.605 215.254C195.421 220.963 165.798 214.52 148.819 211.572C145.716 211.033 122.203 205.926 120.744 208.802C126.902 212.518 128.4 220.072 123.81 225.802C124.799 227.092 132.843 230.927 134.603 230.638L134.837 230.302C137.019 241.771 147.78 245.323 156.716 237.444C158.114 238.968 171.257 246.089 172.958 245.471C176.478 251.699 178.359 250.829 183.447 252.937C184.27 255.93 189.006 258.655 191.123 260.923C203.085 273.735 207.503 291.18 200.797 307.642C195.6 320.403 185.921 331.387 179.91 343.939C178.465 346.957 166.545 374.201 167.808 376.509C165.229 379.996 164.088 383.07 164.911 387.349C162.952 389.167 161.488 396.264 159.058 399.323L159.069 399.641L160.386 399.904C164.72 400.733 167.29 402.169 169.163 406.133C172.706 403.402 177.469 397.673 181.141 394.343C184.207 391.561 187.74 389.291 190.902 386.602C211.079 369.446 233.151 344.441 262.273 356.373C266.72 358.194 276.837 362.856 278.702 367.308C263.115 359.278 248.666 355.809 233.635 366.23C222.889 373.68 210.985 384.81 200.988 393.604C187.077 405.84 173.076 417.862 159.555 430.535C152.284 437.349 135.402 458.902 128.679 438.854Z"/>
6 | <path transform="scale(3.37017 3.37017)" d="M123.81 225.802C120.192 228.905 115.27 230.658 110.623 228.739C97.3721 223.266 106.039 201.28 120.744 208.802C126.902 212.518 128.4 220.072 123.81 225.802Z"/>
7 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M113.582 211.793C122.878 211.04 124.738 221.388 116.656 224.681C107.385 225.491 105.746 215.161 113.582 211.793Z"/>
8 | <path transform="scale(3.37017 3.37017)" d="M160.386 399.904C164.72 400.733 167.29 402.169 169.163 406.133C170.345 415.432 162.49 424.4 154.345 418.198C147.229 412.78 150.881 400.54 160.386 399.904Z"/>
9 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M160.386 404.507C167.432 404.743 165.93 413.303 160.84 415.456C153.817 416.64 154.106 406.554 160.386 404.507Z"/>
10 | <path transform="scale(3.37017 3.37017)" d="M81.2319 200.032C94.4824 199.513 97.401 215.81 84.8788 219.423C71.6412 222.251 68.8434 203.193 81.2319 200.032Z"/>
11 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M82.0675 204.52C89.5931 204.287 90.3433 212.812 84.0014 215.317C76.4218 215.821 75.7216 206.921 82.0675 204.52Z"/>
12 | <path transform="scale(3.37017 3.37017)" d="M172.958 245.471C168.569 231.986 180.189 222.032 191.606 228.114C204.617 235.045 197.944 253.887 183.447 252.937C178.359 250.829 176.478 251.699 172.958 245.471Z"/>
13 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M183.447 231.229C195.032 229.114 197.396 245.746 186.605 248.485C174.966 249.156 172.2 233.935 183.447 231.229Z"/>
14 | <path transform="scale(3.37017 3.37017)" d="M134.837 230.302C134.436 207.522 170.976 216.896 156.716 237.444C147.78 245.323 137.019 241.771 134.837 230.302Z"/>
15 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M146.741 221.289C157.687 221.279 158.046 234.484 148.062 237.444C137.68 237.077 136.363 223.803 146.741 221.289Z"/>
16 | <path transform="scale(3.37017 3.37017)" d="M248.272 128.937C259.864 122.951 271.645 132.32 267.345 143.497C263.287 154.044 249.351 153.017 243.938 144.466C241.835 138.071 242.859 133.294 248.272 128.937Z"/>
17 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M253.602 131.553C264.29 130.46 267.592 143.105 257.218 146.617C247.185 147.799 243.64 134.972 253.602 131.553Z"/>
18 | <path transform="scale(3.37017 3.37017)" d="M167.808 376.509C186.465 361.929 192.265 398.337 172.403 394.166C168.692 393.386 166.436 390.594 164.911 387.349C164.088 383.07 165.229 379.996 167.808 376.509Z"/>
19 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M175.458 378.289C182.179 378.368 183.539 387.689 175.458 390.334C167.325 390.219 166.529 380.489 175.458 378.289Z"/>
20 | <path transform="scale(3.37017 3.37017)" d="M254.961 94.1515C274.48 90.3718 270.386 122.153 251.781 112.618C244.814 106.899 245.625 97.2213 254.961 94.1515Z"/>
21 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M257.218 98.5842C261.322 98.8268 262.358 100.553 263.379 104.431C262.444 107.456 261.392 109.009 258.073 109.733C250.274 109.883 249.68 99.8028 257.218 98.5842Z"/>
22 | <path transform="scale(3.37017 3.37017)" d="M220.337 242.087C234.571 236.695 246.057 264.337 224.515 270.263C202.975 271.058 202.904 243.408 220.337 242.087Z"/>
23 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M220.337 246.783C232.189 243.105 237.056 261.195 224.515 265.519C210.695 267.904 207.419 248.962 220.337 246.783Z"/>
24 | <path transform="scale(3.37017 3.37017)" d="M254.218 208.023C256.404 206.512 260.392 207.787 262.506 208.89C274.603 215.202 271.686 231.897 258.073 235.831C238.43 238.855 234.863 207.989 254.218 208.023Z"/>
25 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M254.218 211.793C267.272 211.256 269.682 227.847 257.218 231.229C243.991 232.795 241.667 214.917 254.218 211.793Z"/>
26 | <path transform="scale(3.37017 3.37017)" d="M286.716 242.087C304.499 240.139 308.829 266.307 290.29 270.263C269.843 271.053 267.795 244.847 286.716 242.087Z"/>
27 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M286.716 246.783C298.801 245.49 302.05 261.574 290.29 265.519C277.21 267.497 273.392 250.215 286.716 246.783Z"/>
28 | <path transform="scale(3.37017 3.37017)" d="M229.475 285.32C251.081 287.455 248.211 310.226 233.006 313.199C215.396 314.413 209.73 291.958 229.475 285.32Z"/>
29 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M229.475 290.551C241.836 289.75 244.195 304.57 233.006 308.536C220.418 310.273 217.025 293.587 229.475 290.551Z"/>
30 | <path transform="scale(3.37017 3.37017)" d="M278.217 285.32C295.473 284.095 299.853 307.798 282.178 312.186C263.316 314.789 259.234 288.092 278.217 285.32Z"/>
31 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M278.217 289.793C289.964 289.013 292.951 303.076 282.178 307.605C268.937 308.925 265.976 293.469 278.217 289.793Z"/>
32 | <path transform="scale(3.37017 3.37017)" d="M325.556 230.302C327.808 229.278 331.813 231.475 333.529 232.844C343.52 240.808 337.282 255.342 325.556 256.34C306.916 256.153 306.956 230.547 325.556 230.302Z"/>
33 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M323.846 234.788C335.462 233.63 336.894 248.528 326.818 251.688C315.296 253.664 311.989 238.071 323.846 234.788Z"/>
34 | <path transform="scale(3.37017 3.37017)" d="M254.218 163.805C272.189 162.481 275.373 184.857 258.943 189.299C241.66 191.811 236.859 168.488 254.218 163.805Z"/>
35 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M254.961 168.287C266.149 167.698 268.61 181.915 258.073 184.807C246.411 185.755 243.759 171.423 254.961 168.287Z"/>
36 | <path transform="scale(3.37017 3.37017)" d="M208.3 319.345C224.317 320.298 224.324 342.747 208.3 343.829C192.019 343.756 189.857 320.834 208.3 319.345Z"/>
37 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M206.072 323.566C216.489 322.726 220.013 335.518 209.319 339.369C198.414 340.457 196.156 327.32 206.072 323.566Z"/>
38 | <path transform="scale(3.37017 3.37017)" d="M302.592 319.345C318.341 318.177 321.79 340.072 305.194 343.829C288.526 343.866 286.041 321.806 302.592 319.345Z"/>
39 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M303.383 323.566C313.6 324.03 314.762 336.316 305.194 339.369C293.925 339.039 292.925 325.568 303.383 323.566Z"/>
40 | <path transform="scale(3.37017 3.37017)" d="M254.218 260.832C269.91 259.811 273.26 280.946 257.218 285.32C240.632 286.406 237.547 263.035 254.218 260.832Z"/>
41 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M254.961 265.519C264.049 264.222 266.296 277.959 256.648 280.756C245.961 280.906 244.025 267.38 254.961 265.519Z"/>
42 | <path transform="scale(3.37017 3.37017)" d="M353.69 221.289C367.23 218.813 374.012 240.198 356.1 245.471C338.48 244.15 338.66 222.674 353.69 221.289Z"/>
43 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M353.69 225.802C363.729 224.935 365.661 238.305 356.1 240.434C346.144 241.246 343.812 229.245 353.69 225.802Z"/>
44 | <path transform="scale(3.37017 3.37017)" d="M320.007 346.795C335.795 345.639 338.265 366.031 323.846 369.465C309.207 371.849 304.931 350.352 320.007 346.795Z"/>
45 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M320.901 351.553C330.747 351.303 331.471 362.536 322.894 365.121C313.635 365.623 312.185 353.699 320.901 351.553Z"/>
46 | <path transform="scale(3.37017 3.37017)" d="M189.426 345.81C202.095 344.264 207.386 363.619 192.228 368.086C177.656 370.311 172.849 348.486 189.426 345.81Z"/>
47 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M189.426 350.31C197.807 350.382 199.953 360.231 192.228 363.718C182.294 365.326 180.35 352.439 189.426 350.31Z"/>
48 | <path transform="scale(3.37017 3.37017)" d="M381.943 213.792C397.351 212.902 400.265 230.436 386.409 235.831C371.33 236.875 367.895 218.105 381.943 213.792Z"/>
49 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M383.387 218.059C392.487 218.77 392.912 228.856 384.802 231.229C376.13 231.467 375.25 219.935 383.387 218.059Z"/>
50 | <path transform="scale(3.37017 3.37017)" d="M335.987 375.31C349.382 374.117 351.073 391.56 338.494 394.505C325.002 395.546 322.662 377.936 335.987 375.31Z"/>
51 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M335.987 379.776C343.115 378.749 344.033 387.391 338.494 390.334C331.336 390.898 328.983 382.608 335.987 379.776Z"/>
52 | <path transform="scale(3.37017 3.37017)" d="M409.503 208.802C421.599 207.954 424.356 223.488 412.779 227.01C400.21 228.762 397.155 211.746 409.503 208.802Z"/>
53 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M410.369 213.792C416.604 213.292 417.502 220.572 411.682 222.692C405.388 222.866 403.965 214.971 410.369 213.792Z"/>
54 | <path transform="scale(3.37017 3.37017)" d="M351.509 400.782C363.61 399.999 366.545 415.826 354.825 418.946C341.645 419.89 339.897 403.753 351.509 400.782Z"/>
55 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M352.292 405.258C358.624 405.033 359.119 412.574 353.69 414.528C347.125 414.8 346.894 407.664 352.292 405.258Z"/>
56 | <path transform="scale(3.37017 3.37017)" d="M432.748 203.591C443.007 201.734 447.103 215.722 435.865 219.423C425.245 219.591 421.212 205.704 432.748 203.591Z"/>
57 | <path fill="#7BBCDE" transform="scale(3.37017 3.37017)" d="M432.748 208.023C437.64 207.603 439.89 211.851 435.865 215.317C431.352 214.911 428.02 210.741 432.748 208.023Z"/>
58 | </svg>
59 |
```
--------------------------------------------------------------------------------
/src/connectors/sqlite/index.ts:
--------------------------------------------------------------------------------
```typescript
1 | /**
2 | * SQLite Connector Implementation
3 | *
4 | * Implements SQLite database connectivity for DBHub using better-sqlite3
5 | * To use this connector: Set DSN=sqlite:///path/to/database.db in your .env file
6 | */
7 |
8 | import {
9 | Connector,
10 | ConnectorType,
11 | ConnectorRegistry,
12 | DSNParser,
13 | SQLResult,
14 | TableColumn,
15 | TableIndex,
16 | StoredProcedure,
17 | ExecuteOptions,
18 | } from "../interface.js";
19 | import Database from "better-sqlite3";
20 | import { SafeURL } from "../../utils/safe-url.js";
21 | import { obfuscateDSNPassword } from "../../utils/dsn-obfuscate.js";
22 | import { SQLRowLimiter } from "../../utils/sql-row-limiter.js";
23 |
24 | /**
25 | * SQLite DSN Parser
26 | * Handles DSN strings like:
27 | * - sqlite:///path/to/database.db (absolute path)
28 | * - sqlite://./relative/path/to/database.db (relative path)
29 | * - sqlite:///:memory: (in-memory database)
30 | */
31 | class SQLiteDSNParser implements DSNParser {
32 | async parse(dsn: string): Promise<{ dbPath: string }> {
33 | // Basic validation
34 | if (!this.isValidDSN(dsn)) {
35 | const obfuscatedDSN = obfuscateDSNPassword(dsn);
36 | const expectedFormat = this.getSampleDSN();
37 | throw new Error(
38 | `Invalid SQLite DSN format.\nProvided: ${obfuscatedDSN}\nExpected: ${expectedFormat}`
39 | );
40 | }
41 |
42 | try {
43 | // Use SafeURL helper to handle special characters properly
44 | const url = new SafeURL(dsn);
45 | let dbPath: string;
46 |
47 | // Handle in-memory database
48 | if (url.hostname === "" && url.pathname === "/:memory:") {
49 | dbPath = ":memory:";
50 | }
51 | // Handle file paths
52 | else {
53 | // Get the path part, handling both relative and absolute paths
54 | if (url.pathname.startsWith("//")) {
55 | // Absolute path: sqlite:///path/to/db.sqlite
56 | dbPath = url.pathname.substring(2); // Remove leading //
57 | } else {
58 | // Relative path: sqlite://./path/to/db.sqlite
59 | dbPath = url.pathname;
60 | }
61 | }
62 |
63 | return { dbPath };
64 | } catch (error) {
65 | throw new Error(
66 | `Failed to parse SQLite DSN: ${error instanceof Error ? error.message : String(error)}`
67 | );
68 | }
69 | }
70 |
71 | getSampleDSN(): string {
72 | return "sqlite:///path/to/database.db";
73 | }
74 |
75 | isValidDSN(dsn: string): boolean {
76 | try {
77 | return dsn.startsWith('sqlite://');
78 | } catch (error) {
79 | return false;
80 | }
81 | }
82 | }
83 |
84 | interface SQLiteTableInfo {
85 | name: string;
86 | type: string;
87 | notnull: number;
88 | dflt_value: string | null;
89 | pk: number;
90 | }
91 |
92 | interface SQLiteTableNameRow {
93 | name: string;
94 | }
95 |
96 | export class SQLiteConnector implements Connector {
97 | id: ConnectorType = "sqlite";
98 | name = "SQLite";
99 | dsnParser = new SQLiteDSNParser();
100 |
101 | private db: Database.Database | null = null;
102 | private dbPath: string = ":memory:"; // Default to in-memory database
103 |
104 | async connect(dsn: string, initScript?: string): Promise<void> {
105 | const config = await this.dsnParser.parse(dsn);
106 | this.dbPath = config.dbPath;
107 |
108 | try {
109 | this.db = new Database(this.dbPath);
110 | console.error("Successfully connected to SQLite database");
111 |
112 | // If an initialization script is provided, run it
113 | if (initScript) {
114 | this.db.exec(initScript);
115 | console.error("Successfully initialized database with script");
116 | }
117 | } catch (error) {
118 | console.error("Failed to connect to SQLite database:", error);
119 | throw error;
120 | }
121 | }
122 |
123 | async disconnect(): Promise<void> {
124 | if (this.db) {
125 | try {
126 | // Check if the database is still open before attempting to close
127 | if (!this.db.inTransaction) {
128 | this.db.close();
129 | } else {
130 | // If in transaction, try to rollback first
131 | try {
132 | this.db.exec('ROLLBACK');
133 | } catch (rollbackError) {
134 | // Ignore rollback errors, proceed with close
135 | }
136 | this.db.close();
137 | }
138 | this.db = null;
139 | } catch (error) {
140 | // Log the error but don't throw to prevent test failures
141 | console.error('Error during SQLite disconnect:', error);
142 | this.db = null;
143 | }
144 | }
145 | return Promise.resolve();
146 | }
147 |
148 | async getSchemas(): Promise<string[]> {
149 | if (!this.db) {
150 | throw new Error("Not connected to SQLite database");
151 | }
152 |
153 | // SQLite doesn't have the concept of schemas like PostgreSQL or MySQL
154 | // It has a concept of "attached databases" where each database has a name
155 | // The default database is called 'main', and others can be attached with names
156 | // We always return 'main' as the default schema name
157 | return ["main"];
158 | }
159 |
160 | async getTables(schema?: string): Promise<string[]> {
161 | if (!this.db) {
162 | throw new Error("Not connected to SQLite database");
163 | }
164 |
165 | // In SQLite, schema parameter is ignored since SQLite doesn't have schemas like PostgreSQL
166 | // SQLite has a single namespace for tables within a database file
167 | // You could use 'schema.table' syntax if you have attached databases, but we're
168 | // accessing the 'main' database by default
169 | try {
170 | const rows = this.db
171 | .prepare(
172 | `
173 | SELECT name FROM sqlite_master
174 | WHERE type='table' AND name NOT LIKE 'sqlite_%'
175 | ORDER BY name
176 | `
177 | )
178 | .all() as SQLiteTableNameRow[];
179 |
180 | return rows.map((row) => row.name);
181 | } catch (error) {
182 | throw error;
183 | }
184 | }
185 |
186 | async tableExists(tableName: string, schema?: string): Promise<boolean> {
187 | if (!this.db) {
188 | throw new Error("Not connected to SQLite database");
189 | }
190 |
191 | // In SQLite, schema parameter is ignored since there's only one schema per database file
192 | // All tables exist in a single namespace within the SQLite database
193 | try {
194 | const row = this.db
195 | .prepare(
196 | `
197 | SELECT name FROM sqlite_master
198 | WHERE type='table' AND name = ?
199 | `
200 | )
201 | .get(tableName) as SQLiteTableNameRow | undefined;
202 |
203 | return !!row;
204 | } catch (error) {
205 | throw error;
206 | }
207 | }
208 |
209 | async getTableIndexes(tableName: string, schema?: string): Promise<TableIndex[]> {
210 | if (!this.db) {
211 | throw new Error("Not connected to SQLite database");
212 | }
213 |
214 | // In SQLite, schema parameter is ignored (no schema concept)
215 | try {
216 | // Get all indexes for the specified table
217 | const indexInfoRows = this.db
218 | .prepare(
219 | `
220 | SELECT
221 | name as index_name,
222 | 0 as is_unique
223 | FROM sqlite_master
224 | WHERE type = 'index'
225 | AND tbl_name = ?
226 | `
227 | )
228 | .all(tableName) as { index_name: string; is_unique: number }[];
229 |
230 | // Get unique info from PRAGMA index_list which provides the unique flag
231 | const indexListRows = this.db
232 | .prepare(`PRAGMA index_list(${tableName})`)
233 | .all() as { name: string; unique: number }[];
234 |
235 | // Create a map of index names to unique status
236 | const indexUniqueMap = new Map<string, boolean>();
237 | for (const indexListRow of indexListRows) {
238 | indexUniqueMap.set(indexListRow.name, indexListRow.unique === 1);
239 | }
240 |
241 | // Get the primary key info
242 | const tableInfo = this.db
243 | .prepare(`PRAGMA table_info(${tableName})`)
244 | .all() as SQLiteTableInfo[];
245 |
246 | // Find primary key columns
247 | const pkColumns = tableInfo.filter((col) => col.pk > 0).map((col) => col.name);
248 |
249 | const results: TableIndex[] = [];
250 |
251 | // Add regular indexes
252 | for (const indexInfo of indexInfoRows) {
253 | // Get the columns for this index
254 | const indexDetailRows = this.db
255 | .prepare(`PRAGMA index_info(${indexInfo.index_name})`)
256 | .all() as {
257 | name: string;
258 | }[];
259 | const columnNames = indexDetailRows.map((row) => row.name);
260 |
261 | results.push({
262 | index_name: indexInfo.index_name,
263 | column_names: columnNames,
264 | is_unique: indexUniqueMap.get(indexInfo.index_name) || false,
265 | is_primary: false,
266 | });
267 | }
268 |
269 | // Add primary key if it exists
270 | if (pkColumns.length > 0) {
271 | results.push({
272 | index_name: "PRIMARY",
273 | column_names: pkColumns,
274 | is_unique: true,
275 | is_primary: true,
276 | });
277 | }
278 |
279 | return results;
280 | } catch (error) {
281 | throw error;
282 | }
283 | }
284 |
285 | async getTableSchema(tableName: string, schema?: string): Promise<TableColumn[]> {
286 | if (!this.db) {
287 | throw new Error("Not connected to SQLite database");
288 | }
289 |
290 | // In SQLite, schema parameter is ignored for the following reasons:
291 | // 1. SQLite doesn't have schemas in the same way as PostgreSQL or MySQL
292 | // 2. Each SQLite database file is its own separate namespace
293 | // 3. The PRAGMA commands operate on the current database connection
294 | try {
295 | const rows = this.db.prepare(`PRAGMA table_info(${tableName})`).all() as SQLiteTableInfo[];
296 |
297 | // Convert SQLite schema format to our standard TableColumn format
298 | const columns = rows.map((row) => ({
299 | column_name: row.name,
300 | data_type: row.type,
301 | // In SQLite, primary key columns are automatically NOT NULL even if notnull=0
302 | is_nullable: (row.notnull === 1 || row.pk > 0) ? "NO" : "YES",
303 | column_default: row.dflt_value,
304 | }));
305 |
306 | return columns;
307 | } catch (error) {
308 | throw error;
309 | }
310 | }
311 |
312 | async getStoredProcedures(schema?: string): Promise<string[]> {
313 | if (!this.db) {
314 | throw new Error("Not connected to SQLite database");
315 | }
316 |
317 | // SQLite doesn't have built-in stored procedures like other databases.
318 | // While SQLite does support user-defined functions, these are registered through
319 | // the C/C++ API or language bindings and cannot be introspected through SQL.
320 | // Triggers exist in SQLite but they're not the same as stored procedures.
321 | //
322 | // We return an empty array because:
323 | // 1. SQLite has no native stored procedure concept
324 | // 2. User-defined functions cannot be listed via SQL queries
325 | // 3. We don't want to misrepresent triggers as stored procedures
326 |
327 | return [];
328 | }
329 |
330 | async getStoredProcedureDetail(procedureName: string, schema?: string): Promise<StoredProcedure> {
331 | if (!this.db) {
332 | throw new Error("Not connected to SQLite database");
333 | }
334 |
335 | // SQLite doesn't have true stored procedures:
336 | // 1. SQLite doesn't support the CREATE PROCEDURE syntax
337 | // 2. User-defined functions are created programmatically, not stored in the DB
338 | // 3. Cannot introspect program-defined functions through SQL
339 |
340 | // Throw an error since SQLite doesn't support stored procedures
341 | throw new Error(
342 | "SQLite does not support stored procedures. Functions are defined programmatically through the SQLite API, not stored in the database."
343 | );
344 | }
345 |
346 |
347 | async executeSQL(sql: string, options: ExecuteOptions): Promise<SQLResult> {
348 | if (!this.db) {
349 | throw new Error("Not connected to SQLite database");
350 | }
351 |
352 | try {
353 | // Check if this is a multi-statement query
354 | const statements = sql.split(';')
355 | .map(statement => statement.trim())
356 | .filter(statement => statement.length > 0);
357 |
358 | if (statements.length === 1) {
359 | // Single statement - determine if it returns data
360 | let processedStatement = statements[0];
361 | const trimmedStatement = statements[0].toLowerCase().trim();
362 | const isReadStatement = trimmedStatement.startsWith('select') ||
363 | trimmedStatement.startsWith('with') ||
364 | trimmedStatement.startsWith('explain') ||
365 | trimmedStatement.startsWith('analyze') ||
366 | (trimmedStatement.startsWith('pragma') &&
367 | (trimmedStatement.includes('table_info') ||
368 | trimmedStatement.includes('index_info') ||
369 | trimmedStatement.includes('index_list') ||
370 | trimmedStatement.includes('foreign_key_list')));
371 |
372 | // Apply maxRows limit to SELECT queries if specified (not PRAGMA/ANALYZE)
373 | if (options.maxRows) {
374 | processedStatement = SQLRowLimiter.applyMaxRows(processedStatement, options.maxRows);
375 | }
376 |
377 | if (isReadStatement) {
378 | const rows = this.db.prepare(processedStatement).all();
379 | return { rows };
380 | } else {
381 | // Use run() for statements that don't return data
382 | this.db.prepare(processedStatement).run();
383 | return { rows: [] };
384 | }
385 | } else {
386 | // Multiple statements - use native .exec() for optimal performance
387 | // Note: .exec() doesn't return results, so we need to handle SELECT statements differently
388 | const readStatements = [];
389 | const writeStatements = [];
390 |
391 | // Separate read and write operations
392 | for (const statement of statements) {
393 | const trimmedStatement = statement.toLowerCase().trim();
394 | if (trimmedStatement.startsWith('select') ||
395 | trimmedStatement.startsWith('with') ||
396 | trimmedStatement.startsWith('explain') ||
397 | trimmedStatement.startsWith('analyze') ||
398 | (trimmedStatement.startsWith('pragma') &&
399 | (trimmedStatement.includes('table_info') ||
400 | trimmedStatement.includes('index_info') ||
401 | trimmedStatement.includes('index_list') ||
402 | trimmedStatement.includes('foreign_key_list')))) {
403 | readStatements.push(statement);
404 | } else {
405 | writeStatements.push(statement);
406 | }
407 | }
408 |
409 | // Execute write statements using native .exec() for optimal performance
410 | if (writeStatements.length > 0) {
411 | this.db.exec(writeStatements.join('; '));
412 | }
413 |
414 | // Execute read statements individually to collect results
415 | let allRows: any[] = [];
416 | for (let statement of readStatements) {
417 | // Apply maxRows limit to SELECT queries if specified
418 | statement = SQLRowLimiter.applyMaxRows(statement, options.maxRows);
419 | const result = this.db.prepare(statement).all();
420 | allRows.push(...result);
421 | }
422 |
423 | return { rows: allRows };
424 | }
425 | } catch (error) {
426 | throw error;
427 | }
428 | }
429 | }
430 |
431 | // Register the SQLite connector
432 | const sqliteConnector = new SQLiteConnector();
433 | ConnectorRegistry.register(sqliteConnector);
434 |
```
--------------------------------------------------------------------------------
/src/connectors/__tests__/mysql.integration.test.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { describe, it, expect, beforeAll, afterAll } from 'vitest';
2 | import { MySqlContainer, StartedMySqlContainer } from '@testcontainers/mysql';
3 | import { MySQLConnector } from '../mysql/index.js';
4 | import { IntegrationTestBase, type TestContainer, type DatabaseTestConfig } from './shared/integration-test-base.js';
5 | import type { Connector } from '../interface.js';
6 |
7 | class MySQLTestContainer implements TestContainer {
8 | constructor(private container: StartedMySqlContainer) {}
9 |
10 | getConnectionUri(): string {
11 | return this.container.getConnectionUri();
12 | }
13 |
14 | async stop(): Promise<void> {
15 | await this.container.stop();
16 | }
17 | }
18 |
19 | class MySQLIntegrationTest extends IntegrationTestBase<MySQLTestContainer> {
20 | constructor() {
21 | const config: DatabaseTestConfig = {
22 | expectedSchemas: ['testdb', 'information_schema'],
23 | expectedTables: ['users', 'orders', 'products'],
24 | supportsStoredProcedures: false // Disabled due to container privilege restrictions
25 | };
26 | super(config);
27 | }
28 |
29 | async createContainer(): Promise<MySQLTestContainer> {
30 | const container = await new MySqlContainer('mysql:8.0')
31 | .withDatabase('testdb')
32 | .withRootPassword('rootpass')
33 | .start();
34 |
35 | return new MySQLTestContainer(container);
36 | }
37 |
38 | createConnector(): Connector {
39 | return new MySQLConnector();
40 | }
41 |
42 | createSSLTests(): void {
43 | describe('SSL Connection Tests', () => {
44 | it('should handle SSL mode disable connection', async () => {
45 | const baseUri = this.connectionString;
46 | const sslDisabledUri = baseUri.includes('?') ?
47 | `${baseUri}&sslmode=disable` :
48 | `${baseUri}?sslmode=disable`;
49 |
50 | const sslDisabledConnector = new MySQLConnector();
51 |
52 | // Should connect successfully with sslmode=disable
53 | await expect(sslDisabledConnector.connect(sslDisabledUri)).resolves.not.toThrow();
54 |
55 | // Check SSL status - cipher should be empty when SSL is disabled
56 | const result = await sslDisabledConnector.executeSQL("SHOW SESSION STATUS LIKE 'Ssl_cipher'", {});
57 | expect(result.rows).toHaveLength(1);
58 | expect(result.rows[0].Variable_name).toBe('Ssl_cipher');
59 | expect(result.rows[0].Value).toBe('');
60 |
61 | await sslDisabledConnector.disconnect();
62 | });
63 |
64 | it('should handle SSL mode require connection', async () => {
65 | const baseUri = this.connectionString;
66 | const sslRequiredUri = baseUri.includes('?') ?
67 | `${baseUri}&sslmode=require` :
68 | `${baseUri}?sslmode=require`;
69 |
70 | const sslRequiredConnector = new MySQLConnector();
71 |
72 | // In test containers, SSL may not be supported, so we expect either success or SSL not supported error
73 | try {
74 | await sslRequiredConnector.connect(sslRequiredUri);
75 |
76 | // If connection succeeds, check SSL status - cipher should be non-empty when SSL is enabled
77 | const result = await sslRequiredConnector.executeSQL("SHOW SESSION STATUS LIKE 'Ssl_cipher'", {});
78 | expect(result.rows).toHaveLength(1);
79 | expect(result.rows[0].Variable_name).toBe('Ssl_cipher');
80 | expect(result.rows[0].Value).not.toBe('');
81 | expect(result.rows[0].Value).toBeTruthy();
82 |
83 | await sslRequiredConnector.disconnect();
84 | } catch (error) {
85 | // If SSL is not supported by the test container, that's expected
86 | expect(error instanceof Error).toBe(true);
87 | expect((error as Error).message).toMatch(/SSL|does not support SSL/);
88 | }
89 | });
90 | });
91 | }
92 |
93 | async setupTestData(connector: Connector): Promise<void> {
94 | // Create users table
95 | await connector.executeSQL(`
96 | CREATE TABLE IF NOT EXISTS users (
97 | id INT AUTO_INCREMENT PRIMARY KEY,
98 | name VARCHAR(100) NOT NULL,
99 | email VARCHAR(100) UNIQUE NOT NULL,
100 | age INT
101 | )
102 | `, {});
103 |
104 | // Create orders table
105 | await connector.executeSQL(`
106 | CREATE TABLE IF NOT EXISTS orders (
107 | id INT AUTO_INCREMENT PRIMARY KEY,
108 | user_id INT,
109 | total DECIMAL(10,2),
110 | created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
111 | FOREIGN KEY (user_id) REFERENCES users(id)
112 | )
113 | `, {});
114 |
115 | // Create products table in main database
116 | await connector.executeSQL(`
117 | CREATE TABLE IF NOT EXISTS products (
118 | id INT AUTO_INCREMENT PRIMARY KEY,
119 | name VARCHAR(100) NOT NULL,
120 | price DECIMAL(10,2)
121 | )
122 | `, {});
123 |
124 | // Insert test data
125 | await connector.executeSQL(`
126 | INSERT IGNORE INTO users (name, email, age) VALUES
127 | ('John Doe', '[email protected]', 30),
128 | ('Jane Smith', '[email protected]', 25),
129 | ('Bob Johnson', '[email protected]', 35)
130 | `, {});
131 |
132 | await connector.executeSQL(`
133 | INSERT IGNORE INTO orders (user_id, total) VALUES
134 | (1, 99.99),
135 | (1, 149.50),
136 | (2, 75.25)
137 | `, {});
138 |
139 | await connector.executeSQL(`
140 | INSERT IGNORE INTO products (name, price) VALUES
141 | ('Widget A', 19.99),
142 | ('Widget B', 29.99)
143 | `, {});
144 |
145 | // Note: Stored procedures/functions are skipped in tests due to container privilege restrictions
146 | }
147 | }
148 |
149 | // Create the test suite
150 | const mysqlTest = new MySQLIntegrationTest();
151 |
152 | describe('MySQL Connector Integration Tests', () => {
153 | beforeAll(async () => {
154 | await mysqlTest.setup();
155 | }, 120000);
156 |
157 | afterAll(async () => {
158 | await mysqlTest.cleanup();
159 | });
160 |
161 | // Include all common tests
162 | mysqlTest.createConnectionTests();
163 | mysqlTest.createSchemaTests();
164 | mysqlTest.createTableTests();
165 | mysqlTest.createSQLExecutionTests();
166 | if (mysqlTest.config.supportsStoredProcedures) {
167 | mysqlTest.createStoredProcedureTests();
168 | }
169 | mysqlTest.createErrorHandlingTests();
170 | mysqlTest.createSSLTests();
171 |
172 | describe('MySQL-specific Features', () => {
173 | it('should execute multiple statements with native support', async () => {
174 | // First insert the test data
175 | await mysqlTest.connector.executeSQL(`
176 | INSERT INTO users (name, email, age) VALUES ('Multi User 1', '[email protected]', 30);
177 | INSERT INTO users (name, email, age) VALUES ('Multi User 2', '[email protected]', 35);
178 | `, {});
179 |
180 | // Then check the count
181 | const result = await mysqlTest.connector.executeSQL(
182 | "SELECT COUNT(*) as total FROM users WHERE email LIKE 'multi%'",
183 | {}
184 | );
185 |
186 | expect(result.rows).toHaveLength(1);
187 | expect(Number(result.rows[0].total)).toBe(2);
188 | });
189 |
190 | it('should handle MySQL-specific data types', async () => {
191 | await mysqlTest.connector.executeSQL(`
192 | CREATE TABLE IF NOT EXISTS mysql_types_test (
193 | id INT AUTO_INCREMENT PRIMARY KEY,
194 | json_data JSON,
195 | timestamp_val TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
196 | enum_val ENUM('small', 'medium', 'large') DEFAULT 'medium'
197 | )
198 | `, {});
199 |
200 | await mysqlTest.connector.executeSQL(`
201 | INSERT INTO mysql_types_test (json_data, enum_val)
202 | VALUES ('{"key": "value"}', 'large')
203 | `, {});
204 |
205 | const result = await mysqlTest.connector.executeSQL(
206 | 'SELECT * FROM mysql_types_test WHERE id = LAST_INSERT_ID()',
207 | {}
208 | );
209 |
210 | expect(result.rows).toHaveLength(1);
211 | expect(result.rows[0].enum_val).toBe('large');
212 | expect(result.rows[0].json_data).toBeDefined();
213 | });
214 |
215 | it('should handle MySQL auto-increment properly', async () => {
216 | const insertResult = await mysqlTest.connector.executeSQL(
217 | "INSERT INTO users (name, email, age) VALUES ('Auto Inc Test', '[email protected]', 40)",
218 | {}
219 | );
220 |
221 | expect(insertResult).toBeDefined();
222 |
223 | const selectResult = await mysqlTest.connector.executeSQL(
224 | 'SELECT LAST_INSERT_ID() as last_id',
225 | {}
226 | );
227 |
228 | expect(selectResult.rows).toHaveLength(1);
229 | expect(Number(selectResult.rows[0].last_id)).toBeGreaterThan(0);
230 | });
231 |
232 | it('should work with MySQL-specific functions', async () => {
233 | const result = await mysqlTest.connector.executeSQL(`
234 | SELECT
235 | VERSION() as mysql_version,
236 | DATABASE() as current_db,
237 | USER() as current_user_info,
238 | NOW() as timestamp_val
239 | `, {});
240 |
241 | expect(result.rows).toHaveLength(1);
242 | expect(result.rows[0].mysql_version).toBeDefined();
243 | expect(result.rows[0].current_db).toBe('testdb');
244 | expect(result.rows[0].current_user_info).toBeDefined();
245 | expect(result.rows[0].timestamp_val).toBeDefined();
246 | });
247 |
248 | it('should handle MySQL transactions correctly', async () => {
249 | // Test explicit transaction
250 | await mysqlTest.connector.executeSQL(`
251 | START TRANSACTION;
252 | INSERT INTO users (name, email, age) VALUES ('Transaction Test 1', '[email protected]', 45);
253 | INSERT INTO users (name, email, age) VALUES ('Transaction Test 2', '[email protected]', 50);
254 | COMMIT;
255 | `, {});
256 |
257 | const result = await mysqlTest.connector.executeSQL(
258 | "SELECT COUNT(*) as count FROM users WHERE email LIKE 'trans%@example.com'",
259 | {}
260 | );
261 | expect(Number(result.rows[0].count)).toBe(2);
262 | });
263 |
264 | it('should handle MySQL rollback correctly', async () => {
265 | // Get initial count
266 | const beforeResult = await mysqlTest.connector.executeSQL(
267 | "SELECT COUNT(*) as count FROM users WHERE email = '[email protected]'",
268 | {}
269 | );
270 | const beforeCount = Number(beforeResult.rows[0].count);
271 |
272 | // Test rollback
273 | await mysqlTest.connector.executeSQL(`
274 | START TRANSACTION;
275 | INSERT INTO users (name, email, age) VALUES ('Rollback Test', '[email protected]', 55);
276 | ROLLBACK;
277 | `, {});
278 |
279 | const afterResult = await mysqlTest.connector.executeSQL(
280 | "SELECT COUNT(*) as count FROM users WHERE email = '[email protected]'",
281 | {}
282 | );
283 | const afterCount = Number(afterResult.rows[0].count);
284 |
285 | expect(afterCount).toBe(beforeCount);
286 | });
287 |
288 | it('should respect maxRows limit for SELECT queries', async () => {
289 | // Test basic SELECT with maxRows limit
290 | const result = await mysqlTest.connector.executeSQL(
291 | 'SELECT * FROM users ORDER BY id',
292 | { maxRows: 2 }
293 | );
294 |
295 | expect(result.rows).toHaveLength(2);
296 | expect(result.rows[0]).toHaveProperty('name');
297 | expect(result.rows[1]).toHaveProperty('name');
298 | });
299 |
300 | it('should respect existing LIMIT clause when lower than maxRows', async () => {
301 | // Test when existing LIMIT is lower than maxRows
302 | const result = await mysqlTest.connector.executeSQL(
303 | 'SELECT * FROM users ORDER BY id LIMIT 1',
304 | { maxRows: 3 }
305 | );
306 |
307 | expect(result.rows).toHaveLength(1);
308 | expect(result.rows[0]).toHaveProperty('name');
309 | });
310 |
311 | it('should use maxRows when existing LIMIT is higher', async () => {
312 | // Test when existing LIMIT is higher than maxRows
313 | const result = await mysqlTest.connector.executeSQL(
314 | 'SELECT * FROM users ORDER BY id LIMIT 10',
315 | { maxRows: 2 }
316 | );
317 |
318 | expect(result.rows).toHaveLength(2);
319 | expect(result.rows[0]).toHaveProperty('name');
320 | expect(result.rows[1]).toHaveProperty('name');
321 | });
322 |
323 | it('should not affect non-SELECT queries', async () => {
324 | // Test that maxRows doesn't affect INSERT/UPDATE/DELETE
325 | const insertResult = await mysqlTest.connector.executeSQL(
326 | "INSERT INTO users (name, email, age) VALUES ('MaxRows Test', '[email protected]', 25)",
327 | { maxRows: 1 }
328 | );
329 |
330 | expect(insertResult.rows).toHaveLength(0); // INSERTs don't return rows by default
331 |
332 | // Verify the insert worked
333 | const selectResult = await mysqlTest.connector.executeSQL(
334 | "SELECT * FROM users WHERE email = '[email protected]'",
335 | {}
336 | );
337 | expect(selectResult.rows).toHaveLength(1);
338 | expect(selectResult.rows[0].name).toBe('MaxRows Test');
339 | });
340 |
341 | it('should handle maxRows with complex queries', async () => {
342 | // Test maxRows with JOIN queries
343 | const result = await mysqlTest.connector.executeSQL(`
344 | SELECT u.name, o.total
345 | FROM users u
346 | JOIN orders o ON u.id = o.user_id
347 | ORDER BY o.total DESC
348 | `, { maxRows: 2 });
349 |
350 | expect(result.rows.length).toBeLessThanOrEqual(2);
351 | expect(result.rows.length).toBeGreaterThan(0);
352 | expect(result.rows[0]).toHaveProperty('name');
353 | expect(result.rows[0]).toHaveProperty('total');
354 | });
355 |
356 | it('should not apply maxRows to CTE queries (WITH clause)', async () => {
357 | // Test that maxRows is not applied to CTE queries (WITH clause)
358 | try {
359 | const result = await mysqlTest.connector.executeSQL(`
360 | WITH user_summary AS (
361 | SELECT name, age FROM users WHERE age IS NOT NULL
362 | )
363 | SELECT * FROM user_summary ORDER BY age
364 | `, { maxRows: 2 });
365 |
366 | // Should return all rows since WITH queries are not limited
367 | expect(result.rows.length).toBeGreaterThan(2);
368 | expect(result.rows[0]).toHaveProperty('name');
369 | expect(result.rows[0]).toHaveProperty('age');
370 | } catch (error) {
371 | // Some MySQL versions might not support CTE, that's okay
372 | console.log('CTE not supported in this MySQL version, skipping test');
373 | }
374 | });
375 |
376 | it('should handle maxRows with multiple SELECT statements', async () => {
377 | // Test maxRows with multiple SELECT statements only
378 | const result = await mysqlTest.connector.executeSQL(`
379 | SELECT name FROM users WHERE age > 20 ORDER BY name LIMIT 10;
380 | SELECT name FROM users WHERE age > 25 ORDER BY name LIMIT 10;
381 | `, { maxRows: 1 });
382 |
383 | // Should return only 1 row from each SELECT statement (due to maxRows limit)
384 | // MySQL multi-statement may return more complex results, so we check that maxRows was applied
385 | expect(result.rows.length).toBeGreaterThan(0);
386 | expect(result.rows.length).toBeLessThanOrEqual(2); // At most 1 from each SELECT
387 | if (result.rows.length > 0) {
388 | expect(result.rows[0]).toHaveProperty('name');
389 | }
390 | });
391 |
392 | it('should ignore maxRows when not specified', async () => {
393 | // Test without maxRows - should return all rows
394 | const result = await mysqlTest.connector.executeSQL(
395 | 'SELECT * FROM users ORDER BY id',
396 | {}
397 | );
398 |
399 | // Should return all users (at least the original 3 plus any added in previous tests)
400 | expect(result.rows.length).toBeGreaterThanOrEqual(3);
401 | });
402 | });
403 | });
```