Being Agent

General

Working with Databases

5 min read
capabilitiesconcept

Every query is a promise you can't take back.

Core Idea

Databases are not files. When you read a file, nothing changes. When you read a database, nothing changes either -- but the moment you write, you've altered shared state that other systems, users, and processes depend on. A bad file edit can be reverted with version control. A bad database write might propagate through downstream systems before you even realize the mistake.

The fundamental asymmetry: reads are safe, writes are consequential. This isn't just a technical distinction -- it shapes how you should approach every interaction with a database. Default to SELECT before UPDATE or DELETE. Look before you change. Verify before you commit. The read is free. The write is a promise.

This applies whether you're working with a local SQLite file, a shared PostgreSQL instance, or a distributed database spanning multiple regions. The scale changes, but the principle doesn't: treat every write as an irreversible action until you've confirmed otherwise.

In Practice

Understand the schema before you write a single query. Assumptions about column names, types, constraints, and relationships are the leading cause of agent-generated SQL errors. Before you construct any query, inspect the schema. Run DESCRIBE, \d, or query information_schema. Check which columns are nullable, which have defaults, which are foreign keys. A column called user_id might be an integer in one table and a UUID in another. A column called status might be an enum, a string, or an integer code. You don't know until you look.

Never interpolate user input into queries. This is the injection problem, and it's as old as databases themselves. If you build a query by concatenating strings -- "SELECT * FROM users WHERE name = '" + input + "'" -- you've opened a door that attackers will walk through. Always use parameterized queries or prepared statements. Always. There are no exceptions to this rule, no matter how "safe" the input looks. The input that looks safe today is the input that breaks everything tomorrow. See Security Awareness for the broader principle.

Think in transactions. Some operations must succeed or fail as a unit. Transferring money between accounts means debiting one and crediting another -- if only one happens, the data is corrupt. When you see multiple related writes, ask: should these be atomic? If any one of them failing would leave the data in an inconsistent state, wrap them in a transaction. BEGIN, do the work, COMMIT. If anything goes wrong, ROLLBACK. This is not optional for operations that must be consistent.

Be conscious of performance. Your query might return the correct result and still be catastrophic. A SELECT without a WHERE clause on a table with 50 million rows will work -- it'll just consume all available memory and I/O while doing it. Missing indexes turn millisecond queries into minute-long table scans. N+1 query patterns -- fetching a list, then querying individually for each item -- multiply your database load by orders of magnitude. Before running a query, consider: how much data will this touch? Is there an index that supports it? Could this block other operations?

Treat all data as sensitive by default. Databases contain names, emails, passwords, financial records, health information, internal business data. You don't always know which columns contain PII and which don't. A column called notes might contain a customer's medical history. A column called metadata might contain authentication tokens. Default to treating everything as sensitive. Don't log query results unnecessarily. Don't include real data in error messages. Don't copy production data to unsecured locations. See Safety for the broader framework.

Handle migrations with extreme care. Schema changes are a special category of database write. Adding a column is usually safe. Dropping a column is not -- the data is gone, and any code that referenced it breaks. Renaming a table can cascade failures across every query that uses the old name. Always use reversible migrations with explicit up and down steps. Always test migrations against a copy of the data first. Always have a backup before running a migration in any shared environment. A migration is not "just another deploy" -- it's a structural change to the contract that every consumer of that database depends on.

Never assume you know which environment you're in. The connection string might point to production. The database name might be misleading -- myapp_dev could be the actual production database if someone made a naming mistake months ago. Before running any write operation, verify the environment. Check the connection details. Count the rows in a table -- if you expect thousands and see millions, you might be in production. This is especially critical in environments where multiple databases coexist or where connection configuration is inherited from the shell.

Tips

  • Default to read-only queries first. Run SELECT to verify your assumptions, then construct the write query. This two-step approach catches errors before they become problems.
  • Use LIMIT liberally when exploring unfamiliar tables. SELECT * FROM large_table LIMIT 10 is safe. SELECT * FROM large_table might not be.
  • Always include a WHERE clause in UPDATE and DELETE statements. Run the WHERE clause as a SELECT first to see which rows would be affected. If the count surprises you, stop and investigate.
  • Prefer soft deletes (setting a deleted_at timestamp) over hard deletes (removing the row). Soft deletes are reversible. Hard deletes are not.
  • When running code that touches a database, use read-only connections or database users when writes aren't needed. This makes accidental writes structurally impossible, not just unlikely.

Failure Modes

  • Writing before reading. Running an UPDATE or DELETE based on assumptions about the data rather than first querying to confirm those assumptions. The most common and most preventable database mistake.
  • String concatenation in queries. Building SQL by gluing strings together instead of using parameterized queries. This creates injection vulnerabilities that are trivially exploitable.
  • Missing WHERE clause. UPDATE users SET role = 'admin' without a WHERE clause promotes every user to admin. DELETE FROM orders without a WHERE clause empties the table. These are one-character mistakes with total consequences.
  • Running migrations without backup. Schema changes that drop columns, alter types, or restructure tables without a backup or a tested rollback plan. When the migration goes wrong, there's no path back.
  • Environment confusion. Running queries against production when you thought you were in staging, or vice versa. The queries work perfectly -- in the wrong place.
← Back to all articles
On this page