search

stripe / pg-schema-diff

Go library for diffing Postgres schemas and generating SQL migrations
MIT License
365 stars 26 forks source link

How do you deal with unsupported changes on the db schema? #169

Closed ghost closed 1 month ago

ghost commented 2 months ago

I couldn't see any mentioning about what you do with SQL statements you do not know how to handle it. I'm thinking of using this to also determine whether there is a noticeable difference, which is why I'd like to know what you do about differences you not yet know how to handle.

Should running this lib be enough to identify all changes on my database, and I'll get the notice that I have to do some things manually, or would I need another software to check whether there are any changes left which this lib does not yet know how to translate into commands?

bplunkett-stripe commented 2 months ago

There a couple options here. If you are not using this for schema migrations:

ghost commented 2 months ago

I wasn't thinking of any particular option - I rather wanted to know your default strategy of handling changes which you don't yet know how to put into SQL commands.

Maybe best if I use pg_dump --schema-only for now and put the expected schema into the repo. Thanks for letting me know.

Would be nice though to have an option in the app which would error out on any not-yet supported feature. I've seen privileges, views and types on the list (https://github.com/stripe/pg-schema-diff?tab=readme-ov-file#unsupported-migrations) - would be nice for those at least to have a parameter on which the execution would error out with a not implemented error - but maybe that has already been your strategy until now, right?

I was just wondering about your default strategy on this - depending on this I'd also create tickets when I discover something unexpected. Do you aim for silently ignoring it or do you want to throw not-implemented errors?

bplunkett-stripe commented 2 months ago

So that's a great question! I think the ideal state is we only error if the underlying property changes. As in, we still support diffing a database that contains unsupported schema but will loudly error if that unsupported schema changes.

This also relates to #170.

ghost commented 2 months ago

That would be perfect!

A bit more about my background: I'm currently using TypeORM in my project, which has all this as a feature, but I want to be more flexible in writing my query, so I was considering moving to kysely, where I can write my database as I want (not generated by objects as in an ORM), but as of now I don't know how to let developers generate a migration script.

I want my project to be self-maintained - means, nobody should have to intervene when releasing a new version.

While developing a new feature, programmers would use this tool to generate a good first draft of a migration script (up and down) and get notified if they are using features which are not yet supported by this tool, so they know they'll have to do some manual work to get those changes also reflected in the migration script.

Additionally I want to use this tool to verify in my build-pipeline that there are no changes to the database after running the migration script.

If these two use-cases are supported (well, for the second I'd write some bash around the command in which I'd expect an empty result or something the like we would agree on, something I can rely on) I'd be more than happy 🎉 but all of this is dependent on your vision on this project @bplunkett-stripe.

bplunkett-stripe commented 1 month ago

Yes, both are totally possible. The diffing aspect (seeing if anything has changed) is an easier problem than generating a "good" schema migration. Obviously, the tooling will miss what it doesn't know about. The tooling right now will either error (ErrNotImplemented) or return an empty schema migration.

So I think in the interim, your script has two scenarios it may encounter for unsupported SQL: either silently failing (no diff found even though there is a diff) or ErrNotImplemented if a diff is found but not migrating is not yet supported. Both behaviors are present in the tool, but the latter behavior is preferable.

Is there a particular set of SQL you are concerned that tooling does not support? (Materialized) views has been the biggest ask so far.

aleclarson commented 1 month ago

Additionally I want to use this tool to verify in my build-pipeline that there are no changes to the database after running the migration script.

I think comparing before/after pg_dump --schema-only output would work for this?

 

P.S. @simon-munit, I'd be curious what you think of pg-nano (still pre-alpha), which uses pg-schema-diff. It may be the best option if you don't mind writing Postgres functions instead of writing SQL in your TS files. If you're interested, you can ask me questions over there :)

ghost commented 1 month ago

@aleclarson true - maybe best to follow this 👍

Thanks for all the time and help so far. I'll keep an eye on this lib and see how I can use it in the future.