SQLite Table Column Alterations with Dates and Default Values

For the past several months, I’ve been working on a mobile application that uses SQLite as a local database (using Ionic React and TypeORM). I have no previous production-level experience with SQLite. One thing that can be a little jarring is that the process of handling schema changes seems more convoluted than other popular databases.

I wanted to add timestamps with default values of “now” to a table, something like:

await queryRunner.query(`
  ALTER TABLE cats ADD COLUMN createdAt TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP;
`);

await queryRunner.query(`
  ALTER TABLE cats ADD COLUMN updatedAt TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP;
`);

But that led to an error with the following message when the migration ran:

Query: in selectSQL cursor Cannot add a column with non-constant default

After finding this StackOverflow response to a related question, I added a migration that generally followed Making Other Kinds Of Table Schema Changes in the Alter Table SQLite documentation. Something like:

/*
 * 1. Create new table
 * 2. Copy data from old table to new table
 * 3. Drop old table
 * 4. Rename new table to old table name
 * */
export class AddCatsTimestamps1908773635544
  implements MigrationInterface
{
  public async up(queryRunner: QueryRunner): Promise<void> {
    // 1. Create new table
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS temp_cats(
        id INTEGER PRIMARY KEY NOT NULL,
        name TEXT,
        createdAt TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updatedAt TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        masterId INTEGER,
        CONSTRAINT "FK_Cats_Master" FOREIGN KEY ("masterId") REFERENCES "masters" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION
      );
    `);

    // 2. Copy data from old table to new table
    await queryRunner.query(`
      INSERT INTO temp_cats (id, name, masterId)
      SELECT id, name, masterId
      FROM cats;
    `);

    // 3. Drop old table
    await queryRunner.query(`
      DROP TABLE cats;
    `);

    // 4. Rename new table to old table name
    await queryRunner.query(`
      ALTER TABLE temp_cats RENAME TO cats;
    `);
  }

I was reluctant to add a migration like this. It felt like I was polluting the codebase. Ultimately, however, the logic is simple to follow, and it was potentially good to recollect the few schema alterations I had made previously into one statement for the table.