Apéndice B — B. SQL de bolsillo

Las 20 queries que un backend usa

NotaEn una frase

Cheatsheet agnóstico: CRUD, JOINs, agregaciones, índices, EXPLAIN — las 20 queries que cubren el 95% de lo que un backend le pide a una BD relacional, agrupadas por lo que resuelven.

B.1 CRUD — las 4 de siempre

-- 1. Leer uno (siempre parametrizado: $1 / %s / ?)
SELECT id, email, name FROM users WHERE id = $1;

-- 2. Leer lista filtrada + ordenada + paginada
SELECT id, title, done FROM tasks
WHERE user_id = $1 AND done = false
ORDER BY created_at DESC
LIMIT 21;                        -- pide 21, muestra 20: el 21 dice "hay más"

-- 3. Insertar y recuperar lo generado
INSERT INTO tasks (user_id, title) VALUES ($1, $2)
RETURNING id, created_at;

-- 4. Actualizar / borrar SIEMPRE con WHERE
UPDATE tasks SET done = true WHERE id = $1 AND user_id = $2;   -- ownership en la query
DELETE FROM tasks WHERE id = $1 AND user_id = $2;

B.2 Relaciones — las que devuelven lo que la UI muestra

-- 5. JOIN: la tarea con su dueño
SELECT t.id, t.title, u.name AS owner
FROM tasks t JOIN users u ON u.id = t.user_id;

-- 6. LEFT JOIN: incluir aunque no haya hijo
SELECT p.id, p.name, COUNT(t.id) AS task_count
FROM projects p LEFT JOIN tasks t ON t.project_id = p.id
GROUP BY p.id;

-- 7. EXISTS: "¿hay alguno que cumpla?" — más barato que COUNT
SELECT EXISTS (SELECT 1 FROM team_members
               WHERE team_id = $1 AND user_id = $2 AND role = 'admin');

-- 8. IN: cualquiera de la lista
SELECT * FROM tasks WHERE project_id IN ($1, $2, $3);

-- 9. Subquery: filtrar por el resultado de otra pregunta
SELECT * FROM users WHERE id IN
  (SELECT user_id FROM tasks GROUP BY user_id HAVING COUNT(*) > 10);

B.3 Agregaciones — responder preguntas, no listar filas

-- 10. Contar y agrupar
SELECT status, COUNT(*) FROM tasks GROUP BY status;

-- 11. Agregado con condición (el "cuántos sin hacer" por usuario)
SELECT user_id, COUNT(*) FILTER (WHERE done = false) AS pending
FROM tasks GROUP BY user_id;

-- 12. HAVING: WHERE sobre grupos
SELECT team_id FROM team_members GROUP BY team_id HAVING COUNT(*) > 50;

-- 13. MAX/más reciente por grupo
SELECT DISTINCT ON (document_id) document_id, rev, created_at
FROM revisions ORDER BY document_id, rev DESC;

B.4 Transacciones — todo o nada

-- 14. La transacción de negocio
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = $1;
UPDATE accounts SET balance = balance + 100 WHERE id = $2;
COMMIT;          -- o ROLLBACK si cualquier pieza falla

-- 15. Lock de fila: "nadie más toque esto hasta que yo termine"
SELECT balance FROM accounts WHERE id = $1 FOR UPDATE;

-- 16. Upsert: insertar o actualizar según choque
INSERT INTO sessions (id, user_id, expires_at) VALUES ($1,$2,$3)
ON CONFLICT (id) DO UPDATE SET expires_at = EXCLUDED.expires_at;

B.5 Performance — ver qué hace la BD

-- 17. El índice que sigue el patrón de acceso
CREATE INDEX CONCURRENTLY idx_tasks_user_created
  ON tasks (user_id, created_at DESC);     -- igualdad primero, orden después

-- 18. Ver el plan: ¿usa el índice o escanea todo?
EXPLAIN ANALYZE SELECT * FROM tasks WHERE user_id = $1 ORDER BY created_at DESC;
-- Seq Scan en tabla grande = alarma; Index Scan/Bitmap = bien

-- 19. Estadísticas frescas (sin esto, el planner decide con datos viejos)
ANALYZE tasks;

-- 20. JSONB: la columna flexible cuando el esquema varía
SELECT snapshot->>'title' FROM revisions WHERE snapshot @> '{"done":true}';

