SQLite column alterations with non-constant default

SQLite is a relational database management system contained in a C library. In contrast to other database management systems, SQLite is not a separate process that is accessed from the client application, but it is part of it. SQLite implements most of the standard defined for SQL, but some more advanced features are still missing.

Altering a table with SQL

To modify the schema of a table is not a desirable operation, but sometimes it proves to be necessary. This kind of alterations are supported by the SQL standard through the ALTER TABLE statement. SQLite supports the following alterations:

-- Rename a table
ALTER TABLE [schema.]table_name
  RENAME TO new_table_name;

-- Add a new column
ALTER TABLE [schema.]table_name
  ADD COLUMN column_definition;

-- Rename a column
ALTER TABLE [schema.]table_name
  RENAME COLUMN old_column_name TO new_column_name;

-- Drop a column
ALTER TABLE [schema.]table_name
  DROP COLUMN column_name;

Column definition

In most cases, the tool provided natively by SQLite is enough to perform the desired alteration. But trouble arise if the column to be altered has a non-constant default value.
That is because the DBMS would not be able to determine the new value of the column for the existing rows.

Example

Imagine, for example, that we have a table with a column that has a default value of the current timestamp:

-- Original table
CREATE TABLE chrono_table (
  id INTEGER PRIMARY KEY,
  start_timestamp TIMESTAMP NON NULL
);
-- Goal table
CREATE TABLE chrono_table (
  id INTEGER PRIMARY KEY,
  start_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  end_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Of course, it would be possible to create a new table with the expected schema and copy the data from the old table to the new one.

-- Create the new table
CREATE TABLE chrono_table_new (
  id INTEGER PRIMARY KEY,
  start_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  end_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Copy the data
INSERT INTO chrono_table_new (id, start_timestamp)
  SELECT id, start_timestamp FROM chrono_table;
-- Drop the old table
DROP TABLE chrono_table;
-- Rename the new table
ALTER TABLE chrono_table_new RENAME TO chrono_table;

But this approach may become impractical if the migration involves a great number of tables with a large number of rows. There is an alternative, although quite messy, way.

SQL schema alterations

While it is not to be considered a best practice, it is possible to alter the schema of a table by directly modifying the sqlite_master table.

This can be done quite easily with the following SQL statement:

-- Add the new column with a temporary default value
ALTER TABLE chrono_table ADD COLUMN end_timestamp TIMESTAMP DEFAULT NULL;

-- Allow the modification of the sqlite_master table
PRAGMA writable_schema = on;

-- Set a new schema for the table
UPDATE sqlite_master
  SET sql = 'CREATE TABLE chrono_table (
    id INTEGER PRIMARY KEY,
    start_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    end_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );'
  WHERE type = 'table' AND name = 'chrono_table';

-- Revert the permission to modify the sqlite_master table
PRAGMA writable_schema = off;

This approach does not provide many advantages over the previous one, since it requires a similar amount of work and provides way less readability and protection from mistakes.

A more surgical approach

Instead of modifying the whole table, requiring a different treatment for each one of them, the query can be improved by using a different SET statement.

-- Add the new column with a temporary default value
ALTER TABLE chrono_table ADD COLUMN end_timestamp TIMESTAMP DEFAULT NULL;

-- Allow the modification of the sqlite_master table
PRAGMA writable_schema = on;

-- Set a new schema for the table
-- It uses the nested 'replace' functions to replace the column description with the new one
UPDATE sqlite_master
SET sql = replace(
                  replace(sql, ' TIMESTAMP NOT NULL', ' TIMESTAMP DEFAULT CURRENT_TIMESTAMP'),
                  ' TIMESTAMP DEFAULT NULL',
                  ' TIMESTAMP DEFAULT CURRENT_TIMESTAMP'
                  )
    WHERE type = 'table'
    AND name IN ('chrono_table');

-- Revert the permission to modify the sqlite_master table
PRAGMA writable_schema = off;

The main advantage of this method is that is easily scalable to any number of tables, by simply including them in the IN clause. The main drawback is that the text match must be meticulously crafted to avoid leaving the database in an inconsistent state.

Populating the new column

Of course, the new column will be empty after the alteration. Only the newly inserted rows will benefit from the DEFAULT CURRENT_TIMESTAMP clause. If having it filled with NULL is not acceptable, it is trivial to set a desired value.

-- Set any theoretically non existent null value in the start_timestamp column to 1970-01-01 00:00:00
UPDATE chrono_table SET start_timestamp = datetime(0, 'unixepoch') WHERE end_timestamp IS NULL;
-- Set the null values in the new column to the current timestamp
UPDATE chrono_table SET end_timestamp = CURRENT_TIMESTAMP WHERE end_timestamp IS NULL;