Skip to main content
RoutMe

Playbook

How to connect a database to an AI agent safely

By the RoutMe team. Last updated .

Connect an AI agent to a database through a dedicated read-only role, expose only the columns it needs through views, scope every query to the verified customer, validate queries before they run and cap their time and size. Prefer fixed, parameterized queries behind named tools over letting a model write free-form SQL.

Decide whether to connect a database at all

If a REST API already exposes the data you need, calling it is often safer, because the API enforces business rules and authorization. Direct database access makes sense when no API exists, when the API is missing a read you need, or when reporting data lives in a warehouse. Either way the principles below apply. The comparison in MCP vs REST API vs RAG for support can help you choose, and connecting a REST API covers the other path.

Step 1: Create a dedicated read-only role

Make a new role used only by the agent. Grant it connection rights and SELECT on specific objects and nothing else: no INSERT, UPDATE, DELETE, no DDL, no ownership. On PostgreSQL, the documentation on roles and privileges describes how GRANT SELECT works, and you can also set the session to read-only so a mistake fails even if a grant slips through. A dedicated role gives you a clean audit trail, an easy revocation switch and a place to attach limits.

Store its credential in a secret manager, not in a prompt, a document or a chat transcript. Rotate it on a schedule, and when staff change.

Step 2: Expose views, not tables

Base tables often hold columns the agent never needs: password hashes, internal notes, full payment fields, staff comments. Create views that select only the columns required for support answers, with friendly names, and grant the agent role access to the views only. This also gives you a stable contract. You can refactor tables without changing what the agent sees.

For an order lookup, a view might return the order number, status, item names, carrier and tracking reference, and exclude cost price, internal flags and payment details.

Step 3: Scope rows to the verified customer

The most important rule: the model must not decide whose data it reads. Identity should come from verification in the conversation, such as the WhatsApp number tied to the account or a second factor like the order email, and the tool should apply it. In practice:

  1. The tool takes the verified customer identifier from the session, not from model-written parameters.
  2. The query always includes a filter such as WHERE customer_id = $1.
  3. Where your database supports it, row-level security enforces the same rule as a second line of defense.

If a customer asks for "all orders for jane@example.com" and that is not who they verified as, the tool returns nothing. The reasoning behind this is in prompt injection and read-only tools, because injected text may try to supply a different identifier.

Step 4: Prefer named, parameterized queries

The safest pattern is a small catalog of tools, each wrapping a fixed query with typed parameters: get_order_status, list_recent_orders, get_appointment. The model picks a tool and supplies parameters, and your code executes the query with bound parameters. This avoids SQL injection, keeps results predictable and makes it simple to test.

Free-form natural-language-to-SQL is flexible and harder to secure. If you do allow it, add layers:

  • Parse the statement and allow only a single SELECT.
  • Reject semicolons, comments, function calls outside an allowlist and references to objects outside the permitted views.
  • Add a row LIMIT if absent.
  • Run it as the read-only role regardless of what the parser concluded.
  • Log the statement and the result size.

Validation is a safety net. The role permissions are the actual boundary.

Step 5: Limit time, size and load

An expensive query is a denial-of-service risk even when it is read-only. Configure:

  • A statement timeout for the agent role.
  • A maximum row count returned to the model, and a maximum width per field.
  • A connection limit for the role.
  • A read replica or a reporting copy, if the primary is busy.
  • Indexes on the columns used by the fixed queries.

Returning less data also lowers the chance that the model repeats something it should not. Return the fields the answer needs, not the entire record.

Step 6: Log and review

Log tool name, parameters, row count and duration, and tie them to the conversation. Review unusual patterns: many calls in a short span, empty results for different identifiers, unusually broad queries. Apply your normal retention rules to the logs, since they may contain personal data. A security page should explain what your vendor stores too.

Step 7: Test before launch

  1. Try to read a column outside the view.
  2. Try to read another customer's row using a different identifier.
  3. Try to write: insert, update, delete, drop. Every one must fail.
  4. Run an expensive query and confirm the timeout fires.
  5. Rotate the credential and confirm the agent fails closed.

Keep these as a repeatable checklist and run it after schema changes.

How RoutMe approaches this

RoutMe can connect to databases and APIs through MCP, and the agent is read-only by default. We recommend the pattern above: a dedicated read-only role, views, verified-identity scoping and narrow tools. The shape of tool you build matters more than the label. Our read-only explainer describes the principle, and the developers page lists connection options. Your database credentials and network rules remain yours to control, so review them with your own security team.

Sources

Explore the product: RoutMe for Shopify, solutions and the documentation.

Frequently asked questions

Should the agent ever get write access to the database?

Avoid it for support. If an action must change data, expose a narrow, separate tool with a confirmation step or a person approving it, as in automating returns safely.

Is row-level security required?

It is not required, but it is a strong second layer. Tool-level scoping is the first layer, and row-level security protects you if a tool has a bug.

Can the model write SQL for me?

It can, and you should treat the result as untrusted input. Use a read-only role, allow only single SELECT statements and cap time and rows.

Where should the credential live?

In a secrets manager that the agent platform reads at runtime. Never paste it into instructions or documents.

Put these ideas to work