ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite

sqlite alter-table
dan04 picture dan04 · Aug 30, 2010 · Viewed 111.9k times · Source

We've recently had the need to add columns to a few of our existing SQLite database tables. This can be done with ALTER TABLE ADD COLUMN. Of course, if the table has already been altered, we want to leave it alone. Unfortunately, SQLite doesn't support an IF NOT EXISTS clause on ALTER TABLE.

Our current workaround is to execute the ALTER TABLE statement and ignore any "duplicate column name" errors, just like this Python example (but in C++).

However, our usual approach to setting up database schemas is to have a .sql script containing CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS statements, which can be executed using sqlite3_exec or the sqlite3 command-line tool. We can't put ALTER TABLE in these script files because if that statement fails, anything after it won't be executed.

I want to have the table definitions in one place and not split between .sql and .cpp files. Is there a way to write a workaround to ALTER TABLE ADD COLUMN IF NOT EXISTS in pure SQLite SQL?

Answer

MPelletier picture MPelletier · Aug 30, 2010

I have a 99% pure SQL method. The idea is to version your schema. You can do this in two ways:

  • Use the 'user_version' pragma command (PRAGMA user_version) to store an incremental number for your database schema version.

  • Store your version number in your own defined table.

In this way, when the software is started, it can check the database schema and, if needed, run your ALTER TABLE query, then increment the stored version. This is by far better than attempting various updates "blind", especially if your database grows and changes a few times over the years.

Related questions

How do I rename a column in a SQLite database table?

I would need to rename a few columns in some tables in a SQLite database. I know that a similar question has been asked on stackoverflow previously, but it was for SQL in general, and the case of SQLite was …

Read More
Modify a Column's Type in sqlite3

I'm pretty new to SQLite 3 and just now I had to add a column to an existing table I had. I went about doing that by doing: ALTER TABLE thetable ADD COLUMN category;. Of course, I forgot to specify that …

Read More
SQLite Modify Column

I need to modify a column in a SQLite database but I have to do it programatically due to the database already being in production. From my research I have found that in order to do this I must do …

Read More