Add a diff command

[khaelys]

It would be nice to have a diff command between run migration and actual database status. Ideally maybe something to generating the json to reach the target database state.

[gulcin]

Ciao @khaelys 👋🏼

I recently joined the pgroll team and have been going through long-open issues. I’d love to understand if this is still something you’re interested in! Could you share more about why diffing would be helpful for you and the specific use cases you have in mind?

Grazie! 🙌

[michaelschufi]

Hi @gulcin

I'll try to give a bit of insights into this.

Context: We are using drizzle-orm as ORM of choice (for many reasons). For drizzle we define our schema using the orm's functions.

Task

Create a new feature. This feature requires a new table in the database. The schema changes:

1. Create a new table with a few columns
2. Some time later: Rename a column

DX

There are of course many ways to do migrations and schema changes. However, I found this to be the least friction for me and my colleagues. Mainly because a dev doesn't have to think about DB migrations as much as with other workflows.

DX with Drizzle only

0. Create a new db branch
1. Extend the schema in the code
2. Run drizzle-kit push to push the changes to the database
3. Code the feature and realize a column renaming is required
4. Update the schema in the code
5. Run drizzle-kit push to push the changes to the database
6. Finally, run drizzle-kit generate to generate a migration file based on the previous migration

This is a really nice DX. You can quickly iterate on the schema in the code and push the changes to the database. Also the commands are optionless, making it very straightforward to work with.

Only at the end, when you are happy with the schema, you generate a migration file to later run the migration on the original db branch.

What pgroll would need

To replace the drizzle-kit push command, we would need to have a command that diffs the current state of the database with the current state of the schema defined in the code. Since pgroll doesn't talk "drizzle-orm", pgroll would need to have the ability to diff two sql drizzle-kit export files.

Now, I'm not sure if this is something that you guys are planning to implement. Diffing two sql files is a bit odd after all. Maybe there will be a way to diff two pgroll migration files? With pgroll convert we can convert the sql files to pgroll migration files after all. Still I can imagine the workflow being cumbersome. It would likely need a script that first runs drizzle, then pgroll diff, then a migration.

Conclusion

It would probably make a bite more sense to have a pgroll diff command actually diff db schemas (i.e. two different databases / db branches) and generate a migration file based on that.

This would then replace the last step of the drizzle workflow. I.e. making a single migration based on the diff of two databases. But this also means that the drizzle-kit push would be nice to have for every step before that.

Edit I've written about something related here: https://github.com/xataio/pgroll/issues/249#issuecomment-2514579566

[gulcin]

Hi @michaelschufi thanks so much for laying out your workflow in detail and sharing your insights, this is exactly the sort of real‑world feedback we need!

Your ideal DX is to be able to iterate schema changes in code first and have those changes applied immediately to your dev database, just like drizzle-kit push and only generate a one‑off migration at the end. I agree that a pgroll diff that works directly against two live databases (or two branches of the same database) would give you exactly that. That would let you iterate in-place, just like drizzle-kit push.

You’re right that diffing two raw SQL files exported by Drizzle is brittle. Instead, if you first converted those exports into pgroll migrations then diffed the resulting migrations, you’d get closer but it’s still two steps too many. A native “DB‑to‑DB diff” command is a cleaner, single‑step solution.

We're building a new Xata platform that will let you diff your feature branch against main and generate a single migration file covering all your changes. That’s exactly the “one‑step” diff experience we’re aiming to deliver.

I’ll keep you posted here as we nail down the design and roll out updates!

[michaelschufi]

Thank you. It's great to hear that the feedback is appreciated 😊

Yes, exactly. I don't think there's anything that has been misunderstood. 👍 I'm looking forward to the new Xata features. It sounds exciting!

In the meantime, I would just like to mention that drizzle-kit push is currently not working due to a strange permission issue (it has worked last summer). cmck has already brought it up with the team, but since it is relevant to this DX, you might want to check it out. Some details are here: https://discord.com/channels/996791218879086662/1332276361028698234/1367084977891708990

[khaelys]

Hi @gulcin, I'm sorry for the late answer, but I forgot exactly what I had in mind two years ago. Here's what it comes to my mind now:

• Detect schema drift between 2 databases.
• Another option is mentioned above in more detail by @michaelschufi , sometimes I manually change my database locally and then I would like to automatically have the migrations being generated at the end.