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

SQL

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

Prisma

datasource db {
  provider = "postgresql"
  url = env("PRISMA_DB_URL")
}

model User {
  @@map("users")

  username String @id

  comments Comment[]
  memberships Membership[]
  projects Project[]

  inserted_at DateTime @default(now())
  updated_at DateTime @updatedAt
}

model Project {
  @@map("projects")

  id String @id @default(uuid())
  name String

  owner User @relation(fields: [owner_id], references: [username], onDelete: Cascade)
  owner_id String

  issues Issue[]
  memberships Membership[]

  inserted_at DateTime @default(now())
  updated_at DateTime @updatedAt
}

model Membership {
  @@map("memberships")

  project Project @relation(fields: [project_id], references: [id], onDelete: Cascade)
  project_id String

  user User @relation(fields: [user_id], references: [username], onDelete: Cascade)
  user_id String

  inserted_at DateTime @default(now())

  @@id([project_id, user_id])
}

model Issue {
  @@map("issues")

  id String @id @default(uuid())
  title String
  description String?

  project Project @relation(fields: [project_id], references: [id], onDelete: Cascade)
  project_id String

  comments Comment[]

  inserted_at DateTime @default(now())
  updated_at DateTime @updatedAt
}

model Comment {
  @@map("comments")

  id String @id @default(uuid())
  text String

  author User @relation(fields: [author_id], references: [username], onDelete: Cascade)
  author_id String

  issue Issue @relation(fields: [issue_id], references: [id], onDelete: Cascade)
  issue_id String

  inserted_at DateTime @default(now())
  updated_at DateTime @updatedAt
}

Using your migrations framework

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

Ecto

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

Laravel

With Laravel you can use the statement method on the DB facade.

First, create a migration:

php artisan make:migration electrify_items

Then use DB::statement in the up function:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration {
  public function up(): void {
    DB::statement("ALTER TABLE items ENABLE ELECTRIC");
  }
};

Prisma

With Prisma you customize a migration to include an unsupported feature.

First, use the --create-only flag to generate a new migration without applying it:

npx prisma migrate dev --create-only

Open the generated migration.sql file and add the electrify call:

ALTER TABLE items ENABLE ELECTRIC;

Apply the migration:

npx prisma migrate dev

Rails

With Rails you can execute SQL in the change method of your migration class.

First, create a migration:

rails generate migration ElectrifyItems

Then e.g.:

class ElectrifyItems < ActiveRecord::Migration[7.0]
  def change
    execute "ALTER TABLE items ENABLE ELECTRIC"
  end
end

SQLAlchemy

With SQLAlchemy/Alembic you can use the Operations.execute method.

First, create a migration:

alembic revision -m "electrify items"

Then execute the SQL in the upgrade function:

# ... docstring and revision identifiers ...

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.execute('ALTER TABLE items ENABLE ELECTRIC')

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.