PostgreSQL: o Banco de Dados Servidor, com Npgsql, JSONB e Arrays

Um banco de dados que roda como servidor, atende muitos usuários ao mesmo tempo e conhece decimais exatos, listas e documentos JSON: o PostgreSQL é o destino natural da Livraria.

"EF Core com Desempenho" terminou reconhecendo os limites do SQLite para um sistema com muitos usuários: gravações serializadas, nenhum acesso pela rede, nenhum controle de usuários e permissões, e a falta de tipos importantes, como um decimal exato, que nos obrigou a converter preços em centavos desde "SQL Essencial com SQLite". Nesta aula a Livraria muda para o PostgreSQL, um banco de dados servidor de código aberto que se tornou, na última década, a escolha padrão de muitas empresas para sistemas novos. Veremos como executá-lo, como o C# conversa com ele e os recursos que o diferenciam: tipos ricos, busca de texto sem distinção de caixa, listas e documentos JSON dentro das colunas.

Um pouco de história

O PostgreSQL nasceu na Universidade da Califórnia em Berkeley, como o projeto POSTGRES, liderado por Michael Stonebraker a partir de 1986. Stonebraker já tinha criado o Ingres, um dos primeiros bancos relacionais, e o nome POSTGRES significava justamente "depois do Ingres". Em 1996, com o suporte a SQL incorporado, o projeto passou a se chamar PostgreSQL e a ser desenvolvido por uma comunidade independente, sem uma empresa dona. Stonebraker recebeu o Prêmio Turing, o mais importante da computação, em 2014, em grande parte por esse trabalho.

O PostgreSQL é conhecido por três qualidades: rigor com os dados (ele recusa o que não é válido, em vez de "consertar" em silêncio), aderência ao padrão SQL e extensibilidade, com novos tipos, funções, índices e até linguagens acrescentáveis por extensões. É o banco de dados preferido em várias pesquisas recentes com desenvolvedores, e está disponível como serviço gerenciado em todos os grandes provedores de nuvem.

Executando o PostgreSQL

A forma mais simples de ter um PostgreSQL local é com o Docker, que executa programas em contêineres isolados. Ele ganha uma aula própria na fase do site, e aqui basta saber que ele baixa e roda uma imagem pronta:

docker run --name livraria-pg \
  -e POSTGRES_PASSWORD=senha-de-desenvolvimento \
  -e POSTGRES_DB=livraria \
  -p 5432:5432 \
  -v livraria-pg-dados:/var/lib/postgresql/data \
  -d postgres:17

O contêiner expõe o servidor na porta 5432, a padrão do PostgreSQL, com um banco chamado livraria. O volume nomeado (-v) guarda os dados fora do contêiner, para que eles sobrevivam se o contêiner for recriado. Use a versão 17 ou mais recente. Para explorar o banco, o cliente de linha de comando psql vem dentro da imagem (docker exec -it livraria-pg psql -U postgres -d livraria), e ferramentas gráficas como o pgAdmin e o DBeaver são opções populares.

A cadeia de conexão e os segredos

Um banco servidor exige autenticação, e a cadeia de conexão passa a carregar uma senha:

Host=localhost;Port=5432;Database=livraria;Username=postgres;Password=senha-de-desenvolvimento

E aqui surge uma regra de segurança que vale para todo o resto do curso: senhas nunca ficam no código-fonte. Código vai para repositórios, repositórios são compartilhados e às vezes vazam, e uma senha escrita num arquivo .cs fica no histórico do Git para sempre, mesmo que o arquivo seja corrigido depois. A forma mínima é ler a cadeia de uma variável de ambiente:

string cadeia = Environment.GetEnvironmentVariable("LIVRARIA_BANCO")
    ?? throw new InvalidOperationException("Defina a variável de ambiente LIVRARIA_BANCO.");

Durante o desenvolvimento, o .NET oferece ainda os segredos de usuário (dotnet user-secrets), que guardam valores sensíveis num arquivo fora da pasta do projeto. Na fase de API veremos o sistema de configuração completo, que combina arquivos, variáveis de ambiente e cofres de segredos.

Npgsql: o provedor

