Pgmigrate: Write migrations using plain SQL

I did something similar leveraging SQL table and DB comments. In hindsight a migrations table would've been easier to understand and maintain. Still I could see an incrementing counter working for resource constrained projects.

Do you prepare your rollback snippets in advance? If not, how do you deal with urgent rollbacks? If yes, how do you make sure those snippets work in advance?

Good suggestions.

I didn’t really care about crc checks, my idea is to not modify the files after you’ve submitted them.

The program uses natural sorting, not dependent on filesystem.

It is half-baked because my colleague posted it here while I was working on it for the lulz.

The first four of your points seem to be the sort of things that could be handled based on being strict in your source control. If you enforce naming rules for the files, don't allow for changing the history of the repo and only run migrations in production from source-controlled files then those are not really problems, right? I'm guessing you could even have automatic checking of those rules in CI.

very mature at this stage too, lots of good defaults.

I use sqitch quite heavily on one of our projects, and miss it on the one where we use Ecto migrations with Elixir (although, for in-built migration functionality, Ecto migrations are very good).

Installing sqitch and pgTAP (a unit testing framework for PostgreSQL) has always been a pain, so I ended up making a docker image that we now use at work: https://github.com/kineticcafe/docker-sqitch-pgtap

This approach (declarative schema management) is definitely gaining in popularity. I'm the author of https://www.skeema.io which provides declarative schema changes for MySQL and MariaDB -- it's now used by a few hundred companies and is downloaded/installed 30k times per month, due in part to heavy usage in CI/CD pipelines. There are a few other tools in this space, such as Migra and sqldef.

How do you handle locks and transforming data? Usually those two things is what forces you to write migrations manually.

When you write SQL yourself you get 2 major advantages over (many) ORMs and query builders.

1. There's no surprises in the SQL generated (or when it's generated) and when you are interacting with the DB. In the worst case for ORMs, simply changing a field on a ORM controlled model can result in IO with the DB. That can be pretty surprising

2. SQL, once learned, is fairly straight forward and easy to read. Once you are comfortable with it, even complex CTEs don't take too much effort to grok. I'd argue that there are minor readability gains from ORMs (in general). The biggest value add to ORMs is integration with things like intellisense. However, IDEs like Intellij are becoming context sensitive such that you can still get that intellisense even when writing SQL in something like Java.

Right tool and job and everything. However, writing SQL is often treated as if it were assembly or something. SQL is not that. It's a fairly high level DSL for set operations.

An ORM (object-oriented mapping) is, by its nature, a wrapper on top of a language designed to work with relational data in third normal form (think of this format as reducing data duplication). Objects are more like graphs. Developers like to abstract above complexity and are often tempted to skip learning SQL altogether when using ORMs. You can get away with this when there are low volumes of data or database operations map cleanly with basic CRUD program operations.

To answer you question though, using SQL is not just about raw performance, it is also about not having to copy large volumes of data from the database to the application server for processing. So in that regard it saves memory. The reason for this is that not all ORM operations can be cleanly mapped to SQL.

SQL is a simple language and it is sometimes easier to reason about what the developer is trying to do rather than reading application code. It is also sometimes easier to run stand-alone sql against the database so it's useful for troubleshooting. Of course there are many downsides but you asked for some good reasons to use it :)

The language tends to be more powerful and make more sense (specifically than the craziness of ORMs, not vs. query builders that often at least try to have reasonable semantics), and despite most of my friends who love ORMs seemingly having to keep re-learning how to use a database every few years due to whatever platform fad everyone is rabid about (both due to shifting frameworks and libraries), my knowledge of SQL has managed to just get stronger over the past quarter century of my using SQL. The mental model you get from actually specifying the query you want is also, I will claim, really important: I believe it helps you decide on what the correct API even is to access your data, such that when you later attempt to add abstractions or move the bits around to different machines/locations, it becomes easier to manage.

I'll make a second (though I do believe "weaker" point): you are frankly likely to want to port your entire project at some point to some new framework / language, and if most of your system's behavior is just gluing SQL statements together with input models, that's going to be super easy. (The counter-argument, meanwhile, always feels really lame to me: "you might want to change your database at some point". Not only do people effectively never do that, but somehow even projects that use ORMs almost never achieve true database agnosticism either. Meanwhile, such a change is something that seems to come up a lot less often than "the awkward framework I'm using is now considered deprecated", in the grand scheme of things that are almost no database solutions that have ever mattered anyway, and the differences between them are sufficiently small vs. the differences between ORM framework concepts that porting has never felt like that big of a deal, unless you are literally going all-in on some database-specific component in which case there was likely a reason you were doing that and the ORM likely wasn't going to be useful anyway.)

At least in Rails, DDL migrations syntax is not really part of the ORM syntax; it's something separate, that mimics SQL.

I recognize and support the advantages of using an ORM in application code, but I find the migrations syntax (which, again, is different) a useless cognitive burden.

Because it's easier.

+1 for dbmate, it's good.

You can get most of the benefit of an ORM mapper with migration functionality from something like this or sqitch (I use sqitch). There are really good reasons to use this over the built-in ORM mapper migrations:

- The built-in ORM mapper migrations facility might be an afterthought (I’m looking at you, Sequelize)

- You explicitly want to separate database migrations from code changes so that the code needs to be written in such a way that it can run (at reduced functionality if required) with or without the migration, and the presence of the migration should not affect the execution of the code.

It’s way too easy with in-built migrations to make a migration that results in downtime. Practicing direct migration with SQL drivers is going to be safer every time.

> be able to apply some real intelligence to SQL definitions.

That sounds interesting, could you elaborate on what that looks like? Are you thinking things like generating ORM code off just the SQL, or doing merges of schema changes in Git, or what?

Something that I would like to see would be an Excel-like app like pgadmin or phpmyadmin that lets you modify the schema, generate migrations as you perform these modifications, and save those migrations in a folder tracked in your source code repo.

Azure DevOps is just another runner for your migration. If it works from your machine it should also work in AzDO, so as long as the pipeline has direct access to your database.

> Where does it store the state of what scripts were executed so that it doesn't have to redo those the next time? It stores this information in a table called "migrations".

You can do that, it uses natural sort (not filesystem depending)