SQLite altera o tipo de coluna sem perder dados

Por que o SQLite não pode alterar um tipo de coluna com a tabela ALTER

SQLite ALTER TABLE a implementação suporta um conjunto restrito de operações: RENAME TABLE, RENAME COLUMN, ADD COLUMNe DROP COLUMN (requer SQLite 3.35.0 ou posterior; verifique com SELECT sqlite_version();). Não há ALTER COLUMN ou MODIFY COLUMN variante. A tentativa de usar sintaxe emprestada de outros bancos de dados produz um erro de análise imediato.

Esta limitação decorre parcialmente do sistema de afinidade de tipo do SQLite. Em vez de impor tipos de coluna estritos no momento da gravação, o SQLite usa um modelo de afinidade flexível onde qualquer coluna pode armazenar qualquer tipo de valor. Uma coluna declarada como INTEGER pode conter texto, um blob ou um número real. O tipo declarado influencia a preferência de armazenamento, não a aplicação. Isso contrasta fortemente com PostgreSQL ou MySQL, onde o mecanismo rejeita ou força valores que não correspondem ao tipo de coluna declarado.

O único método suportado para alterar o tipo declarado de uma coluna é uma reconstrução completa da tabela SQLite, que envolve a criação de uma nova tabela, a cópia de dados e a troca de nomes.

A consequência prática: o único método suportado para alterar o tipo declarado de uma coluna é uma reconstrução completa da tabela SQLite, que envolve a criação de uma nova tabela, a cópia de dados e a troca de nomes.

ALTER TABLE products ALTER COLUMN price TYPE REAL;

Compreendendo o que uma reconstrução de tabela exclui

A reconstrução de uma tabela não é uma simples operação de renomeação. Eliminar a tabela original tem consequências em cascata. Você deve antecipar e criar um roteiro contra essas consequências antes de qualquer passo destrutivo.

Índices e gatilhos

Quando você elimina uma tabela, o SQLite remove automaticamente todos os índices e gatilhos associados a ela. Ele não preserva esses objetos nem os reassocia a uma tabela de substituição. Se a tabela original tivesse cinco índices e dois gatilhos, todos os sete objetos desapareceriam com DROP TABLE. Você deve recriá-los depois de renomear a nova tabela no lugar. Deixar de capturar essas definições de antemão significa reconstruí-las a partir da memória ou do código do aplicativo, um processo em que índices ou gatilhos individuais são facilmente perdidos.

Referências de chave estrangeira e riscos em cascata

As tabelas que fazem referência à tabela de destino por meio de chaves estrangeiras introduzem um modo de falha mais perigoso. Se uma tabela filha definir uma chave estrangeira com ON DELETE CASCADEeliminar a tabela pai enquanto a aplicação de chave estrangeira estiver ativa excluirá silenciosamente todas as linhas correspondentes na tabela filho. Isso pode limpar os dados de produção sem nenhuma mensagem de erro e nenhum caminho de recuperação além da restauração de um backup.

Audite os relacionamentos de chave estrangeira de entrada antes de iniciar qualquer reconstrução:

SELECT
    m.name        AS referencing_table,
    fkl.id        AS fk_id,
    fkl."from"    AS local_column,
    fkl."table"   AS referenced_table,
    fkl."to"      AS referenced_column,
    fkl.on_delete AS on_delete_action
FROM sqlite_master AS m
JOIN pragma_foreign_key_list(m.name) AS fkl
    ON fkl."table" = 'products'   
WHERE m.type = 'table'
ORDER BY m.name, fkl.id;

Execute isso em seu banco de dados. Qualquer on_delete valor de CASCADE representa um risco de perda de dados durante a reconstrução.

Verificações de segurança pré-migração

Exportar esquema e índices atuais

A captura da definição completa do esquema, incluindo todos os DDL de índice e gatilho, fornece uma referência de reversão e as instruções exatas necessárias para a restauração após a reconstrução.

SELECT type, name, sql
FROM sqlite_master
WHERE tbl_name = 'products'
  AND sql IS NOT NULL;

