How to Configure Pydantic AI MySQL Read Only Access
You set up pydantic ai mysql read only access by creating a restricted database user. Then you pass that user into your agent tool dependencies. Many teams start by giving agents the same database login that the main app uses. That shortcut lets an agent alter rows or drop tables if a prompt goes wrong. A read-only setup stops write commands at the database level. How pydantic ai mysql read only access works Pydantic AI agents use typed tools to talk to external systems like MySQL. You define
Your coding agents run with your access.
Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.
You set up pydantic ai mysql read only access by creating a restricted database user. Then you pass that user into your agent tool dependencies. Many teams start by giving agents the same database login that the main app uses. That shortcut lets an agent alter rows or drop tables if a prompt goes wrong. A read-only setup stops write commands at the database level.
How pydantic ai mysql read only access works
Pydantic AI agents use typed tools to talk to external systems like MySQL. You define a tool function that accepts a database session through the dependency system. When the model needs data, it calls that tool with structured arguments.
To keep the database safe, you first create a dedicated role inside MySQL. You grant this role select privileges on specific tables or views only. You leave out insert, update, delete, and schema change rights.
Next, your Python app opens a database pool using this restricted MySQL account. You pass that pool into the agent run context. When the agent runs a query tool, MySQL checks the role before running the statement. If the agent tries to change data, the database rejects the command right away. You can also set a read-only transaction mode on the client session for extra safety.
What native database roles solve and what they miss
Native MySQL roles do a great job of blocking write commands and table drops. They enforce a hard boundary at the storage engine level. Even if an agent writes a bad update statement, the database refuses to run it.
Still, database roles only decide who can log in and which tables they can read. They do not inspect what individual select statements do while in flight. An agent with select rights on a customer table can still read every column.
This creates a gap when tables hold private data like emails or phone numbers. A read-only user can pull card numbers or national IDs into the model context. Native roles also struggle to stop an agent from running a slow, unindexed join. You must also store and rotate static database passwords across every host running your agent. Managing these keys across test and live apps adds extra manual work for your team.
Auditor Read-Only Access + MySQL Access Governance: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
How to tighten agent query rules in Python
You can reduce risk further by constraining how the agent builds queries in Python. Instead of letting the model write raw SQL strings, give it narrow tool functions. Each tool should represent one clear business question with validated inputs.
Pydantic models validate every argument before the tool runs any database query. For example, a tool can require a numeric customer ID and a short date range. The Python function then places those checked values into a fixed select template.
You should also enforce row limits and query timeouts on every database call. If a query takes too long, the client cancels the run and returns a clean error. Creating MySQL views that leave out sensitive columns also helps limit what the agent reads. Still, maintaining custom views for every agent tool takes time as your schema grows.
Check MySQL queries and results on the wire
MySQL grants control who logs in. They do not check single queries or mask sensitive columns once a session starts. With hoop.dev, you run an open-source proxy, a sidecar for your MySQL. It runs next to MySQL, RDS, or Aurora and reads the MySQL wire protocol.
Multi-statement batches and prepared queries are split and checked one by one by hoop.dev guardrails. When a rule refuses a query, MySQL returns a native error packet with your message. On the response path, hoop.dev data masking rewrites string values in returned rows that match your rules. Every query and decision is logged per resource. See the MIT-licensed project on GitHub.
Frequently asked questions
Can a Pydantic AI agent still lock a MySQL database with read-only permissions?
Yes, a read-only user can still run heavy queries that eat up CPU and memory. You should set statement timeouts and row limits so bad queries stop fast.
Should I let the model generate raw SQL queries for MySQL?
Allowing raw SQL increases the chance of syntax errors and wide data dumps. Using typed tool inputs with fixed query templates is much safer for live apps.
How do I keep sensitive columns out of the model prompt?
You can build restricted MySQL views or use data masking to rewrite matched fields in results. Masking replaces items like emails and card numbers before the agent reads the rows.
Related guides
In this post