O provedor ADO.NET do PostgreSQL é o Npgsql, um projeto de código aberto maduro e muito rápido. A forma moderna de usá-lo parte de um NpgsqlDataSource, um objeto de vida longa que concentra a configuração e o pool de conexões:

using Npgsql;

await using NpgsqlDataSource fonte = NpgsqlDataSource.Create(cadeia);

await using NpgsqlCommand comando = fonte.CreateCommand(
    "SELECT titulo, preco FROM produtos WHERE preco < @limite ORDER BY preco");
comando.Parameters.AddWithValue("limite", 40m);

await using NpgsqlDataReader leitor = await comando.ExecuteReaderAsync();
while (await leitor.ReadAsync())
    Console.WriteLine($"{leitor.GetString(0)}: {leitor.GetDecimal(1):C}");

Tudo o que aprendemos em "ADO.NET" continua valendo: parâmetros, leitores, transações. Duas diferenças práticas. Primeiro, GetDecimal: o PostgreSQL tem o tipo numeric, um decimal exato, e o preço é guardado em reais, sem conversão. Segundo, aqui os métodos assíncronos são realmente assíncronos: há rede entre o programa e o banco, e cada await libera a thread durante a espera, como em "async e await". O Dapper de "Dapper: SQL Seu, Sem a Cerimônia" funciona sobre conexões do Npgsql sem nenhuma mudança.

EF Core com PostgreSQL

Para o Entity Framework, o provedor é o Npgsql.EntityFrameworkCore.PostgreSQL. A troca de banco muda a configuração do contexto, e quase nada mais:

DbContextOptions<LivrariaContext> opcoes = new DbContextOptionsBuilder<LivrariaContext>()
    .UseNpgsql(cadeia)
    .UseSnakeCaseNamingConvention()
    .Options;

E a configuração do preço perde o conversor de centavos:

builder.Property(p => p.Preco).HasPrecision(10, 2);   // numeric(10,2): até 99.999.999,99, exato

HasPrecision(10, 2) declara a coluna como numeric(10,2): dez dígitos no total, dois depois da vírgula. O decimal do C# vai e volta do banco sem arredondamento e sem conversão, e todas as operações (ordenar, somar, comparar) acontecem no banco com exatidão. A soma do exercício 1 de "EF Core: Relacionamentos e Migrations", que exigia acessar a coluna em centavos, vira simplesmente Sum(l => l.Estoque * l.Preco).

Migrations são específicas de cada provedor, porque o SQL gerado é diferente. As migrations criadas para o SQLite não servem para o PostgreSQL. Na mudança de banco, criamos um novo conjunto de migrations a partir do modelo, com dotnet ef migrations add Inicial. Como manter as duas opções vivas no mesmo projeto é o assunto da última aula desta fase.

Datas e horas: o rigor do PostgreSQL

O PostgreSQL tem dois tipos principais para momentos no tempo: timestamp, uma data e hora sem fuso, e timestamptz, um instante absoluto, guardado sempre em UTC. O Npgsql mapeia DateTimeOffset e DateTime para timestamptz, e aqui ele aplica um rigor que surpreende na primeira vez: gravar um DateTime que não seja UTC (como o de DateTime.Now, que tem Kind local) lança exceção. A intenção é impedir o erro clássico de gravar horários locais de máquinas em fusos diferentes como se fossem o mesmo instante. A regra prática: grave instantes como DateTimeOffset ou como DateTime.UtcNow, e converta para o fuso do usuário só na exibição. Datas sem hora, como a data de uma venda, usam DateOnly, mapeado para o tipo date.

Busca sem distinção de maiúsculas e sem acentos

No SQLite, a comparação de textos só ignorava maiúsculas com COLLATE NOCASE, e só para letras sem acento. O PostgreSQL oferece o operador ILIKE, um LIKE que não diferencia maiúsculas, exposto no EF Core como uma função:

var encontrados = await contexto.Produtos
    .Where(p => EF.Functions.ILike(p.Titulo, "%sertão%"))
    .ToListAsync();

