A forma de um banco de dados

Toda consulta que você escrever na vida se apoia em um modelo mental. Uma vez que você o tenha, SQL deixa de ser um encantamento mágico copiado de um desenvolvedor e passa a ser algo sobre o qual você consegue raciocinar. Esta aula constrói esse modelo, e é a única aula do curso com mais leitura do que digitação.

Tabelas, linhas e colunas

Um banco de dados relacional guarda dados em tabelas. Uma tabela é uma grade:

• uma coluna é um campo, com nome e tipo, iguais para todas as entradas
• uma linha é uma entrada, uma coisa: um post, uma categoria, uma tag

Nosso blog tem quatro tabelas. Olhe para categories, a mais simples:

Três linhas, quatro colunas. Uma planilha com regras, e as regras são a parte interessante.

A chave primária

Olhe para a coluna id. Ela é a chave primária: o valor que identifica uma linha de forma única e nunca se repete dentro da tabela. Duas categorias jamais podem compartilhar um id.

Neste schema as chaves primárias são SERIAL, o que significa que o próprio banco atribui o próximo número sempre que uma linha é inserida. Você não o escolhe, e não pode contar com ele ser sequencial depois de exclusões, mas dentro de uma tabela ele é garantidamente único.

👨‍🏫 Para quem testa, a chave primária é a impressão digital (🫆) da linha. Quando você encontrar uma linha ruim, anote o id dela. É a única informação que continua verdadeira, não importa como o registro seja editado depois, e é o que transforma "um dos posts está errado" em "o post 14 está errado", que é algo em que um desenvolvedor consegue agir.

A chave estrangeira, e a forma um-para-muitos

Agora abra posts:

\d posts

Entre suas colunas está category_id, e no final da saída você vê isto:

Foreign-key constraints:
    "posts_category_id_fkey" FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL

Uma chave estrangeira é uma coluna que aponta para a chave primária de outra tabela. posts.category_id guarda o id de uma linha em categories. É assim que as duas tabelas se conectam, e isso nos dá um relacionamento um-para-muitos: uma categoria tem muitos posts, um post tem no máximo uma categoria.

A chave estrangeira também é uma regra que o banco de dados impõe. Você não consegue guardar category_id = 999 se não existir a categoria 999; o banco rejeita a linha. Essa é uma garantia sobre os seus dados que nenhuma quantidade de código de aplicação pode oferecer com a mesma confiança, e é o motivo pelo qual quem testa deveria ler o schema antes de escrever o primeiro teste.

Dois detalhes dessa constraint importam mais adiante:

• category_id é anulável. Um post pode não ter categoria nenhuma. Esse único fato está por trás de toda uma família de erros de teste, e ganha uma aula só para ele.
• ON DELETE SET NULL. Se uma categoria for excluída, seus posts sobrevivem, com category_id virando NULL. Quem decidiu o que acontece foi o banco de dados, não a aplicação.

A tabela de junção, e a forma muitos-para-muitos

Um post pode ter várias tags, e uma tag pode estar em vários posts. Isso é muitos-para-muitos, e não há como expressar com uma coluna em nenhum dos lados. Qual coluna guardaria três valores?

Então um schema relacional usa uma quarta tabela, post_tags:

\d post_tags

Duas colunas, ambas chaves estrangeiras, e juntas elas formam a chave primária. Cada linha significa "este post tem esta tag". Um post com três tags produz três linhas aqui. É só isso que uma tabela de junção é: uma lista de pares.

Duas consequências que valem levar com você:

• Como (post_id, tag_id) é a chave primária, a mesma tag não pode ser anexada duas vezes ao mesmo post. O banco torna a duplicata impossível.
• Ambas as chaves estrangeiras são ON DELETE CASCADE, então excluir um post remove automaticamente suas linhas aqui. As tags em si sobrevivem, porque outros posts ainda podem usá-las.

👨‍🏫 Sempre que você vir uma tabela cujo nome parece dois outros nomes de tabela colados (post_tags, order_items, user_roles), você está olhando para um relacionamento muitos-para-muitos. Em nove de cada dez casos é também onde estão os bugs interessantes, porque é a única parte do schema que a aplicação precisa manter na mão.

Lendo um schema que você não escreveu

