{"_id":"@bundu/ntl-postgres-mcp-server","name":"@bundu/ntl-postgres-mcp-server","dist-tags":{"beta":"0.2.0-beta.1","latest":"0.2.0-beta.1"},"versions":{"0.2.0-beta.1":{"name":"@bundu/ntl-postgres-mcp-server","version":"0.2.0-beta.1","description":"MCP server for a PostgreSQL-backed openNTL node — a copyable template for Postgres MCP servers on Cloudflare Workers","license":"Apache-2.0","repository":{"type":"git","url":"git+https://github.com/openNTL/ntl.git","directory":"mcp/ntl-postgres-mcp-server"},"homepage":"https://openntl.org/guides/postgres-mcp","type":"module","main":"dist/index.js","scripts":{"build":"tsc","typecheck":"tsc --noEmit","test":"vitest run","test:watch":"vitest","dev":"wrangler dev","deploy":"wrangler deploy","inspect":"npx @modelcontextprotocol/inspector"},"dependencies":{"@modelcontextprotocol/sdk":"^1.22.0","postgres":"^3.4.7","zod":"^3.25.76"},"devDependencies":{"@cloudflare/workers-types":"^5.20260820.1","@electric-sql/pglite":"^0.3.14","typescript":"^5.9.3","vitest":"^3.2.4","wrangler":"^4.42.0"},"engines":{"node":">=20"},"_id":"@bundu/ntl-postgres-mcp-server@0.2.0-beta.1","gitHead":"247af42aba15144e09d40ef2f8f4d808e496774c","types":"./dist/index.d.ts","bugs":{"url":"https://github.com/openNTL/ntl/issues"},"_nodeVersion":"22.22.2","_npmVersion":"10.9.7","dist":{"integrity":"sha512-1WKHDPi5ZfD/OiiuTQfocy7AN5p5CUwJu5V5p4XG+X4BVW3sA31FHhjMHr0Jh5gk65gWgKGZDBXewj0EElZAow==","shasum":"7fc99058dbbfa4ffdfec414c72ebf80502daf3ae","tarball":"https://registry.npmjs.org/@bundu/ntl-postgres-mcp-server/-/ntl-postgres-mcp-server-0.2.0-beta.1.tgz","fileCount":48,"unpackedSize":309486,"signatures":[{"keyid":"SHA256:DhQ8wR5APBvFHLF/+Tc+AYvPOdTpcIDqOhxsBHRwC7U","sig":"MEQCIGoEJ+hZRZRNFjm/TFI8j+7HHEU7jkH3OPtf1Y7G0+9eAiB8UE4vH2n0GMRwkS2sFZD9ifxiYRw29YytAIvFgDTrlg=="}]},"_npmUser":{"name":"bryanfawcett","email":"bryan@nyuchi.com"},"directories":{},"maintainers":[{"name":"bryanfawcett","email":"bryan@nyuchi.com"}],"_npmOperationalInternal":{"host":"s3://npm-registry-packages-npm-production","tmp":"tmp/ntl-postgres-mcp-server_0.2.0-beta.1_1787783632686_0.41876097890204034"},"_hasShrinkwrap":false}},"time":{"created":"2026-08-26T22:33:52.533Z","0.2.0-beta.1":"2026-08-26T22:33:52.899Z","modified":"2026-08-26T22:33:53.138Z"},"maintainers":[{"name":"bryanfawcett","email":"bryan@nyuchi.com"}],"description":"MCP server for a PostgreSQL-backed openNTL node — a copyable template for Postgres MCP servers on Cloudflare Workers","homepage":"https://openntl.org/guides/postgres-mcp","repository":{"type":"git","url":"git+https://github.com/openNTL/ntl.git","directory":"mcp/ntl-postgres-mcp-server"},"bugs":{"url":"https://github.com/openNTL/ntl/issues"},"license":"Apache-2.0","readme":"# ntl-postgres-mcp-server\n\nAn MCP server for a PostgreSQL-backed [openNTL](https://openntl.org) node,\nrunning on Cloudflare Workers.\n\nIt is also **a template**. If you want a Postgres MCP server for your own\nschema, copy this directory and replace the domain tools — the auth, read-only\nenforcement, formatting, error handling and test harness are the parts worth\nkeeping, and they are the parts that take longest to get right.\n\nModelled on the shape of the Supabase MCP server, so an agent that knows one\nknows this one.\n\n```\nnpm install\nnpm test                  # 167 tests, real Postgres, no mocks\nnpx wrangler deploy\n```\n\n## Why you might copy this\n\nMost database MCP servers get four things wrong. This one is built around\navoiding them, and each has tests that would fail if it regressed.\n\n**1. Read-only enforced by the database, not by parsing SQL.**\n\nThe obvious approach is to inspect the query and reject anything that looks\nlike a write. Every implementation that does this is bypassable:\n\n```sql\nWITH x AS (DELETE FROM t RETURNING *) SELECT * FROM x;   -- no leading DELETE\nSELECT my_function_that_writes();                        -- writes in a function\n/* SELECT */ INSERT INTO t VALUES (1);                   -- comment-prefixed\nSELECT * INTO new_table FROM t;                          -- SELECT that creates\n```\n\nA blocklist has to anticipate all of it. Postgres already knows which\nstatements write, so read-only tools run inside `BEGIN TRANSACTION READ ONLY`\nand the *database* rejects the write with SQLSTATE 25006. There is nothing for\na cleverly-phrased statement to slip past.\n\nThere is exactly one way out of a transaction, and it is not clever phrasing —\nit is ending the transaction:\n\n```sql\nCOMMIT; DROP TABLE ntl.synapses;\n```\n\nThat works only over the *simple* query protocol, which accepts several\ncommands in one string and honours transaction control. So the boundary is the\nprotocol, not a check on the string: read-only queries are pinned to the\nextended protocol, which accepts exactly one command, and Postgres rejects the\nsmuggled statement with SQLSTATE 42601 before it runs. The simple-protocol path\nthrows if it is ever reached inside a read-only transaction, and is only\nrouted to when the operator has enabled writes.\n\nThis is worth dwelling on if you copy the file, because an earlier version of\nit had the hole: `COMMIT; DROP TABLE canary` returned *success* on both\ndrivers, and the trailing `COMMIT` that should have complained produced only a\nnotice, which was being swallowed. The transaction was doing its job; the\nprotocol underneath it was not.\n\n[`test/safety.test.ts`](test/safety.test.ts) fires 16 blocklist bypasses and 8\ntransaction escapes at it, and after each one asserts the data is untouched\nrather than merely that an error came back.\n\n**2. Writes off by default.**\n\nA server holding database credentials should not be able to mutate anything\nunless an operator said so. `ALLOW_WRITES` defaults to `false`, and write tools\nare **omitted from the tool list** rather than registered-and-refusing —\noffering a tool that can only fail wastes an agent's turn.\n\n**3. Bounded output.**\n\nA tool that returns a million rows does not help an agent, it exhausts the\ncontext the agent needs to reason with. Output is capped and **says so** when\ntruncated. Silent truncation is worse than an error: an agent that believes it\nsaw a whole table will draw conclusions from a fragment.\n\n**4. Errors that say what to do next.**\n\nPostgres puts the actionable part in `detail` and `hint`, and most wrappers\ndrop both. Every error here carries SQLSTATE, detail, hint and position, and\nthe read-only refusal explains how to enable writes rather than just saying no.\n\n## Tools\n\nRead-only, always available:\n\n| Tool | What it does |\n|---|---|\n| `ntl_list_tables` | Tables, views, row estimates, sizes, optionally columns |\n| `ntl_list_extensions` | Installed and available extensions |\n| `ntl_list_migrations` | Migrations applied through this server |\n| `ntl_generate_typescript_types` | TypeScript interfaces from the live schema |\n| `ntl_execute_sql` | Arbitrary SQL, read-only unless writes are enabled |\n| `ntl_get_advisors` | Security and performance lint over the live database |\n| `ntl_get_activity` | Current connections and slowest statements |\n| `ntl_search_docs` | openNTL documentation, indexed offline |\n\nopenNTL domain tools — these are what make it openNTL's server rather than a\ngeneric Postgres one:\n\n| Tool | What it answers |\n|---|---|\n| `ntl_list_synapses` | What has the node learned? Weights, per-type affinity, decayed vs stored weight |\n| `ntl_get_learning_health` | Is the model actually learning? Exploration and pending ratios |\n| `ntl_list_journal` | Routing decisions and their outcomes — the training data |\n| `ntl_get_node_status` | Identity, topology, activation snapshot, dedup entries |\n\nWrite tools, only when `ALLOW_WRITES=true`:\n\n| Tool | What it does |\n|---|---|\n| `ntl_apply_migration` | Apply DDL in one transaction and record it in a ledger |\n| `ntl_init_schema` | Create the openNTL schema. Idempotent. |\n\nPlus a resource, `ntl://schema/postgres`, serving the reference DDL — useful\nfor an agent about to write a migration.\n\n### Two tools worth stealing\n\n`ntl_get_advisors` is where a database MCP server stops being a SQL pipe. It\nlints the live database — tables granted to `PUBLIC`, `SECURITY DEFINER`\nfunctions without a pinned `search_path`, unindexed foreign keys, missing\nprimary keys, unused indexes, bloat — and every finding carries a remediation.\nA finding an operator cannot act on is noise.\n\nNote what it deliberately does *not* do: no check requires a sequential scan of\nuser data. An advisory pass must not itself be the incident.\n\n`ntl_get_learning_health` is worth copying for its *shape* rather than its\ncontent: it does not just return numbers, it interprets them. Exploration at\nzero across multiple peers means the node has stopped learning. Pending near\n100% means no receipts are arriving and the weights reflect nothing. An agent\nhanded raw counters would have to know the domain to see either.\n\n## Deploying\n\n```bash\n# 1. Hyperdrive, so connections are pooled outside the isolate\nwrangler hyperdrive create ntl-postgres \\\n  --connection-string=\"postgres://user:pass@host/db\"\n# paste the id into wrangler.toml\n\n# 2. Auth. Not optional — see below.\nopenssl rand -hex 32 | wrangler secret put MCP_AUTH_TOKEN\n\n# 3. Ship\nwrangler deploy\n```\n\nThen point a client at it:\n\n```json\n{\n  \"mcpServers\": {\n    \"ntl-postgres\": {\n      \"url\": \"https://ntl-postgres-mcp-server.<your-subdomain>.workers.dev/mcp\",\n      \"headers\": { \"Authorization\": \"Bearer <your-token>\" }\n    }\n  }\n}\n```\n\n### Hyperdrive is not optional either\n\nWithout connection pooling, every Worker invocation opens its own Postgres\nconnection. A traffic burst exhausts `max_connections` long before it exhausts\nanything else, and the failure looks like a database outage rather than a\ncapacity problem. `DATABASE_URL` exists for local development; use Hyperdrive\nin production.\n\n### Auth is required, and the server refuses to run without it\n\nIf `MCP_AUTH_TOKEN` is unset the server returns 500 to every request rather\nthan serving unauthenticated. An MCP server with database credentials and no\nauth is an open SQL console on the public internet. Comparison is\ntiming-safe.\n\nThere is one unauthenticated route, `/health`, which reports nothing about the\ndatabase.\n\n### Connect as a role that cannot write\n\nRead-only transactions bound what the *SQL* can do. They say nothing about what\nthe *role* can do, so a bug in this server is still bounded by the grants on the\ncredentials you hand it. Give the read-only deployment a role with no write\ngrants:\n\n```sql\nCREATE ROLE ntl_mcp_ro LOGIN PASSWORD '…';\nGRANT USAGE ON SCHEMA ntl TO ntl_mcp_ro;\nGRANT SELECT ON ALL TABLES IN SCHEMA ntl TO ntl_mcp_ro;\nALTER DEFAULT PRIVILEGES IN SCHEMA ntl GRANT SELECT ON TABLES TO ntl_mcp_ro;\n```\n\nThen the transaction and the grants have to both fail before anything is\nwritten. This costs nothing and is the difference between one layer and two.\n\n### Enabling writes\n\nWrites are enabled per environment, not per request, so it is a deliberate act\nagainst a named target:\n\n```bash\nwrangler deploy --env admin   # ALLOW_WRITES=true\n```\n\nKeep the read-only deployment as the default one agents talk to.\n\n## Copying this as a template\n\n```bash\ncp -r mcp/ntl-postgres-mcp-server my-mcp-server\n```\n\nThree files to change:\n\n| File | What to do |\n|---|---|\n| `src/db.ts` | Implement `SqlExecutor` for your driver. Four methods. |\n| `src/tools/ntl.ts` | Replace with your domain tools. Delete what does not apply. |\n| `wrangler.toml` | Swap the Hyperdrive binding for what your database needs. |\n\nLargely portable as-is: `src/safety.ts`, `src/format.ts`, `src/index.ts`,\n`src/tools/schema.ts`, `src/tools/sql.ts`, and the whole test harness.\n\n### If you point this at a different engine\n\nTwo assumptions here are Postgres-specific and will bite:\n\n- **Transactional DDL.** `ntl_apply_migration` runs every statement in one\n  transaction and rolls back together. MySQL does not support this; a migration\n  that half-applies leaves a schema no version number describes. If you port\n  this, either apply one statement per migration or say plainly that rollback\n  is not guaranteed.\n- **`SET TRANSACTION READ ONLY`.** The whole safety model rests on the\n  database enforcing it. If your engine has no equivalent, you do not have\n  read-only mode — do not pretend otherwise by falling back to SQL parsing.\n  Check your driver's multi-statement behaviour too: a driver that quietly\n  batches commands over a protocol that honours `COMMIT` gives the transaction\n  away.\n\n## Testing\n\nNo mocks. A mocked database passes while the SQL is wrong, which is the only\nfailure mode these tests exist to catch.\n\n```bash\nnpm test                    # PGlite — real Postgres in WASM, no service needed\nTEST_DATABASE_URL=\"postgres://user@localhost/db\" npm test   # both\n```\n\nThe suite runs against every configured backend. That is not belt-and-braces:\nit caught two real bugs during development.\n\n**Multi-statement SQL.** Parameterised statements go through the extended\nprotocol, which accepts exactly one command — so `ntl_init_schema` failed with\nSQLSTATE 42601 on a script that worked fine as separate statements. Hence\n`exec()` alongside `query()` in `SqlExecutor`.\n\n**BIGINT type instability.** postgres.js returns BIGINT as a string, PGlite as\na number. A tool's structured output changed type depending on which driver\nserved it. Now normalised to a string at the driver boundary — correct anyway,\nsince BIGINT exceeds `Number.MAX_SAFE_INTEGER` and openNTL stores nanosecond\ntimestamps in it.\n\nBoth were invisible to a single-backend suite.\n\n### Layers\n\n| File | Covers |\n|---|---|\n| `test/safety.test.ts` | Read-only bypasses, identifier injection, rollback |\n| `test/tools.test.ts` | Every tool against a real seeded schema |\n| `test/protocol.test.ts` | The real MCP client, transport, schema validation, annotations |\n\nThe protocol layer is worth testing separately: a tool can be perfectly correct\nand still unusable because its input schema rejects valid arguments.\n\n## Local development\n\n```bash\n# Against a local Postgres\necho 'DATABASE_URL=\"postgres://localhost/ntl\"' >> .dev.vars\necho 'MCP_AUTH_TOKEN=\"dev-token\"' >> .dev.vars\nnpm run dev\n\n# Poke at it\nnpx @modelcontextprotocol/inspector\n```\n\n## Licence\n\nApache 2.0, as with the rest of openNTL. Copy it.\n","readmeFilename":"README.md","_rev":"1-bc0ab26f4e9b7d53a966bff94b5d4055"}