Skip to main content

Migrations

ElectricSQL is designed to work with and on top of a Postgres data model.

If you don't have a data model you can create one using your preferred migrations tooling. You then use the same migrations tooling to extend your data model with DDLX statements to expose data to the replication machinery.

For example, assuming you have a table called projects, you can enable replication, grant public read access to it and write access to the project owner as follows:

ALTER TABLE projects
ENABLE ELECTRIC;

ELECTRIC ASSIGN 'projects:owner'
TO projects.owner_id;

ELECTRIC GRANT ALL
ON projects
TO 'projects:owner';

ELECTRIC GRANT SELECT
ON projects
TO ANYONE;
Work in progress

See the Limitations section below and the Roadmap page for more context.

Creating a data model

If you have your own Postgres-backed application, use the data model from that and continue using whatever method you're currently using to define the database schema.

Alternatively, if you need to create a data model, you can do so using SQL statements like CREATE TABLE, or a migrations tool like Prisma or Ecto.

Expand the box below for sample code:

Copy code to create data model
CREATE TABLE users (
username text NOT NULL PRIMARY KEY,

inserted_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp without time zone NOT NULL
);

CREATE TABLE projects (
id uuid NOT NULL PRIMARY KEY,
name text NOT NULL,

owner_id text NOT NULL REFERENCES users(username) ON DELETE CASCADE ON UPDATE CASCADE,

inserted_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp without time zone NOT NULL
);

CREATE TABLE memberships (
project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE ON UPDATE CASCADE,
user_id text NOT NULL REFERENCES users(username) ON DELETE CASCADE ON UPDATE CASCADE,

inserted_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE issues (
id uuid NOT NULL PRIMARY KEY,
title text NOT NULL,
description text,

project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE ON UPDATE CASCADE,

inserted_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp without time zone NOT NULL
);

CREATE TABLE comments (
id uuid NOT NULL PRIMARY KEY,
content text NOT NULL,

author_id text NOT NULL REFERENCES users(username) ON DELETE CASCADE ON UPDATE CASCADE,
issue_id uuid NOT NULL REFERENCES issues(id) ON DELETE CASCADE ON UPDATE CASCADE,

inserted_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp without time zone NOT NULL
);

Using your migrations framework

Use your prefered migrations framework to execute DDLX statements. For example:

With Phoenix/Ecto you can use the execute/1 function.

First, create a migration:

mix ecto.gen.migration electrify_items

Then e.g.:

defmodule MyApp.Repo.Migrations.ElectrifyItems do
use Ecto.Migration

def change do
execute "ALTER TABLE items ENABLE ELECTRIC"
end
end

See Integrations -> Backend and API -> DDLX for more information.

Limitations

There are currently a number of limitations on the data models and migrations that ElectricSQL supports.

Forward migrations

We only currently support forward migrations. Rollbacks must be implemented as forward migrations.

Additive migrations

We only currently support additive migrations. This means you can't remove or restrict a field. Instead, you need to create new fields and tables (that are pre-constrained on creation) and switch / mirror data to them.

Data types and constraints

See the pages on Types and Constraints.