# Directory Structure
```
├── .gitignore
├── package-lock.json
├── package.json
├── README.md
├── src
│ └── index.ts
└── tsconfig.json
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | node_modules/
2 | dist/
3 | .env
4 |
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # Ollama MCP Database Assistant
2 |
3 | An interactive chat interface that combines Ollama's LLM capabilities with PostgreSQL database access through the Model Context Protocol (MCP). Ask questions about your data in natural language and get AI-powered responses backed by real SQL queries.
4 |
5 | ## Features
6 |
7 | - Natural language interface to your PostgreSQL database
8 | - Automatic SQL query generation
9 | - Schema-aware responses
10 | - Interactive chat interface
11 | - Secure, read-only database access
12 |
13 | ## Prerequisites
14 |
15 | - Node.js 16 or higher
16 | - A running PostgreSQL database
17 | - [Ollama](https://ollama.ai) installed and running locally
18 | - The qwen2.5-coder:7b-instruct model pulled in Ollama
19 |
20 | ## Setup
21 |
22 | 1. Clone the repository:
23 |
24 | ```bash
25 | git clone [your-repo-url]
26 | cd [your-repo-name]
27 | ```
28 |
29 | 2. Install dependencies:
30 |
31 | ```bash
32 | npm install
33 | ```
34 |
35 | 3. Pull the required Ollama model:
36 |
37 | ```bash
38 | ollama pull qwen2.5-coder:7b-instruct
39 | ```
40 |
41 | 4. Create a `.env` file in the project root:
42 |
43 | ```bash
44 | DATABASE_URL=postgresql://user:password@localhost:5432/dbname
45 | OLLAMA_MODEL=qwen2.5-coder:7b-instruct # Optional - this is the default
46 | ```
47 |
48 | ## Usage
49 |
50 | 1. Start the chat interface:
51 |
52 | ```bash
53 | npm start
54 | ```
55 |
56 | 2. Ask questions about your data in natural language:
57 |
58 | ```
59 | Connected to database. You can now ask questions about your data.
60 | Type "exit" to quit.
61 |
62 | What would you like to know about your data? Which products generated the most revenue last month?
63 | Analyzing...
64 |
65 | [AI will generate and execute a SQL query, then explain the results]
66 | ```
67 |
68 | 3. Type 'exit' to quit the application.
69 |
70 | ## How It Works
71 |
72 | 1. The application connects to your PostgreSQL database through the [PostgreSQL MCP server](https://www.npmjs.com/package/@modelcontextprotocol/server-postgres)
73 | 2. It loads and caches your database schema
74 | 3. When you ask a question:
75 | - The schema and question are sent to Ollama
76 | - Ollama generates an appropriate SQL query
77 | - The query is executed through MCP
78 | - Results are sent back to Ollama for interpretation
79 | - You receive a natural language response
80 |
81 | ## Environment Variables
82 |
83 | | Variable | Description | Default |
84 | | ------------ | ---------------------------- | ------------------------- |
85 | | DATABASE_URL | PostgreSQL connection string | Required |
86 | | OLLAMA_MODEL | Ollama model to use | qwen2.5-coder:7b-instruct |
87 |
88 | ## Security
89 |
90 | - All database access is read-only
91 | - SQL queries are restricted to SELECT statements
92 | - Database credentials are kept secure in your .env file
93 |
94 | ## Development
95 |
96 | Built with:
97 |
98 | - TypeScript
99 | - Model Context Protocol (MCP)
100 | - Ollama
101 | - PostgreSQL
102 |
103 | ## Troubleshooting
104 |
105 | ### Common Issues
106 |
107 | 1. "Failed to connect to database"
108 |
109 | - Check your DATABASE_URL in .env
110 | - Verify PostgreSQL is running
111 | - Check network connectivity
112 |
113 | 2. "Failed to connect to Ollama"
114 |
115 | - Ensure Ollama is running (`ollama serve`)
116 | - Verify the model is installed (`ollama list`)
117 |
118 | 3. "Error executing query"
119 | - Check database permissions
120 | - Verify table/column names in the schema
121 |
122 | ## License
123 |
124 | MIT
125 |
126 | ## Contributing
127 |
128 | 1. Fork the repository
129 | 2. Create your feature branch
130 | 3. Commit your changes
131 | 4. Push to the branch
132 | 5. Open a Pull Request
133 |
```
--------------------------------------------------------------------------------
/tsconfig.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "compilerOptions": {
3 | "target": "ES2023",
4 | "module": "NodeNext",
5 | "rootDir": "./src",
6 | "outDir": "./dist",
7 | "esModuleInterop": true,
8 | "forceConsistentCasingInFileNames": true,
9 | "strict": true,
10 | "skipLibCheck": true
11 | },
12 | "include": ["src/**/*"],
13 | "exclude": ["node_modules"]
14 | }
15 |
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "name": "ollama-mcp-db",
3 | "version": "1.0.0",
4 | "description": "An interactive chat interface that combines Ollama's LLM capabilities with PostgreSQL database access through the Model Context Protocol (MCP).",
5 | "type": "module",
6 | "main": "dist/index.js",
7 | "scripts": {
8 | "start": "tsx src/index.ts",
9 | "build": "tsc",
10 | "serve": "node dist/index.js"
11 | },
12 | "keywords": [
13 | "ollama",
14 | "mcp",
15 | "ai",
16 | "llm",
17 | "postgres",
18 | "sql"
19 | ],
20 | "author": "",
21 | "license": "MIT",
22 | "devDependencies": {
23 | "@types/node": "^20.0.0",
24 | "ts-node": "^10.9.2",
25 | "tsx": "^4.19.2",
26 | "typescript": "^5.7.2"
27 | },
28 | "dependencies": {
29 | "@modelcontextprotocol/sdk": "^1.0.4",
30 | "dotenv": "^16.4.7",
31 | "ollama": "^0.5.11"
32 | }
33 | }
34 |
```
--------------------------------------------------------------------------------
/src/index.ts:
--------------------------------------------------------------------------------
```typescript
1 | import ollama from "ollama";
2 | import { Client } from "@modelcontextprotocol/sdk/client/index.js";
3 | import { StdioClientTransport } from "@modelcontextprotocol/sdk/client/stdio.js";
4 | import {
5 | ReadResourceResultSchema,
6 | ListResourcesResultSchema,
7 | CallToolResultSchema,
8 | } from "@modelcontextprotocol/sdk/types.js";
9 | import dotenv from "dotenv";
10 | import { fileURLToPath } from "url";
11 |
12 | // Load environment variables from .env file
13 | dotenv.config();
14 |
15 | // Check for required environment variables
16 | const databaseUrl = process.env.DATABASE_URL;
17 | if (!databaseUrl) {
18 | console.error("Error: DATABASE_URL not found in environment or .env file");
19 | console.error("Please create a .env file with the following format:");
20 | console.error("DATABASE_URL=postgres://user:password@localhost:5432/dbname");
21 | process.exit(1);
22 | }
23 |
24 | interface DatabaseSchema {
25 | column_name: string;
26 | data_type: string;
27 | }
28 |
29 | interface ColumnMetadata {
30 | description: string;
31 | examples: string[];
32 | foreignKey?: {
33 | table: string;
34 | column: string;
35 | };
36 | }
37 |
38 | class OllamaMCPHost {
39 | private client: Client;
40 | private transport: StdioClientTransport;
41 | private modelName: string;
42 | private schemaCache: Map<string, DatabaseSchema[]> = new Map();
43 | private columnMetadata: Map<string, Map<string, ColumnMetadata>> = new Map();
44 | private chatHistory: { role: string; content: string }[] = [];
45 | private readonly MAX_HISTORY_LENGTH = 20;
46 | private readonly MAX_RETRIES = 5;
47 |
48 | private static readonly QUERY_GUIDELINES = `
49 | When analyzing questions:
50 | 1. First write a SQL query to get the necessary information. Identify which tables contain the relevant information by looking at:
51 | - Table names and their purposes
52 | - Column names and descriptions
53 | - Foreign key relationships
54 | 2. Use the 'query' tool to execute the SQL query
55 | 3. If unsure about table contents, write a sample query first:
56 | SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name LIMIT 5;
57 | 4. For complex questions, break down into multiple queries:
58 | - First query to validate data availability
59 | - Second query to get detailed information
60 | 5. Always include appropriate JOIN conditions when combining tables
61 | 6. Use WHERE clauses to filter irrelevant data
62 | 7. Consider using ORDER BY for sorted results
63 |
64 | Important: Only use SELECT statements - no modifications allowed!
65 |
66 | When you are finished, analyze the results and provide a natural language response.`;
67 |
68 | constructor(modelName?: string) {
69 | this.modelName =
70 | modelName || process.env.OLLAMA_MODEL || "qwen2.5-coder:7b-instruct";
71 | this.transport = new StdioClientTransport({
72 | command: "npx",
73 | args: ["-y", "@modelcontextprotocol/server-postgres", databaseUrl!],
74 | });
75 | this.client = new Client(
76 | { name: "ollama-mcp-host", version: "1.0.0" },
77 | { capabilities: {} }
78 | );
79 | }
80 |
81 | private async detectTableRelationships(): Promise<void> {
82 | // Query the database to find foreign key relationships
83 | const sql = `
84 | SELECT
85 | tc.table_name as table_name,
86 | kcu.column_name as column_name,
87 | ccu.table_name AS foreign_table_name,
88 | ccu.column_name AS foreign_column_name
89 | FROM information_schema.table_constraints tc
90 | JOIN information_schema.key_column_usage kcu
91 | ON tc.constraint_name = kcu.constraint_name
92 | JOIN information_schema.constraint_column_usage ccu
93 | ON ccu.constraint_name = tc.constraint_name
94 | WHERE constraint_type = 'FOREIGN KEY'
95 | `;
96 |
97 | try {
98 | const result = await this.executeQuery(sql);
99 | const relationships = JSON.parse(result);
100 |
101 | // Create initial metadata for foreign keys
102 | relationships.forEach((rel: any) => {
103 | const tableMetadata =
104 | this.columnMetadata.get(rel.table_name) || new Map();
105 |
106 | tableMetadata.set(rel.column_name, {
107 | description: `Foreign key referencing ${rel.foreign_table_name}.${rel.foreign_column_name}`,
108 | examples: [],
109 | foreignKey: {
110 | table: rel.foreign_table_name,
111 | column: rel.foreign_column_name,
112 | },
113 | });
114 |
115 | this.columnMetadata.set(rel.table_name, tableMetadata);
116 | });
117 | } catch (error) {
118 | console.error("Error detecting table relationships:", error);
119 | }
120 | }
121 |
122 | private buildSystemPrompt(includeErrorContext: string = ""): string {
123 | let prompt =
124 | "You are a data analyst assistant. You have access to a PostgreSQL database with these tables:\n\n";
125 |
126 | // Add detailed schema information
127 | for (const [tableName, schema] of this.schemaCache.entries()) {
128 | prompt += `Table: ${tableName}\n`;
129 | prompt += "Columns:\n";
130 |
131 | for (const column of schema) {
132 | const metadata = this.columnMetadata
133 | .get(tableName)
134 | ?.get(column.column_name);
135 | prompt += `- ${column.column_name} (${column.data_type})`;
136 |
137 | if (metadata) {
138 | prompt += `: ${metadata.description}`;
139 | if (metadata.foreignKey) {
140 | prompt += ` [References ${metadata.foreignKey.table}.${metadata.foreignKey.column}]`;
141 | }
142 | }
143 | prompt += "\n";
144 | }
145 | prompt += "\n";
146 | }
147 |
148 | // Add query guidelines
149 | prompt += "\nQuery Guidelines:\n";
150 | prompt += OllamaMCPHost.QUERY_GUIDELINES;
151 |
152 | if (includeErrorContext) {
153 | prompt += `\nPrevious Error Context: ${includeErrorContext}\n`;
154 | prompt +=
155 | "Please revise your approach and try a different query strategy.\n";
156 | }
157 |
158 | return prompt;
159 | }
160 |
161 | async connect() {
162 | await this.client.connect(this.transport);
163 |
164 | // First detect relationships
165 | await this.detectTableRelationships();
166 |
167 | // Then load schemas
168 | const resources = await this.client.request(
169 | { method: "resources/list" },
170 | ListResourcesResultSchema
171 | );
172 |
173 | for (const resource of resources.resources) {
174 | if (resource.uri.endsWith("/schema")) {
175 | const schema = await this.client.request(
176 | {
177 | method: "resources/read",
178 | params: { uri: resource.uri },
179 | },
180 | ReadResourceResultSchema
181 | );
182 |
183 | if (schema.contents[0]?.text) {
184 | try {
185 | const tableName = resource.uri.split("/").slice(-2)[0];
186 | this.schemaCache.set(
187 | tableName,
188 | JSON.parse(schema.contents[0].text as string)
189 | );
190 | } catch (error) {
191 | console.error(
192 | `Failed to parse schema for resource ${resource.uri}:`,
193 | error instanceof Error ? error.message : String(error)
194 | );
195 | }
196 | }
197 | }
198 | }
199 | }
200 |
201 | private async executeQuery(sql: string): Promise<string> {
202 | const response = await this.client.request(
203 | {
204 | method: "tools/call",
205 | params: {
206 | name: "query",
207 | arguments: { sql },
208 | },
209 | },
210 | CallToolResultSchema
211 | );
212 |
213 | if (!response.content?.[0]?.text) {
214 | throw new Error("No text content received from query");
215 | }
216 | return response.content[0].text as string;
217 | }
218 |
219 | private addToHistory(role: string, content: string) {
220 | this.chatHistory.push({ role, content });
221 | while (this.chatHistory.length > this.MAX_HISTORY_LENGTH) {
222 | this.chatHistory.shift();
223 | }
224 | }
225 |
226 | async processQuestion(question: string): Promise<string> {
227 | try {
228 | let attemptCount = 0;
229 | let lastError: string | undefined;
230 |
231 | while (attemptCount <= this.MAX_RETRIES) {
232 | const messages = [
233 | { role: "system", content: this.buildSystemPrompt(lastError) },
234 | ...this.chatHistory,
235 | { role: "user", content: question },
236 | ];
237 |
238 | if (attemptCount === 0) {
239 | this.addToHistory("user", question);
240 | }
241 |
242 | console.log(
243 | attemptCount > 0 ? `\nRetry attempt ${attemptCount}...` : ""
244 | );
245 |
246 | // Get response from Ollama
247 | const response = await ollama.chat({
248 | model: this.modelName,
249 | messages: messages,
250 | });
251 |
252 | // Extract SQL query
253 | const sqlMatch = response.message.content.match(
254 | /```sql\n([\s\S]*?)\n```/
255 | );
256 | if (!sqlMatch) {
257 | return response.message.content;
258 | }
259 |
260 | const sql = sqlMatch[1].trim();
261 | console.log("Executing SQL:", sql);
262 |
263 | try {
264 | // Execute the query
265 | const queryResult = await this.executeQuery(sql);
266 | this.addToHistory("assistant", response.message.content);
267 |
268 | // Ask for result interpretation
269 | const interpretationMessages = [
270 | ...messages,
271 | { role: "assistant", content: response.message.content },
272 | {
273 | role: "user",
274 | content: `Here are the results of the SQL query: ${queryResult}\n\nPlease analyze these results and provide a clear summary.`,
275 | },
276 | ];
277 |
278 | const finalResponse = await ollama.chat({
279 | model: this.modelName,
280 | messages: interpretationMessages,
281 | });
282 |
283 | this.addToHistory("assistant", finalResponse.message.content);
284 | return finalResponse.message.content;
285 | } catch (error) {
286 | lastError = error instanceof Error ? error.message : String(error);
287 | if (attemptCount === this.MAX_RETRIES) {
288 | return `I apologize, but I was unable to successfully query the database after ${
289 | this.MAX_RETRIES + 1
290 | } attempts. The last error was: ${lastError}`;
291 | }
292 | }
293 |
294 | attemptCount++;
295 | }
296 |
297 | return "An unexpected error occurred while processing your question.";
298 | } catch (error) {
299 | console.error("Error processing question:", error);
300 | return `An error occurred: ${
301 | error instanceof Error ? error.message : String(error)
302 | }`;
303 | }
304 | }
305 |
306 | async cleanup() {
307 | await this.transport.close();
308 | }
309 | }
310 |
311 | async function main() {
312 | const host = new OllamaMCPHost();
313 | const readline = (await import("readline")).default.createInterface({
314 | input: process.stdin,
315 | output: process.stdout,
316 | });
317 |
318 | try {
319 | await host.connect();
320 | console.log(
321 | "\nConnected to database. You can now ask questions about your data."
322 | );
323 | console.log('Type "exit" to quit.\n');
324 |
325 | const askQuestion = (prompt: string) =>
326 | new Promise<string>((resolve) => {
327 | readline.question(prompt, resolve);
328 | });
329 |
330 | while (true) {
331 | const userInput = await askQuestion(
332 | "\nWhat would you like to know about your data? "
333 | );
334 |
335 | if (userInput.toLowerCase() === "exit") {
336 | console.log("\nGoodbye!\n");
337 | readline.close();
338 | await host.cleanup();
339 | process.exit(0);
340 | }
341 |
342 | console.log("\nAnalyzing...\n");
343 | const answer = await host.processQuestion(userInput);
344 | console.log("\n", answer, "\n");
345 | }
346 | } catch (error) {
347 | console.error(
348 | "Error:",
349 | error instanceof Error ? error.message : String(error)
350 | );
351 | readline.close();
352 | await host.cleanup();
353 | process.exit(1);
354 | }
355 | }
356 |
357 | if (process.argv[1] === fileURLToPath(import.meta.url)) {
358 | main().catch(console.error);
359 | }
360 |
361 | export default OllamaMCPHost;
362 |
```