ILIKE trata corretamente letras acentuadas: "SERTÃO" e "sertão" são iguais. Mas ele não ignora os acentos: "sertao" continua diferente de "sertão". Para isso, o PostgreSQL tem a extensão unaccent, que remove os acentos de um texto. Extensões são habilitadas uma vez por banco, e a forma limpa de fazer isso é numa migration:

migrationBuilder.Sql("CREATE EXTENSION IF NOT EXISTS unaccent;");

Com ela, o Npgsql expõe EF.Functions.Unaccent, e a busca que o cliente espera, sem se preocupar com acentos nem maiúsculas, fica assim:

var encontrados = await contexto.Produtos
    .Where(p => EF.Functions.ILike(EF.Functions.Unaccent(p.Titulo), "%" + termo + "%"))
    .ToListAsync();

É a busca que o exercício 4 de "LINQ, Parte I" não conseguia fazer em memória e que a aula de EF Core só resolvia pela metade. Para buscas textuais de verdade, com relevância, variações de palavras e grandes volumes, o PostgreSQL tem ainda um mecanismo completo de busca textual (tsvector e tsquery), também acessível pelo Npgsql.

Arrays: listas dentro de uma coluna

Um livro pode pertencer a vários gêneros: romance, realismo, clássico brasileiro. No modelo relacional puro, isso exigiria uma tabela de gêneros e uma tabela de ligação entre livros e gêneros. O PostgreSQL oferece uma alternativa para listas simples: colunas do tipo array. No domínio, Produto ganha uma propriedade:

public string[] Generos { get; private set; } = [];

public void ClassificarEm(params string[] generos) =>
    Generos = generos.Select(g => g.Trim().ToLowerInvariant()).Distinct().ToArray();

O Npgsql mapeia string[] para text[] sem nenhuma configuração, e as consultas LINQ sobre o array são traduzidas para os operadores do PostgreSQL:

var romances = await contexto.Produtos
    .Where(p => p.Generos.Contains("romance"))
    .ToListAsync();
// WHERE p.generos @> ARRAY['romance']::text[]

O @> é o operador de contenção ("o array da coluna contém este"), e a escolha dele pelo Npgsql tem um motivo: ao contrário de = ANY, ele pode usar um índice. Para que buscas em arrays sejam rápidas com muitos registros, o PostgreSQL oferece um tipo de índice próprio para valores compostos, o GIN, criado com builder.HasIndex(p => p.Generos).HasMethod("gin").

Quando usar um array, e quando usar uma tabela de ligação? Arrays servem bem para listas simples de valores, que pertencem ao próprio registro e não têm atributos próprios, como etiquetas e gêneros. Se o gênero precisar de dados próprios, como uma descrição, uma página no site ou uma ordem de exibição, ele é uma entidade, e merece uma tabela.

JSONB: documentos dentro de uma coluna

Outro cenário comum: informações que variam de um produto para outro e não merecem uma coluna cada. A editora, o ano da edição, o idioma, o tradutor, o número de páginas. Alguns livros têm todos esses dados, outros quase nenhum. O PostgreSQL tem o tipo jsonb, que guarda documentos JSON num formato binário, validado e consultável.

No domínio, um record descreve os detalhes:

public sealed record DetalhesEdicao
{
    public string? Editora { get; init; }
    public int? AnoPublicacao { get; init; }
    public string? Idioma { get; init; }
    public string? Tradutor { get; init; }
    public int? Paginas { get; init; }
}

E Produto ganha public DetalhesEdicao? Detalhes { get; private set; }, com um método para defini-los. Na configuração, o tipo próprio de "EF Core: Relacionamentos e Migrations" ganha uma variação: em vez de uma tabela, ele é guardado como JSON numa coluna:

builder.OwnsOne(p => p.Detalhes, d => d.ToJson());

O objeto é serializado para jsonb ao gravar e reconstruído ao ler. E, o mais interessante, as consultas LINQ atravessam o documento:

var dosAnos1890 = await contexto.Produtos
    .Where(p => p.Detalhes != null
             && p.Detalhes.AnoPublicacao >= 1890
             && p.Detalhes.AnoPublicacao < 1900)
    .Select(p => new { p.Titulo, p.Detalhes!.Editora })
    .ToListAsync();