Esta consulta retorna linhas separadas para a definição da tabela, cada índice e cada gatilho. Copie e armazene o sql valores da coluna. Na CLI do SQLite, .schema products produz saída semelhante, mas não separa os tipos de objetos de maneira tão limpa para fins de script. (Observação: .schema é um comando de ponto CLI e não está disponível por meio de drivers programáticos.)

Verifique os tipos de dados atuais com typeof()

Como o sistema de afinidade de tipos do SQLite permite tipos mistos em uma única coluna, os tipos reais armazenados podem ser diferentes do tipo de coluna declarado. Executando um typeof() a agregação revela a verdadeira distribuição do tipo:

SELECT typeof(price), COUNT(*)
FROM products
GROUP BY typeof(price);

Uma coluna declarada como TEXT pode retornar linhas mostrando integer, reale text valoriza todos coexistindo. Esta linha de base informa quais valores podem não ser convertidos corretamente durante o CAST() etapa e fornece um ponto de comparação para verificação pós-migração.

Protetores de qualidade de dados pré-voo

Antes de iniciar a transação de migração, verifique se os dados sobreviverão à conversão de tipo sem corrupção silenciosa:

SELECT
    COUNT(*) AS null_count,
    CASE WHEN COUNT(*) = 0 THEN 'OK' ELSE 'ABORT: NULL values present' END AS status
FROM your_table
WHERE your_column IS NULL;


SELECT
    COUNT(*) AS bad_value_count,
    CASE WHEN COUNT(*) = 0 THEN 'OK' ELSE 'ABORT: non-numeric text will coerce to 0' END AS status
FROM your_table
WHERE typeof(your_column) = 'text'
  AND CAST(your_column AS REAL) = 0.0
  AND TRIM(your_column) NOT IN ('0', '0.0', '.0');


SELECT
    COUNT(*)                        AS row_count,
    SUM(CAST(your_column AS REAL))  AS column_sum,
    MIN(CAST(your_column AS REAL))  AS column_min,
    MAX(CAST(your_column AS REAL))  AS column_max
FROM your_table;

Se o Guard 1 retornar algum NULL e sua coluna de destino for NOT NULLcorrija os dados primeiro. Se o Guard 2 retornar alguma linha, esses valores se tornarão silenciosamente 0.0 – resolva-os antes de prosseguir.

Faça backup do banco de dados

Nunca execute uma migração destrutiva sem um backup. A CLI do SQLite fornece um comando integrado:

.backup main backup_products_migration.db

Observação: .backup é um comando de ponto CLI. Não é SQL válido e produzirá um erro de sintaxe se executado através de um driver de linguagem de programação (por exemplo, Python sqlite3Nó better-sqlite3). Se estiver usando um driver, use uma cópia do sistema de arquivos do .db arquivo antes de conectar ou use a API SQLite Online Backup.

Uma cópia em nível de arquivo funciona apenas se o banco de dados não estiver no modo WAL e nenhum gravador estiver ativo. Correr PRAGMA journal_mode; primeiro; se o resultado for walcopie o .db, -wale -shm arquivos juntos ou use o .backup Comando CLI, que lida com o modo WAL com segurança. A principal restrição é que o backup deve ser feito antes do início de qualquer gravação.

O procedimento de reconstrução segura da tabela, passo a passo

Etapa 1: desabilitar chaves estrangeiras

PRAGMA foreign_keys = ON;








PRAGMA foreign_keys = OFF;

Isso evita exclusões em cascata quando a tabela original é eliminada. Esta é uma restrição SQLite universal: PRAGMA foreign_keys não tem efeito se emitido dentro de uma transação, independentemente do driver ou interface utilizada. Deve ser emitido antes BEGIN TRANSACTION.

Etapa 2: iniciar uma transação

BEGIN TRANSACTION;

Envolver toda a reconstrução em uma transação garante a atomicidade. Se alguma etapa falhar, um ROLLBACK retorna o banco de dados ao seu estado pré-migração. Executar etapas individualmente fora de uma transação corre o risco de deixar o banco de dados em um estado semimigrado, onde a tabela antiga desapareceu, mas a nova tabela está incompleta.