Você tem três formas de aprender um schema, e quem testa bem usa as três.

1. O arquivo de schema. Neste projeto, db/schema.sql tem 40 linhas e conta tudo: as tabelas, os tipos, o que é NOT NULL, o que é UNIQUE, as chaves estrangeiras e os índices. Quando um projeto tem um, leia primeiro.

2. O próprio banco de dados. \dt lista as tabelas, \d <tabela> descreve uma. Isso funciona em qualquer banco, inclusive em um de produção cujo código-fonte você nunca viu, que é exatamente quando você mais precisa.

3. O desenho. Para quatro tabelas, um esboço vence os dois:

categories                posts                         tags
----------                -----                         ----
id         PK   <-------- category_id  FK               id         PK
name       UNIQUE         id           PK               name       UNIQUE
slug       UNIQUE         title                         slug       UNIQUE
created_at                body                          created_at
                          created_at
                                  \                    /
                                   \   post_tags      /
                                    -- post_id  FK --
                                       tag_id   FK

As setas apontam da chave estrangeira para a chave primária que ela referencia. Quando você consegue desenhar isso para uma aplicação, consegue consultá-la.

O que o schema conta a quem testa antes de qualquer teste rodar

Ler um schema é, em si, uma atividade de teste. Cada constraint responde a uma pergunta que você teria de fazer a um desenvolvedor, e cada constraint ausente é um caso de teste:

👨‍🏫 Essa última linha é a que eu quero que você guarde. Metade das discussões de "isso é bug?" se resolve em trinta segundos lendo o schema.

Visão geral dos comandos 📖

\dt

Lista as tabelas do banco de dados atual.

Sintaxe:

\dt

Exemplo:

\dt

\d

Descreve uma tabela: colunas, tipos, nulabilidade, defaults, índices e chaves estrangeiras.

Sintaxe:

\d <nome-da-tabela>

Exemplo:

\d post_tags

Conteúdo sugerido 📚

• Data Definition - documentação oficial do PostgreSQL
• Constraints - documentação oficial do PostgreSQL
• Foreign keys - documentação oficial do PostgreSQL

Exercício 🎯

Sem rodar um único SELECT, use \d nas quatro tabelas e responda ao seguinte, anotando suas respostas antes de conferir o spoiler:

1. Quais colunas do schema inteiro são UNIQUE?
2. Se alguém excluir a categoria Databases, o que acontece com os posts dela?
3. Se alguém excluir um post que tem três tags, quantas linhas somem de post_tags, e quantas de tags?
4. Dois posts diferentes podem ter exatamente o mesmo título?

🙊 Aqui estão as respostas:

1. categories.name, categories.slug, tags.name e tags.slug. Mais as chaves primárias, que são únicas por definição: categories.id, posts.id, tags.id e o par (post_id, tag_id) em post_tags.
2. Nada é excluído. ON DELETE SET NULL significa que os posts sobrevivem com category_id virando NULL. Eles então aparecem como posts sem categoria.
3. Três linhas somem de post_tags, por causa do ON DELETE CASCADE. Zero linhas somem de tags: as tags em si pertencem ao blog inteiro, não àquele post, e outros posts ainda podem estar usando-as. Encontrar as tags que não pertencem mais a nenhum post é um ótimo exercício, e fazemos exatamente isso daqui a duas aulas.
4. Sim. Não há constraint UNIQUE em posts.title, então títulos duplicados são permitidos por design. Vamos criar um par deles de propósito mais adiante, e depois encontrá-los com uma consulta.

Mostre ao mundo o que você aprendeu 🌎

Para mostrar à sua rede profissional o que você aprendeu nesta aula, publique o seguinte no LinkedIn.

Estou fazendo o curso "SQL para Testadores" do @Walmyr Lima e Silva Filho na @Talking About Testing School, onde aprendi a ler um schema de banco de dados como quem testa: chaves primárias, chaves estrangeiras, relacionamentos um-para-muitos e muitos-para-muitos, e o que cada constraint conta sobre a aplicação antes de qualquer teste rodar. #TalkingAboutTesting #TATSchool #SQLForTesters #SQL #PostgreSQL #Testes

👨‍🏫 Lembre-se de me marcar no seu post. Aqui está meu perfil no LinkedIn.