App Crashes After Database Upgrade (Schema Changes)

#huawei #harmony #harmonyos

Read the original article：App Crashes After Database Upgrade (Schema Changes)

Problem Description

When the database schema is modified (for example, changing the CREATE TABLE statement, adding a new column, or renaming an existing column), the application crashes after upgrade.

This typically happens because the app code expects the new schema, but the underlying RDB file on the device still has the old structure. As a result, queries fail with errors like "no such column", "SQLite: Generic error", or unexpected crashes during data access.

Background Knowledge

In HarmonyOS NEXT (ArkTS), you open the database with relationalStore.getRdbStore(context, config) and run your own SQL (e.g., CREATE TABLE, ALTER TABLE) explicitly.
Schema versioning: use store.version (aligned with SQLite’s PRAGMA user_version) to drive migrations.
Typical flow on startup:
- Open the DB → read store.version.
- If store.version < EXPECTED_VERSION, run incremental SQL steps (ALTER TABLE …, CREATE INDEX …, data backfills).
- After successful steps, set store.version = EXPECTED_VERSION.
Crashes after an app update usually mean the on-device DB is still at an older version while the code expects the new schema (e.g., new/renamed columns), so queries/reads/writes fail at runtime.

Troubleshooting Process

Check the error logs – usually "no such column" or "table does not exist".
Compare the current store.version with your expected version to confirm if the migration ran.
Inspect the actual schema using PRAGMA table_info('Trips') to see which columns exist.
Verify migration logic – ensure that when schema changes (e.g., new column added), store.version is incremented and proper ALTER TABLE or CREATE statements are executed before the app queries the new schema.

Analysis Conclusion

The crash is caused by a schema–code mismatch: the on-device DB still has the old table layout, while the new app build expects the updated schema.

The fix is to run a versioned migration before any read/write: check store.version, apply incremental ALTER/CREATE steps inside a transaction, only then set store.version to the target value.

Keep steps idempotent and follow with a quick smoke query to confirm the new columns/tables exist.

Solution

Implement a versioned migration that runs before any queries.

Use store.version to decide what SQL to execute, then bump it after success.

Initial version (v1 – no migration yet)

import { relationalStore } from '@kit.ArkData';
import { BusinessError } from '@kit.BasicServicesKit';

const SQL_CREATE_TABLE = `CREATE TABLE IF NOT EXISTS Trips (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  template TEXT,
  start_date INTEGER,
  days INTEGER
);`;

export interface TripModel {
  id: number;
  template: string;
  startDate: string;
  days: number;
}

export class AppDatabase {
  private database?: relationalStore.RdbStore;

  constructor(context: Context) {
    this.initializeRDB(context)
      .then(() => {
        console.info(`Succeeded in getting RdbStore, version: ${this.database?.version}`);
      })
      .catch((err: BusinessError) => {
        console.error(`Failed to get RdbStore. Code:${err.code}, message:${err.message}`);
      });
  }

  private async initializeRDB(context: Context): Promise<void> {
    const store = await relationalStore.getRdbStore(context, {
      name: 'trips_database.db',
      securityLevel: relationalStore.SecurityLevel.S1,
    });

    // Create base schema for fresh installs
    await store.executeSql(SQL_CREATE_TABLE);

    // No migration in v1, default --> store.version = 0
    this.database = store;
  }

  async getTrips(): Promise<TripModel[]> {
    if (!this.database) {
      console.error('Failed to get trips, RdbStore is not initialized.');
      return [];
    }

    try {
      const predicates = new relationalStore.RdbPredicates('Trips');
      const resultSet = await this.database.query(
        predicates,
        ['id', 'template', 'start_date', 'days']
      );
      if (!resultSet || resultSet.rowCount === 0) {
        return [];
      }

      const trips: TripModel[] = [];
      while (resultSet.goToNextRow()) {
        const startDate = new Date(resultSet.getLong(resultSet.getColumnIndex('start_date')));
        trips.push({
          id: resultSet.getLong(resultSet.getColumnIndex('id')),
          template: resultSet.getString(resultSet.getColumnIndex('template')),
          startDate: startDate.toLocaleDateString(),
          days: resultSet.getLong(resultSet.getColumnIndex('days')),
        });
      }
      resultSet.close();
      return trips;
    } catch (err) {
      console.error(`Failed to query trips. Code:${err.code}, message:${err.message}`);
      return [];
    }
  }
}

