Comment by mike_hearn

It's interesting how many people are just writing off the whole premise of letting agents work directly with databases. SQL is a language and not an API because it was intended to be hand written and often still is. There are lots of legitimate use cases for this if the safety level can be raised high enough.

I'm doing some research on this topic at the moment, along with many other people on the database team at Oracle. The article is written from a Postgres perspective. Most of these problems don't surface with a better database engine and you don't need to do any work to solve them. For people also interested in this topic, here's some notes on how things line up:

• Statement timeouts: an indirect hack. What you actually want are resource usage limits like CPU wall clock time excluding lock waits. That's "CREATE PROFILE" in Oracle, or if you want more power (e.g. RAM / IOP limits too) there's https://docs.oracle.com/en/database/oracle/oracle-database/2...

• Writes: This boils down to the user might make a mistake and yes, sure, no database can magically stop all mistakes. The usual fix here is to define stored procedures that provide a set of safe write operations. Same in all DBs.

• Soft deletes: You can turn on Flashback (time travelling SQL) and then just use regular DELETEs. You can also undo transactions in some cases in Oracle even if other transactions happened afterwards as the DB can generate SQL for you that attempts to undo the effects.

• Idempotency keys: Built in if you turn on Transaction Guard. https://docs.oracle.com/en/database/oracle/oracle-database/2...

• Assumption that connections are brief: Oracle has built in server-side DB pooling and horizontal scalability, so separate 'bouncers' aren't needed. You can just let agents connect directly without needing special infrastructure.

• Observability: you can associate metadata with connections that are then associated with recorded queries so agent/step can be looked up given a query. You can then find these queries if they're holding locks.

• Schema is a contract with eng: Well named schemas with comments are a good practice in any DB. Oracle views are automatically writeable in many cases (i.e. the SELECT query is inverted), so if your schemas are messy you can use views to clean them up and those views are still usable to make changes to the data if you need to.

• Scoping blast radius: Lots of security features in the DB to do this, as well as things not typically considered security like transaction priority levels to support lock breaking (run agents at low priority and app server writes will kill agent transactions). And you can easily make DB CoW snapshots.

So once your DB handles all the basics like that, the interesting things remaining are really all the semantics that are encoded into the application layer that the database doesn't see.

Pointing agents (or humans) at a live database to write arbitrary SQL might sound scary but it's the exact use case databases have been designed for from the beginning, and at least some of them have lots of features designed to make this as safe as possible. It can be that even more features are needed - that's the sort of question I'm currently exploring. But the foundation is there, at least.