SQLite Change Column Type Without Losing Data

How to Change a Column Type in SQLite Without Losing Data

Export the current schema, indexes, and triggers using sqlite_master.
Audit inbound foreign keys for ON DELETE CASCADE risks.
Run pre-flight data quality guards (typeof(), NULL checks, aggregate baselines).
Disable foreign keys with PRAGMA foreign_keys = OFF and begin a transaction.
Create a new table with the target column type, replicating all constraints.
Copy data into the new table using INSERT ... SELECT with explicit CAST().
Drop the old table, rename the new one, and restore all indexes and triggers.
Verify with integrity_check, foreign_key_check, typeof(), and aggregate comparison.

Changing a column type in SQLite without losing data requires a full table rebuild, a procedure that carries risk of silent data destruction if executed carelessly. Unlike MySQL or PostgreSQL, SQLite provides no ALTER COLUMN command. The only path forward involves creating a new table with the desired schema, migrating data with explicit type conversion, and carefully restoring dependent objects like indexes, triggers, and foreign key relationships. This guide provides a complete, copy-paste SQLite migration script template with verification gates at every stage. It adds pre-flight data guards, foreign-key cascade auditing, and post-migration aggregate verification on top of the four-step rebuild that most references describe.

Why SQLite Cannot Change a Column Type with ALTER Table

SQLite's ALTER TABLE implementation supports a narrow set of operations: RENAME TABLE, RENAME COLUMN, ADD COLUMN, and DROP COLUMN (requires SQLite 3.35.0 or later; verify with SELECT sqlite_version();). There is no ALTER COLUMN or MODIFY COLUMN variant. Attempting to use syntax borrowed from other databases produces an immediate parse error.

This limitation stems partly from SQLite's type affinity system. Rather than enforcing strict column types at write time, SQLite uses a flexible affinity model where any column can store any type of value. A column declared as INTEGER can hold text, a blob, or a real number. The declared type influences storage preference, not enforcement. This stands in sharp contrast to PostgreSQL or MySQL, where the engine rejects or coerces values that do not match the declared column type.

The only supported method to change a column's declared type is a full SQLite table rebuild, which involves creating a new table, copying data, and swapping names.

The practical consequence: the only supported method to change a column's declared type is a full SQLite table rebuild, which involves creating a new table, copying data, and swapping names.

-- This fails in SQLite:
ALTER TABLE products ALTER COLUMN price TYPE REAL;
-- Error: near "ALTER": syntax error

-- In PostgreSQL, this would work:
-- ALTER TABLE products ALTER COLUMN price TYPE REAL USING price::REAL;

Understanding What a Table Rebuild Deletes

A table rebuild is not a simple rename operation. Dropping the original table has cascading consequences. You must anticipate and script against these consequences before any destructive step.

Indexes and Triggers

When you drop a table, SQLite automatically removes all indexes and triggers associated with it. It does not preserve these objects or reassociate them with a replacement table. If the original table had five indexes and two triggers, all seven objects vanish with DROP TABLE. You must re-create them after renaming the new table into place. Failing to capture these definitions beforehand means reconstructing them from memory or application code, a process where individual indexes or triggers are easily missed.

Foreign Key References and Cascade Risks

Tables that reference the target table through foreign keys introduce a more dangerous failure mode. If a child table defines a foreign key with ON DELETE CASCADE, dropping the parent table while foreign key enforcement is active will silently delete every matching row in the child table. This can wipe production data with no error message and no recovery path short of restoring a backup.

Audit inbound foreign key relationships before starting any rebuild:

-- Discover which tables define foreign keys pointing to 'products'
-- using the pragma_foreign_key_list table-valued function (requires SQLite >= 3.16.0)
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'   -- CHANGE: your target table name
WHERE m.type = 'table'
ORDER BY m.name, fkl.id;

-- Rows where on_delete_action = 'CASCADE' are data-loss risks.
-- Action required: verify each before proceeding.

Run this against your database. Any on_delete value of CASCADE represents a data loss risk during the rebuild.

