Hassle-Free Database Migrations with Prisma Migrate(prisma.io)
prisma.io
Hassle-Free Database Migrations with Prisma Migrate
https://www.prisma.io/blog/prisma-migrate-ga-b5eno5g08d0b
27 comments
Perhaps I'm missing something, but I don't see the functionality to rollback a migration?
Database schema is not actually the most important thing i need to be a productive programmer.
What i need more is a smart ORM which gives me tool to calculate dependent fields based on other fields changes, then automatically store it on a field. Secondly, it should provide smart many2many relationship with smart caching techniques.
Those are features that prevents me to continue with RoR.
What i need more is a smart ORM which gives me tool to calculate dependent fields based on other fields changes, then automatically store it on a field. Secondly, it should provide smart many2many relationship with smart caching techniques.
Those are features that prevents me to continue with RoR.
Hi there,
I think Prisma might cover "smart many2many relationships". What exactly do you mean by smart caching techniques? Prisma is applying the dataloader pattern from GraphQL automatically under the hood, which we find to be a much easier thing to reason about compared to all the complexity of multi-tiered caching in Hibernate.
The dependent field requirement is super interesting, and something we will tackle in the future based on Change Data Capture. With Prisma you will be able to write a simple declarative data transformation, and the system will automatically keep the field up-to-date, even if the database is changed by another application, or manually by a user.
Hope that makes sense? :-)
I think Prisma might cover "smart many2many relationships". What exactly do you mean by smart caching techniques? Prisma is applying the dataloader pattern from GraphQL automatically under the hood, which we find to be a much easier thing to reason about compared to all the complexity of multi-tiered caching in Hibernate.
The dependent field requirement is super interesting, and something we will tackle in the future based on Change Data Capture. With Prisma you will be able to write a simple declarative data transformation, and the system will automatically keep the field up-to-date, even if the database is changed by another application, or manually by a user.
Hope that makes sense? :-)
[deleted]
> What i need more is a smart ORM which gives me tool to calculate dependent fields based on other fields changes, then automatically store it on a field.
Is the ORM really the layer where you want those features? Because at the RDBMS layer you have views, materialized views, and triggers for that.
If you really want this at the ORM level, you can do it in RoR with e.g. model callbacks or custom attribute writers.
> Secondly, it should provide smart many2many relationship with smart caching techniques. Those are features that prevents me to continue with RoR.
RoR's many2many associations already do caching, though.
I don't see what you're lacking with RoR on either point.
Is the ORM really the layer where you want those features? Because at the RDBMS layer you have views, materialized views, and triggers for that.
If you really want this at the ORM level, you can do it in RoR with e.g. model callbacks or custom attribute writers.
> Secondly, it should provide smart many2many relationship with smart caching techniques. Those are features that prevents me to continue with RoR.
RoR's many2many associations already do caching, though.
I don't see what you're lacking with RoR on either point.
Congrats on releasing this. I'm a big believer in declarative schema management. Migrate looks great, and I like how it allows autopilot, but that workflow step to inspect the generated migration AND to alter it, if it's incorrect or sub-optimal. This looks like a great tool.
We've been working on a similar tool that we've since donated to the CNCF and is now a Sandbox project (https://schemahero.io) that uses YAML to define a schema, and it's controlled by a Kubernetes operator. We realized that version control for the schema is great, and then you get GitOps integration so you can easily deploy and rollback. One of the compelling values of YAML for us was a) Kubernetes, so it's a default and b) Open Policy Agent can start to introduce a policies.
Prsima Migrate looks great. I think we'll see more tools like this as folks want to version control and implement policies and store metadata around their schemas to tightly couple the deployment to the app version.
We've been working on a similar tool that we've since donated to the CNCF and is now a Sandbox project (https://schemahero.io) that uses YAML to define a schema, and it's controlled by a Kubernetes operator. We realized that version control for the schema is great, and then you get GitOps integration so you can easily deploy and rollback. One of the compelling values of YAML for us was a) Kubernetes, so it's a default and b) Open Policy Agent can start to introduce a policies.
Prsima Migrate looks great. I think we'll see more tools like this as folks want to version control and implement policies and store metadata around their schemas to tightly couple the deployment to the app version.
Thank you!
SchemaHero looks super interesting. I'll check with the team to make sure they take a look. Introducing a Kubernetes operator is pretty ambitious.
SchemaHero looks super interesting. I'll check with the team to make sure they take a look. Introducing a Kubernetes operator is pretty ambitious.
What I need from migrations is collaboration.
When I make a schema migration locally and push it to the repository, then 3 other PRs with their own migrations are being merged before mine, it is very likely that my local database will be in the wrong state.
Correct me if I'm wrong, but using an ORM (be it Django, Prisma, liquibase, ...) to handle your migrations will only generate the SQL queries for you, it won't handle "migration history merges" which is what you'll most likely need.
Your schema may be versioned in your repository, but not your data.
Therefore, you still need to coordinate your developers when database change are needed. And you'll most likely need to rm -rf your database locally and recreate it.
Unfortunately, that is not an easy problem to solve. Fortunately, this should not be a problem on production environments (where their history is more linear).
I'd be curious of any work on that subject.
When I make a schema migration locally and push it to the repository, then 3 other PRs with their own migrations are being merged before mine, it is very likely that my local database will be in the wrong state.
Correct me if I'm wrong, but using an ORM (be it Django, Prisma, liquibase, ...) to handle your migrations will only generate the SQL queries for you, it won't handle "migration history merges" which is what you'll most likely need.
Your schema may be versioned in your repository, but not your data.
Therefore, you still need to coordinate your developers when database change are needed. And you'll most likely need to rm -rf your database locally and recreate it.
Unfortunately, that is not an easy problem to solve. Fortunately, this should not be a problem on production environments (where their history is more linear).
I'd be curious of any work on that subject.
The Prisma Migrate team is thinking about these issues. I'll let them elaborate when they wake up tomorrow :-)
We are taking inspiration from this great writeup on how Github has implemented a workflow that enable their developers to collaborate on Schema Migrations at scale: https://github.blog/2020-02-14-automating-mysql-schema-migra...
We are taking inspiration from this great writeup on how Github has implemented a workflow that enable their developers to collaborate on Schema Migrations at scale: https://github.blog/2020-02-14-automating-mysql-schema-migra...
(prisma migrate team member here)
The development flow in migrate (the `migrate dev`) command is quite pedantic: it will check things like migrations missing from your migrations folder but already applied to the dev database, or migrations that were modified since they were applied (via a checksum) and guide you towards resolving the problem. That can happen because of merges, but even more commonly when you are just switching branches locally, or editing migrations.
We're also looking into ways to integrate with your CI and review process to provide more insight, for example when your branch gets stale and you need to "rebase" your migrations on the migrations from the main branch. It's something we are actively exploring, and we're more than happy to get your feedback and ideas on github/slack :)
My personal view is that _some level_ of consciousness of the migration process (schema and data) will always be required from developers on large enough projects. What we can do is build tools that help people getting their migrations right, but it can't be completely automated. There's a lot more to do, now that we have a stable, production ready foundation.
The development flow in migrate (the `migrate dev`) command is quite pedantic: it will check things like migrations missing from your migrations folder but already applied to the dev database, or migrations that were modified since they were applied (via a checksum) and guide you towards resolving the problem. That can happen because of merges, but even more commonly when you are just switching branches locally, or editing migrations.
We're also looking into ways to integrate with your CI and review process to provide more insight, for example when your branch gets stale and you need to "rebase" your migrations on the migrations from the main branch. It's something we are actively exploring, and we're more than happy to get your feedback and ideas on github/slack :)
My personal view is that _some level_ of consciousness of the migration process (schema and data) will always be required from developers on large enough projects. What we can do is build tools that help people getting their migrations right, but it can't be completely automated. There's a lot more to do, now that we have a stable, production ready foundation.
Thank you for your clarification.
I like to think about a single migration being a function and a migration history multiple functions being composed.
Category theory's main subject (I think) is composition and a lot of good ideas can be taken from there (see this paper: "Functorial Data Migration"[1]).
There is an obvious relationship between a schema and the data, and a migration operates on both. The schema might be versioned, but unless you use a Schema-less database (ie: MongoDB) and handle the data versioning application side, your data is not.
Any document/graph-based database can avoid this problem by assigning a version to your documents (like apiVersion+kind in Kubernetes), then the application should know how to process each version of the data.
In an SQL database, it would require different tables which complexify the data model.
IMHO, in every case, you need to handle some of the logic application side or maintain an history of your data (bye-bye storage space?).
[1] - http://math.mit.edu/~dspivak/informatics/CD-FDM.pdf
I like to think about a single migration being a function and a migration history multiple functions being composed.
Category theory's main subject (I think) is composition and a lot of good ideas can be taken from there (see this paper: "Functorial Data Migration"[1]).
There is an obvious relationship between a schema and the data, and a migration operates on both. The schema might be versioned, but unless you use a Schema-less database (ie: MongoDB) and handle the data versioning application side, your data is not.
Any document/graph-based database can avoid this problem by assigning a version to your documents (like apiVersion+kind in Kubernetes), then the application should know how to process each version of the data.
In an SQL database, it would require different tables which complexify the data model.
IMHO, in every case, you need to handle some of the logic application side or maintain an history of your data (bye-bye storage space?).
[1] - http://math.mit.edu/~dspivak/informatics/CD-FDM.pdf
Ooh it's interesting you mention David Spivak's work, I got interested into it recently. Some of these ideas are being commercialized by https://conexus.com/ (he's a co-founder). You might also be interested by Project Cambria[1] for novel ideas about data and schema migrations based on lenses.
We haven't gone in the fancy direction yet, because we wanted to first have the tool our users told us they were missing - namely a migration tool similar to what other ORMs have, but playing on the strengths of the Prisma schema. But these novel ideas about migrations are definitely something we always read with a lot of interest and love to discuss — I hope we get to implement something radical like that some day.
[1]: https://www.inkandswitch.com/cambria.html
We haven't gone in the fancy direction yet, because we wanted to first have the tool our users told us they were missing - namely a migration tool similar to what other ORMs have, but playing on the strengths of the Prisma schema. But these novel ideas about migrations are definitely something we always read with a lot of interest and love to discuss — I hope we get to implement something radical like that some day.
[1]: https://www.inkandswitch.com/cambria.html
EF 6 had an interesting take on this problem. If two migrations had the same parent (aka we both branched and added a migration), whoever merged second would have to generate a "merge" migration to resolve the conflict. It wasn't the most elegant solution, or even a solution period, but it made the problem explicit. This happened because the full schema model was serialized and encoded into the migration journal table.
This is the actual model of git.
You try to push on a remote after somebody else, you have to create a merge commit.
But it would be nice to have a rebase to keep linearity in your history.
You try to push on a remote after somebody else, you have to create a merge commit.
But it would be nice to have a rebase to keep linearity in your history.
Definitely got me interested, and I know I ought to just use Postgres, but I'm really keen to use openCypher. Would it be possible to hook prisma up to graph dbs?
(bion)-[:loves]->(graphs)
(bion)-[:loves]->(graphs)
Postgres has a graph extension work in progress, that may interest you: https://age.incubator.apache.org/
"AGE is an acronym for A Graph Extension... The goal of the project is to create single storage that can handle both relational and graph model data so that users can use standard ANSI SQL along with openCypher, the Graph query language."
"AGE is an acronym for A Graph Extension... The goal of the project is to create single storage that can handle both relational and graph model data so that users can use standard ANSI SQL along with openCypher, the Graph query language."
Migrations as a service? I feel like I'm going crazy here. It's like we're moving to microservices for not just deployment but our development, but its even worse because now not only does everything have to cross network boundaries, it crosses corporate boundaries. How does anybody expect to write software that lasts when your whole app is dependent on 15 different service providers not selling out and screwing you over to get their VC payday?
More like "Database as a service with integrated tooling for migrations".
Prisma (and Prisma Migrate) can be self hosted, so no corporate boundaries needs to be crossed.
They can run on the same host as your application, so the only overhead is on the loopback interface (localhost).
The SaaS solutions for a young business who does not have the time/skill/resources to deploy/maintain the infrastructure are great.
You should always consider the pros/cons before choosing between a managed or self-hosted solution. And once you scale, you can always go for the self-hosted solution.
Prisma (and Prisma Migrate) can be self hosted, so no corporate boundaries needs to be crossed.
They can run on the same host as your application, so the only overhead is on the loopback interface (localhost).
The SaaS solutions for a young business who does not have the time/skill/resources to deploy/maintain the infrastructure are great.
You should always consider the pros/cons before choosing between a managed or self-hosted solution. And once you scale, you can always go for the self-hosted solution.
> Prisma (and Prisma Migrate) can be self hosted, so no corporate boundaries needs to be crossed.
Just to clarify: Prisma is an open-source ORM [1] that's available via an npm package, so there really isn't a component that needs to be "hosted" here. (You might be referring to Prisma 1 which came with its own DB proxy server that was run via Docker, but Prisma 2 [2] doesn't have this Docker container any more and is "just" an npm library).
[1] https://github.com/prisma/prisma
[2] https://www.prisma.io/blog/announcing-prisma-2-n0v98rzc8br1
Just to clarify: Prisma is an open-source ORM [1] that's available via an npm package, so there really isn't a component that needs to be "hosted" here. (You might be referring to Prisma 1 which came with its own DB proxy server that was run via Docker, but Prisma 2 [2] doesn't have this Docker container any more and is "just" an npm library).
[1] https://github.com/prisma/prisma
[2] https://www.prisma.io/blog/announcing-prisma-2-n0v98rzc8br1
Yes I was referring to Prisma 1, never looked in details to Prisma 2 because we moved to Hasura before Prisma 2 got released.
I loved Prisma 1, and probably would love Prisma 2 too, but Hasura was enabling us to query already existing databases without rewriting the schema (and having to maintain it), which was a necessity at the time.
I loved Prisma 1, and probably would love Prisma 2 too, but Hasura was enabling us to query already existing databases without rewriting the schema (and having to maintain it), which was a necessity at the time.
Ah I see, that makes a lot of sense, thanks for clarifying :) Prisma 2 actually is quite different compared to Prisma 1 as there's no native GraphQL layer any more and it's now pretty much "just an ORM".
Nikolas from the Prisma team here. Could you elaborate what you mean with "as a service" in this context exactly?
Prisma Migrate is entirely open source and works via a CLI. My understanding of "X as a service" typically includes some web service layer which is why I have a hard time following what you mean with your comment exactly.
Prisma Migrate is entirely open source and works via a CLI. My understanding of "X as a service" typically includes some web service layer which is why I have a hard time following what you mean with your comment exactly.
This looks similar to Django's migration system. There you change your model, and issue a "makemigrations"-command. Django then automatically generates python code that you run to change your database to sync with your model. You can commit that file and get it under version control. It has support for history merges so that it works collaboratively.
More details here: https://docs.djangoproject.com/en/3.1/topics/migrations/
More details here: https://docs.djangoproject.com/en/3.1/topics/migrations/
I have yet to see a database migration system as elegant and easy to use as the one that comes with Rails.
Check out: https://github.com/thuss/standalone-migrations
Check out: https://github.com/thuss/standalone-migrations
I just recently started using Prisma for a work project and so far I am very pleasantly surprised. I don't think I'll ever want to use something like TypeORM or similar again.
We also used their Nexus library to set up a graphql server, and that also worked pretty well. There is a library which bridges the gap between prisma and nexus, making it super easy to set up CRUD operations, but it seems we started using prisma right in the middle of them totally revamping that library.
The fact that the prisma developers are aware of technical debt to the point that they are able and willing to totally ice a project in order to redesign it properly is also something I like, because sometimes that is really what you need to do, but most aren't willing to do. Naturally, this can cause some friction, but I work in an industry where I regularly have to work with horrible libraries and software written by developers that don't care and just keep on piling up crap on top of their already huge pile of crap.
Congrats and thanks for all the hard work.
We also used their Nexus library to set up a graphql server, and that also worked pretty well. There is a library which bridges the gap between prisma and nexus, making it super easy to set up CRUD operations, but it seems we started using prisma right in the middle of them totally revamping that library.
The fact that the prisma developers are aware of technical debt to the point that they are able and willing to totally ice a project in order to redesign it properly is also something I like, because sometimes that is really what you need to do, but most aren't willing to do. Naturally, this can cause some friction, but I work in an industry where I regularly have to work with horrible libraries and software written by developers that don't care and just keep on piling up crap on top of their already huge pile of crap.
Congrats and thanks for all the hard work.
Prisma needs to make the Hasura-style automatic API generation easier. Currently it requires too much setup and moving parts.
We have been using liquibase[1] in a variety of java & non-java projects and it has proven to be a really flexible & powerful open source solution.
It shines really well when you need to manage not just tables and views, but also stored procedures, custom types, triggers etc.
[1] https://www.liquibase.org/
It shines really well when you need to manage not just tables and views, but also stored procedures, custom types, triggers etc.
[1] https://www.liquibase.org/