Implementing Schema Changes

The PowerSync sync protocol is schemaless, and not directly affected by schema changes.

Replicating data from the source database to sync buckets may be affected by server-side (Postgres) schema changes, and may need reprocessing in some cases.

The client-side schema is just a view on top of the schemaless data. Updating this schema is immediate when the new version of the app runs, with no client-side migrations required.

The developer is responsible to keep schema changes backwards-compatible with older versions of client apps. PowerSync has some functionality to assist with this:

Different sync rules can be applied based on parameters such as client version.
Sync rules can apply simple data transformations to keep data in a format compatible with older clients.

PowerSync keeps the sync buckets up to date with any incremental data changes, as recorded in the Postgres WAL. This is also referred to as DML (Data Manipulation Language) queries.

However, this does not include DDL (Data Definition Language), which includes:

Creating, dropping or renaming tables.
Changing replica identity of a table.
Adding, dropping or renaming columns.
Changing the type of a column.

Schema changes affecting sync rules

DROP table

Dropping a table is not directly detected by PowerSync, and previous data may be preserved. To make sure the data is removed, TRUNCATE the table before dropping, or remove the table from sync rules.

CREATE table

The new table is detected as soon as data is inserted.

DROP and re-CREATE table

This is a special case of combining DROP and CREATE. If a dropped table is created again, and data is inserted into the new table, the schema change is detected by PowerSync. PowerSync will delete the old data in this case, as if TRUNCATE was called before dropping.

RENAME table

A renamed table is handled similarly to dropping the old table, and creating a new table with the new name.

The rename is only detected when data is inserted, updated or deleted to the new table. At this point, PowerSync effectively does a TRUNCATE of the old table, and replicates the new table.

This may be a slow operation if the table is large, and all other replication will be blocked until the new table is replicated.

Change REPLICA IDENTITY

The replica identity of a table is considered changed if either:

The type of replica identity changes (DEFAULT, INDEX, FULL, NOTHING).
The name or type of columns part of the replica identity changes.

The latter can happen if:

Using REPLICA IDENTITY FULL, and any column is added, removed, renamed, or the type changed.
Using REPLICA IDENTITY DEFAULT, and the type of any column in the primary key is changed.
Using REPLICA IDENTITY INDEX, and the type of any column in the replica index is changed.
The primary key or replica index is removed or changed.

When the replica identity changes, the entire table is re-replicated again. This may be a slow operation if the table is large, and all other replication will be blocked until the table is replicated again.

Sync rules affected by schema changes will fail "soft" — an alert would be generated, but the system will continue processing changes.

Column changes

Column changes such as adding, dropping, renaming columns, or changing column types, are not automatically detected by PowerSync (unless it affects the replica identity as described above).

Adding a column with a NULL default value will generally not cause issues. Existing records will have a missing value instead of NULL value, but those are generally treated the same on the client.

Adding a column with a different default value, whether it's a static or computed value, will not have this default automatically replicated for existing rows. To propagate this value, make an update to every existing row.

Removing a column will not have the values automatically removed for existing rows on PowerSync. To propagate the change, make an update to every existing row.

Changing a column type, and/or changing the value of a column using an ALTER TABLE statement, will not be automatically replicated to PowerSync. In some cases, the change will have no effect on PowerSync (for example changing between VARCHAR and TEXT types). When the values are expected to change, make an update to every existing row to propagate the changes.

Publication changes

A table is not replicated unless it is part of the powersync publication.

If a table is added to the publication, it is treated the same as a new table, and any existing data is replicated. This may be a slow operation if the table is large, and all other replication will be blocked until the new table is replicated.

There are additional changes that can be made to a table in a publication:

Which operations are replicated (insert, update, delete and truncate).
Which rows are replicated (row filters).

Those changes are not automatically picked up by PowerSync during replication, and can cause PowerSync to miss changes if the changes are filtered out. PowerSync will not automatically recover the data when for example removing a row filter. Use these with caution.

Schema and sync rule changes affecting clients

The sync system itself is schemaless — the client syncs any data as received, in JSON format, regardless of the data model on the client.

The schema as supplied on the client is only a view on top of the schemaless data.

If tables not described by the schema are synced, it is stored internally, but not accessible.
Same applies for columns not described by the schema.
When there is a type mismatch, SQLite's CAST functionality is used to cast to the type described by the schema.
1. Data is internally stored as JSON.
2. SQLite's CAST is used to cast values to TEXT, INTEGER or REAL.
3. Casting between types should never error, but it may not fully represent the original data. For example, casting an arbitrary string to INTEGER will likely result in a "0" value.
4. Full rules for casting between types are described here: https://www.sqlite.org/lang_expr.html#castexpr
Removing a table is handled on the client as if the table exists with no data.
Removing a column is handled on the client as if the values are undefined.

Nothing in PowerSync will fail hard if there are incompatible schema changes. But depending on how the app uses the data, app logic may break. For example, removing a table that the app actively uses may break workflows in the app.

To avoid certain types of breaking changes on older clients, sync rule transformations may be used.

PowerSync Docs