Migrations

ElectricSQL is a local-first SQL system that integrates with SQLite and Postgres. Both of these are relational databases that require a schema to be defined using DDL statements.

In order to make active-active replication work, ElectricSQL takes over the process of defining and applying migrations. This guide explains how to use the ElectricSQL tooling to manage migrations and explains how the migrations system works.

Pre-reqs

Make sure you’ve installed the CLI tool and initialised your application.

Tl;dr

Create a new migration:

electric migrations new "Create items table"

This will create a file at ./migrations/<TIMESTAMP>_create_items_table/migration.sql. Open this in your text editor and edit the SQL as desired, for example:

CREATE TABLE IF NOT EXISTS items (
  value TEXT PRIMARY KEY NOT NULL
) WITHOUT ROWID;

Build your migrations:

electric build

Sync them up to your backend:

electric sync

Workflow

1. Create a new migration

Generate new migrations to define and evolve your DDL schema. To generate a new migration run:

electric migrations new "Adding Foos and Bars"

This creates a subfolder in the migrations directory called something like 20221116162335423_adding_foos_and_bars. All migration subfolder names are prefixed by a timestamp and ordered by folder name.

This subfolder is created containing a migration.sql file. This file is intended to be edited (by you) and should have DDL statements written into it (by hand or using some automated process). For example, you could edit your migration.sql to look like this in order to create tables for foos and bars:

CREATE TABLE IF NOT EXISTS foos (
  value TEXT PRIMARY KEY NOT NULL
) WITHOUT ROWID;

CREATE TABLE IF NOT EXISTS bars (
  value TEXT PRIMARY KEY NOT NULL
) WITHOUT ROWID;

2. Build your migrations

Once you’ve finished editing your migration, run:

electric build

This builds a manifest file at migrations/manifest.json and an importable Javascript file at .electric/:app/:env/index.js that contains all your migrations. The metadata in this file will have a "env": "local" to indicate that it was built from your local migrations. The build migrations also contain triggers added by ElectricSQL to make replication work (in a foolproof way).

3. Sync up to the backend

You can now sync your migrations up to your backend environment:

electric sync

This uploads your local migrations to the backend sync service, validates them and applies them to your backend database. It then syncs down the migrations from your backend and builds a new importable Javascript file at the same.electric/:app/:env/index.js. The metadata in this file will have a "env": ENVIRONMENT_NAME to indicate that it was built directly from and matches the named app environment.

 Warning – When you are ready to deploy your app you should always use the sync command rather than the build to generate the index.js file to ensure that the migrations in it exactly match those on the server.

By default this will sync to the default environment. You can change the environment you’re targetting using electric config update --env ENV. Or you can use the --env option directly when you build and sync:

electric build --env production
electric sync --env production

Sync will fail if:

• a migration has a name that is lower in sort order than one already applied on the server
• a migration has the same name but different contents (verified by sha256 hash) than one already applied on the server

This is because a migration cannot be modified once it has been applied. If this happens you have two options. Either revert the local changes you have made to the conflicted migration using the revert command below. Or reset the database for the cloud sync service using the console.

4. Import into your application code

You can now import your migrations into your app, with the guarantee that your app code is using the same database schema as your backend database and replication service.

Utility commands

There are a few extra sub-commands to help you view and manage your migrations.

List migrations

To list all your migrations and their sync status in each environment on the server run:

electric migrations list

This will show a list of all your migrations and their status in each application environment that you have created.

Revert

If you have already synchronised a migration to the server once and then change it locally you will not be able to sync it a second time. Once a migration has been applied on the server it can’t be changed. Instead, you should create a new migration with your desired changes after it.

If you find yourself in this situalion you can copy the migration held on the server to replace the modified version you have locally with the command:

electric migrations revert MIGRATION_NAME

Where MIGRATION_NAME is the name of the folder containing the migration that you want to revert, e.g.:

electric migrations revert 20221116162335423_adding_foos_and_bars

Reset

Alternatively, in a development environment, if you don’t mind wiping your data, you can reset your backend environment and re-apply migrations from scratch.

electric reset [--env ENV]

This will delete your cloud database and replication pipeline and and re-create them. It’s like dropping and re-creating your whole database.

 Warning – electric reset causes entire data loss. Never use it in production, unless you know exactly what you’re doing.

Once you’ve reset, you can sync to bring your backend up-to-date with your local schema:

electric sync [--env ENV]

How migrations work

ElectricSQL apps run an embedded SQLite database on client devices. These database instances need to share and evolve a DDL schema. Propagating and synchronising this schema is hard because nodes often have poor connectivity and can be offline for days or weeks. You can’t stop the world whilst you apply a migration. You can’t stop stale clients writing data. There’s no way to enforce that all clients are using the same version of the schema at the same time.

So how do we make a workable local-first system that uses and evolves a DDL schema? We use causal consistency and translation functions, also known as lenses.

ElectricSQL’s solution to DDL migrations is based on causal consistency and lenses.

Using causal consistency for migration propagation

ElectricSQL’s replication layer provides Transactional Causal+ Consistency. You can read about it in our reference docs. Martin Kleppmann also has some great videos on causal consistency and logical time.

The crux of casual consistency is that if an update X relies on another update Y then you will never see X without having seen Y. ElectricSQL takes advantage of this delivery guarantee by writing a data record whenever a migration is applied and marking subsequent writes as dependent on it. This ensures that all nodes recieving an update have always recieved the DDL schema its using and allows us to apply a migration once (anywhere in the system) and propagate it using the same TCC+ replication layer as ordinary data.

Using lenses to adapt data from stale clients

Causal consistency solves propagation across the network but doesn’t address the problem of stale clients operating with mismatching schemas. We address this with translation functions. Translation functions map one schema to another. Bi-directional translation functions – which can map back and forward between schemas – are called lenses by the Cambria project that our solution takes inspiration from.

By translating data updates from stale clients to the new scema, through a chain of lenses, we can continue to accept updates and prevent data loss, even when clients remain offline for a long time (making local changes whilst, unbeknownst to them, the main system schema evolves in parallel).

Safe migration tooling

Schema evolution is a hard problem and even with causal delivery and lenses there are scenarios where a stale client can create data that’s incompatible with the evolved schema of the whole system. This can occur with destructive migrations. For example, removing tables that the client relies on.

Currently, we reject dangerous migrations and force you to evolve your schema in a safe, additive manner. We’re working on more advanced tooling that uses a combination of static analysis and automatically assisted rollout to help you apply a broader range of real-world migrations in a data-loss avoiding way.

 Warning – Development

We do not currently support a reset mode or a “keep schema in sync” mode. In the meantime, you can simulate this yourself by deleting the local SQLite database file. This is also useful for troubleshooting issues with synchronization (e.g. migration files not matching).

 Warning – Limitations

ElectricSQL provides migrations across Postgres and SQLite databases. Despite both system being based on SQL standard, there are differences between the two in the types they support, trigger definitions, etc. While we’re working to reduce the impedance between the two systems, be sure to check our limitations page to know current limitations.

Next step

• reactivity