Updating Schema in SQLite3
By R. S. Doiel, 2020-04-16
SQLite3 is a handy little database as single file tool. You can interact with the file through largely standard SQL commands and embed it easily into applications via the C libraries that the project supports. It is also available from various popular scripting languages like Python, PHP, and Lua. One of the things I occasionally need to do and always seems to forget it how to is modify a table schema where I need to remove a column1. So here are some of the basics do I can quickly find them later and avoid reading various articles tutorials because the search engines doesn’t return the page in the SQLite documentation.
In the next sections I’ll be modeling a simple person object with a id, uname, display_name, role and updated fields.
Creating a person table
CREATE TABLE IF NOT EXISTS "person" "id" INTEGER NOT NULL PRIMARY KEY, ("uname" VARCHAR(255) NOT NULL, "role" VARCHAR(255) NOT NULL, "display_name" VARCHAR(255) NOT NULL, "updated" INTEGER NOT NULL);
Adding a column
We will create a junk column which we will remove later.
schema person .ALTER TABLE person ADD COLUMN junk VARCHAR(255) NOT NULL; schema person.
Dropping a column
To drop a column in SQLite you need to actually create a new table, migrate the data into it then drop the old table and finally rename it. It is best to wrap this in a transaction.
BEGIN TRANSACTION; CREATE TABLE IF NOT EXISTS "person_new" "id" INTEGER NOT NULL PRIMARY KEY, ("uname" VARCHAR(255) NOT NULL, "role" VARCHAR(255) NOT NULL, "display_name" VARCHAR(255) NOT NULL, "updated" INTEGER NOT NULL); INSERT INTO person_new SELECT id, uname, role, display_name, updated FROM person; DROP TABLE person; ALTER TABLE person_new RENAME TO person; COMMIT;
ALTER TABLE table_name DROP COLUMN column_namedoes not work in SQLite3↩︎