ElectricSQL is still under development and we do not ensure API retro-compatibility. Get in touch with us if you want to use ElectricSQL in production.

Schema modeling constraints

To support active-active asynchronous replication there are a few differences in the way we typically model applications that developer must be sensible to.

  1. Primary keys are permanent: If one user changes the primary key for a row while another user updates that row, in which row should the updates be applied? To prevent the ambiguity, we don’t allow changing the primary key for resources.

  2. No auto increment for unique identifiers: It is common to use sequence generators for generating unique global identifiers. With ElectricSQL users generate identifiers on their local database without coordination and therefore it is necessary prevent collisions across databases. One can use random generators like uuids, or use a suffix to distinguish identifiers generated at different sources.

  3. No migration rollbacks: We do not support rollbacks or downgrading migrations. The DDL schema must always be evolved forwards. To rollback a change, create a new migration that changes the schema accordingly. For example, if you created a table and you want to rollback, create a new migration dropping the table.

  4. Versioning: The developer must ensure that past versions of the application still work with new schema versions. We plan to introduce support for lenses to allow adapting newer schema versions to older versions of code.

Postgres <> SQLite limitations

We are actively working to support Postgres <> SQLite replication. There are currently some restrictions in order to allow replication to work between the two systems:

  1. Use SQLite data-types (INTEGER, REAL, TEXT, BLOB): Migrations are defined using SQLite-compatible SQL and types are internally mapped to the closest Postgres type. The supported types are defined here.
  2. No support for triggers across Postgres and SQLite: Because SQLite and Postgres use different syntax for declaring triggers, the only option right now is to eject the migrations system and apply triggers manually.
  3. Unique and check constraints are disabled
  4. Tables must be defined WITHOUT ROWID
  5. Table names and columns must be all be lower case
  6. No support for composite primary keys
  7. No support for extensions Extensions need to be installed out of band and we do not guarantee replication will work for extension-managed tables.

We are working to remove these restrictions.