Adding data update capabilities (write, update, delete) from your AI agent lets it not only read data but also modify your database or business system dynamically. This is powerful but requires careful design for security and reliability.

🛠️ Lesson: Adding Data Update Capabilities (Write/Update/Delete) from the AI Agent

🎯 Lesson Objective

By the end of this lesson, you will be able to:

  • Enable your AI agent to safely generate and execute SQL commands that modify data (INSERT, UPDATE, DELETE)

  • Implement strict validation and permission checks to prevent dangerous or unintended modifications

  • Integrate these capabilities into your LangChain agent workflow

  • Build audit trails and rollback mechanisms for safer operations

1️⃣ Understanding Data Modification Queries

Query Type Purpose Example
INSERT Add new data INSERT INTO customers (name, email) VALUES ('John', '[email protected]')
UPDATE Change existing data UPDATE orders SET status='shipped' WHERE order_id=123
DELETE Remove data DELETE FROM sessions WHERE last_active < '2024-01-01'

2️⃣ Key Challenges & Safeguards

Challenge Solution
SQL Injection risk Use parameterized queries or whitelist fields
Accidental data loss Require confirmations before destructive queries
Unauthorized access Implement strict authentication & authorization
Rollback & audit Log all changes, maintain backups, use transactions
Complex query safety Restrict allowed query types & patterns

3️⃣ Design Pattern: AI-Assisted SQL Generation with Human Approval

Step 1: AI Generates Proposed SQL Update

def generate_update_sql(natural_language: str) -> str:
    # Use LLM to convert NL to SQL update query
    prompt = f"You are a SQL expert. Generate a safe {['INSERT', 'UPDATE', 'DELETE']} statement based on: {natural_language}. Only generate the SQL, no explanations."
    response = llm.call_as_chat([
        {"role": "system", "content": "You generate safe SQL for data modification only."},
        {"role": "user", "content": prompt}
    ])
    return response.content.strip()

Step 2: Present Proposed Query for Human Review

  • Display the generated SQL to a trusted user/admin via UI

  • Allow edits or rejection before execution

Step 3: Validate and Execute Query Safely

def validate_sql(query: str) -> bool:
    allowed_statements = ('insert', 'update', 'delete')
    query_lower = query.strip().lower()
    if not any(query_lower.startswith(stmt) for stmt in allowed_statements):
        return False
    # Add additional safety checks (e.g., no DROP/TRUNCATE, no multiple statements)
    # Optional: Check query against whitelist of tables and columns
    return True

def execute_modification_query(query: str):
    if not validate_sql(query):
        return "Query validation failed. Only INSERT, UPDATE, DELETE are allowed."
    try:
        with engine.begin() as connection:  # Transaction block
            connection.execute(text(query))
        return "Query executed successfully."
    except Exception as e:
        return f"Execution error: {str(e)}"

Step 4: Integrate Into LangChain Tool

def update_agent_tool(user_input: str):
    sql_query = generate_update_sql(user_input)
    # Here you would add UI step for human review in production
    return execute_modification_query(sql_query)

update_tool = Tool(
    name="DataModificationTool",
    func=update_agent_tool,
    description="Generates and safely executes data modification SQL queries (INSERT, UPDATE, DELETE) based on user input."
)

4️⃣ Optional: Add Audit Logging & Rollbacks

  • Maintain a log table recording all data modification queries with user info and timestamps.

  • Use database transactions to roll back on errors.

  • Implement backup/restore strategies for critical data.

5️⃣ Example Use Cases

Scenario Natural Language Input Example
Add a new customer “Add a customer named Alice with email [email protected]“
Update order status “Mark order #456 as shipped”
Delete old sessions “Delete sessions inactive since Jan 1, 2023”

6️⃣ Security Best Practices Recap

  • Always validate and sanitize AI-generated SQL before running it

  • Require human approval for sensitive or destructive operations

  • Implement role-based access control in your app

  • Log all changes with user context

  • Use transactions to ensure data integrity

✅ Summary

Step Why It Matters
AI generates data modification SQL Automates updates based on natural language
Validation & human review Prevents errors & security breaches
Safe execution & transaction Ensures data integrity and rollback ability
Auditing & logging Enables accountability and troubleshooting

🔜 Next Steps

 

  1. Building a web UI for human review and approval of update queries?

  2. Implementing role-based permissions and authentication?

  3. Setting up audit logging and database triggers?

  4. Adding rollback and backup strategies integrated with your AI agent?

 

55
PreviousNext