Etapa 3: Crie a nova tabela com o tipo de coluna de destino

Escreva o completo CREATE TABLE instrução que espelha o esquema original, alterando apenas a declaração de tipo da coluna de destino. Usando um _new sufixo evita colisões de nomes. Se uma tentativa de migração anterior deixou um _new mesa no lugar, verifique-a antes de deixá-la cair:

SELECT CASE
  WHEN EXISTS (
    SELECT 1 FROM sqlite_master WHERE type='table' AND name='products_new'
  )
  THEN (
    SELECT CASE WHEN COUNT(*) > 0
      THEN 'ABORT: products_new has ' || COUNT(*) || ' rows. Inspect before dropping.'
      ELSE 'OK: products_new is empty, safe to drop.'
    END FROM products_new
  )
  ELSE 'OK: products_new does not exist.'
END AS preflight_new_table;


DROP TABLE IF EXISTS products_new;

CREATE TABLE products_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL,       
    category TEXT,
    created_at TEXT DEFAULT (datetime('now'))  
);

Você deve replicar cada restrição, valor padrão e NOT NULL declaração da tabela original exatamente, exceto pela coluna que está sendo alterada.

Etapa 4: copiar dados com CAST() explícito

INSERT INTO products_new (id, name, price, category, created_at)
SELECT id, name, CAST(price AS REAL), category, created_at
FROM products
ORDER BY id;

O explícito CAST() torna a conversão de tipo intencional e visível. Saber como CAST() se comporta antes de confiar nele: lançando um valor de texto como 'abc' para REAL produz 0.0não é um erro. Fundição NULL conservas NULL. Se a coluna de destino for NOT NULL e a fonte contém NULL valores, o INSERT falhará, que é exatamente o que a proteção NULL pré-voo e as verificações de qualidade dos dados existem para capturar. Valores de texto contendo representações numéricas válidas (como '19.99') converta corretamente. Valores que não podem ser convertidos de forma significativa são silenciosamente forçados a zero.

Transmitir um valor de texto como 'abc' para REAL produz 0.0não é um erro. Valores que não podem ser convertidos de forma significativa são silenciosamente forçados a zero.

Etapa 5: elimine a tabela antiga

DROP TABLE products;

Como as chaves estrangeiras foram desabilitadas na Etapa 1, isso não aciona exclusões em cascata nas tabelas filhas.

Etapa 6: renomear a nova tabela

ALTER TABLE products_new RENAME TO products;

Etapa 7: restaurar índices e gatilhos

Execute novamente o CREATE INDEX e CREATE TRIGGER declarações capturadas durante a fase de pré-migração:

CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_price ON products(price);





CREATE TRIGGER trg_products_updated
AFTER UPDATE ON products
WHEN OLD.created_at IS NEW.created_at  
  AND OLD.name IS NOT NEW.name         
  
BEGIN
    UPDATE products
    SET created_at = datetime('now')   
    WHERE id = NEW.id;
END;

Essas instruções devem fazer referência ao nome da tabela final (products), não o nome temporário.

Etapa 8: confirmar e reativar chaves estrangeiras

COMMIT;


PRAGMA foreign_keys = ON;

O PRAGMA foreign_keys = ON declaração deve vir depois do COMMITfora da transação.

Verificação pós-migração

Integridade PRAGMA_verificação

Isso verifica se há corrupção estrutural no banco de dados, incluindo integridade da árvore B e consistência do índice, mas não não verifique os relacionamentos de chave estrangeira. Isso requer PRAGMA foreign_key_checkque você deve executar separadamente.

PRAGMA Foreign_key_check

Isso verifica referências de chave estrangeira órfãs, linhas em tabelas filhas que apontam para linhas pai que não existem mais. Um conjunto de resultados vazio significa que todos os relacionamentos de chave estrangeira estão intactos.

Verifique os tipos de coluna com typeof() novamente

