pgroll usage with object relational mappers (ORM)

[deeprobin]

pgroll looks very interesting to me. It solves a persistent problem.

I usually use EFCore in the .NET environment as ORM. Also in the Node environment there are ORMs like typeorm, mikroorm & more.

Is there a good approach here that works well with pgroll?

[endigma]

+1 for entgo support

[exekias]

Thank you for opening the first issue from the community! 🌟

We want to look into supporting ORMs to automatically integrate with pgroll, the kind of integration we are looking into is for the ORMs that execute migrations on their own, we could potentially integrate pgroll so that instead of directly executing the migration against Postgres, they would use pgroll for that job.

As of today, things should work by following a few steps, in my mind this is the workflow you would need to leverage pgroll together with an ORM like EFCore & friends, moving the migration execution to pgroll instead of having it managed by the ORM:

1. Configure your ORM/client to use pgroll version schemas, this is explained in the docs, for some clients this could also be just adding a parameter to the connection string
2. Define & start a migration with pgroll
3. Adapt your app to use the new schema
   • Update the schema definitions for your ORM
   • Update the client to use the new version schema created by pgroll start
4. Deploy your new version of the app using the new schema
5. Once the deploy is done and there are no older versions running, call pgroll complete so the only schema available is the new one

[ben-pr-p]

What do you think about creating a pgroll-rest server, distributed as a Docker container with an OpenAPI API?

You could do HTTP requests to get the current schema name to use, submit migrations, rollback, etc.

Translation the migration object specification to OpenAPI would also make it easy to create translation layers for other languages.

[endigma]

Another server to run in docker just to migrate my DB? I think having it not be basically in-place replacement for other migration tools would basically kill adoption.

[ben-pr-p]

I see the options as:

1. Go FFI from pgroll to runtime of choice (hard, creates extra unique complications per each new environment)
2. Expect pgroll binary to be on deployed system and interact via sys call (requires users to customize their own Docker container and install an extra binary)
3. Interact over the network and deploy another container

Definitely not ideal, but I think 1 and 2 are worse for adoption, most likely.

[endigma]

Well you don't necessarily have to instrument the pgroll binary itself, it takes schema no? Lots of ORMs have code for generating migrations in SQL, they could likely support similar facilities for generating the pgroll JSON, which could then be run externally.

[exekias]

Wondering if it will help here, next release will bring an official docker image for pgroll: https://github.com/xataio/pgroll/pull/209

[ben-pr-p]

@exekias how are you imagining the Docker image will help?

I think we either need the Docker image to have a REST/HTTP API, or we need an easy way to install the pgroll binary into images built for deploying Node/Python/Ruby, etc.

[endigma]

Yeah a docker image doesn't really make pgroll programmable