Pre-Migration Safety Checks

Export Current Schema and Indexes

Capturing the full schema definition, including all index and trigger DDL, provides both a rollback reference and the exact statements needed for restoration after the rebuild.

-- Extract full table DDL, indexes, and triggers
SELECT type, name, sql
FROM sqlite_master
WHERE tbl_name = 'products'
  AND sql IS NOT NULL;

This query returns separate rows for the table definition, each index, and each trigger. Copy and store the sql column values. In the SQLite CLI, .schema products produces similar output but does not separate object types as cleanly for scripting purposes. (Note: .schema is a CLI dot-command and is not available via programmatic drivers.)

Verify Current Data Types with typeof()

Because SQLite's type affinity system permits mixed types within a single column, the actual stored types may differ from the declared column type. Running a typeof() aggregation reveals the true type distribution:

-- Baseline type distribution before migration
SELECT typeof(price), COUNT(*)
FROM products
GROUP BY typeof(price);

A column declared as TEXT might return rows showing integer, real, and text values all coexisting. This baseline tells you which values may not convert cleanly during the CAST() step, and it gives you a comparison point for post-migration verification.

Pre-flight Data Quality Guards

Before starting the migration transaction, verify that the data will survive type conversion without silent corruption:

-- Guard 1: NULL check (required if target column is NOT NULL)
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;

-- Guard 2: Non-convertible value check (text that casts to 0.0 but is not '0')
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');

-- Guard 3: Pre-migration aggregate (save this value for post-migration comparison)
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;
-- Record these values. All four must match post-migration equivalents.

If Guard 1 returns any NULLs and your target column is NOT NULL, fix the data first. If Guard 2 returns any rows, those values will silently become 0.0 -- resolve them before proceeding.

Back Up the Database

Never run a destructive migration without a backup. The SQLite CLI provides a built-in command:

.backup main backup_products_migration.db

Note: .backup is a CLI dot-command. It is not valid SQL and will produce a syntax error if executed via a programming language driver (e.g., Python sqlite3, Node better-sqlite3). If using a driver, use a file-system copy of the .db file before connecting, or use the SQLite Online Backup API.

A file-level copy works only if the database is not in WAL mode and no writer is active. Run PRAGMA journal_mode; first; if the result is wal, copy the .db, -wal, and -shm files together, or use the .backup CLI command, which handles WAL mode safely. The key constraint is that the backup must be taken before any writes begin.

The Safe Table Rebuild Procedure, Step by Step

Step 1: Disable Foreign Keys

-- Establish known-good state first
PRAGMA foreign_keys = ON;

-- Run all pre-flight checks (schema export, typeof(), data quality guards) here
-- ...

-- Disable FK enforcement immediately before the transaction.
-- If anything between here and COMMIT errors, run:
--   PRAGMA foreign_keys = ON;
-- before any further queries in this session.
PRAGMA foreign_keys = OFF;

This prevents cascade deletions when the original table is dropped. This is a universal SQLite constraint: PRAGMA foreign_keys has no effect if issued inside a transaction, regardless of the driver or interface used. It must be issued before BEGIN TRANSACTION.

Step 2: Begin a Transaction

BEGIN TRANSACTION;

Wrapping the entire rebuild in a transaction ensures atomicity. If any step fails, a ROLLBACK returns the database to its pre-migration state. Running steps individually outside a transaction risks leaving the database in a half-migrated state where the old table is gone but the new table is incomplete.

Step 3: Create the New Table with the Target Column Type

Write the full CREATE TABLE statement mirroring the original schema, changing only the target column's type declaration. Using a _new suffix avoids name collisions. If a prior migration attempt left a _new table in place, check it before dropping:

-- Check for leftover data before destroying prior attempt
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;

-- Only proceed with DROP after confirming output is 'OK'
DROP TABLE IF EXISTS products_new;

CREATE TABLE products_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL,       -- Changed from TEXT to REAL
    category TEXT,
    created_at TEXT DEFAULT (datetime('now'))  -- Note: datetime('now') returns UTC
);

