Schema migrations for a Uniface app on SQLite - without a migration tool

Over five stages, the database of the customer app from the series Building a customer app in Uniface 10 grew from about 35 to 95 tables. There is no migration framework. Uniface has no Flyway, and adding a Java or .NET tool just to run a few CREATE TABLE statements against a single SQLite file felt like the wrong trade.

What replaced it is a short, boring procedure and two checks inside the app. Both are below, including the parts that are specific to the Uniface IDE.

Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) and the SQLite version of its SLE driver (3.50.2), October 2026.

The rule: only add, never change

The last five stages only added tables, indexes and settings. No column was renamed, no table rebuilt, no data moved. New information about an existing record goes into a new table with the same key - for example company data for a customer:

CREATE TABLE CUSTOMER_PARTY (
  CUSTOMER_ID  INTEGER NOT NULL PRIMARY KEY,
  PARTY_TYPE   VARCHAR(10) NOT NULL,
  COMPANY_NAME VARCHAR(100),
  ...
);

instead of ALTER TABLE CUSTOMER ADD COLUMN …. That keeps every existing form, entity and service untouched, and it makes every migration safe to reason about: a stage either created its objects or it didn't. There is no half-migrated table.

It is not free. Reading a customer with all its details now means joins over several 1:1 tables, and the "customer" exists in more places than one. For an app that grows in stages, that was the better trade; for a schema designed in one go, it would not be.

New tables, no new entities

In Uniface, a table is usually also an entity in an application model, with fields, keys and relationships, so forms can paint it. The 60 new tables have none of that. They are only ever read and written by services, and the services use sql and sql/data with the path from the assignment file:

[PATHS]
$CUSTOMERS   SLE:C:\Projects\CustomerApp\customers.db

    sql/data "SELECT JOB_CODE, JOB_TYPE, INTERVAL_DAYS, NEXT_RUN FROM JOB_DEF WHERE IS_ACTIVE = 1 AND NEXT_RUN <= '%%(vDay)' ORDER BY NEXT_RUN, JOB_CODE", "CUSTOMERS"

Like every service in this app, each new service component has exactly one entity painted on it: a non-database dummy, SVC_DMY.NOMODEL. The compiler answers with warning 1016 ("entity not found in application model, generating now"), which is expected and the only warning these services produce.

This decision makes a migration a pure database change. There is nothing to import into the repository, no model to keep in sync, and the next compile doesn't care. The price: forms can't paint these tables directly; a form that needs them calls a service operation.

Step 1: one script per stage

Each stage has one SQL file with plain statements, one per line:

CREATE TABLE APPROVAL (APPROVAL_ID INTEGER NOT NULL PRIMARY KEY, APPROVAL_TYPE VARCHAR(12) NOT NULL, ...);
CREATE INDEX IX_APPROVAL_STATUS ON APPROVAL (STATUS, REQUESTED_AT);
CREATE TABLE PRICE_HISTORY (...);
CREATE INDEX IX_PRICE_HISTORY_PRODUCT ON PRICE_HISTORY (PRODUCT_ID, VALID_FROM);
...
INSERT OR REPLACE INTO APP_SETTING (SETTING_KEY, SETTING_VALUE) VALUES ('SCHEMA_VERSION', '23');

The last line records the version in a settings table. That one row is what the app later checks.

The script and a short model description go into the folder of the stage, and the script is the documentation of what changed. Nobody has to reconstruct it from sqlite_master later.

Step 2: back up the file

backup\customers_before_stage23_20261016.db

A plain copy of the database file, named after the stage and the date. With SQLite, the whole database is one file - this is the cheapest rollback plan there is. Take the copy when nothing is writing to the file; a copy of a file in the middle of a write is not a backup.

Step 3: dry run on a copy

The script runs first against a second copy of the database, with a SQLite client outside the app. Then three checks on that copy:

PRAGMA integrity_check;
SELECT type, name, sql FROM sqlite_master ORDER BY type, name;
SELECT SETTING_VALUE FROM APP_SETTING WHERE SETTING_KEY = 'SCHEMA_VERSION';

