[xmatos]

I like the idea of a tool like this, but I'm not sure it's a better process than having migration scripts. And I dislike migration scripts.

To me, the perfect process would be, using an MVC web framework as an example, to generate a temp schema from models and run a schema diff tool like this, to update the destination database. That would eliminate migration scripts, but would probably slow down the db sync process, once your db start getting bigger.

Now, if you're not using an ORM, I think it's a bad idea to use a tool like this to update a production db from development. It will work for a single dev, but not for a team project, since you'd need a common dev schema that would be used as a source for the diff, and that will get clogged quickly.

It's still a nice tool to find and update small inconsistencies between different environments. Nice work!

[stephenr]

I’ve been using a couple of python tools wrapped in two shell scripts to achieve what I think works well:

migration-capture.sh (using https://github.com/mmatuson/SchemaSync) creates a temporary db using a base schema SQL file, applies previously created migrations, and then creates up/down migration scripts by comparing that to a db that’s been modified manually or by an mvc model tool, etc.

migration-apply.sh (using https://github.com/gabfl/dbschema) applies the “up” scripts that haven’t been applied previously OR runs the “down” scripts corresponding to “up” scripts that have been run previously but are not found in the deployed scripts (ie rolling back a deployment automatically runs the down script for migrations that were in the rolled back versions)

Right now my only concern/goal for improvement with the setup is to replace the python tool the apply script relies on, with a pure shell solution so it’s one less dependency to need to install on production machines.

Edit: spelling typo

Edit2: Wtf is a bass schema.

[GordonS]

I'm working with Postgres using Marten, and it let's you do exactly this. I have a console app that builds a model in memory, compares it to a database, and generates a migration script - it works really well.

I recently tried to replicate this with SQL Server and Entity Framework Core, but trying to reverse engineer a database to a model seems impossible.. I can get close, but there are always some things it gets wrong that need manually fixing... at which point I may as well write the whole damn script by hand anyway. sigh I wish the EF team would just add real support for reverse engineering databases.