Add a migration (v2 – add location column + index)

import { relationalStore } from '@kit.ArkData';
import { BusinessError } from '@kit.BasicServicesKit';

const SQL_CREATE_TABLE = `CREATE TABLE IF NOT EXISTS Trips (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  template TEXT,
  start_date INTEGER,
  days INTEGER
);`;

// 🔁 Bump this when schema changes (aligned with SQLite PRAGMA user_version)
const EXPECTED_VERSION = 2;

export interface TripModel {
  id: number;
  template: string;
  startDate: string;
  days: number;
  location: string; // new in v2
}

export class AppDatabase {
  private database?: relationalStore.RdbStore;

  constructor(context: Context) {
    this.initializeRDB(context)
      .then(() => {
        console.info(`Succeeded in getting RdbStore, version: ${this.database?.version}`);
      })
      .catch((err: BusinessError) => {
        console.error(`Failed to get RdbStore. Code:${err.code}, message:${err.message}`);
      });
  }

  private async initializeRDB(context: Context): Promise<void> {
    const store = await relationalStore.getRdbStore(context, {
      name: 'trips_database.db',
      securityLevel: relationalStore.SecurityLevel.S1,
    });

    // 1) Ensure baseline schema exists for fresh installs
    await store.executeSql(SQL_CREATE_TABLE);

    // 2) Run migration for existing installs (versioned)
    await this.migrateIfNeeded(store);

    this.database = store;
  }

  // Incremental migration, bumping store.version only after successful steps
  private async migrateIfNeeded(store: relationalStore.RdbStore): Promise<void> {
    if (store.version >= EXPECTED_VERSION) {
      return;
    }

    store.beginTransaction();
    try {
      switch (store.version) {
        case 0:
          // Some devices may start at 0 for a brand-new file
          await store.executeSql(SQL_CREATE_TABLE);
          // set version then --> fall through to case 1.
          store.version = 1;
        case 1: // v1 -> v2: add column + index
          await store.executeSql(`ALTER TABLE Trips ADD COLUMN location TEXT DEFAULT ''`);
          await store.executeSql(`CREATE INDEX IF NOT EXISTS idx_trips_start_date ON Trips(start_date)`);
          store.version = 2;
          break;
        default:
          throw new Error(`Unsupported DB version: ${store.version}`);
      }
      store.commit();
    } catch (e) {
      store.rollBack();
      throw new Error(`Migration failed: ${e}`);
    }
  }

  async getTrips(): Promise<TripModel[]> {
    if (!this.database) {
      console.error('Failed to get trips, RdbStore is not initialized.');
      return [];
    }

    try {
      const predicates = new relationalStore.RdbPredicates('Trips');
      const resultSet = await this.database.query(
        predicates,
        ['id', 'template', 'start_date', 'days', 'location'] // includes new column
      );
      if (!resultSet || resultSet.rowCount === 0) {
        return [];
      }

      const trips: TripModel[] = [];
      while (resultSet.goToNextRow()) {
        const startDate = new Date(resultSet.getLong(resultSet.getColumnIndex('start_date')));
        trips.push({
          id: resultSet.getLong(resultSet.getColumnIndex('id')),
          template: resultSet.getString(resultSet.getColumnIndex('template')),
          startDate: startDate.toLocaleDateString(),
          days: resultSet.getLong(resultSet.getColumnIndex('days')),
          location: resultSet.getString(resultSet.getColumnIndex('location')),
        });
      }
      resultSet.close();
      return trips;
    } catch (err) {
      console.error(`Failed to query trips. Code:${err.code}, message:${err.message}`);
      return [];
    }
  }
}

Verification Result

After adding the migration logic:

Upgrade test: Install the v1 app, insert some trips, then upgrade to v2 (with the new location column). The app no longer crashes — existing rows are still accessible, and new rows can include the location value.
Fresh install test: Install v2 directly. The DB initializes with the correct schema (location column present). Queries and inserts work without migration steps.
Smoke query: Calling getTrips() returns results including the new column when available, confirming the migration was applied successfully.

DEV Community