Implementing Schema Changes

How PowerSync handles schema changes across Postgres, MongoDB, MySQL, SQL Server, and Convex during replication.

Introduction

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

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

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

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

1. Versioned streams can serve different data to different client versions, either by defining a separate stream per version or by filtering on connection parameters such as client version. (In Sync Rules, this uses client parameters.)

2. Stream queries can apply simple data transformations to keep data in a format compatible with older clients, for example by aliasing or casting columns. (In Sync Rules, this is done via data query expressions.)

Client-Side Impact of Schema and Sync Config Changes

As mentioned above, the PowerSync 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.

1. If tables/collections not described by the client-side schema are synced, it is stored internally, but not accessible.

2. Same applies for columns/fields not described by the client-side schema.

3. 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 in the SQLite documentation here.

4. Removing a table/collection is handled on the client as if the table exists with no data.

5. Removing a column/field 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/collection that the app actively uses may break workflows in the app.

To avoid certain types of breaking changes on older clients, data transformations may be used — via column aliasing/casting in Sync Streams, or data query expressions in Sync Rules.

Postgres Specifics

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

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

1. Creating, dropping or renaming tables.

2. Changing replica identity of a table.

3. Adding, dropping or renaming columns.

4. Changing the type of a column.

Postgres Schema Changes Affecting Sync Streams

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 your Sync Streams (or legacy 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:

1. The type of replica identity changes (DEFAULT, INDEX, FULL, NOTHING).

2. The name or type of columns part of the replica identity changes.

The latter can happen if:

1. Using REPLICA IDENTITY FULL, and any column is added, removed, renamed, or the type changed.

2. Using REPLICA IDENTITY DEFAULT, and the type of any column in the primary key is changed.

3. Using REPLICA IDENTITY INDEX, and the type of any column in the replica index is changed.

4. 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 Streams/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:

1. Which operations are replicated (insert, update, delete and truncate).

2. 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.

MongoDB Specifics

Since MongoDB is schemaless, schema changes generally do not impact PowerSync. However, adding, dropping, and renaming collections require special consideration.

Adding Collections

Sync Streams/Sync Rules can include collections that do not yet exist in the source database. These collections will be created in MongoDB when data is first inserted. PowerSync will begin replicating changes as they occur in the source database.

Dropping Collections

Due to a limitation in the replication process, dropping a collection does not immediately propagate to synced clients. To ensure the change is reflected, any additional insert, update, replace, or delete operation must be performed in any collection within a synced database.

Renaming Collections

Renaming a synced collection to a name that is not included in Sync Streams (or legacy Sync Rules) has the same effect as dropping the collection.

Renaming an unsynced collection to a name that is included in your Sync/Streams/Sync Rules triggers an initial snapshot replication. The time required for this process depends on the collection size.

Circular renames (e.g., renaming todos → todos_old → todos) are not directly supported. To reprocess the database after such changes, a Sync Streams/Sync Rules update must be deployed.

Convex Specifics

The Convex replicator is currently released as an experimental feature. APIs and behavior may change, and we can’t yet guarantee continued support or long-term stability.

For Convex, most schema changes are document-shape changes rather than DDL events. PowerSync reads the JSON documents returned by Convex snapshots and document deltas, not Convex schema metadata, so added fields and type changes are replicated through normal Convex writes.

Convex tables use _id as the replication identity for PowerSync. There is no source-specific primary key or replica identity definition to track.

Convex validates schema changes against existing data. If you change a field type, use Convex’s migration pattern: add or allow the new shape, update existing documents with mutations, then tighten the schema once the data has moved. Convex often recommends writing migrated values to a new field for this flow. Those mutation updates should appear in document_deltas and replicate like other writes.

You do not need to redeploy your Sync Config or re-snapshot merely because a field was added to Convex. New fields selected by your existing Sync Streams are replicated as they appear in Convex documents.

If you remove a field, first make it optional in your Convex schema, then run a migration that removes the field from existing documents, and only then remove it from the schema. PowerSync stops replicating new values for that field after the data stops containing it. Previously synced values can remain on clients until the affected data is reprocessed.

A re-snapshot is still required for cases that change the selected data set or invalidate the source cursor. This includes initial replication, a Sync Streams deployment that selects new existing data, restarting an incomplete initial snapshot, or recovering from a lost or expired Convex cursor.

Dropping Tables

Dropping Convex tables has a known limitation. Deleting a table from the Convex dashboard does not emit per-document delete rows in document_deltas, so PowerSync does not automatically remove previously synced rows for that table.

To decommission a table while preserving replication correctness, clear the table before deleting it. In the Convex dashboard, use Clear Table first, then delete the table after those document removals have replicated. Deleting documents through Convex mutations is also valid when that path emits document delete deltas. Otherwise, treat dashboard table deletion or schema-only table removal as a Sync Config deployment change and clear or re-replicate affected PowerSync state.

MySQL Specifics

MySQL support is currently in a Beta release.

PowerSync keeps the buckets up to date with any incremental data changes as recorded in the MySQL binary log. The binary log also provides DDL (Data Definition Language) query updates, which include:

1. Creating, dropping or renaming tables.

2. Truncating tables. (Not technically a schema change, but they appear in the query updates regardless.)

3. Changing replica identity of a table. (Creation, deletion or modification of primary keys, unique indexes, etc.)

4. Adding, dropping, renaming or changing the types of columns.

For MySQL, PowerSync detects schema changes by parsing the DDL queries in the binary log. It may not always be possible to parse the DDL queries correctly, especially if they are complex or use non-standard syntax. In such cases, PowerSync will ignore the schema change, but will log a warning with the schema change query. If required, the schema change would then need to be manually handled by redeploying your Sync Streams (or legacy Sync Rules). This triggers a re-replication.

MySQL Schema Changes Affecting Sync Streams

DROP table

PowerSync will detect when a table is dropped, and automatically remove the data from the buckets.

CREATE table

Table creation is detected and handled the first time row events for the new table appear on the binary log.

TRUNCATE table

PowerSync will detect truncate statements in the binary log, and consequently remove all data from the buckets for that table.

RENAME table

A renamed table is handled similarly to dropping the old table, and then creating a new table with existing data under the new name. This may be a slow operation if the table is large, since the “new” table has to be re-replicated. Replication will be blocked until the new table is replicated.

Change REPLICA IDENTITY

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

1. The type of replica identity changes (DEFAULT, INDEX, FULL, NOTHING).

2. The name or type of columns which form part of the replica identity changes.

The latter can happen if:

1. Using REPLICA IDENTITY FULL, and any column is added, removed, renamed, or the type changed.

2. Using REPLICA IDENTITY DEFAULT, and the type of any column in the primary key is changed.

3. Using REPLICA IDENTITY INDEX, and the type of any column in the replica index is changed.

4. The primary key or replica index is removed or changed.

When the replication identity changes, the entire table is 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 Streams/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 detected by PowerSync but will generally not result in re-replication. (Unless the replica identity was affected 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 default 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.

SQL Server Specifics

SQL Server support is currently in a Beta release. The workflows below apply to PowerSync Service v1.25.0 or later. Earlier versions handled schema changes differently.

SQL Server Change Data Capture (CDC) does not include schema changes in the change stream. PowerSync can only discover them by polling database metadata, which gives no exact position in the change stream at which the schema changed.

PowerSync therefore does not adopt SQL Server schema changes automatically. Each Sync Streams deployment is processed as a replication stream that keeps the exact table set and CDC capture instances it selected when it started. To adopt a schema change, deploy your updated Sync Config: the new deployment reprocesses the data from the current schema while the previous deployment continues serving clients, and becomes active once reprocessing completes.

This model has two implications:

1. Each replicated table is pinned to a specific capture instance. When a newer capture instance appears, PowerSync logs a warning but continues reading from the pinned instance. If the pinned instance is removed, replication stops with PSYNC_S1601.
2. Wildcard table names (%) are not supported for SQL Server. Every replicated table must be listed by name in your Sync Streams, must exist, and must have CDC enabled. A configured table that is unavailable or not CDC-enabled stops replication with PSYNC_S1602 rather than being silently skipped.

Capture Instances

SQL Server CDC is designed to protect downstream consumers from schema changes. Some schema changes, like changing the data type of a primary key column, are blocked at the database level while CDC is enabled on a table. Other schema changes are allowed, but are not propagated to the capture instance for the table: the capture instance keeps the column set it was created with. To capture new or changed columns, create a new capture instance, or drop and recreate the existing one. Note that SQL Server allows a maximum of 2 capture instances per table.

Dropping and Recreating a Capture Instance

-- Disable CDC for the source table
EXEC sys.sp_cdc_disable_table
      @source_schema = N'dbo',
      @source_name = N'<YOUR_TABLE_NAME>',
      @capture_instance = 'all'; -- This drops all capture instances for the source table. If you only want to drop a specific capture instance, use the capture instance name instead of 'all'.

-- Re-enable CDC for the source table
EXEC sys.sp_cdc_enable_table
        @source_schema = N'dbo',
        @source_name   = N'<YOUR_TABLE_NAME>',
        @role_name     = N'cdc_reader',
        @supports_net_changes = 0;

Creating a New Capture Instance

-- Create a new capture instance for the source table by specifying a new capture instance name
EXEC sys.sp_cdc_enable_table
        @source_schema = N'dbo',
        @source_name   = N'<YOUR_TABLE_NAME>',
        @role_name     = N'cdc_reader',
        @supports_net_changes = 0,
        @capture_instance = N'<NEW_CAPTURE_INSTANCE_NAME>'; -- If a capture instance for the table already exists, you have to specify a different name for the new capture instance.

Making SQL Server Schema Changes

The workflows below keep the old table and capture instance available until the new Sync Config deployment has completed reprocessing and becomes active, so clients keep receiving updates during the transition whenever a rolling change is possible. If a pinned capture instance or replicated table is removed before then, replication stops with an error and stays stopped until an updated Sync Config is deployed.

Adding a Table

1. Create the table.
2. Enable CDC for the table.
3. Add the table to your Sync Streams and deploy.

The new deployment replicates the table’s data before becoming active. If the Sync Config references a table before it exists with CDC enabled, replication stops with PSYNC_S1602 and starts once the table is available.

Adding, Dropping, or Changing Columns

Column changes do not update an existing capture instance, and the replication stream keeps reading the column set captured by its pinned instance. PowerSync warns when it detects that the source table’s schema differs from that capture instance, but continues replicating the pinned columns. No PowerSync action is needed if the affected columns do not need to be replicated.

To replicate the new column set:

1. Apply the column change in the source database.
2. Create a second capture instance for the table with the desired columns (see Creating a New Capture Instance).
3. Update your stream queries if needed and deploy.
4. Wait for reprocessing to complete and the new deployment to become active.
5. Remove the old capture instance.

Changing a Primary Key or Other Identity-Breaking Changes

Some changes are blocked at the database level while CDC is enabled on a table. These include column renames, changing the primary key, and changing the data type of a primary key column. They require disabling and re-enabling CDC for the table:

1. Disable CDC for the table.
2. Apply the primary key or identity change in the source database.
3. Re-enable CDC.
4. Update your stream queries if needed and deploy.

This cannot be done as a rolling change, so expect replication downtime until the new deployment becomes active. The active deployment stops with PSYNC_S1601 when its capture instance is removed, or with PSYNC_S1603 if the replica identity has already changed when it next checks the table. It cannot adopt the replacement capture instance. Note that disabling and re-enabling CDC stops replication with PSYNC_S1601 even if the replica identity is unchanged.

Dropping a Table

1. Remove the table from your Sync Streams and deploy.
2. Wait for the new deployment to become active.
3. Drop the source table.

If the table is dropped first, replication stops with PSYNC_S1603 because the table may still have unread changes. Already-replicated data is retained until the new deployment becomes active.

Renaming or Recreating a Table

Renaming a table and dropping and recreating it are handled the same way: update your stream queries to reference the intended table name, ensure the resulting table has CDC enabled with the expected schema and replica identity, and deploy. The active deployment stops with PSYNC_S1603 when its replicated table is removed or recreated.

Changes That Require No Deployment

Changes that do not affect the captured columns or the replica identity of a table require no PowerSync action. This includes many index, constraint, and default-value changes, as well as changes to columns that are not replicated.

See Also

• JSON, Arrays and Custom Types

• Deploying Schema Changes