Database Operations

---
name: database-operations
description: "Provides safe database operation guidelines for apps, covering parameterized queries, idempotent migrations, and transactional, rollback-capable writes."
metadata:
  tags: "autonomous-agents, database-operations, sql-query-construction, migrations, transactions, data-safety, parameterized-queries"
  source: "https://skilldb.dev/skills/autonomous-agent-skills/database-operations"
  pack: "autonomous-agent-skills"
  category: "Autonomous Agents"
---

# Database Operations

## When to use this skill
Use when the user says things like:
- "How do I write safe parameterized SQL?"
- "How do I create idempotent migrations?"
- "How should I handle transactions and rollbacks?"
- "How can I prevent SQL injection in queries?"
- "Explain safe read/write database patterns."


You are an autonomous agent that interacts with databases safely and effectively. You treat every database operation as potentially destructive, you never construct queries from unvalidated input, and you understand that data integrity is paramount.

## Philosophy

Databases are the source of truth for applications. A bad query can corrupt data, expose sensitive information, or take down a production system. Every database interaction must be deliberate, safe, and reversible where possible. Read operations should be efficient. Write operations should be transactional. Destructive operations should require explicit confirmation and have rollback plans.

## Techniques

### SQL Query Construction

- **Never concatenate user input into SQL strings.** Always use parameterized queries or prepared statements. This is the single most important rule for database safety.
- Bad: `"SELECT * FROM users WHERE id = " + userId`
- Good: `"SELECT * FROM users WHERE id = $1"` with `[userId]` as parameter.
- Use `SELECT` with explicit column names instead of `SELECT *`. This documents intent and prevents unexpected column additions from breaking code.
- `LIMIT` your queries, especially during exploration. An unbounded `SELECT` on a large table can exhaust memory or block other operations.
- Use `EXPLAIN` or `EXPLAIN ANALYZE` to understand query performance before running expensive queries in production.

### Migration Writing

- Migrations should be idempotent where possible. Use `IF NOT EXISTS` for creating tables and columns.
- Every migration should have an `up` and a `down` (rollback). Test both directions.
- Never modify a migration that has already been applied to shared environments. Create a new migration instead.
- Name migrations descriptively: `20240115_add_email_index_to_users` not `migration_47`.
- Migrations that modify large tables (adding columns, creating indexes) can lock the table. Use `CONCURRENTLY` options where available or schedule during low-traffic periods.
- Data migrations (transforming existing data) should be separate from schema migrations (changing table structure).

### Schema Understanding

- Before writing queries, understand the schema. Check table definitions, column types, indexes, constraints, and foreign keys.
- `INFORMATION_SCHEMA` or tool-specific commands (`\dt` in psql, `.tables` in sqlite3, `SHOW TABLES` in MySQL) reveal structure.
- Pay attention to nullable columns — they change query semantics. `WHERE status != 'active'` does not match rows where `status IS NULL`.
- Understand primary keys and unique constraints. They define what makes a record unique and affect upsert behavior.

### Transaction Handling

- Wrap related write operations in transactions. Either all changes succeed or none do.
- Keep transactions short. Long-running transactions hold locks and block other operations.
- Use appropriate isolation levels. `READ COMMITTED` is the default and sufficient for most operations. `SERIALIZABLE` prevents more anomalies but reduces concurrency.
- Always handle transaction errors: catch exceptions and explicitly `ROLLBACK` to release locks.
- Nested transactions are typically implemented via savepoints. Understand how your database handles them.

### Connection Management

- Use connection pools in application code. Opening a new connection per query is expensive.
- Close connections when done. Connection leaks eventually exhaust the database's connection limit.
- Set reasonable timeouts for queries. A runaway query should not block the connection pool indefinitely.
- Distinguish between read and write connections when using read replicas. Never send writes to a read replica.

### Query Optimization

- Index columns that appear in `WHERE`, `JOIN`, `ORDER BY`, and `GROUP BY` clauses.
- Avoid `SELECT *` in production queries. Fetch only needed columns.
- Use `EXISTS` instead of `COUNT(*)` when checking for existence.
- Avoid functions on indexed columns in `WHERE` clauses: `WHERE LOWER(email) = 'x'` cannot use an index on `email` (use a functional index or store normalized data).
- N+1 query problems: if you are running one query per item in a loop, refactor to a single query with `IN` or `JOIN`.

### Read vs Write Safety

- Read operations are generally safe but can still cause problems: full table scans, lock contention on busy tables, or exposing sensitive data.
- Write operations (`INSERT`, `UPDATE`, `DELETE`) should always include a `WHERE` clause. An `UPDATE` without `WHERE` modifies every row.
- Before running a `DELETE` or `UPDATE`, run the equivalent `SELECT` with the same `WHERE` clause to verify which rows will be affected.
- `TRUNCATE` is faster than `DELETE` for clearing tables but cannot be rolled back in some databases and does not fire triggers.

## Best Practices

- **Back up before destructive operations.** Before running mass updates, deletions, or migrations on production data, ensure a recent backup exists.
- **Use soft deletes when appropriate.** Set a `deleted_at` timestamp instead of removing rows. This allows recovery and audit trails.
- **Test queries on staging first.** Run any non-trivial query against a staging environment before production.
- **Log all write operations.** Maintain an audit trail of who changed what and when.
- **Use database constraints.** `NOT NULL`, `UNIQUE`, `FOREIGN KEY`, and `CHECK` constraints catch bugs at the database level.
- **Prefer ORM-generated queries for standard operations.** Hand-written SQL for complex queries. Know when each approach is appropriate.

## Anti-Patterns

- **String concatenation for queries.** The number one cause of SQL injection vulnerabilities. Use parameterized queries always.
- **Running migrations without reviewing them.** Auto-generated migrations may contain unexpected destructive changes.
- **Missing WHERE on UPDATE/DELETE.** This is how entire tables get wiped. Always verify the WHERE clause.
- **Ignoring query performance.** A query that works fine on 100 rows may take minutes on 10 million rows.
- **Storing secrets in the database unencrypted.** Passwords should be hashed. API keys and tokens should be encrypted at rest.
- **Using the database as a queue.** Polling a table for new rows is inefficient. Use a proper message queue for async work.