Coming from a MS SQL Server environment where the tooling for database development are pretty good, i'm finding it difficult to find decent tooling for maintaining schema changes.

For example if you've ever used Microsoft Sql Server Data Tools in visual studio, you are presented with the entire schema as script files that are used to create tables, views, stored procedures as individual files. When you want to change one of those files you simply open it up make the apropriate changes to the file through a designer if it's a table or view or just a script file for stored procedures and save them. The files get checked out for you and when you commit the changes the version history is kept to the schema as part of the changeset. When you want to take the changes and push them to a database, you simply publish those using the menus and SSDT's determines what changes need pushed and generates the appropriate script to run against the database and runs it. You can use this feature to generate change scripts for major version changes to include in installers or to distribute as you see fit. (ie. v1.1 to v1.2 included in a patch file). The advantage here is that the developers don't need to necessarily know the exact syntax for changing a table or view (although most due) but the heavy lifting of doing the schema comparison and change script generation are done for you. Also i can make lots of changes to the schema and then when I feel it's appropriate push them to the database at my leisure.

From what I've seen, the only comparable tooling for PostgreSQL is SQL Manager for PostgreSQL since it does integrate with source controls and gives you the designer tools to make changes without having to know the exact syntax of the change you're wanting to make. I do have several issues with it though. You have to have a live database available to make the changes to and when you do make those changes, they are immediate AND the get checked into source control right then. That's a big no no in my book. I don't want to check into source control until i've run through the changes and tested them. Also I work on large development teams and checkins are gated (the build has to succeed before the changes are actually commited) and if every change triggers a build it most certainly will break if you are doing something incrementally.

I've looked at MigratorDotNet but that just seems like a big pain in the XXX. Having to write the scripts manually or writing code to change the db which requires an exe to run when the installer runs just seems wrong.

We don't have dedicated DBA's nor are we working on a central development database so that pretty much limits the types of tools we can use.

Does anyone have any good suggestions for how to accomplish the SSDT way of doing development?

Thanks,
Garick