Executando novamente o mesmo typeof() a agregação da pré-migração confirma que todos os valores agora carregam o tipo esperado.

SELECT * FROM pragma_integrity_check()
WHERE integrity_check != 'ok';



SELECT * FROM pragma_foreign_key_check();



SELECT typeof(price), COUNT(*)
FROM products
GROUP BY typeof(price);



SELECT
    COUNT(*)         AS row_count,     
    SUM(price)       AS column_sum,    
    MIN(price)       AS column_min,    
    MAX(price)       AS column_max     
FROM products;

Se o typeof() os resultados mostram tipos inesperados, a contagem de linhas difere da tabela original ou os valores agregados não correspondem, a migração introduziu perda de dados ou erros de coerção. Reverta para o backup e investigue.

Modelo completo de script de migração

Este modelo combina cada etapa em um único bloco comentado. Substitua os comentários do espaço reservado por nomes de tabelas, definições de colunas e instruções de índice reais.

SELECT type, name, sql FROM sqlite_master
WHERE tbl_name = 'your_table' AND sql IS NOT NULL;

SELECT typeof(your_column), COUNT(*)
FROM your_table GROUP BY typeof(your_column);

SELECT COUNT(*) AS row_count FROM your_table;


SELECT
    COUNT(*) AS null_count,
    CASE WHEN COUNT(*) = 0 THEN 'OK' ELSE 'ABORT: NULL values present' END AS status
FROM your_table
WHERE your_column IS NULL;


SELECT
    COUNT(*) AS bad_value_count,
    CASE WHEN COUNT(*) = 0 THEN 'OK' ELSE 'ABORT: non-numeric text will coerce to 0' END AS status
FROM your_table
WHERE typeof(your_column) = 'text'
  AND CAST(your_column AS REAL) = 0.0
  AND TRIM(your_column) NOT IN ('0', '0.0', '.0');


SELECT
    COUNT(*)                        AS row_count,
    SUM(CAST(your_column AS REAL))  AS column_sum,
    MIN(CAST(your_column AS REAL))  AS column_min,
    MAX(CAST(your_column AS REAL))  AS column_max
FROM your_table;


SELECT
    m.name        AS referencing_table,
    fkl.id        AS fk_id,
    fkl."from"    AS local_column,
    fkl."table"   AS referenced_table,
    fkl."to"      AS referenced_column,
    fkl.on_delete AS on_delete_action
FROM sqlite_master AS m
JOIN pragma_foreign_key_list(m.name) AS fkl
    ON fkl."table" = 'your_table'   
WHERE m.type = 'table'
ORDER BY m.name, fkl.id;










PRAGMA foreign_keys = OFF;




BEGIN TRANSACTION;





SELECT CASE
  WHEN EXISTS (
    SELECT 1 FROM sqlite_master WHERE type='table' AND name='your_table_new'
  )
  THEN (
    SELECT CASE WHEN COUNT(*) > 0
      THEN 'ABORT: your_table_new has ' || COUNT(*) || ' rows. Inspect before dropping.'
      ELSE 'OK: your_table_new is empty, safe to drop.'
    END FROM your_table_new
  )
  ELSE 'OK: your_table_new does not exist.'
END AS preflight_new_table;


DROP TABLE IF EXISTS your_table_new;

CREATE TABLE your_table_new (
    id INTEGER PRIMARY KEY,
    your_column INTEGER NOT NULL,  
    other_column TEXT
);



INSERT INTO your_table_new (id, your_column, other_column)
SELECT id, CAST(your_column AS INTEGER), other_column
FROM your_table
ORDER BY id;


DROP TABLE your_table;


ALTER TABLE your_table_new RENAME TO your_table;






COMMIT;


PRAGMA foreign_keys = ON;




SELECT * FROM pragma_integrity_check()
WHERE integrity_check != 'ok';



SELECT * FROM pragma_foreign_key_check();



SELECT typeof(your_column), COUNT(*)
FROM your_table GROUP BY typeof(your_column);


SELECT COUNT(*) AS row_count FROM your_table;


