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;
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
- Prisma
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
);
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
- Laravel
- Prisma
- Rails
- SQLAlchemy
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
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");
}
};
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
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
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.