The sqlite_master output of the copy is saved. After the real migration, the same query on the real file must return exactly the same rows. That comparison catches the typical mistakes of a manual migration: a statement that was skipped, a statement executed twice (which fails for CREATE TABLE, but not for INSERT OR REPLACE), or a statement run against the wrong file.

Step 4: apply it, one statement at a time

The real migration runs inside the Uniface IDE, because that is where the connection to the database is configured. The IDE has an EDIT SQL dialog: a text field, an SQL button, a result area. Two things about it are worth knowing:

• Commit explicitly. Statements executed there are not committed automatically. Each statement is followed by COMMIT in the same dialog. Until then the change is not in the file.
• Don't use Ctrl+A in the dialog. In the Uniface IDE, Ctrl+A is the ^ACCEPT key and closes the dialog. To replace the text in the field, use Ctrl+Home, then Ctrl+Shift+End, then type.

One statement at a time sounds slow. For 15 statements it takes a few minutes, and every statement gets its own result. A script with one failed line in the middle would leave you guessing which statements went through.

The backup and the dry run were the work. The real run should be uneventful.

Step 5: let the app check itself

Two operations check the schema from inside the app.

A schema check compares the tables that should exist with the tables that do, and reads the version:

    call GET_TEXT("SELECT SETTING_VALUE FROM APP_SETTING WHERE SETTING_KEY = 'SCHEMA_VERSION'", pVersion)
    sql/data "SELECT name FROM sqlite_master WHERE type = 'table'", "CUSTOMERS"
    ...
    if (pVersion != "23")
        pError = "The schema version is %%(pVersion), expected 23."
        return -1
    endif

A broader system check does the same for the tables and indexes of the newer stages, with one query each. SQLite's VALUES clause makes the expected list a table, and NOT IN against sqlite_master returns what is missing:

entry MISSING_OBJECTS
params
    string pType : IN
    string pValues : IN
    string pMissing : OUT
endparams
    call GET_TEXT("SELECT COALESCE(GROUP_CONCAT(v.column1, ', '), '') FROM (VALUES %%(pValues)) v WHERE v.column1 NOT IN (SELECT name FROM sqlite_master WHERE type = '%%(pType)')", pMissing)
    return 0
end

called with

    vIndexes = "('UX_PRODUCT_SKU'), ('UX_SALES_DOC_NO'), ('IX_SALES_DOC_CUSTOMER'), ..."
    call MISSING_OBJECTS("index", vIndexes, vMissing)

An empty result means everything is there. A non-empty one names exactly what is missing - which is the information you need at the moment the app starts behaving strangely after someone restored an old backup.

The same system check also validates that numeric settings are numbers:

SELECT COALESCE(GROUP_CONCAT(SETTING_KEY, ', '), '')
  FROM APP_SETTING
 WHERE SETTING_KEY IN ('SLA_HIGH', 'DUNNING_FEE_1', ...)
   AND (COALESCE(SETTING_VALUE, '') = '' OR SETTING_VALUE GLOB '*[^0-9]*');

GLOB '*[^0-9]*' matches any value that contains a character other than a digit.

Step 6: run all tests and note the checksum

The full test suite runs against the migrated file. All tests run inside one transaction that is rolled back, so the file must be byte-identical afterwards. Its MD5 checksum goes into the project notes - once per stage, after the migration. From then on, any change of that checksum outside a planned migration is a finding.

What I would do differently with a bigger team

• A version table instead of a setting. One row per applied script with a timestamp and a checksum of the script. That is what migration tools do, and it answers "was script 21 applied twice?" without detective work.
• Statements that are safe to repeat. CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS would make a re-run harmless. I left them out on purpose, so that an accidental second run fails loudly - with one person and a dry run, loud failure was more useful than silent success.
• Real ALTER TABLE migrations would need SQLite's "create new table, copy, drop, rename" dance for anything beyond adding a column. The add-only rule avoided that entirely.

Takeaways

Only add while you can: new tables with the same key instead of changed tables.

One script per stage, ending with the version number.

Back up, dry run on a copy, compare sqlite_master.

Apply one statement at a time and commit explicitly - in the Uniface IDE, without Ctrl+A.

Let the app check its own schema: version, tables, indexes, settings.