Modify column name in SQLite

You are using a web application or desktop application that uses a SQLite database. And then, you decide to rename a column or change the column data type. This post shows how to rename a column in SQLite.

In SQLite, you cannot just do ALTER TABLE table RENAME COLUMN old_column TO new_column;. As of SQLite version 3.25.0, you can.

In our example, we have a table blog in a file called blog.sqlite3. This is the database schema of the table blog:

CREATE TABLE IF NOT EXISTS blog (
  id INTEGER PRIMARY KEY NOT NULL,
  title varchar(255) NOT NULL,
  slug varchar(100) UNIQUE NOT NULL,
  contnt text NOT NULL,
  created_at date NOT NULL,
  updated_at date DEFAULT NULL
);

We will rename the field contnt to content.

SQLite version 3.25.0 and higher

Check your SQLite version first.

$ sqlite3 --version
3.38.2 2022-03-26 13:51:10

If you have SQLite version 3.25.0 or higher, you can just do this:

ALTER TABLE blog RENAME COLUMN contnt to content;

If this works, great! If it does not, then read on. Also, feel free to share this post.

SQLite versions lower than 3.25.0

Let us backup the database schema first. Type this:

sqlite3 blog.sqlite3

This takes us into the SQLite console. Now, list the schema of blog:

sqlite> .schema blog

Output:

CREATE TABLE blog (
  id INTEGER PRIMARY KEY NOT NULL,
  title varchar(255) NOT NULL,
  slug varchar(100) UNIQUE NOT NULL,
  contnt text NOT NULL,
  created_at date NOT NULL,
  updated_at date DEFAULT NULL
);

Create the new CREATE TABLE command

In a text editor, copy the new CREATE TABLE command using the output from above. Change this CREATE TABLE command to reflect the updated field name content.

CREATE TABLE blog (
  id INTEGER PRIMARY KEY NOT NULL,
  title varchar(255) NOT NULL,
  slug varchar(100) UNIQUE NOT NULL,
  content text NOT NULL,
  created_at date NOT NULL,
  updated_at date DEFAULT NULL
);

Do not run the new CREATE TABLE command yet.

Rename table blog to tmp

Run this command in the SQLite console:

sqlite> alter table blog rename to tmp;

Verify that table blog has been renamed to tmp.

sqlite> .tables
tmp

Run CREATE TABLE blog

Now, in the SQLite console, run the updated CREATE command from above. Also, verify that you now have tables tmp and blog.

sqlite> CREATE TABLE blog (
  id INTEGER PRIMARY KEY NOT NULL,
  title varchar(255) NOT NULL,
  slug varchar(100) UNIQUE NOT NULL,
  content text NOT NULL,
  created_at date NOT NULL,
  updated_at date DEFAULT NULL
);
sqlite> .tables
blog  tmp 

Insert all rows from tmp to blog

Copy all the records from tmp to blog.

insert into blog(id,title,slug,content,created_at,updated_at)
select id,title,slug,content,created_at,updated_at from tmp;

The above command should successfully copy all records from tmp to blog.

Verify that they are all copied with:

select * from blog

Delete table tmp

Now that you have your updated table blog, you can drop your old table tmp.

sqlite> drop table tmp;
sqlite> .tables
blog

If you have any questions, please contact me at arulbOsutkNiqlzziyties@gNqmaizl.bkcom. You can also post questions in our Facebook group. Thank you.