5 ms·
Pgmigrate: Write migrations using plain SQL
- givehimagun 5y agoWe do exactly that at work - Liquibase with SQL for about 7 years now. It's wonderful and you don't have to learn anything on top of your SQL dialect. Also, it makes database-first a breeze since you can export your changes to SQL from any IDE these days and drop it right into a migration. https://docs.liquibase.com/workflows/liquibase-community/migrate-with-sql.html https://docs.liquibase.com/workflows/liquibase-community/mig...
- PaulHoule 5y agoI remember using something like this for a system based in ColdFusion and Microsoft SQL server circa 2005. The missing magic in tools like liquibase is a low-maintenance parser framework. If we had composable grammars it wouldn't be that hard for a tool to track the SQL syntax of various databases and be able to apply some real intelligence to SQL definitions. Trouble is nothing good ever happens in parsing frameworks because everybody things they have the same problem as Go and they worry too much about the speed of parsing.
- octopoc 5y ago> 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.
- bob1029 5y agoWe do something like this with SQLite. Fun fact - you don't even need to maintain a special unicorn "_migrations" table or any other external state to keep track of things with SQLite migration. You can simply utilize the user_version pragma: https://sqlite.org/pragma.html#pragma_user_version https://sqlite.org/pragma.html#pragma_user_version We have a DatabaseVersion constant in the classes that own each type of SQLite database, and all they have to do at ctor time is query the database for current version and run a for loop over the difference to execute the required migration scripts. Our migrators are one-way (we don't define a matching 'down.sql'). We would push new code to back something out of a SQL schema and then increment our counter just like if we added something new. Decrementing/skipping is disallowed since this would cause information loss on the migration path. Having a monotonic version number per type of database makes it super easy to keep everything on rails for us. For database access, we also use raw SQL via Dapper.
- paulryanrogers 5y agoI 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.
- DenisM 5y agoDo 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?
- bob1029 5y agoThe SQLite usage is very tightly integrated with the software. Any urgent rollback is effectively a roll-forward fix of whatever broken software is in production.
- DenisM 5y agoWhen I say urgent rollback I mean rollback within 30 seconds of realizing something went wrong. Do you have this capability?
- megous 5y agoShouldn't you do DB updates in production in a way that N backend always works at least with N+1 schema? So you just rollback the backend...
- redis_mlc 5y agoThis is the correct answer in almost all cases for managing production databases. However, developers who use postgres never got the memo, and are trying to do mass rollback transactions of schema changes "cause it worked on my dev notebook." Source: DBA who manages tables with billions of rows.
- developuh 5y agoCan you please explain this like I'm 5. I am still learning and I would like to understand how to do upgrades in a better way.
- oneplane 5y agoThis seems to assume SQL-first development instead of using a DBMS just as a storage back-end.
- Avalaxy 5y agoOhhh perfect, I was just looking for a simple way to do migrations in PostgreSQL without having to build an app and write python code. Just pure SQL is perfect! Question: do you know if it will work with Azure DevOps? Where does it store the state of what scripts were executed so that it doesn't have to redo those the next time?
- mongrelion 5y agoAzure 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".
- autarch 5y agoHave you seen Sqitch (https://sqitch.org/ https://sqitch.org/)? It does exactly this, it's a battle-tested system with a decent number of users, and it supports many database. I didn't dig deep into this new system, but it looks very much like Sqitch at a glance.
- halostatue 5y agoI 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 https://github.com/kineticcafe/docker-sqitch-pgtap
- autarch 5y agoThere are official Sqitch Docker images - https://hub.docker.com/r/sqitch/sqitch/ https://hub.docker.com/r/sqitch/sqitch/ But I don't know if they have pgTAP on them. Edit: I just checked. It doesn't look like they do - https://github.com/sqitchers/docker-sqitch/blob/main/Dockerfile https://github.com/sqitchers/docker-sqitch/blob/main/Dockerf...
- halostatue 5y agoI just did an update and release 1.1.0 of my docker image with support for pgTAP from 9.6 to 14. I should write up examples of how we use it, but we use a variant of the `run` script in the docker image. I’ve also built my docker image with multiple architectures as I run on arm64 but need to run on amd64 servers. The Sqitch docker images appear to be _just_ amd64 images. The other difference is that I’m using alpine instead of debian-slim.
- vechagup 5y agohttps://flywaydb.org/ https://flywaydb.org/ is another contender in this space, that also allows for migrations written in Java if needed.
- nhoughto 5y agovery mature at this stage too, lots of good defaults.
- a_person_2017 5y agoYou might want to look at https://flywaydb.org/download https://flywaydb.org/download. 1. This is a redgate product 2. They do have a community edition (no money option.)
- hpen 5y agoBesides raw performance, why would I ever want to write SQL instead of using some type of ORM or query builder??
- pizza234 5y agoAt 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.
- cogman10 5y agoWhen 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.
- hpen 5y agoNice so I actually do know SQL somewhat and use it extensively at work. I have started using other options in my side projects and found it to be more streamlined. Just curious where ORMs fail really.
- cogman10 5y agoI definitely understand the draw of ORMs. The big issue with SQL in most programming languages is the pretty large impedance. It's like writing go in a Java source file. Doable, yes, but not something anyone really likes to do. However, ORMs can easily cut the other way. So, it's all about making sure you know what you are doing.
- electroly 5y agoWith SQL Server Data Tools (SSDT) we get desired-state schema management, which seems much better than writing any kind of migrations. In practice it works well, and the code you're writing is plain DDL. I wonder why it's not more commonly seen in other ecosystems.
- jeltz 5y agoHow do you handle locks and transforming data? Usually those two things is what forces you to write migrations manually.
- electroly 5y agoSSDT gets you most of the way but not all the way there. It can rebuild a table by copying the rows if you're changing things that are evident from a diff of the production schema against your desired state, if it's not possible with ALTER TABLE. But for complicated arbitrary transformations, it does make you use a trapdoor where you write a migration script that gets executed after the desired state is applied. In practice, we consciously avoid doing this, but we are perhaps making sacrifices for the tool's limitations. I've occasionally done hacks like creating a new table with the desired new shape, then creating a view that converts the old data to the new format and merges it with the new data. SSDT is capable of deploying that, but it's not the greatest situation.
- evanelias 5y agoThis approach (declarative schema management) is definitely gaining in popularity. I'm the author of https://www.skeema.io 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.
- legulere 5y agoThe reason why people use ORM mappers with their migration functionality is that you get rid of a lot of repetitive stuff and get an okay result for 95%. I have the feeling, if you don’t have a lot of different tables and migrations, writing them by hand probably saves you time compared to learning the intricacies of an ORM mapper (they are usually very leaky abstractions, so you have to read their source code to understand what’s going on). If you already know an ORM-mapper then it’s usually not worth it. The real trap seems to be to believe that ORM mappers will abstract the database away and that you don’t need to understand your database anymore.
- halostatue 5y agoYou 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.
- mschaef 5y agoSeveral years ago, I wrote something similar for Clojure, as part of a way to make it super simple to put a SQL database behind a Clojure all. Essential goal was for close to single jar file deployment. https://github.com/mschaef/sql-file https://github.com/mschaef/sql-file Doesn’t get a lot of use aside from a few small things I use it for, but has been nice to have around. (This was before I knew of Flyway…. These days I might just link to that for the migration part.)
- xemoka 5y agoIf this interest you, both dbmate [https://github.com/amacneil/dbmate https://github.com/amacneil/dbmate] and golang/migrate [https://github.com/golang-migrate/migrate https://github.com/golang-migrate/migrate] are in very similar spaces---and can be provided as a single executable.
- onnnon 5y ago+1 for dbmate, it's good.
- kklisura 5y agoTo be honest this looks like half-baked. Couple of points: - migration queries for up and down should be persisted. Here migrations are always read from files. If one changes down migration it might not match the up migration. - migration content should be tracked (crc, hashing, whatever) to prevent any changes to migrations once applied - my opinion is that each migration should have strict order and not be relied upon file sorts to prevent adding migrations in the middle of existing ones. This is accomplished by enforcing each id/migration number in files to be incremental - 1-to-1 mapping should be enforced and errors should be produced if there are migrations in database, but not present in files - one nit pick: two tables for migrations seems overkill This is mostly coming from Java background and having the above things was really life saver in some situations. I think the Playframwork's way of managing migrations was really good. Flyway is good too for SpringBoot (not sure if exists something besides it), but lacks down migrations.
- SahAssar 5y agoThe 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.
- tobyhinloopen 5y agoGood 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.
- pmarreck 5y agoI'd prefer something like a UTC datetime prefix to the numeric sorting dependence, but...
- tobyhinloopen 5y agoYou can do that, it uses natural sort (not filesystem depending)