Graphql-engine: Add flag to generate migrations in SQL instead of YAML

[tmattio]

It would be great to have a flag in the console to save the migrations in SQL instead of YAML.

I want my migrations in SQL and it seems that my only option currently is to either create my tables with the SQL panel, or create the migrations manually.

[karuppiah7890]

I would like to give this a shot. Will ping here about my progress. Also, a heads up, I don't know Haskell. But I think I can learn what's necessary and do it on the go. 😁

[karuppiah7890]

Okay, I want to understand the issue in a better manner 😅 I'm able to create migrations - sql file along with some metadata yaml file, from an existing server with tables. And then I'm able to apply it too, to a new server. Is this an old issue that's not closed?

@tmattio Could you explain the workflow you expect and what you are trying to achieve? cc @shark-h

[karuppiah7890]

Okay, sorry 😅 I'm not an expert with databases or migrations. But with some experimenting I have guessed the expectation over here. So, when I use the command hasura migrate create "name", it creates two yaml files, one for up (apply) and one for down (rollback). There are no sql files. And you want sql files. Right? And you want to be able to apply the migration from cli with the sql files. I'm trying that out now

[karuppiah7890]

I finally read the docs instead of just experimenting with the CLI and the DB. According to the docs, sql files are just for user's convenience, and that SQL can be written into the yaml files. Looking at this issue, looks like you want to have the sql files (for using with other tools too?) so I was thinking about what has to be considered while doing such a thing. Some things to note were -

1. Just sql affects only the database schema. But if you want to track the new table/function/view that you add and show it in the graphql query results, you need to add some metadata using yaml files
2. There's already a --sql-from-file option for hasura migrate create that takes a sql file path creates a up action with it. So, it's possible to create a up action sql file and then for down, you can copy paste file name and change up to down and write down the rollback strategy sql in it

Currently, when no options are given for hasura migrate create, two yaml files with empty arrays are created. I figured an extra thing that can be done for the above issue is - create two sql files too (along with yaml files), when --sql option is mentioned. Let me know if it makes sense. I'll raise a PR with this solution. I would be willing to discuss more on this 👍 @tmattio @shark-h Do review and let me know what you had in mind for this issue and what were your thoughts on the solution(s)

[karuppiah7890]

Raised a PR for this 😄 cc @shahidhk

[shahidhk]

With 1.2.0-beta.1 release, we're introducing a new workflow for the CLI which will always create only SQL files while using the migrate create command.