O provedor traduz o acesso a Detalhes.AnoPublicacao para os operadores de JSON do PostgreSQL, e o filtro acontece no banco.

JSONB é poderoso, e por isso mesmo tentador demais. Um aviso de desenho: dados dentro de um documento JSON não têm as garantias das colunas. Não há chave estrangeira, as restrições são mais difíceis de impor e as consultas sobre eles tendem a ser menos eficientes que sobre colunas indexadas. Use JSONB para dados opcionais, variáveis e pouco consultados. Dados centrais para o negócio, que aparecem em filtros, junções e relatórios, como o preço, o autor e o estoque, continuam merecendo colunas próprias. Quando praticamente todos os dados de um sistema forem variáveis e documentais, talvez o problema peça um banco de documentos, assunto de duas aulas adiante.

Juntando tudo: a Livraria no PostgreSQL

Com o contexto configurado para o Npgsql, as migrations recriadas e a extensão unaccent habilitada:

await using (var contexto = new LivrariaContext(opcoes))
{
    var machado = new Autor("Machado de Assis");
    var dom = new LivroImpresso("Dom Casmurro", machado, 59.90m, pesoGramas: 350, estoque: 10);
    dom.ClassificarEm("Romance", "Realismo", "Clássico brasileiro");
    dom.DefinirDetalhes(new DetalhesEdicao { Editora = "Garnier", AnoPublicacao = 1899, Idioma = "pt-BR" });

    var guimaraes = new Autor("Guimarães Rosa");
    var sertao = new LivroImpresso("Grande Sertão: Veredas", guimaraes, 89.00m, pesoGramas: 1200, estoque: 2);
    sertao.ClassificarEm("Romance", "Regionalismo");
    sertao.DefinirDetalhes(new DetalhesEdicao { AnoPublicacao = 1956, Paginas = 608 });

    contexto.AddRange(dom, sertao);
    await contexto.SaveChangesAsync();
}

await using (var contexto = new LivrariaContext(opcoes))
{
    string termo = "sertao";   // o cliente digitou sem acento
    var busca = await contexto.Produtos
        .AsNoTracking()
        .Where(p => EF.Functions.ILike(EF.Functions.Unaccent(p.Titulo), "%" + termo + "%"))
        .Select(p => p.Titulo)
        .ToListAsync();
    Console.WriteLine($"Busca por \"{termo}\": {string.Join(", ", busca)}");

    var romances = await contexto.Produtos
        .AsNoTracking()
        .Where(p => p.Generos.Contains("romance"))
        .OrderBy(p => p.Preco)
        .Select(p => new { p.Titulo, p.Preco, Ano = p.Detalhes!.AnoPublicacao })
        .ToListAsync();
    foreach (var r in romances)
        Console.WriteLine($"{r.Titulo,-25}{r.Preco,10:C}  ({r.Ano})");

    decimal valorEmEstoque = await contexto.Produtos
        .OfType<LivroImpresso>()
        .SumAsync(l => l.Estoque * l.Preco);
    Console.WriteLine($"Valor em estoque: {valorEmEstoque:C}");
}

Busca por "sertao": Grande Sertão: Veredas
Dom Casmurro               R$ 59,90  (1899)
Grande Sertão: Veredas     R$ 89,00  (1956)
Valor em estoque: R$ 777,00

(10 × 59,90 + 2 × 89,00 = 599,00 + 178,00 = 777,00.) Observe o que mudou em relação ao SQLite: a busca encontra "Sertão" a partir de "sertao"; os gêneros são uma lista consultável; os detalhes da edição são um documento que o banco sabe filtrar; e a soma do valor em estoque é uma expressão direta, feita no banco com decimais exatos, sem nenhum conversor. O domínio, com suas validações, seus eventos e sua proteção de estoque, não percebeu a troca de banco.

O PostgreSQL é uma escolha excelente, mas não é a única que você vai encontrar. Uma parcela enorme dos sistemas em produção, de lojas virtuais a hospedagens compartilhadas com painel de controle, roda sobre outro banco de código aberto, ainda mais antigo e popular. Ele fala o mesmo SQL em quase tudo, mas tem diferenças de comportamento que pegam de surpresa quem vem do PostgreSQL, a começar por comparações de texto que ignoram acentos sem que ninguém tenha pedido.

