How to rename or delete a column in SQLite?

I recently worked on a project involving SQLite, and while creating database migrations, I realized that SQLite does not support the complete ALTER TABLE syntax completely.

Out of the box, you are able to rename a table, or add a column to an existing table, but that's it.

Keeping that in mind, to delete or rename a table in SQLite, we need to:

1. Create a copy of the table
2. Copy all the data to the new table
3. Drop the old table
4. Rename the new table with the name of the old table

Here's how:

sqlite3

sqlite> .open database.sqlite3
sqlite> .schema
CREATE TABLE IF NOT EXISTS "cards" (`_id` INTEGER PRIMARY KEY, `deck` INTEGER, `author` INTEGER, `front` TEXT NOT NULL, `back` TEXT, `createdAt` DATETIME, `updatedAt` DATETIME, `deleted` TINYINT(1) DEFAULT 0);

Running the commands above will give you the schema for the database and you can select the table you want to alter.

To see that we have the correct data, we run a few commands to ensure we get a nice output, and then we SELECT * FROM cards; to see all the data in a nice tabular format.

.mode column
.headers on
SELECT * FROM cards;

_id         front                     back
----------  ------------------------  ----------------------------------
1           hey there! `hello world`  print("hello world")
2           What are the advantages   * Immutability improves performanc

Then we copy the output from the .schema command, change the table name, and paste it into the SQLite console. If we want to rename the author column, or drop the front column, we can modify the CREATE TABLE command accordingly and then run the command.

CREATE TABLE IF NOT EXISTS
"cards_new"
(`_id` INTEGER PRIMARY KEY,
`deck` INTEGER,
`author_new` INTEGER,
`back` TEXT,
`createdAt` DATETIME,
`updatedAt` DATETIME,
`deleted` TINYINT(1) DEFAULT 0);

Next, we copy all the data from the old table.

INSERT INTO "cards_new" SELECT _id, deck, author AS author_new, back, createdAt, updatedAt, deleted FROM cards;

And now, finally, to rename the table:

DROP TABLE IF EXISTS cards;
ALTER TABLE cards_new RENAME TO cards;

And there you have it, that's how you rename or delete a column from an SQLite table!