B.6 Las reglas detrás de las queries

  • Parametrizado siempre — $1/%s/? nunca se concatena; la inyección SQL se muere en esta línea (cap. 9).

La inyección SQL es el clásico de romper la query: si concatenas input del usuario en el SQL, el usuario escribe SQL. ' OR 1=1-- borra tablas. La vacuna: queries parametrizadas ($1), siempre.

  • WHERE con ownership — AND user_id = $2 convierte “borra la tarea 5” en “borra la tarea 5 si es tuya” (cap. 17).
  • LIMIT con ORDER BY — límite sin orden es un límite a la suerte: el orden sin índice es sort en memoria (cap. 12).

La memoria RAM es el escritorio de trabajo del programa: rápida, pero se borra al apagar. Los datos que deben sobrevivir van a disco o a la base de datos. Por eso let tasks = [] pierde todo al reiniciar.

Un índice es la tabla de contenido de una tabla: sin él, buscar un usuario es leer las 10 millones de filas (seq scan); con él, es ir directo a la página. Se crea según cómo se consulta, y cada uno cuesta escrituras.

  • EXPLAIN antes de indexar — el índice responde a un patrón de acceso medido, no a una corazonada.
  • FOR UPDATE dentro de la tx — fuera de transacción no bloquea nada: el lock vive y muere con el BEGIN/COMMIT (cap. 13).

Un lock de fila (SELECT ... FOR UPDATE) dice “esta fila es mía hasta que mi transacción termine”: otros que la pidan esperan. Es como el candado del probador de ropa — evita que dos editen lo mismo.

B.7 Mini reto

Reescribir de memoria las 5 queries más usadas de Taskflow.

Una query es la pregunta que le haces a la base de datos en SQL: SELECT * FROM tasks WHERE done = false. La BD traduce la pregunta a un plan de búsqueda — por eso los índices importan.

Las 5 que un CRUD real repite todo el día: (1) leer uno por id con ownership, (2) listar filtrado + orden + LIMIT 21, (3) insert con RETURNING, (4) update con doble WHERE (id + user_id), (5) EXISTS de membresía. Si salen de memoria con los $1 en su sitio y el ownership en el WHERE — eso es el nivel que una entrevista pide.

B.8 Vocabulario técnico del capítulo

Un array es una colección ordenada de elementos accedidos por posición: ["a","b","c"][0] es "a" (se cuenta desde 0). Python las llama listas, Go slices — misma idea, distinto acento.

return hace dos cosas a la vez: devuelve el resultado Y termina la función — lo que esté debajo nunca corre. return task = “aquí está el plato, salgo de la cocina”.

Un snapshot es una foto completa del estado en un momento: el documento entero en la revisión 14. Restaurar = leer la foto, no reconstruir sumando cambios. Cuesta más disco; regala simplicidad.

JSON (JavaScript Object Notation) es el formato universal para mover datos entre programas: texto con llaves y corchetes, {"title": "Comprar leche", "done": false}. Casi cualquier lenguaje lo lee y lo escribe.

Una base de datos es el programa que guarda datos de forma permanente y los responde rápido. Relacional (Postgres): tablas con relaciones. No relacional: documentos, clave-valor, grafos — cada una para una forma de dato distinta.

El esquema es el plano de la base de datos: qué tablas hay, qué columnas tiene cada una y de qué tipo. Es contrato: una fila que no cumple el esquema no entra. Se cambia con migraciones, no a mano.

Una transacción es un grupo de operaciones que se confirman juntas o no se confirma ninguna: transferir dinero = restar de A y sumar a B. Si falla a la mitad sin transacción, el dinero desapareció.

Un JOIN combina tablas por su relación: “cada tarea con el nombre de su proyecto”. Es la superpotencia relacional — en una query traes lo que en NoSQL serían varios viajes.

Paginar es servir la lista en páginas (20 por 20) en vez de las 10 millones. LIMIT 21 + cursor = la página siguiente se pide con la última fila vista, no con “salta 40000” (offset, que se degrada).

CRUD = Create, Read, Update, Delete — las cuatro operaciones básicas sobre un recurso. POST/GET/PUT/DELETE /tasks. El 80% de las APIs del mundo es CRUD bien hecho sobre varias tablas.

Los roles agrupan permisos: viewer lee, editor escribe, admin gestiona. RBAC = el permiso depende del rol que tienes en ese recurso — puedes ser admin de un equipo y viewer de otro.

B.9 Lo que deberías saber hacer ahora