Postgraphile: Using GraphQL Schema to generate Postgres migrations

[flux627]

We are in early stages for planning the architecture for our startup, and I am evaluating this as an option. Something that occurred to me while reading this:

https://www.graphile.org/postgraphile/postgresql-schema-design/

Since there is such a close relationship to how schemas are defined in Postgres and how they map to the generated GraphQL schema, it seems to me that it would be a big simplification (in terms of developer experience) if we could programmatically generate Postgres migration files from a human-generated GraphQL Schema, rather than the other way around. This would allow for an easier "GraphQL First" experience, could open up possibilities for extending functionality through custom directives, and even pave the way for Django's South-style automatic creation of migrations. Does this resonate with the goals of this project?

Please excuse me if this is completely off-base, as all this is still a bit new to me. Input appreciated.

[benjie]

This is something I've thought about a bit (in fact I have pages of notes on it... migrations are definitely a pain point!) but there's a lot of complexity in it - e.g. Changing the data type of a column or renaming a column vs deleting and recreating the column. We'd also need companion code for function bodies/etc. Another thing that's problematic is complex indexes; I've considered doing them with directives, but by the time you're doing that you may as well be writing the raw SQL.

I'm definitely interested in seeing a tool that generates a PostgreSQL schema from a GraphQL schema, but I think it'd probably only be super useful for bootstrapping and not for migrations thereafter without a lot of careful thought, design and tests.

If this were to be part of the PostGraphQL suite, it would be a separate package rather than being built in to postgraphql itself, I think, unless there was a very good reason it needs to live in the same package.

[flux627]

Thanks for the response. I agree that it would be best to be part of a separate repo at least at first (just like South was with Django). The reason why it could be beneficial to be part of this main repo would be to more easily coordinate compatibility / feature changes, but I can also understand how that may be an unwanted burden.

I'd imagine one would be able to utilize a lot of the thinking that has gone into South into making something like this. In the remove or update case you described, the createmigrations cli command actually asks you questions in those kind of ambiguous cases to help determine what you'd like to do. Sometimes you have to dance a bit with it by making changes incrementally, but in worse case scenarios you can simply opt-out of autogeneration and write your own (though I've found this to be quite rare).

For complex indices, I think directives are just fine. Overall, the amount of imperative code you're saved from needing to write is worth it, I think.

Deprecation directives could also be used for hinting in some cases, as well as keep the code self-documenting.

This may be something my team looks into developing in the future. I'll update this issue if we end up moving in that direction.

[benjie]

Awesome; I'd love a tool like this 👍

[micimize]

One route towards this would be to use graphql-code-generator to generate sqlalchemy models, then use that to autogenerate migrations, only using postgraphile to run the server itself.

Very appealing prospect, but pretty disjoint from postgraphile's own design goals

[kron4eg]

Tables definitions are only like 15% of my migrations suite for example, and it's still pretty simple. I don't think it will greatly help to generate migrations from graphql schema. Some very-very simple (create table, alter table statements) and that's it. Which is trivial actually.

There is graphcool for graphql-schema->database migrations workflow.

I think the power of postgraphile is in opposite workflow database migrations->graphql-schema.

P.S.
Both are valid, it's just matter of taste and knowledge of PostgreSQL.

[benjie]

Yeah, about 80% of my icebergs are under the water too 👍

(Only about 20% of my schema is exposed via GraphQL)

[hmaurer]

Should this be closed? It seems pretty clear that this is out of scope of the project.

[benjie]

👍

[bjm88]

prisma.io is doing graphQL to database auto migrations, no SQL, very nice dev experience to focus on graphql SDL and api. One issue is right now it seems to always do full join tables, even for 1to1 or 1toM when they are only really needed for Many to Many.

[benjie]

Yeah, I’ve used that. It’s very easy to “corrupt” your database with prisma migrations, I did it by accident in my first 20 minutes of using Prisma and it took me a while to realise why the data was no longer being returned by the queries. Once in that broken state I could not determine a way to solve it, even with the help of their instructor and a few follow-up migrations, so I just reset the database. This just goes to show how complex and error-prone doing migrations from a GraphQL schema can be - a tool that does it would have to be extremely well tested and reliable before I let it anywhere near my production database!