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.
Install the ElectricSQL CLI tool, for example using Homebrew (see the installation guide for more options):
brew install electric-sql/tap/electric
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
electric migrations sync --env staging
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
to manage migrations at a different path:
electric migrations init APP_ID --dir ./some/other/path/migrations
migrations folder it adds a migration subfolder with a name automatically derived from the current
time in UTC and the title
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
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
CREATE TABLE IF NOT EXISTS foos ( value TEXT PRIMARY KEY ) WITHOUT ROWID; CREATE TABLE IF NOT EXISTS bars ( value TEXT PRIMARY KEY ) WITHOUT ROWID;
Once you’ve finished editing your migration, run:
electric migrations build
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.
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,
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
--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
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.
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.
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
Migrations must be defined using SQLite-compatible SQL. This allows schemas to be compatible between SQLite and Postgres. The supported types are defined here.
You must not use autoincrementing integer IDS. Instead, you must use binary identifiers, typically uuids.
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.