Logo ← PostgreSQL Blog

The AI Database Engineer: Prevent SQL Catastrophes

We are entering an era where we treat AI not just as a chatbot, but as a “Junior DBA.” We let it write complex SQL queries, design schema…

The AI Database Engineer: Prevent SQL Catastrophes

We are entering an era where we treat AI not just as a chatbot, but as a “Junior DBA.” We let it write complex SQL queries, design schema migrations, and even optimize PL/pgSQL functions.

But there is a massive risk: AI hallucinations in a frontend codebase break the UI. AI hallucinations in a database break the business.

If you are using LLMs to assist with PostgreSQL engineering, you cannot rely on “hope.” You need a defense system. I have developed a three-layer framework to ensure AI-generated SQL is performant, safe, and clean.

Layer 1: Governance (The Standard)

The first layer is about defining what “good SQL” looks like before the AI even starts typing. AI models are trained on the entire internet, which includes a lot of terrible, non-performant SQL. We must narrow their focus.

1. Ruthless Linting with SQLFluff

Just as we lint Python or JavaScript, we must lint SQL. AI often gets lazy with formatting. I enforce a strict .sqlfluff configuration that the AI must adhere to:

  • Ban SELECT *: It kills performance and breaks code when schemas change. Explicit column selection is mandatory.
  • Mandatory Aliasing: Every table in a JOIN must have a clear, abbreviated alias.
  • Case Consistency: Keywords (SELECT, FROM) must be uppercase; identifiers must be snake_case.

2. The “Anti-Lazy” Data Type Policy

AI loves JSONB. Why? Because it’s easy. It doesn't have to think about normalization or schema design. It just dumps everything into a JSON blob.

  • The Rule: Usage of JSONB is forbidden for core relational data.
  • The Constraint: Dates must be TIMESTAMPTZ, not strings. Enums must be used for state columns instead of raw text.

3. Naming Conventions as Law

AI creates generic names like idx1 or temp_table. In a production database, this is unacceptable. We enforce strict naming patterns via system prompts:

  • Indexes: idx_{table}_{columns}
  • Foreign Keys: fk_{source_table}_{target_table}
  • Primary Keys: Always id (or table_id depending on your preference), but never inconsistent.

Layer 2: Validation (The Auditor)

The second layer is the “Review Phase.” Even syntactically correct SQL can be architecturally disastrous. This is where we introduce the concept of the “Auditor Agent.”

1. The Architecture Check

I use a secondary AI context to review the code generated by the first one. It checks against specific architectural principles:

  • Index Coverage: “You added a Foreign Key. Did you also add the corresponding index to prevent locking issues during deletes?”
  • Lock Awareness: “This migration alters a column type on a 100GB table. This will cause an ACCESS EXCLUSIVE lock. Rewrite it using a concurrent strategy."

2. Unit Testing with pgTAP

If an AI writes a PL/pgSQL function, it is required to write a test for it. In the Postgres world, we use pgTAP.

I don’t accept a function unless it comes with a companion test.sql file:

-- AI must generate this proof of work
SELECT plan(2);
SELECT has_function('public', 'calculate_mrr');
SELECT is( calculate_mrr('2024-01-01'), 1000.00, 'MRR Calculation verified' );
SELECT * FROM finish();

This forces the AI to “think” about edge cases. If the test fails, the code is rejected automatically.

Layer 3: Guardrails (The Hard Stop)

The final layer is the safety net. These are hard blocks in the CI/CD pipeline or Git Hooks that prevent dangerous code from ever reaching production, no matter what the AI says.

1. The “Destructive Command” Block

AI sometimes tries to solve a migration conflict by “resetting” things. I have a grep-check in my pre-commit hooks that scans for keywords:

  • DROP DATABASE
  • TRUNCATE
  • DROP TABLE (without a specific flagged override)
  • DELETE (without a WHERE clause)

If these patterns are found, the commit is blocked immediately.

2. Configuration Lockdown

AI agents should never be allowed to modify server configuration files unless explicitly authorized. Files like postgresql.conf, pg_hba.conf, and generic .env files are locked via file permissions or strict .gitignore rules to prevent an AI from accidentally opening up the database to 0.0.0.0/0.

Conclusion

AI is an incredibly powerful force multiplier for Database Engineers. It can write boilerplate migrations and complex queries in seconds. But without Governance, Validation, and Guardrails, it is a liability.

Treat your AI assistant like a brilliant but inexperienced intern: Give them strict rules, check their work thoroughly, and never give them the keys to production without supervision.