SELECT
    COUNT(*)           AS row_count,     
    SUM(your_column)   AS column_sum,    
    MIN(your_column)   AS column_min,    
    MAX(your_column)   AS column_max     
FROM your_table;

Salve este modelo e adapte-o por migração. A igualdade na contagem de linhas é uma verificação necessária, mas não suficiente. Compare também o typeof() distribuição e os agregados da coluna (SUM(), MIN(), MAX()) contra valores pré-migração para detectar coerção silenciosa.

Armadilhas comuns e como evitá-las

Truncamento silencioso de dados durante CAST

Transmitir um valor de texto como 'hello' para REAL rendimentos 0.0não é um erro. SQLite não gera avisos para conversões sem sentido. Execute o protetor de qualidade de dados pré-voo para capturar valores de texto não numéricos que seriam forçados a zero. Se a linha de base typeof() check mostra tipos mistos, inspecione as linhas não conformes individualmente antes de executar a migração.

Esquecendo de restaurar índices

As consultas continuam funcionando sem índices, o que torna esse modo de falha invisível durante os testes. Sem o índice, as consultas que o utilizavam anteriormente voltam para varreduras completas da tabela; em tabelas grandes, isso pode ser muito mais lento. Sempre verifique os índices restaurados com SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="your_table"; após a migração.

Executando com chaves estrangeiras habilitadas

Descartando a tabela original com PRAGMA foreign_keys = ON gatilhos ON DELETE CASCADE em cada tabela filho com essa cláusula. Isso exclui silenciosamente as linhas secundárias sem nenhum caminho de desfazer.

Descartando a tabela original com PRAGMA foreign_keys = ON gatilhos ON DELETE CASCADE em cada tabela filho com essa cláusula. Isso exclui silenciosamente as linhas secundárias sem nenhum caminho de desfazer.

Falhas parciais fora de uma transação

Executar etapas de reconstrução individualmente, sem BEGIN TRANSACTION e COMMITsignifica uma falha ou erro após DROP TABLE mas antes ALTER TABLE ... RENAME deixa o banco de dados sem a tabela antiga ou a nova. Sempre envolva a sequência destrutiva em uma transação.

Perguntas frequentes

Posso alterar o tipo de dados de uma coluna no SQLite?

Não com ALTER TABLE. SQLite não suporta ALTER COLUMN ou MODIFY COLUMN. A única abordagem suportada é uma reconstrução completa da tabela: crie uma nova tabela com o tipo desejado, copie os dados usando CAST()elimine a tabela antiga e renomeie a nova. O procedimento passo a passo acima cobre esse processo com todas as verificações de segurança necessárias.

O SQLite impõe tipos de coluna?

SQLite usa um sistema de afinidade de tipo onde o tipo de coluna declarado é uma preferência, não uma restrição. Uma coluna declarada como INTEGER pode armazenar texto, blobs ou números reais. No entanto, SQLite 3.37 (lançado em 27/11/2021) introduziu STRICT tabelas, que impõem tipos declarados no momento da gravação. Adicionando STRICT para CREATE TABLE permite a imposição, mas apenas para os seis nomes de tipos permitidos: INT, INTEGER, REAL, TEXT, BLOBe ANY. Outros nomes de tipo (por exemplo, VARCHAR, DECIMAL) causar um CREATE TABLE erro no modo estrito.

Isso funcionará com SQLite em produção (Django, Rails, Electron)?

O procedimento SQL funciona de forma idêntica, independentemente do aplicativo host. As ferramentas de migração ORM fornecem wrappers para este padrão exato: Django’s RunSQL operação, Alambique op.execute()e Trilhos’ execute dentro de um arquivo de migração permitem a incorporação de SQL bruto que executa a reconstrução. Verifique se o seu ORM agrupa toda a reconstrução em uma única transação; alguns executores de migração confirmam automaticamente entre instruções. Teste uma cópia do banco de dados de produção antes de executar a migração, pois digite o comportamento de coerção durante CAST() depende inteiramente dos dados reais presentes na tabela.