SQLite é o banco de dados mais instalado do mundo — está presente em todos os smartphones Android e iPhone, em todos os navegadores, no sistema operacional macOS, em centenas de milhões de dispositivos embarcados. Mas na web com PHP, é frequentemente ignorado ou mal compreendido.
SQLite não é um banco de dados "de brinquedo". É uma escolha deliberada e poderosa para cenários específicos: aplicações de pequeno a médio porte com tráfego moderado, ferramentas de linha de comando, ambientes de teste, cache local, e qualquer situação onde você não queira (ou não possa) manter um servidor de banco de dados separado.
O que é o SQLite e como ele funciona
Ao contrário do MySQL e PostgreSQL, que são sistemas cliente-servidor, o SQLite é uma biblioteca que se incorpora diretamente à sua aplicação. O banco de dados inteiro é um único arquivo em disco. Não há servidor para iniciar, não há configuração de usuários e senhas, não há processo separado.
Isso traz consequências importantes:
Vantagens: zero configuração, portabilidade total (copie o arquivo e leve o banco junto), perfeito para desenvolvimento e testes, funciona em qualquer hospedagem sem dependências externas.
Limitações: escritas concorrentes são serialized (apenas uma escrita por vez, embora o WAL mode melhore isso), não é adequado para aplicações com alta concorrência de escrita, não suporta alguns tipos de dados avançados.
Criando e conectando ao banco
<?php
declare(strict_types=1);
// O banco é criado automaticamente se não existir
// Use :memory: para um banco em memória (ideal para testes)
$pdo = new PDO('sqlite:banco.db', options: [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
// Ativar WAL mode: melhora performance de leitura concorrente
$pdo->exec('PRAGMA journal_mode = WAL');
// Ativar foreign keys (desativado por padrão no SQLite!)
$pdo->exec('PRAGMA foreign_keys = ON');
// Ajustar tamanho do cache
$pdo->exec('PRAGMA cache_size = -64000'); // 64MB
echo "SQLite " . $pdo->query('SELECT sqlite_version()')->fetchColumn() . "\n";
O detalhe do PRAGMA foreign_keys = ON é crítico e frequentemente esquecido. Por padrão, o SQLite não verifica integridade referencial — chaves estrangeiras são aceitas mas não validadas. Sempre ative esse pragma na abertura da conexão.
Schema e tipos de dados
O SQLite usa um sistema de tipos chamado "type affinity" — diferente de outros bancos, ele é bastante flexível (você pode inserir um texto numa coluna INTEGER). Na prática, use os tipos normalmente e o SQLite fará o melhor:
<?php
$pdo->exec("
CREATE TABLE IF NOT EXISTS notas (
id INTEGER PRIMARY KEY AUTOINCREMENT,
titulo TEXT NOT NULL,
conteudo TEXT,
criada_em TEXT NOT NULL DEFAULT (datetime('now')),
atualizada_em TEXT,
arquivada INTEGER NOT NULL DEFAULT 0 -- 0 = false, 1 = true
)
");
$pdo->exec("
CREATE TABLE IF NOT EXISTS tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nome TEXT NOT NULL UNIQUE
)
");
$pdo->exec("
CREATE TABLE IF NOT EXISTS notas_tags (
nota_id INTEGER NOT NULL REFERENCES notas(id) ON DELETE CASCADE,
tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (nota_id, tag_id)
)
");
// Índice para busca por texto
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_notas_titulo ON notas(titulo)");
Full-Text Search — busca de texto com FTS5
Uma das joias do SQLite é o módulo FTS5 (Full-Text Search version 5), que permite busca textual eficiente sem precisar de Elasticsearch ou Typesense para casos simples:
<?php
// Criando tabela virtual de FTS
$pdo->exec("
CREATE VIRTUAL TABLE IF NOT EXISTS notas_fts
USING fts5(titulo, conteudo, content='notas', content_rowid='id')
");
// Trigger para manter o índice atualizado
$pdo->exec("
CREATE TRIGGER IF NOT EXISTS notas_fts_insert
AFTER INSERT ON notas BEGIN
INSERT INTO notas_fts(rowid, titulo, conteudo)
VALUES (new.id, new.titulo, new.conteudo);
END
");
$pdo->exec("
CREATE TRIGGER IF NOT EXISTS notas_fts_delete
AFTER DELETE ON notas BEGIN
INSERT INTO notas_fts(notas_fts, rowid, titulo, conteudo)
VALUES ('delete', old.id, old.titulo, old.conteudo);
END
");
// O terceiro trigger é o mais esquecido, e o que estraga a busca em
// silêncio. Sem ele, editar uma nota deixa o índice preso ao texto antigo:
// a busca passa a devolver a nota por uma palavra que ela não contém mais e
// deixa de encontrá-la pelo texto novo. Nada falha, nada aparece no log.
// Em tabela de conteúdo externo, atualizar é apagar e inserir de novo.
$pdo->exec("
CREATE TRIGGER IF NOT EXISTS notas_fts_update
AFTER UPDATE ON notas BEGIN
INSERT INTO notas_fts(notas_fts, rowid, titulo, conteudo)
VALUES ('delete', old.id, old.titulo, old.conteudo);
INSERT INTO notas_fts(rowid, titulo, conteudo)
VALUES (new.id, new.titulo, new.conteudo);
END
");
// Inserindo e buscando
$pdo->prepare("INSERT INTO notas (titulo, conteudo) VALUES (?, ?)")
->execute(['PHP e SQLite', 'SQLite é excelente para aplicações PHP de médio porte']);
$pdo->prepare("INSERT INTO notas (titulo, conteudo) VALUES (?, ?)")
->execute(['PostgreSQL avançado', 'JSONB e arrays nativos tornam o Postgres poderoso']);
// Busca FTS com ranking por relevância
$stmt = $pdo->prepare("
SELECT n.id, n.titulo, n.conteudo,
bm25(notas_fts) AS relevancia
FROM notas_fts
JOIN notas n ON n.id = notas_fts.rowid
WHERE notas_fts MATCH :termo
ORDER BY relevancia
LIMIT 10
");
$stmt->execute([':termo' => 'PHP SQLite']);
$resultados = $stmt->fetchAll();
SQLite para testes — o caso de uso mais valioso
O uso mais poderoso do SQLite em projetos profissionais é como banco de dados para testes automatizados. Em vez de criar um banco MySQL real para os testes (lento, com estado compartilhado entre execuções), use SQLite em memória:
<?php
// Em um TestCase base (PHPUnit)
abstract class DatabaseTestCase extends TestCase
{
protected PDO $pdo;
protected function setUp(): void
{
// Banco em memória: criado do zero a cada teste, destruído ao final
$this->pdo = new PDO('sqlite::memory:', options: [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$this->pdo->exec('PRAGMA foreign_keys = ON');
$this->migrarSchema();
$this->inserirDadosDeFixture();
}
private function migrarSchema(): void
{
// Um schema escrito para MySQL NÃO roda aqui: AUTO_INCREMENT, ENGINE,
// ENUM, TINYINT(1) e as crases não existem no SQLite, e o arquivo morre
// logo na primeira linha ("near SET: syntax error"). Então guarde um
// schema próprio para os testes — e saiba que isso é o preço desta
// técnica: o que roda contra SQLite não prova nada sobre o dialeto do
// banco de produção. Para o que depende de SQL específico, o teste
// tem de rodar contra o banco de verdade.
$sql = file_get_contents(__DIR__ . '/schema.sqlite.sql');
$this->pdo->exec($sql);
}
private function inserirDadosDeFixture(): void
{
$this->pdo->exec("
INSERT INTO usuarios (nome, email) VALUES
('Alice', 'alice@teste.com'),
('Bob', 'bob@teste.com')
");
}
}
class UsuarioRepositorioTest extends DatabaseTestCase
{
public function test_buscar_por_email_retorna_usuario_existente(): void
{
$repo = new UsuarioRepositorio($this->pdo);
$usuario = $repo->buscarPorEmail('alice@teste.com');
$this->assertNotNull($usuario);
$this->assertSame('Alice', $usuario->nome);
}
public function test_buscar_por_email_retorna_null_para_inexistente(): void
{
$repo = new UsuarioRepositorio($this->pdo);
$usuario = $repo->buscarPorEmail('inexistente@teste.com');
$this->assertNull($usuario);
}
}
Cada teste começa com um banco limpo, sem afetar outros testes. Muito mais rápido que MySQL ou PostgreSQL em memória real.
A pergunta que decide o uso do SQLite não é o tamanho do banco, é o perfil de escrita. Leitura ele aguenta em volume que surpreende; o limite aparece quando vários processos querem gravar ao mesmo tempo, porque a escrita é serializada — e nem o WAL muda isso, ele só impede que uma escrita trave as leituras. Um site de conteúdo, uma ferramenta de linha de comando ou o cache local de uma aplicação cabem bem; uma fila de pedidos com dez workers gravando, não.
O uso mais subestimado continua sendo o banco de teste em memória, e vale saber onde ele mente. O schema do seu projeto provavelmente não roda aqui sem tradução, e o que passa contra SQLite não prova nada sobre o dialeto do banco de produção. O ganho é de velocidade e isolamento — cada teste começa com um banco limpo —, não de fidelidade. Para o que depende de SQL específico, o teste precisa rodar no banco de verdade.
Fontes e leituras recomendadas
- Documentação oficial do SQLite: https://www.sqlite.org/docs.html
- SQLite FTS5: https://www.sqlite.org/fts5.html
- PHP Manual — PDO_SQLite: https://www.php.net/manual/pt_BR/ref.pdo-sqlite.php
- Artigo "Consider SQLite" de Ben Johnson: https://litestream.io/alternatives/
- SQLite — modo WAL, que libera leitura concorrente com escrita: https://www.sqlite.org/wal.html
- SQLite — quando usar e quando nao usar, pela propria equipe do projeto: https://www.sqlite.org/whentouse.html
Exercícios
Exercício 1
Crie uma aplicação de notas pessoais com SQLite: CRUD completo (criar, listar, editar, arquivar, deletar) com tags. Use PRAGMA foreign_keys = ON e WAL mode.
Ver resposta
✓ Resposta: Os quatro PRAGMAs no início não são enfeite: foreign_keys precisa ser ligado em toda conexão (é o erro mais comum com SQLite), o WAL evita que uma escrita trave as leituras, e o busy_timeout troca "database is locked" por uma espera curta.
<?php
declare(strict_types=1);
final class BancoNotas
{
public static function abrir(string $caminho = __DIR__ . '/notas.db'): PDO
{
$pdo = new PDO('sqlite:' . $caminho, null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
// ⚠️ O SQLite IGNORA foreign keys por padrão. Sem esta linha, as
// FKs do schema são pura decoração — e por conexão, não por banco.
$pdo->exec('PRAGMA foreign_keys = ON');
// WAL: leitores não bloqueiam o escritor e vice-versa. Em modo
// journal padrão, uma escrita trava todas as leituras.
$pdo->exec('PRAGMA journal_mode = WAL');
// NORMAL com WAL é seguro e bem mais rápido que FULL.
$pdo->exec('PRAGMA synchronous = NORMAL');
// Espera até 5 s se o banco estiver travado, em vez de falhar já.
$pdo->exec('PRAGMA busy_timeout = 5000');
self::migrar($pdo);
return $pdo;
}
private static function migrar(PDO $pdo): void
{
$pdo->exec(<<<SQL
CREATE TABLE IF NOT EXISTS notas (
id INTEGER PRIMARY KEY AUTOINCREMENT,
titulo TEXT NOT NULL,
conteudo TEXT NOT NULL DEFAULT '',
arquivada INTEGER NOT NULL DEFAULT 0 CHECK (arquivada IN (0,1)),
criada_em TEXT NOT NULL DEFAULT (datetime('now')),
atualizada_em TEXT NOT NULL DEFAULT (datetime('now'))
)
SQL);
$pdo->exec('CREATE TABLE IF NOT EXISTS tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nome TEXT NOT NULL UNIQUE COLLATE NOCASE
)');
$pdo->exec(<<<SQL
CREATE TABLE IF NOT EXISTS nota_tags (
nota_id INTEGER NOT NULL,
tag_id INTEGER NOT NULL,
PRIMARY KEY (nota_id, tag_id),
-- CASCADE aqui é correto: o vínculo não existe sem a nota.
FOREIGN KEY (nota_id) REFERENCES notas(id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
)
SQL);
$pdo->exec('CREATE INDEX IF NOT EXISTS idx_notas_arquivada ON notas(arquivada)');
}
}
final class NotaRepositorio
{
public function __construct(private readonly PDO $pdo) {}
public function criar(string $titulo, string $conteudo, array $tags = []): int
{
$this->pdo->beginTransaction();
try {
$stmt = $this->pdo->prepare(
'INSERT INTO notas (titulo, conteudo) VALUES (:t, :c)'
);
$stmt->execute([':t' => trim($titulo), ':c' => $conteudo]);
$id = (int) $this->pdo->lastInsertId();
$this->sincronizarTags($id, $tags);
$this->pdo->commit();
} catch (Throwable $e) {
$this->pdo->rollBack();
throw $e;
}
return $id;
}
/** @return array{id:int,titulo:string,conteudo:string,tags:string[]}|null */
public function buscar(int $id): ?array
{
$stmt = $this->pdo->prepare('SELECT * FROM notas WHERE id = :id');
$stmt->execute([':id' => $id]);
$nota = $stmt->fetch();
if ($nota === false) {
return null;
}
$nota['tags'] = $this->tagsDe($id);
return $nota;
}
public function listar(bool $incluirArquivadas = false, ?string $tag = null): array
{
$sql = 'SELECT n.*, GROUP_CONCAT(t.nome) AS tags
FROM notas n
LEFT JOIN nota_tags nt ON nt.nota_id = n.id
LEFT JOIN tags t ON t.id = nt.tag_id';
$condicoes = [];
$bind = [];
if (!$incluirArquivadas) {
$condicoes[] = 'n.arquivada = 0';
}
if ($tag !== null) {
$condicoes[] = 'n.id IN (SELECT nt2.nota_id FROM nota_tags nt2
JOIN tags t2 ON t2.id = nt2.tag_id
WHERE t2.nome = :tag)';
$bind[':tag'] = $tag;
}
if ($condicoes !== []) {
$sql .= ' WHERE ' . implode(' AND ', $condicoes);
}
$sql .= ' GROUP BY n.id ORDER BY n.atualizada_em DESC';
$stmt = $this->pdo->prepare($sql);
$stmt->execute($bind);
return array_map(static function (array $l): array {
$l['tags'] = $l['tags'] !== null ? explode(',', $l['tags']) : [];
return $l;
}, $stmt->fetchAll());
}
public function editar(int $id, string $titulo, string $conteudo, array $tags = []): bool
{
$this->pdo->beginTransaction();
try {
$stmt = $this->pdo->prepare(
"UPDATE notas SET titulo = :t, conteudo = :c,
atualizada_em = datetime('now')
WHERE id = :id"
);
$stmt->execute([':t' => trim($titulo), ':c' => $conteudo, ':id' => $id]);
$alterou = $stmt->rowCount() > 0;
if ($alterou) {
$this->sincronizarTags($id, $tags);
}
$this->pdo->commit();
} catch (Throwable $e) {
$this->pdo->rollBack();
throw $e;
}
return $alterou;
}
public function arquivar(int $id, bool $arquivar = true): bool
{
$stmt = $this->pdo->prepare(
"UPDATE notas SET arquivada = :a, atualizada_em = datetime('now') WHERE id = :id"
);
$stmt->execute([':a' => $arquivar ? 1 : 0, ':id' => $id]);
return $stmt->rowCount() > 0;
}
public function deletar(int $id): bool
{
// As linhas de nota_tags saem sozinhas pelo ON DELETE CASCADE —
// desde que o PRAGMA foreign_keys esteja ligado.
$stmt = $this->pdo->prepare('DELETE FROM notas WHERE id = :id');
$stmt->execute([':id' => $id]);
return $stmt->rowCount() > 0;
}
private function sincronizarTags(int $notaId, array $tags): void
{
$this->pdo->prepare('DELETE FROM nota_tags WHERE nota_id = :id')
->execute([':id' => $notaId]);
$inserirTag = $this->pdo->prepare(
'INSERT INTO tags (nome) VALUES (:nome) ON CONFLICT(nome) DO NOTHING'
);
$buscarTag = $this->pdo->prepare('SELECT id FROM tags WHERE nome = :nome');
$vincular = $this->pdo->prepare(
'INSERT INTO nota_tags (nota_id, tag_id) VALUES (:n, :t)'
);
foreach (array_unique(array_map('trim', $tags)) as $nome) {
if ($nome === '') {
continue;
}
$inserirTag->execute([':nome' => $nome]);
$buscarTag->execute([':nome' => $nome]);
$vincular->execute([':n' => $notaId, ':t' => (int) $buscarTag->fetchColumn()]);
}
}
private function tagsDe(int $notaId): array
{
$stmt = $this->pdo->prepare(
'SELECT t.nome FROM tags t JOIN nota_tags nt ON nt.tag_id = t.id
WHERE nt.nota_id = :id ORDER BY t.nome'
);
$stmt->execute([':id' => $notaId]);
return $stmt->fetchAll(PDO::FETCH_COLUMN);
}
}
$repo = new NotaRepositorio(BancoNotas::abrir());
$id = $repo->criar('Comprar café', 'Grão, torra média.', ['casa', 'compras']);
$repo->editar($id, 'Comprar café especial', 'Grão, torra média, 500g.', ['casa']);
$repo->arquivar($id);
print_r($repo->listar(incluirArquivadas: true));Exercício 2
Implemente FTS5 na tabela de notas. Escreva uma função buscar(string $termo): array que use MATCH e retorne resultados ordenados por relevância (bm25).
Ver resposta
✓ Resposta: Três armadilhas do FTS5: o índice externo não se atualiza sem os triggers; bm25() é negativo e ordena ascendente; e texto do usuário nunca vai cru para o MATCH — um simples hífen ou aspas quebra a consulta. O remove_diacritics 2 é o que faz "cafe" achar "café".
<?php
declare(strict_types=1);
final class BuscaNotas
{
public function __construct(private readonly PDO $pdo)
{
$this->criarIndice();
}
private function criarIndice(): void
{
// content='notas' cria um índice EXTERNO: o FTS5 não duplica o texto,
// só guarda o índice invertido e aponta para a tabela original.
// content_rowid liga o rowid do índice ao id da nota.
$this->pdo->exec(<<<SQL
CREATE VIRTUAL TABLE IF NOT EXISTS notas_fts USING fts5(
titulo,
conteudo,
content='notas',
content_rowid='id',
tokenize="unicode61 remove_diacritics 2"
)
SQL);
// Índice externo NÃO se atualiza sozinho: os triggers é que mantêm
// os dois em sincronia. Sem eles, a busca vai devolver lixo.
$this->pdo->exec(<<<SQL
CREATE TRIGGER IF NOT EXISTS notas_ai AFTER INSERT ON notas BEGIN
INSERT INTO notas_fts (rowid, titulo, conteudo)
VALUES (new.id, new.titulo, new.conteudo);
END
SQL);
$this->pdo->exec(<<<SQL
CREATE TRIGGER IF NOT EXISTS notas_ad AFTER DELETE ON notas BEGIN
INSERT INTO notas_fts (notas_fts, rowid, titulo, conteudo)
VALUES ('delete', old.id, old.titulo, old.conteudo);
END
SQL);
$this->pdo->exec(<<<SQL
CREATE TRIGGER IF NOT EXISTS notas_au AFTER UPDATE ON notas BEGIN
INSERT INTO notas_fts (notas_fts, rowid, titulo, conteudo)
VALUES ('delete', old.id, old.titulo, old.conteudo);
INSERT INTO notas_fts (rowid, titulo, conteudo)
VALUES (new.id, new.titulo, new.conteudo);
END
SQL);
}
/** Reconstrói o índice do zero — útil depois de importar em massa. */
public function reindexar(): void
{
$this->pdo->exec("INSERT INTO notas_fts (notas_fts) VALUES ('rebuild')");
}
/**
* bm25() devolve valor NEGATIVO: quanto MENOR, mais relevante. Por isso
* o ORDER BY é ascendente — ordenar DESC traz os piores primeiro.
* Os pesos (2.0, 1.0) dizem que acerto no título vale o dobro.
*/
public function buscar(string $termo, int $limite = 20): array
{
$consulta = $this->prepararConsulta($termo);
if ($consulta === '') {
return [];
}
$stmt = $this->pdo->prepare(<<<SQL
SELECT n.id,
n.titulo,
n.arquivada,
-- snippet: trecho com o termo destacado
snippet(notas_fts, 1, '<mark>', '</mark>', '…', 24) AS trecho,
bm25(notas_fts, 2.0, 1.0) AS relevancia
FROM notas_fts
JOIN notas n ON n.id = notas_fts.rowid
WHERE notas_fts MATCH :termo
AND n.arquivada = 0
ORDER BY relevancia
LIMIT :limite
SQL);
$stmt->bindValue(':termo', $consulta);
$stmt->bindValue(':limite', $limite, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll();
}
/**
* O texto do usuário NÃO pode ir cru para o MATCH: aspas, hífens e
* operadores do FTS5 (AND, OR, NOT, NEAR) causam erro de sintaxe ou
* mudam a semântica da busca.
*/
private function prepararConsulta(string $termo): string
{
$palavras = preg_split('/\s+/u', trim($termo), -1, PREG_SPLIT_NO_EMPTY) ?: [];
$limpas = [];
foreach ($palavras as $palavra) {
$palavra = preg_replace('/["\'()*:^-]/u', '', $palavra);
if (mb_strlen($palavra) >= 2) {
// Aspas duplas tratam o termo como literal.
$limpas[] = '"' . $palavra . '"';
}
}
return implode(' ', $limpas);
}
/** Busca por prefixo: "prog" encontra "programação". */
public function autocompletar(string $prefixo, int $limite = 10): array
{
$prefixo = preg_replace('/[^\p{L}\p{N}]/u', '', $prefixo);
if (mb_strlen($prefixo) < 2) {
return [];
}
$stmt = $this->pdo->prepare(
'SELECT n.id, n.titulo FROM notas_fts JOIN notas n ON n.id = notas_fts.rowid
WHERE notas_fts MATCH :termo ORDER BY bm25(notas_fts) LIMIT :limite'
);
$stmt->bindValue(':termo', '"' . $prefixo . '"*'); // o * é o prefixo
$stmt->bindValue(':limite', $limite, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll();
}
}
$busca = new BuscaNotas($pdo);
print_r($busca->buscar('café')); // acha "cafe" também (remove_diacritics)
print_r($busca->buscar('café torra')); // as duas palavras
print_r($busca->autocompletar('prog'));Exercício 3
Implemente a mesma aplicação usando MySQL (ou PostgreSQL). Crie uma interface NotaRepositorioInterface que ambas as implementações satisfaçam. Escreva testes que rodem contra SQLite :memory: mas garantam que o código funciona.
Ver resposta
✓ Resposta: O teste de contrato compartilhado é o que descobre as diferenças silenciosas entre bancos — e há várias: SQLite não tem boolean (daí o normalizar()), rowCount() no MySQL conta linhas alteradas e não encontradas, e o AUTOINCREMENT se comporta diferente após DELETE.
<?php
declare(strict_types=1);
interface NotaRepositorioInterface
{
public function criar(string $titulo, string $conteudo, array $tags = []): int;
public function buscar(int $id): ?array;
/** @return array<int, array> */
public function listar(bool $incluirArquivadas = false): array;
public function editar(int $id, string $titulo, string $conteudo): bool;
public function arquivar(int $id, bool $arquivar = true): bool;
public function deletar(int $id): bool;
}
// ---------------------------------------- SQLite
final class NotaRepositorioSqlite implements NotaRepositorioInterface
{
public function __construct(private readonly PDO $pdo) {}
public function criar(string $titulo, string $conteudo, array $tags = []): int
{
$stmt = $this->pdo->prepare('INSERT INTO notas (titulo, conteudo) VALUES (:t, :c)');
$stmt->execute([':t' => $titulo, ':c' => $conteudo]);
return (int) $this->pdo->lastInsertId();
}
public function buscar(int $id): ?array
{
$stmt = $this->pdo->prepare('SELECT * FROM notas WHERE id = :id');
$stmt->execute([':id' => $id]);
$nota = $stmt->fetch();
// SQLite não tem boolean: normalizar aqui é o que permite ao teste
// ser idêntico para os dois bancos.
return $nota === false ? null : $this->normalizar($nota);
}
public function listar(bool $incluirArquivadas = false): array
{
$sql = 'SELECT * FROM notas' . ($incluirArquivadas ? '' : ' WHERE arquivada = 0')
. ' ORDER BY id DESC';
return array_map($this->normalizar(...), $this->pdo->query($sql)->fetchAll());
}
public function editar(int $id, string $titulo, string $conteudo): bool
{
$stmt = $this->pdo->prepare(
"UPDATE notas SET titulo = :t, conteudo = :c,
atualizada_em = datetime('now') WHERE id = :id"
);
$stmt->execute([':t' => $titulo, ':c' => $conteudo, ':id' => $id]);
return $stmt->rowCount() > 0;
}
public function arquivar(int $id, bool $arquivar = true): bool
{
$stmt = $this->pdo->prepare('UPDATE notas SET arquivada = :a WHERE id = :id');
$stmt->execute([':a' => $arquivar ? 1 : 0, ':id' => $id]);
return $stmt->rowCount() > 0;
}
public function deletar(int $id): bool
{
$stmt = $this->pdo->prepare('DELETE FROM notas WHERE id = :id');
$stmt->execute([':id' => $id]);
return $stmt->rowCount() > 0;
}
private function normalizar(array $nota): array
{
$nota['id'] = (int) $nota['id'];
$nota['arquivada'] = (bool) $nota['arquivada'];
return $nota;
}
}
// ---------------------------------------- MySQL
final class NotaRepositorioMysql implements NotaRepositorioInterface
{
public function __construct(private readonly PDO $pdo) {}
public function criar(string $titulo, string $conteudo, array $tags = []): int
{
$stmt = $this->pdo->prepare('INSERT INTO notas (titulo, conteudo) VALUES (:t, :c)');
$stmt->execute([':t' => $titulo, ':c' => $conteudo]);
return (int) $this->pdo->lastInsertId();
}
public function buscar(int $id): ?array
{
$stmt = $this->pdo->prepare('SELECT * FROM notas WHERE id = :id');
$stmt->execute([':id' => $id]);
$nota = $stmt->fetch();
return $nota === false ? null : $this->normalizar($nota);
}
public function listar(bool $incluirArquivadas = false): array
{
$sql = 'SELECT * FROM notas' . ($incluirArquivadas ? '' : ' WHERE arquivada = 0')
. ' ORDER BY id DESC';
return array_map($this->normalizar(...), $this->pdo->query($sql)->fetchAll());
}
public function editar(int $id, string $titulo, string $conteudo): bool
{
$stmt = $this->pdo->prepare(
'UPDATE notas SET titulo = :t, conteudo = :c, atualizada_em = NOW() WHERE id = :id'
);
$stmt->execute([':t' => $titulo, ':c' => $conteudo, ':id' => $id]);
// ⚠️ No MySQL, rowCount() conta linhas ALTERADAS. Salvar o mesmo
// conteúdo devolve 0 — por isso o teste não pode assumir que
// "editar com os mesmos valores" retorna true.
return $stmt->rowCount() > 0;
}
public function arquivar(int $id, bool $arquivar = true): bool
{
$stmt = $this->pdo->prepare('UPDATE notas SET arquivada = :a WHERE id = :id');
$stmt->execute([':a' => $arquivar ? 1 : 0, ':id' => $id]);
return $stmt->rowCount() > 0;
}
public function deletar(int $id): bool
{
$stmt = $this->pdo->prepare('DELETE FROM notas WHERE id = :id');
$stmt->execute([':id' => $id]);
return $stmt->rowCount() > 0;
}
private function normalizar(array $nota): array
{
$nota['id'] = (int) $nota['id'];
$nota['arquivada'] = (bool) $nota['arquivada'];
return $nota;
}
}
// ---------------------------------------- Tests/NotaRepositorioTest.php
declare(strict_types=1);
namespace Tests;
use PHPUnit\Framework\Attributes\Test;
use PHPUnit\Framework\TestCase;
/**
* Um caso de teste abstrato, duas subclasses concretas: o MESMO conjunto de
* asserções roda contra os dois bancos. É o teste de contrato — se o SQLite
* passa e o MySQL não, a diferença está exposta.
*/
abstract class NotaRepositorioTestCase extends TestCase
{
abstract protected function criarRepositorio(): NotaRepositorioInterface;
private NotaRepositorioInterface $repo;
protected function setUp(): void
{
$this->repo = $this->criarRepositorio();
}
#[Test]
public function criarDevolveIdPositivo(): void
{
self::assertGreaterThan(0, $this->repo->criar('Título', 'Conteúdo'));
}
#[Test]
public function buscarDevolveANotaCriada(): void
{
$id = $this->repo->criar('Comprar café', 'Torra média');
$nota = $this->repo->buscar($id);
self::assertNotNull($nota);
self::assertSame('Comprar café', $nota['titulo']);
self::assertFalse($nota['arquivada']);
}
#[Test]
public function buscarInexistenteDevolveNull(): void
{
self::assertNull($this->repo->buscar(999_999));
}
#[Test]
public function arquivadaSaiDaListagemPadrao(): void
{
$id = $this->repo->criar('Nota', 'x');
$this->repo->arquivar($id);
self::assertCount(0, $this->repo->listar());
self::assertCount(1, $this->repo->listar(incluirArquivadas: true));
}
#[Test]
public function editarAlteraOsCampos(): void
{
$id = $this->repo->criar('Antigo', 'a');
self::assertTrue($this->repo->editar($id, 'Novo', 'b'));
self::assertSame('Novo', $this->repo->buscar($id)['titulo']);
}
#[Test]
public function deletarRemoveDeVez(): void
{
$id = $this->repo->criar('Some', 'x');
self::assertTrue($this->repo->deletar($id));
self::assertNull($this->repo->buscar($id));
self::assertFalse($this->repo->deletar($id)); // já não existe
}
}
final class NotaRepositorioSqliteTest extends NotaRepositorioTestCase
{
protected function criarRepositorio(): NotaRepositorioInterface
{
// :memory: — rápido, isolado, sem arquivo para limpar.
$pdo = new \PDO('sqlite::memory:', null, null,
[\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
$pdo->exec('PRAGMA foreign_keys = ON');
$pdo->exec("CREATE TABLE notas (
id INTEGER PRIMARY KEY AUTOINCREMENT,
titulo TEXT NOT NULL, conteudo TEXT NOT NULL DEFAULT '',
arquivada INTEGER NOT NULL DEFAULT 0,
criada_em TEXT DEFAULT (datetime('now')),
atualizada_em TEXT DEFAULT (datetime('now')))");
return new NotaRepositorioSqlite($pdo);
}
}
final class NotaRepositorioMysqlTest extends NotaRepositorioTestCase
{
protected function criarRepositorio(): NotaRepositorioInterface
{
if (!getenv('MYSQL_TEST_DSN')) {
self::markTestSkipped('MYSQL_TEST_DSN não configurado.');
}
$pdo = new \PDO(getenv('MYSQL_TEST_DSN'), getenv('MYSQL_TEST_USER'),
getenv('MYSQL_TEST_PASS'),
[\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
$pdo->exec('DROP TABLE IF EXISTS notas');
$pdo->exec('CREATE TABLE notas (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
titulo VARCHAR(200) NOT NULL, conteudo TEXT NOT NULL,
arquivada TINYINT(1) NOT NULL DEFAULT 0,
criada_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
atualizada_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB');
return new NotaRepositorioMysql($pdo);
}
}Exercício 4
Desafio: implemente backup automático do SQLite: uma função backup(string $destinoPath): void que usa a API de backup do SQLite via $pdo->sqliteCreateFunction() ou via linha de comando .dump.
Ver resposta
✓ Resposta: Nunca faça backup de SQLite com copy() puro: em modo WAL as transações recentes vivem no arquivo -wal, e você copiaria um banco desatualizado — ou corrompido, se houver escrita no meio. VACUUM INTO resolve, e o integrity_check no fim garante que o backup abre.
<?php
declare(strict_types=1);
final class BackupSqlite
{
public function __construct(
private readonly PDO $pdo,
private readonly string $caminhoBanco,
) {}
/**
* Copiar o arquivo .db com copy() é a forma ERRADA: se houver escrita
* em andamento, você leva um banco corrompido — e em modo WAL o arquivo
* principal nem contém as transações recentes (elas estão no -wal).
*
* Este método usa VACUUM INTO, que produz um banco íntegro e compactado
* SEM travar a aplicação. Exige SQLite 3.27+ (2019).
*/
public function backup(string $destinoPath): void
{
$destino = $this->prepararDestino($destinoPath);
if (version_compare($this->versaoSqlite(), '3.27.0', '>=')) {
// VACUUM INTO não aceita placeholder: o caminho é validado à mão.
$this->pdo->exec("VACUUM INTO " . $this->pdo->quote($destino));
} else {
$this->backupPorCopiaSegura($destino);
}
$this->verificar($destino);
}
/** Caminho para SQLite antigo: checkpoint + cópia dentro de transação. */
private function backupPorCopiaSegura(string $destino): void
{
// TRUNCATE força tudo do -wal para o arquivo principal e zera o WAL.
$this->pdo->exec('PRAGMA wal_checkpoint(TRUNCATE)');
// A transação exclusiva impede escrita durante a cópia.
$this->pdo->exec('BEGIN IMMEDIATE');
try {
if (!copy($this->caminhoBanco, $destino)) {
throw new RuntimeException("Falha ao copiar para {$destino}.");
}
} finally {
$this->pdo->exec('COMMIT');
}
}
/** Backup em SQL puro — portátil, versionável, mas bem maior. */
public function backupSql(string $destinoPath): void
{
$destino = $this->prepararDestino($destinoPath);
$handle = fopen($destino, 'wb');
fwrite($handle, "PRAGMA foreign_keys=OFF;\nBEGIN TRANSACTION;\n");
$tabelas = $this->pdo->query(
"SELECT name, sql FROM sqlite_master
WHERE type='table' AND name NOT LIKE 'sqlite_%'"
)->fetchAll(PDO::FETCH_ASSOC);
foreach ($tabelas as $tabela) {
fwrite($handle, $tabela['sql'] . ";\n");
$linhas = $this->pdo->query("SELECT * FROM \"{$tabela['name']}\"");
foreach ($linhas as $linha) {
$valores = array_map(
fn($v): string => $v === null ? 'NULL' : $this->pdo->quote((string) $v),
$linha,
);
fwrite($handle, sprintf("INSERT INTO \"%s\" VALUES (%s);\n",
$tabela['name'], implode(',', $valores)));
}
}
// Índices e triggers vêm depois dos dados: inserir com índice ativo
// é bem mais lento.
foreach ($this->pdo->query(
"SELECT sql FROM sqlite_master WHERE type IN ('index','trigger') AND sql IS NOT NULL"
) as $objeto) {
fwrite($handle, $objeto['sql'] . ";\n");
}
fwrite($handle, "COMMIT;\nPRAGMA foreign_keys=ON;\n");
fclose($handle);
}
/** Rotação: mantém os N backups mais recentes. */
public function rotacionar(string $diretorio, int $manter = 7): int
{
$arquivos = glob(rtrim($diretorio, '/') . '/backup-*.db') ?: [];
usort($arquivos, static fn(string $a, string $b): int => filemtime($b) <=> filemtime($a));
$removidos = 0;
foreach (array_slice($arquivos, $manter) as $antigo) {
if (unlink($antigo)) {
$removidos++;
}
}
return $removidos;
}
/** Backup que não abre é papel de parede: sempre verifique. */
private function verificar(string $destino): void
{
if (!is_file($destino) || filesize($destino) === 0) {
throw new RuntimeException("Backup vazio ou inexistente: {$destino}");
}
$copia = new PDO('sqlite:' . $destino, null, null,
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$resultado = $copia->query('PRAGMA integrity_check')->fetchColumn();
if ($resultado !== 'ok') {
throw new RuntimeException("Backup corrompido: {$resultado}");
}
}
private function prepararDestino(string $caminho): string
{
$diretorio = dirname($caminho);
if (!is_dir($diretorio) && !mkdir($diretorio, 0700, true)) {
throw new RuntimeException("Não foi possível criar {$diretorio}.");
}
if (is_file($caminho)) {
// VACUUM INTO recusa sobrescrever arquivo existente.
unlink($caminho);
}
return $caminho;
}
private function versaoSqlite(): string
{
return (string) $this->pdo->query('SELECT sqlite_version()')->fetchColumn();
}
}
$backup = new BackupSqlite($pdo, __DIR__ . '/notas.db');
$arquivo = __DIR__ . '/backups/backup-' . date('Ymd-His') . '.db';
$backup->backup($arquivo);
$backup->rotacionar(__DIR__ . '/backups', manter: 7);
printf("Backup em %s (%.1f KB)%s", $arquivo, filesize($arquivo) / 1024, PHP_EOL);
// 0 3 * * * php /caminho/backup.phpExercício 5
O artigo menciona de passagem que o SQLite aceita texto numa coluna INTEGER. Transforme isso em experimento: grave 'três' e '42' na mesma coluna INTEGER e use typeof() para descobrir o que o banco guardou em cada caso. Depois recrie a tabela como STRICT e observe a diferença. Por fim, escreva a migração de uma tabela para a outra — e responda o que acontece com os dados que já estavam sujos.
Ver resposta
✓ Resposta: O tipo declarado numa tabela comum é uma sugestão, e o typeof() mostra as duas metades disso: 'três' fica guardado como text dentro da coluna INTEGER, enquanto '42' é convertido em silêncio para integer — ou seja, o banco às vezes converte e às vezes não, e você só descobre qual foi consultando linha a linha. Desde a versão 3.37, de 2021, a resposta do próprio SQLite para isso é a tabela STRICT, que recusa com cannot store TEXT value in INTEGER column. A parte que costuma surpreender é a migração: não existe ALTER TABLE que torne uma tabela STRICT, então o caminho é criar a nova, copiar com CAST e trocar os nomes — e o CAST('três' AS INTEGER) devolve 0, sem erro e sem aviso. Rode um SELECT por typeof() antes de migrar: o que estiver fora do tipo vira zero calado.
<?php
declare(strict_types=1);
$pdo = new PDO('sqlite::memory:', options: [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
// ── 1. Tabela comum: o tipo declarado é uma SUGESTÃO ───────────────────
$pdo->exec('CREATE TABLE medidas (id INTEGER PRIMARY KEY, qtd INTEGER, preco REAL)');
$inserir = $pdo->prepare('INSERT INTO medidas (qtd, preco) VALUES (?, ?)');
$inserir->execute(['três', 'caro']); // texto puro: fica texto
$inserir->execute(['42', '9.90']); // texto numérico: vira número
// typeof() revela o que o SQLite REALMENTE guardou, coluna por coluna
foreach ($pdo->query('SELECT qtd, typeof(qtd) AS tq, preco, typeof(preco) AS tp FROM medidas') as $l) {
printf(
" qtd=%-8s %-8s | preco=%-8s %s\n",
var_export($l['qtd'], true), "({$l['tq']})",
var_export($l['preco'], true), "({$l['tp']})"
);
}
// ── 2. A mesma tabela como STRICT (SQLite 3.37+, de 2021) ─────────────
$pdo->exec('CREATE TABLE medidas_strict (
id INTEGER PRIMARY KEY,
qtd INTEGER NOT NULL,
preco REAL NOT NULL
) STRICT');
try {
$pdo->prepare('INSERT INTO medidas_strict (qtd, preco) VALUES (?, ?)')
->execute(['três', 'caro']);
echo " STRICT aceitou texto — versão do SQLite anterior à 3.37?\n";
} catch (PDOException $e) {
echo " STRICT recusou: " . $e->getMessage() . "\n";
}
// ── 3. Migrar sem perder dados: o SQLite não converte tabela em STRICT ─
// com ALTER. O caminho é criar a nova, copiar com CAST e trocar os nomes.
$pdo->exec('INSERT INTO medidas_strict (qtd, preco)
SELECT CAST(qtd AS INTEGER), CAST(preco AS REAL) FROM medidas');
foreach ($pdo->query('SELECT qtd, typeof(qtd) AS t, preco FROM medidas_strict') as $l) {
printf(" migrado: qtd=%-3s (%s) preco=%s\n", $l['qtd'], $l['t'], $l['preco']);
}