You must replicate every constraint, default value, and NOT NULL declaration from the original table exactly, except for the column being changed.

Step 4: Copy Data with Explicit CAST()

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;

The explicit CAST() makes the type conversion intentional and visible. Know how CAST() behaves before relying on it: casting a text value like 'abc' to REAL produces 0.0, not an error. Casting NULL preserves NULL. If the target column is NOT NULL and the source contains NULL values, the INSERT will fail, which is exactly what the pre-flight NULL guard and data quality checks exist to catch. Text values containing valid numeric representations (like '19.99') convert correctly. Values that cannot be meaningfully converted silently coerce to zero.

Casting a text value like 'abc' to REAL produces 0.0, not an error. Values that cannot be meaningfully converted silently coerce to zero.

Step 5: Drop the Old Table

DROP TABLE products;

Because foreign keys were disabled in Step 1, this does not trigger cascade deletes on child tables.

Step 6: Rename the New Table

ALTER TABLE products_new RENAME TO products;

Step 7: Restore Indexes and Triggers

Re-run the CREATE INDEX and CREATE TRIGGER statements captured during the pre-migration phase:

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

-- This is an illustrative example. Test all trigger logic against your actual
-- schema. The WHEN clause prevents recursive trigger firing: it only fires
-- when created_at was NOT changed by the update AND at least one data column
-- actually changed.
CREATE TRIGGER trg_products_updated
AFTER UPDATE ON products
WHEN OLD.created_at IS NEW.created_at  -- Guard: timestamp not yet updated
  AND OLD.name IS NOT NEW.name         -- At least one data column changed
  -- Add additional column guards here for every non-timestamp column
BEGIN
    UPDATE products
    SET created_at = datetime('now')   -- CHANGE: use 'localtime' if needed
    WHERE id = NEW.id;
END;

These statements must reference the final table name (products), not the temporary name.

Step 8: Commit and Re-enable Foreign Keys

COMMIT;

-- Always re-enable, whether migration succeeded or was rolled back
PRAGMA foreign_keys = ON;

The PRAGMA foreign_keys = ON statement must come after the COMMIT, outside the transaction.

Post-Migration Verification

PRAGMA integrity_check

This checks the database for structural corruption including B-tree integrity and index consistency, but does not check foreign key relationships. That requires PRAGMA foreign_key_check, which you must run separately.

PRAGMA foreign_key_check

This scans for orphaned foreign key references, rows in child tables that point to parent rows that no longer exist. An empty result set means all foreign key relationships are intact.

Verify Column Types with typeof() Again

Re-running the same typeof() aggregation from pre-migration confirms that all values now carry the expected type.

-- Structural integrity: zero rows = no corruption
SELECT * FROM pragma_integrity_check()
WHERE integrity_check != 'ok';
-- Expected: 0 rows returned

-- FK consistency: zero rows = no orphans
SELECT * FROM pragma_foreign_key_check();
-- Expected: 0 rows returned

-- Confirm type conversion succeeded
SELECT typeof(price), COUNT(*)
FROM products
GROUP BY typeof(price);
-- Expected: all rows show 'real'

-- Aggregate integrity: compare to pre-migration values from Guard 3
SELECT
    COUNT(*)         AS row_count,     -- must match pre-migration
    SUM(price)       AS column_sum,    -- must match pre-migration
    MIN(price)       AS column_min,    -- must match pre-migration
    MAX(price)       AS column_max     -- must match pre-migration
FROM products;

If the typeof() results show unexpected types, the row count differs from the original table, or the aggregate values do not match, the migration introduced data loss or coercion errors. Roll back to the backup and investigate.

Complete Migration Script Template

This template combines every step into a single, commented block. Replace the placeholder comments with actual table names, column definitions, and index statements.

