PostgreSQL MCP server

Prebuilt SnapLogic agentic-tool pipelines that let an LLM read from and act on PostgreSQL, callable as MCP tools via Pipeline Execute. Published in the SnapLogic pattern catalog.

Overview

This tool pack gives an agent direct SQL access to a PostgreSQL database: run read queries and catalog lookups, execute write statements and stored-function calls, insert and update rows from a field map, and run pgvector similarity searches. Read and write tools are kept separate so a read-only agent can query data without mutation access.

For setup instructions, see SnapLogic MCP Server quickstart.

Example Prompts

  • How many customers signed up in each of the last six months?
  • What tables exist in the public schema?
  • Show me the columns and data types on the orders table
  • List the top 20 accounts by outstanding balance
  • Join orders and customers and give me revenue per region for last quarter
  • Delete the test orders that were created yesterday
  • Call update_customer_status for customer 42 and set them to premium
  • Upsert this product row, updating the price if the SKU already exists
  • Add a last_reviewed_at timestamp column to the accounts table
  • Mark every invoice older than 90 days as written off

Tools

Tool Name Functions
PostgreSQLExecuteRead Run any read-only SQL statement and return the rows.
PostgreSQLExecuteWrite Run any data-modifying SQL statement or stored-function call.
PostgreSQLInsertRows Insert a row into a table from a field map.
PostgreSQLInsertRowsChild Postgre SQL Insert Rows Child
PostgreSQLUpdateRows Update rows matching a condition from a field map.
PostgreSQLUpdateRowsChild Postgre SQL Update Rows Child
PostgreSQLVectorSearch Cosine similarity search over a pgvector embedding column.

Set up the MCP Server tools

  1. Download postgresql_tools.zip.
  2. In SnapLogic Designer, open the target project space (or create one), then choose Import Project / Import Pipelines and select the downloaded postgresql_tools.zip. Designer unpacks each pipeline into the project.
  3. Attach the required account to the connectivity snaps (see Connection Setup).
  4. Expose the project as an MCP server — each pipeline becomes a tool named after its label. New to this? Start with the MCP quickstart, then use the MCP Server Pipeline Builder to generate the server from the imported pipelines.

Configure account

Attach a PostgreSQL account to the PostgreSQL snaps in Designer after import.

These pipelines ship without credentials by design — attach a valid account in Designer before the tools will execute.

See the SnapLogic account documentation for this connector: Postgresql Account.

Important: These pipelines ship without credentials by design — attach a valid account in Designer before the tools will execute. Credentials live in the SnapLogic account store, never in the pipeline JSON.

PostgreSQLExecuteRead

Runs any read-only SQL statement against the connected PostgreSQL database and returns the result rows. Use it for SELECT queries, joins, aggregations, window functions, and catalog introspection against information_schema.

Parameter Type Default Description
sql_statement* string —

The full read-only SQL statement to execute, e.g. a SELECT or an information_schema catalog query.

* Required parameter.

Try asking:

  • How many customers signed up in each of the last six months?
  • What tables exist in the public schema?
  • Show me the columns and data types on the orders table
  • List the top 20 accounts by outstanding balance
  • Join orders and customers and give me revenue per region for last quarter

PostgreSQLExecuteWrite

Runs any data-modifying SQL statement against the connected PostgreSQL database, covering INSERT, UPDATE, DELETE, DDL, upserts, and stored-function calls. Use it for changes that the structured insert and update tools cannot express.

Parameter Type Default Description
sql_statement* string —

The full data-modifying SQL statement or function call to execute.

* Required parameter.

Try asking:

  • Delete the test orders that were created yesterday
  • Call update_customer_status for customer 42 and set them to premium
  • Upsert this product row, updating the price if the SKU already exists
  • Add a last_reviewed_at timestamp column to the accounts table
  • Mark every invoice older than 90 days as written off

PostgreSQLInsertRows

Inserts a new row into a PostgreSQL table from a field map, without requiring the agent to write SQL. Use it for straightforward single-table inserts where the column values are already structured data.

Parameter Type Default Description
table_name* string —

Table to insert into, e.g. customers.
schema_name string —

Schema containing the table. Leave blank to use the default (public).
record* object —

Field map of the new row, keyed by column name, e.g. {name, email, balance}.

* Required parameter.

Try asking:

  • Add a new customer called Acme Corp with email [email protected] and a 5000 balance
  • Insert a row into public.orders for customer 42 with amount 1200
  • Create a record in the leads table for this contact I just captured
  • Add this product to the inventory table in the sales schema

PostgreSQLInsertRowsChild

Lets the LLM postgre sql insert rows child, exposed as an MCP tool callable via Pipeline Execute.

Parameter Type Default Description
table_name string customers

—
schema_name string public

—

PostgreSQLUpdateRows

Updates rows in a PostgreSQL table that match a condition, using a field map of columns and values to change; no SQL required. Confirm the filter with the user before running a broad update, as the condition is applied as given.

Parameter Type Default Description
table_name* string —

Table to update, e.g. customers.
schema_name string —

Schema containing the table. Leave blank to use the default (public).
update_condition* string —

SQL WHERE condition selecting the rows to update, e.g. id = 42.
record* object —

Field map of the columns and values to set, e.g. {balance: 7500, status: 'premium'}.

* Required parameter.

Try asking:

  • Set customer 42's balance to 7500 and their status to premium
  • Update the orders table and mark order 1001 as shipped
  • Change the status to inactive for every customer where last_login < '2025-01-01'
  • Update the email on the contacts row where id = 88

PostgreSQLUpdateRowsChild

Lets the LLM postgre sql update rows child, exposed as an MCP tool callable via Pipeline Execute.

Parameter Type Default Description
table_name string customers

—
schema_name string public

—
update_condition string id = 1

—

PostgreSQLVectorSearch

Runs a pgvector cosine similarity search against an embedding column and returns the nearest rows with their similarity scores. Use it for semantic search, recommendation, or RAG retrieval against a PostgreSQL database.

Parameter Type Default Description
table_name* string —

Table holding the embeddings, e.g. document_embeddings.
vector_column* string —

Name of the pgvector column to search, e.g. embedding.
schema_name string —

Schema containing the table. Leave blank to use the default (public).
where_clause string —

Optional SQL WHERE condition applied to filter candidate rows before ranking.
limit_rows string —

Number of nearest rows to return (default 10).

* Required parameter.

Try asking:

  • Find the 5 most similar documents in document_embeddings using the embedding column
  • Search the knowledge_chunks table for the closest matches on the vector column and return 20 rows
  • Do a similarity search on product_embeddings but only over rows where category = 'hardware'
  • Look for near-duplicate articles in the public.article_vectors table
  • Pull the top 3 nearest neighbours from the embeddings table in the analytics schema