SQL ALTER TABLE Generator

SQL ALTER TABLE: evolving schemas without losing data

ALTER TABLE is the DDL statement that modifies an existing table — adding, removing, renaming, retyping, or reconstraining columns — without dropping and recreating it. It is the workhorse of every database migration in every long-lived application: schemas evolve, requirements change, and ALTER TABLE is how you bring an old table forward without losing data. Used carelessly on a busy production database, however, the same statement can lock the table for minutes and take an entire service offline. This page covers the operations, dialect differences, locking behaviour, and online-migration patterns every backend engineer should know.

Core operations

• ADD COLUMN — appends a new column at the end of the row.
• DROP COLUMN — removes a column and all its data (irreversible without a backup).
• RENAME COLUMN / RENAME TO — renames a column or the whole table.
• ALTER COLUMN / MODIFY COLUMN — changes the type, nullability, or default of a column.
• ADD CONSTRAINT / DROP CONSTRAINT — manages PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK.
• ADD INDEX / DROP INDEX — usually written as separate CREATE INDEX / DROP INDEX statements.

Syntax by dialect

-- PostgreSQL
ALTER TABLE users ADD COLUMN age INTEGER;
ALTER TABLE users ALTER COLUMN age TYPE BIGINT USING age::BIGINT;
ALTER TABLE users RENAME COLUMN name TO full_name;
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- MySQL / MariaDB
ALTER TABLE users ADD age INTEGER, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE users MODIFY COLUMN age BIGINT;
ALTER TABLE users CHANGE COLUMN name full_name VARCHAR(100);

-- SQL Server
ALTER TABLE users ADD age INT NULL;
ALTER TABLE users ALTER COLUMN age BIGINT NOT NULL;
EXEC sp_rename 'users.name', 'full_name', 'COLUMN';

-- SQLite (very limited)
ALTER TABLE users ADD COLUMN age INTEGER;
ALTER TABLE users RENAME COLUMN name TO full_name;  -- since 3.25
-- Anything else: CREATE new, INSERT SELECT, DROP old, RENAME.

Locks, downtime, and online schema changes

ALTER TABLE can hold an exclusive lock that blocks reads and writes for the duration of the operation. On a 500 GB table, that may be hours. The safe playbook differs per engine:

• PostgreSQL: most ADD COLUMN with no default is instant since 11 (metadata-only). Changing types still rewrites the table. Always create indexes with CREATE INDEX CONCURRENTLY. For heavy migrations use pg_repack or pg-osc.
• MySQL / MariaDB: the ALGORITHM=INPLACE, LOCK=NONE hint enables online DDL when supported. For unsupported operations use pt-online-schema-change (Percona Toolkit) or gh-ost (GitHub) — both build a shadow table and switch atomically.
• SQL Server: Enterprise edition supports ONLINE = ON for many operations; Standard does not.
• SQLite: there is no online DDL — the database is locked for the whole operation. Mitigated by SQLite's typical use case (single-writer, small datasets).

Backward-compatible migrations: expand and contract

In a zero-downtime deploy you must never break the old app version while the new one is rolling out. The classic recipe is expand-contract:

1. Expand: add the new column as nullable, deploy code that writes to both old and new columns.
2. Backfill: copy data from old to new in batches, with throttling.
3. Switch reads: deploy code that reads only from the new column.
4. Contract: tighten the constraint (NOT NULL) and drop the old column.

Each step is independently safe and rollback-friendly. Renames are particularly painful because no engine can rename a column without coordinating clients; the expand-contract dance lets you ship rename-equivalent changes without downtime.

Migration frameworks

Production teams never execute ALTER TABLE ad-hoc. A migration framework versions every change in a file, applies it deterministically across environments, and records which versions ran. Popular options: Flyway and Liquibase (JVM), Alembic (Python/SQLAlchemy), Knex.js, Prisma Migrate, and TypeORM (Node.js), Rails migrations (Ruby), golang-migrate (Go), and EF Core Migrations (.NET). Every framework supports an up migration and a matching down; a good habit is to write the down step at the same time and run it locally before merging.

FAQ

How long does ALTER TABLE take? Anywhere from milliseconds to hours. Metadata-only changes (Postgres 11+ ADD COLUMN) are instant; full table rewrites scale with row count.

Is it reversible? The statement itself is not — there is no UNDO ALTER. Reversibility comes from your migration framework's down step, which spells out the inverse change. DROP COLUMN is permanently destructive without a backup.

Will it break my running app? It might. Treat every schema change as a contract change: deploy compatible app code first (expand), then run the migration, then deploy code that depends on the new shape, then contract.

Can I batch multiple operations? Yes — most dialects accept ALTER TABLE t ADD a INT, DROP b, RENAME c TO d. Batching is faster because the table is locked only once, but errors roll back the entire batch.

Why does adding a NOT NULL column lock? The engine must check every row. Add the column as nullable first, backfill, then add NOT NULL in a separate migration (Postgres 12+ can add NOT NULL instantly if a non-volatile default is present).