-- ============================================================
-- SQLite Column Type Migration Script Template
-- ============================================================
-- !! BEFORE RUNNING ANYTHING: replace every instance of your_table,
-- !! your_column, and your_table_new with your actual names.
-- !! Running this template unmodified will produce errors.
--
-- !! .backup requires the sqlite3 CLI. Use a file-system copy
-- !! or the SQLite Online Backup API if using a programming language driver.
--
-- !! On any error before COMMIT: run ROLLBACK; then PRAGMA foreign_keys = ON;
-- !! Then restore from backup if the transaction boundary was breached.
-- ============================================================

-- STEP 0: PRE-FLIGHT (run manually, review output before proceeding)
-- CHANGE: replace 'your_table' and 'your_column' throughout

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;

-- Guard: NULL check (required if target column is NOT NULL)
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;

-- Guard: Non-convertible value check
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');

-- Record pre-migration aggregates for post-migration comparison:
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;

-- FK discovery (requires SQLite >= 3.16.0):
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'   -- CHANGE: your target table name
WHERE m.type = 'table'
ORDER BY m.name, fkl.id;

-- CLI ONLY — the following line is a dot-command and will error in drivers:
-- .backup main pre_migration_backup.db
-- If using a driver, copy the .db file (and -wal/-shm files if in WAL mode)
-- via your file system before proceeding.

-- !! STOP: Review all pre-flight output above.
-- !! Do NOT proceed if any guard returned 'ABORT'.

-- STEP 1: Disable foreign keys (must be outside transaction)
PRAGMA foreign_keys = OFF;

-- On error at any point before COMMIT: ROLLBACK; PRAGMA foreign_keys = ON;

-- STEP 2: Begin atomic transaction
BEGIN TRANSACTION;

-- STEP 3: Create new table with target column type
-- CHANGE: paste your full CREATE TABLE with the modified column

-- Check for leftover _new table with data before dropping:
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;
-- !! Only proceed if result is 'OK'

DROP TABLE IF EXISTS your_table_new;

CREATE TABLE your_table_new (
    id INTEGER PRIMARY KEY,
    your_column INTEGER NOT NULL,  -- CHANGE: target_type here
    other_column TEXT
);

-- STEP 4: Copy data with explicit CAST
-- CHANGE: list all columns, wrap changed column in CAST()
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;

-- STEP 5: Drop original table
DROP TABLE your_table;

-- STEP 6: Rename new table to original name
ALTER TABLE your_table_new RENAME TO your_table;

-- STEP 7: Restore indexes and triggers
-- CHANGE: paste captured CREATE INDEX / CREATE TRIGGER statements
-- CREATE INDEX idx_example ON your_table(your_column);

-- STEP 8: Commit transaction
COMMIT;

-- STEP 9: Re-enable foreign keys (must be outside transaction)
PRAGMA foreign_keys = ON;

-- STEP 10: Post-migration verification

-- Structural integrity: zero rows = no corruption
SELECT * FROM pragma_integrity_check()
WHERE integrity_check != 'ok';
-- Expected: 0 rows returned

-- FK consistency: zero rows = no orphans
SELECT * FROM pragma_foreign_key_check();
-- Expected: 0 rows returned

-- Confirm type conversion succeeded
SELECT typeof(your_column), COUNT(*)
FROM your_table GROUP BY typeof(your_column);
-- Expected: exactly one row with the target type

SELECT COUNT(*) AS row_count FROM your_table;

-- Compare these aggregates to your pre-migration values to detect silent coercion:
SELECT
    COUNT(*)           AS row_count,     -- must match pre-migration
    SUM(your_column)   AS column_sum,    -- must match pre-migration
    MIN(your_column)   AS column_min,    -- must match pre-migration
    MAX(your_column)   AS column_max     -- must match pre-migration
FROM your_table;

Save this template and adapt it per migration. Row count equality is a necessary but not sufficient verification. Also compare the typeof() distribution and the column aggregates (SUM(), MIN(), MAX()) against pre-migration values to detect silent coercion.

Common Pitfalls and How to Avoid Them

Silent Data Truncation During CAST

