[FEATURE] Support local-first Postgres with ElectricSQL

[guillempuche]

Describe what you want

ElectricSQL https://electric-sql.com/ is a growing open source reactive, realtime, local-first apps directly on Postgres.

Drizzle ORM's migrate (the one I'm using 'drizzle-orm/postgres-js/migrator') can't handle "electrifying" of tables in .sql files.

Steps:

1. I generate the migration file 0000_xxx.sql with yarn drizzle-kit generate:pg --config=./database/drizzle.config.ts.

2. I add manually the electrifiying of tables https://electric-sql.com/docs/usage/data-modelling/migrations to .sql generated file.

   ...
   -- After all table alterations are complete, enable Electric SQL
   ALTER TABLE quotes ENABLE ELECTRIC;
   ALTER TABLE authors ENABLE ELECTRIC;
   ALTER TABLE collections ENABLE ELECTRIC;

3. Run Drizzle migration yarn drizzle-kit generate:pg --config=./database/drizzle.config.ts

4. Error. See terminal logs:

...
{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P07',
  message: 'relation "quotes" already exists, skipping',
  file: 'parse_utilcmd.c',
  line: '207',
  routine: 'transformCreateStmt'
}
Migrations failed! PostgresError: syntax error at or near "ELECTRIC"
    at ErrorResponse (file:///Users/guillem/.yarn/berry/cache/postgres-npm-3.4.3-eca46b47d6-10c0.zip/node_modules/postgres/src/connection.js:790:26)
    at handle (file:///Users/guillem/.yarn/berry/cache/postgres-npm-3.4.3-eca46b47d6-10c0.zip/node_modules/postgres/src/connection.js:476:6)
    at Socket.data (file:///Users/guillem/.yarn/berry/cache/postgres-npm-3.4.3-eca46b47d6-10c0.zip/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:518:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Socket.Readable.push (node:internal/streams/readable:390:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42601',
  position: '309',
  file: 'scan.l',
  line: '1176',
  routine: 'scanner_yyerror'
}

[thruflo]

Are you applying the migration via the Electric migration proxy?

https://electric-sql.com/docs/usage/data-modelling/migrations#migrations-proxy

If not, the DDLX syntax will not be supported.

[guillempuche]

Good advice James!

Indeed, the guide says:

If migrations do use DDLX syntax or do affect the electrified part of your schema, then they must be applied via the proxy. If not, they will be rejected and an error will be raised.

[TODO] When finish this database code, I'll open source in this thread, and close the thread if necessary.

[Nick-Lucas]

One immediate issue I hit was that enums are wrapped in DO..END blocks and they're not supported

https://github.com/electric-sql/electric/issues/651

[mianala]

https://github.com/electric-sql/electric/issues/1492

I’d love to contribute to this effort, but I’m not sure where to start or if I’m currently equipped to help effectively. Any guidance on the initial steps or how I might best contribute would be greatly appreciated.