Could this be implemented in PLpgSQL instead of Rust?

[jedwards1211]

I think there are two barriers to adoption in the way this is written:

• it has its own migration format that doesn't seem like we can seamlessly integrate with existing migrations that do arbitrary DDL queries like modify constraints and triggers
• being Rust, we need separate build/deployment code to deploy it alongside our app

I wonder if this is necessary? If there were PLpgSQL functions we could call to perform the various actions, then we could call those functions from our existing migration tools. The actions could be passed to PLpgSQL functions in jsonb format.

[fabianlindfors]

Hi!

I'm designing Reshape to be a full-featured migration tool so by design it's not made to be called from other migration tools. The reason I went with a custom migration format is precisely to avoid arbitrary DDL queries. I want all migrations that Reshape performs to be zero-downtime without the user having to write those queries themselves, which avoids a lot of effort and potential foot guns.

Regarding having Reshape deployed separately, I'm not sure if that can be avoided given how it works. Every migration is a two-phase process: the migration is first started and then completed. In between, you can deploy your application changes gradually without worrying about breakages and the deployment can take however long it needs. This also means that Reshape has to be explicitly built into your deployment process to achieve true zero-downtime. My vision is that Reshape is built into your CI/CD (or other deployment process) and not your application.

Does this make sense? :)

[jedwards1211]

I get what you mean but I still don't understand how it would be impossible to achieve your goals with PLpgSQL functions.

As far as arbitrary DDL queries, it's not like reshape would have to support performing arbitrary DDL via reshape. A SQL migration file could contain whatever DDL statements that Reshape doesn't support along with calls to PLpgSQL functions provided by reshape that only accept operations that reshape supports.

(It seems overambitious to make Reshape be a full-featured migration tool that supports absolutely any possible change one would wish to make to the DB? I mean what about creating and altering domains, enum types, triggers, rules, altering views themselves, creating extensions, migrating Timescale hypertables, etc...)

As far as the two-phase process, assuming that reshape stores migration status in the database (if it doesn't, why not?) is there any reason the deployment process couldn't call PLpgSQL functions to perform migrations, instead of running a Rust executable? Running migrations either way requires access to the db.

[fabianlindfors]

Sorry, should have been a bit more clear. I don't think there is anything stopping Reshape from being implemented in PL/pgSQL. Given the things I mentioned in the previous comment, I believe it's simpler though to have it implemented as a separate client rather than embedded in the database. Simpler both for end users who can use Reshape like most other migration tools (write some files and run a CLI script) and for me as a maintainer as PL/pgSQL is unpleasant and hard to version.

As far as the two-phase process, assuming that reshape stores migration status in the database (if it doesn't, why not?) is there any reason the deployment process couldn't call PLpgSQL functions to perform migrations, instead of running a Rust executable? Running migrations either way requires access to the db.

It does store them in the database so no reason why that wouldn't work but the same reasoning as above applies.

(It seems overambitious to make Reshape be a full-featured migration tool that supports absolutely any possible change one would wish to make to the DB? I mean what about creating and altering domains, enum types, triggers, rules, altering views themselves, creating extensions, etc...)

I aim to eventually support all the changes that actually can be achieved in zero-downtime (enum changes for example). For things where zero-downtime changes can't be automated or are custom in some way, I'm planning on adding an escape hatch to let users run arbitrary SQL queries. Using this escape hatch would of course be at the users own risk.

Ruby on Rails migrations work similar to this with a DSL for most changes and the option to write custom SQL for whatever isn't supported. In their case the DSL isn't strictly necessary though as they don't do much more than run the equivalent DDL directly: https://edgeguides.rubyonrails.org/active_record_migrations.html.

[jedwards1211]

@fabianlindfors well, it's your project of course, for what it's worth I'd be more inclined to use reshape if I have the option of integrating it with my existing migration system. Which would mean something like

SELECT reshape_add_migration('migration_name', $$[
  {
    "actions": {
      "type": "create_table",
      "table": "users",
      "primary_key": "id",
      "children": [
        {
          "actions.columns": {
            "name": "id",
            "type": "INTEGER",
            "generated": "ALWAYS AS IDENTITY"
          },
        },
        {
          "actions.columns": {
            "name": "name",
            "type": "TEXT"
          }
        }
      ]
    }
  },
  {
    "actions": {
      "type": "add_index",
      "table": "users",
      "name": "name_idx",
      "columns: ["name"]
    }
  }
]$$::jsonb);

And then later

SELECT reshape_migrate();
-- ...
SELECT reshape_complete();

[fabianlindfors]

I appreciate the feedback! It would be a bit hard to implement this now with the current design but I'll keep it in mind for the future. It's still early in development so if I hear this from more users, I might change direction :)

[jedwards1211]

Yup! I definitely think it's a neat project, I was just like...hrm, darn, wish it was a bit less of a hassle to integrate with my existing stuff. Though I know you discouraged using it in production

[fabianlindfors]

Thanks! Using it in production right now is definitely not advised but I appreciate anybody willing to take it for a spin and offer some feedback. The more people doing that, the faster Reshape can be production-ready.

If you ever take Reshape for a spin on something else, I'd love to hear your thoughts!