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.
Make sure you’ve installed the CLI tool and initialised your application.
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:
Sync them up to your backend:
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
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:
This builds a manifest 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/: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
synccommand rather than the
buildto generate the
index.jsfile 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.
There are a few extra sub-commands to help you view and 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.
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
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.
electric resetcauses 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.