Hacker News Reader

Comment by code_biologist | Hacker News Reader

How do people deploy this stuff declaratively, especially cleaning up old functions? I'm still surprised something terraform-esque doesn't exist.

Bunch of SQL scripts with "CREATE OR REPLACE" and cross your fingers? Make a schema that a script can wholly own, then blow the whole thing away and reinitialize in a transaction?

View on HN

I don't say this from experience but DDLs in postgres are transactional so I don't understand the problem with wrapping create or replaces in a transaction with no need to cross your fingers.

The transaction behavior is fine, it's more that I've had people delete function declarations from scripts while never cleaning them up in the data warehouse and then you end up with old functions kicking around in prod. My use case is mostly warehousing, not application stuff, so I don't care about full blown migrations. Just "these are the functions that we have declared for use, no more no less".

It seems like with DBT we're getting to declarative table construction, but I'm looking forward to declarative maintenance of other data warehouse aspects.

> it's more that I've had people delete function declarations from scripts while never cleaning them up in the data warehouse

Ah that makes sense. I guess you could hack around that by having a script reading all your declarations that are checked into code and removing any that exist in the db that aren't in that list, or something.

You can use database schema compare tools for this. You create a clean empty database using the new DDL and use the tool to compare that schema with the schema for the version in test or prod. The tool will then show the differences and can create a sql script to update the database in test or prod.

I've used this a lot with SQL Server and used the Redgate tools for that and that worked very well. For PostgreSQL, it seems there's a free tool from devart: dbForge Schema Compare [0]. I've never used it and have no idea how well it works...

[0]: https://www.devart.com/dbforge/postgresql/schemacompare/down...

I personally use Sqitch to manage database migrations. Makes it very easy to set up test databases for all development work.

I use this https://github.com/golang-migrate/migrate in a deploy step to each environment with the upgrade / downgrade scripts committed to the repo alongside the code. The scripts can do pretty much anything you need to do in PG including defining and executing functions.

Reply on news.ycombinator.com