Make DatabaseTool use a read-only transaction #220
Description
One cool feature I have seem from some other Agent database tools is to use enclose all db operations in a READ ONLY transaction as a protection mechanism.
I think that is a good idea. We should add that to https://github.com/supercog-ai/agentic/blob/main/src/agentic/tools/database_tool.py
Some notes:
- Someone might want to use the DB tool for mutations, so we should add an optional flag like "allow_mutations=False" to disable the read only (but that should be the default).
- Postgres syntax: https://www.postgresql.org/docs/current/sql-set-transaction.html
- There is probably NOT any database-agnostic syntax for doing this. This might be a good job for an LLM - to enumerate a big switch statement that returns the right syntax based on the database type. Otherwise we could JUST implement support for postgres/mysql/sqlite.
- Another idea is if we don't know the db type, we could just force a ROLLBACK after the query. I doubt this will work for 100% of the cases (eg if you DROP TABLE does the rollback help?)
Again writing tests for this will be a pain to cover the different db types.