Prisma shadow database is incompatible with the electric proxy

[gorbak25]

I'm struggling to generate new db migrations using prisma.

node@da54a5ab2d97 ~/workspace (main) [1]> yarn prisma migrate dev --create-only --schema prisma/electric-proxy/schema.prisma
yarn run v1.22.19
$ /home/node/workspace/node_modules/.bin/prisma migrate dev --create-only --schema prisma/electric-proxy/schema.prisma
Environment variables loaded from .env
Prisma schema loaded from prisma/electric-proxy/schema.prisma
Datasource "db": PostgreSQL database "sheets", schema "public" at "electric:65432"

Error: P3006

Migration `20230923050414_init` failed to apply cleanly to the shadow database. 
Error:
ERROR: Cannot alter column "" of electrified table "public"."Column"
   0: schema_core::state::DevDiagnostic
             at schema-engine/core/src/state.rs:270

error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.

The problem is that the electric proxy thinks that the shadow database is the main database. I'm right now trying to find a workaround for generating migrations.

[balegas]

Raised an issue internally to investigate this

[kevin-dp]

This may be related to this issue shared on Discord: https://discord.com/channels/933657521581858818/1165673468512182282

As @alco noted there:

Prisma resets the database before it runs any migrations, undoing Electric's initialization.

Could this be the issue?
Baselining may solve this problem: https://www.prisma.io/docs/guides/migrate/developing-with-prisma-migrate/baselining

[gorbak25]

@kevin-dp Prisma never deletes/resets your database when running migrations, the error message which prisma gives may cause you to believe that but that's not what is actually happening. I see there is a misunderstanding on how prisma works under the hood. Let me give you a quick rundown on prisma so you may implement proper electric-proxy support(so prisma migrate dev just works without hacks or a high pain tolerance).

Prisma when tasked with generating migrations needs to answer the question "ok what changes do I need to make so the db is in the desired state". The naive approach would be to look at the current migrations and see what changes to the schema were made and generate a new migration based on the difference. This unfortunately has an issue, it doesn't protect you against schema drift. Let's say before generating the migrations i went to the db and added an index on a table and forgot to write an migration for it. Prisma would have no idea that index exists and may generate invalid migrations! This problem is called schema drift.

To protect and properly handle schema drift prisma uses a so called shadow database. Before doing anything prisma creates a NEW DATABASE, applies all existing migrations to the shadow database and then dumps the schema from the shadow database and the target database. Those schemas are getting diffed so prisma may detect schema drift and act accordingly.

The shadow database is built into prisma and can't be disabled. Half of the prisma commands require it :)

Now let's fire up wireshark and see how this works in practice. First set up wireshark to capture traffic on the electric proxy

And try to generate a migration using prisma

Now let's analyze the tcp streams

We see that prisma made 3 connections to the db

Let's see what data was sent over the wire on those tcp streams

On the first connection prisma checks if the creds are ok, inspects if the target schema exists and checks the version of postgres

On the second connection prisma introspects the existing db schema and creates the SHADOW database

Now the third connection is where the problem is, the previous 2 connections were made to the desired target database. The third one is made to the SHADOW database

Prisma then proceeds to apply all existing migrations to the shadow database. The problem is that electric-proxy sends the queries to the target database and not to the shadow database :( Also if electric-proxy sees DDLX on the shadow database connections it thinks it should act on it.
Generally the creation of the shadow database fails because electric thinks it should talk to the main database.

To properly support prisma electric-proxy should understand that when a connection is made to prisma_migrate_shadow_db_* then it should:

1. proxy queries to the shadow db, not the main db
2. ignore all DDLX ever

With regards to https://discord.com/channels/933657521581858818/1165673468512182282 you came to the wrong conclusion there. Electric was created, installed and available in the main database but not in the shadow database. I also had this issue and that's why I had #651 (comment) in my migrations which checked if the electric schema exists before trying to electrify the tables. Otherwise prisma was unable to create the shadow database.

[kevin-dp]

@gorbak25 Thanks, that's a very clear explanation of how Prisma migrations work and why it fails on the current proxy.

[linear]

VAX-1370 Prisma shadow database is incompatible with the electric proxy

[magnetised]

@gorbak25 Have just merged #769 that hopefully provides support for shadow tables. Please try again with main and feel free to re-open this if you're still having trouble

[alco]

Sidenote: every push to main publishes an updated electricsql/electric:canary image to Docker Hub. That should give you an easier way to try it out compared to building from source.