Casting a text value like 'hello' to REAL yields 0.0, not an error. SQLite does not raise warnings for nonsensical conversions. Run the pre-flight data quality guard to catch non-numeric text values that would coerce to zero. If the baseline typeof() check shows mixed types, inspect the non-conforming rows individually before running the migration.

Forgetting to Restore Indexes

Queries keep working without indexes, which makes this failure mode invisible during testing. Without the index, queries that previously used it fall back to full table scans; on large tables, this can be orders of magnitude slower. Always verify restored indexes with SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='your_table'; after migration.

Running with Foreign Keys Enabled

Dropping the original table with PRAGMA foreign_keys = ON triggers ON DELETE CASCADE on every child table with that clause. This silently deletes child rows with no undo path.

Dropping the original table with PRAGMA foreign_keys = ON triggers ON DELETE CASCADE on every child table with that clause. This silently deletes child rows with no undo path.

Partial Failures Outside a Transaction

Running rebuild steps individually, without BEGIN TRANSACTION and COMMIT, means a crash or error after DROP TABLE but before ALTER TABLE ... RENAME leaves the database without either the old or new table. Always wrap the destructive sequence in a transaction.

Frequently Asked Questions

Can I Change the Datatype of a Column in SQLite?

Not with ALTER TABLE. SQLite does not support ALTER COLUMN or MODIFY COLUMN. The only supported approach is a full table rebuild: create a new table with the desired type, copy data using CAST(), drop the old table, and rename the new one. The step-by-step procedure above covers this process with all necessary safety checks.

Does SQLite Enforce Column Types?

SQLite uses a type affinity system where the declared column type is a preference, not a constraint. A column declared as INTEGER can store text, blobs, or real numbers. However, SQLite 3.37 (released 2021-11-27) introduced STRICT tables, which enforce declared types at write time. Adding STRICT to CREATE TABLE enables enforcement, but only for the six permitted type names: INT, INTEGER, REAL, TEXT, BLOB, and ANY. Other type names (e.g., VARCHAR, DECIMAL) cause a CREATE TABLE error in strict mode.

Will This Work with SQLite in Production (Django, Rails, Electron)?

The SQL procedure works identically regardless of the host application. ORM migration tools provide wrappers for this exact pattern: Django's RunSQL operation, Alembic's op.execute(), and Rails' execute method within a migration file all allow embedding raw SQL that performs the rebuild. Verify that your ORM wraps the entire rebuild in a single transaction; some migration runners auto-commit between statements. Test against a copy of the production database before running the migration, as type coercion behavior during CAST() depends entirely on the actual data present in the table.

SQLite Change Column Type Without Losing Data: The Safe Rebuild Guide

The AI briefing for Developers

How to Change a Column Type in SQLite Without Losing Data

Table of Contents

Why SQLite Cannot Change a Column Type with ALTER Table

Understanding What a Table Rebuild Deletes

Indexes and Triggers

Foreign Key References and Cascade Risks

Pre-Migration Safety Checks

Export Current Schema and Indexes

Verify Current Data Types with typeof()

Pre-flight Data Quality Guards

Back Up the Database

The Safe Table Rebuild Procedure, Step by Step

Step 1: Disable Foreign Keys

Step 2: Begin a Transaction

Step 3: Create the New Table with the Target Column Type

Step 4: Copy Data with Explicit CAST()

Step 5: Drop the Old Table

Step 6: Rename the New Table

Step 7: Restore Indexes and Triggers

Step 8: Commit and Re-enable Foreign Keys

Post-Migration Verification

PRAGMA integrity_check

PRAGMA foreign_key_check

Verify Column Types with typeof() Again

Complete Migration Script Template

Common Pitfalls and How to Avoid Them

Silent Data Truncation During CAST

Forgetting to Restore Indexes

Running with Foreign Keys Enabled

Partial Failures Outside a Transaction

Frequently Asked Questions

Can I Change the Datatype of a Column in SQLite?

Does SQLite Enforce Column Types?

Will This Work with SQLite in Production (Django, Rails, Electron)?