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.

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.

Usage overview

Install the ElectricSQL CLI tool, for example using Homebrew (see the installation guide for more options):

brew install electric-sql/tap/electric

Initialise migrations:

electric migrations init lorem-ipsum-dolores

Evolve your schema:

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
) WITHOUT ROWID;

You can now bundle the migrations for your app:

electric migrations build

And sync migrations to the cloud (see the configuration guide for context on the --env options):

electric migrations sync --env staging

Workflow steps

1. Initialisation

Change directory to the root of your application and run:

electric migrations init APP_ID

This initialises a migrations folder. By default, this is created at ./migrations. You can provide the --dir option to manage migrations at a different path:

electric migrations init APP_ID --dir ./some/other/path/migrations

Inside the migrations folder it adds a migration subfolder with a name automatically derived from the current time in UTC and the title init e.g. 20221116162204816_init. Inside this folder will be a file called migration.sql. You should write your initial SQLite DDL SQL into this file.

APP_ID must be unique the identifier for your application that you get when creating an application using the ElectricSQL console and that you configure your app to connect to. This ID is stored in a generated file called manifest.json in the migrations folder so you don’t have to keep re-typing it.

2. Schema evolution

Generate new migration to define and evolve your 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 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
) WITHOUT ROWID;

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

3. Building

Once you’ve finished editing your migration, run:

electric migrations build

This builds a javascript file at migrations/dist/index.js that contains all your migrations with Electric DB’s added DDL and some metadata.

The metadata in this file will have a `“env”: “local” to indicate the it was built from your local files rather that one of the named app environments.

You can then import this into your app.

You can optionally provide –dir MIGRATIONS_DIR to specify which migration directory to use other than one in the current working directory.

4. Syncing

To sync the migrations with the server, you run:

electric migrations sync

Synchronises changes you have made to migration SQL files in your local migrations folder up to the Electric SQl servers, and builds a new javascript file at dist/index.js that matches the newly synchronised set of migrations.

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.

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.

By default this will sync to the default environment for your app. If you want to use a different one give its name with --env ENVIRONMENT_NAME. eg.

electric migrations sync --env production

If the app environment on the server already has a migration with the same name but different sha256 then this synchronisation will fail 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, if you are working in a development environment that you are happy to reset, you can reset the whole environment’s DB using the web control panel.

If a migration has a name that is lower in sort order than one already applied on the server this sync will fail.

Migration utility commands

There are a few extra sub-commands for migrations to help you 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.

Change APP_ID

You can change the APP_ID stored in the migrations/manifest.json file like this:

electric migrations app different-app-id

Revert a local migration

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 cannot be changed, rather 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 eg

electric migrations revert 20221116162335423_adding_foos_and_bars

Alternatively, if you are only working in a development app environment and don’t mind loosing all data, you can reset the DB

Limitations

Data types

Migrations must be defined using SQLite-compatible SQL. This allows schemas to be compatible between SQLite and Postgres. The supported types are defined here.

Binary IDs

You must not use autoincrementing integer IDS. Instead, you must use binary identifiers, typically uuids.

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.

We intend to improve the tooling around this behaviour but it is intentional, in order to match how schemas must be propagated in a local-first system in production.

 Warning – Versioning

We do not currently support transformation functions. Currently your apps and your server must be using the same version of the schema for sync to work.

 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.

 Warning – Constraints

We support referential integrity using compensations but we do not currently support unique or check constraints. See our integrity and known issues pages for more information.

Next step