Using an alternative migration engine

[Minivera]

Hi,

I don't like writing SQL that much for migrations personally and I have been very successful recently with writing migrations using Prisma or, more specifically for this question, with Atlas. I was wondering if there was any built-in way to "hook" into the migration process and have it run migrations generated with Atlas without necessarily disabling the SQL migrations. If not, is Encore potentially using Terraform behind the scenes for migration? I could also run the official Atlas Terraform Provider as part of the Encore DevOps process (Which would be a very cool idea by the way, if we could run out own DevOps alongside Encore deploys 🤔 )

Thank you!

[Minivera]

I ended up finding a good way of generating encore migration files from atlas and using a service's database as the diff source, which works really well without moving away from the great tools given by Encore. Is this something you'd like me to share as a guide in the "How-to Guides" part of the documentation?

[eandre]

Understanding the structure of the database is critically important for many of the future improvements we want to make to Encore, like improving the developer experience around writing database queries, providing support for GraphQL, and better application observability. The current way of handling migrations doesn't really live up to that, as you point out.

For those reasons improving how Encore handles database migrations has been a long-standing goal, that we haven't quite gotten around to just yet. Like you pointed out, SQL for migrations has a lot of downsides. It's almost impossible to parse, is nobody's favorite language, and is highly specific to relational databases. As it is today it's a big blocker for Encore's understanding of the application's data model.

We'd like to take a stab at solving these issues in a way that dramatically improves the developer experience while also improving Encore's understanding of the database structure, to pave the way for future improvements. However doing so by adding support for other migration engines would be a bit detrimental to the whole effort as it would make the migration to the improved approach more difficult, and would increase the surface area that we'd have to support.

Since you found a good workaround that lets you use other tools in a way that is compatible with Encore's migration support it would be amazing if you wanted to contribute that as a how-to guide!

[Minivera]

However doing so by adding support for other migration engines would be a bit detrimental to the whole effort as it would make the migration to the improved approach more difficult, and would increase the surface area that we'd have to support.

That makes a lot of sense, there's a lot of very cool stuff you can do with database analysis that tools like Hasura provide (In my case, I think query caching and automatic index creation would be huge). I wonder is using something like Atlas' DDL or the Prisma language (Whatever it's called) without the engines powering them could be an interesting way of getting static analysis of a database structure 🤔 But I'm just theorizing here, I look forward to seeing what you end up doing!

Thanks for the detailed answer, I'll get a tutorial ready and create a PR soon.