Fontes e leituras recomendadas

• Documentação do PostgreSQL: https://www.postgresql.org/docs/. É a referência oficial, completa e muito bem escrita, dos tipos às extensões.
• Documentação do Npgsql: https://www.npgsql.org/doc/. Cobre o provedor ADO.NET, NpgsqlDataSource, tipos e desempenho.
• Provedor do EF Core para PostgreSQL: https://www.npgsql.org/efcore/. Traz a configuração, os arrays, a busca textual, as funções e a tradução de consultas.
• Mapeamento de data e hora no Npgsql: https://www.npgsql.org/doc/types/datetime.html. Explica por que o provedor exige UTC para timestamptz e como lidar com isso.
• Mapeamento de JSON no Npgsql com EF Core: https://www.npgsql.org/efcore/mapping/json.html. Mostra ToJson, as consultas sobre documentos e as opções de mapeamento.
• Armazenamento seguro de segredos em desenvolvimento: https://learn.microsoft.com/aspnet/core/security/app-secrets. Documenta o dotnet user-secrets e a leitura de segredos por variáveis de ambiente.

Exercícios

Exercício 1

Configure a Livraria para ler a cadeia de conexão do PostgreSQL da variável de ambiente LIVRARIA_BANCO, falhando com uma mensagem clara se ela não existir. Explique por que isso é melhor do que deixar a cadeia no Program.cs, mesmo num projeto pessoal.

string cadeia = Environment.GetEnvironmentVariable("LIVRARIA_BANCO")
    ?? throw new InvalidOperationException(
        "A variável de ambiente LIVRARIA_BANCO não está definida. " +
        "Exemplo: Host=localhost;Database=livraria;Username=postgres;Password=...");

DbContextOptions<LivrariaContext> opcoes = new DbContextOptionsBuilder<LivrariaContext>()
    .UseNpgsql(cadeia)
    .UseSnakeCaseNamingConvention()
    .Options;

Exercício 2

O código abaixo lança exceção ao salvar no PostgreSQL. Explique o motivo e corrija-o.

pedido.PagoEm = DateTime.Now;   // propriedade DateTime mapeada para timestamptz
await contexto.SaveChangesAsync();

public DateTimeOffset? PagoEm { get; private set; }
// ...
pedido.PagoEm = DateTimeOffset.UtcNow;   // ou relogio.GetUtcNow(), com o TimeProvider de "Testes Automatizados"

Exercício 3

Habilite a extensão unaccent por uma migration e escreva uma busca por autor que encontre "Graciliano Ramos" a partir do termo "GRACILIANO" e "Guimarães Rosa" a partir de "guimaraes".

public partial class HabilitaUnaccent : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder) =>
        migrationBuilder.Sql("CREATE EXTENSION IF NOT EXISTS unaccent;");

    protected override void Down(MigrationBuilder migrationBuilder) =>
        migrationBuilder.Sql("DROP EXTENSION IF EXISTS unaccent;");
}

static Task<List<string>> BuscarAutoresAsync(LivrariaContext contexto, string termo) =>
    contexto.Set<Autor>()
        .Where(a => EF.Functions.ILike(EF.Functions.Unaccent(a.Nome), "%" + termo + "%"))
        .Select(a => a.Nome)
        .ToListAsync();

Exercício 4

Escreva uma consulta que devolva os livros que pertencem a pelo menos um dos gêneros de uma lista recebida por parâmetro, como ["regionalismo", "modernismo"].

string[] procurados = ["regionalismo", "modernismo"];

var encontrados = await contexto.Produtos
    .AsNoTracking()
    .Where(p => p.Generos.Any(g => procurados.Contains(g)))
    .Select(p => new { p.Titulo, p.Generos })
    .ToListAsync();

Exercício 5

Um colega propõe guardar todos os dados de um produto (título, preço, estoque, autor) num único campo jsonb, "para ter flexibilidade". Dê três argumentos contra e diga quando um desenho parecido faria sentido.