We 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.
I 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.
> 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.
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:
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.
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?
The 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.
This 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.
I'm not the original commenter, nor am I experienced with upgrading in general, but what I understand from the comment is that you always make sure that your current schema is compatible with the previous backend.
For example, this could mean that you don't delete any column that were still in use in the previous backend version.
This would have the advantage of letting you rollback your backend alone if anything wrong happens.
Doing as you say requires two deployment for each change with a pause in between, and likely two separate merges (does it?). This reduces the deployment velocity. I might have to do as you describe when I start scaling out - there is no hope of updating many servers in sync so might as well embrace multi-version.
Ohhh 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?
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".
Have you seen Sqitch (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.
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
I 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.
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.
Nice 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.
I 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.
Yep, a migration script (if you have data you care about) is very important, it should be reviewed before being excluded. It has the power to blow away your data if done poorly. So having an explicit SQL statement (whether hand crafted or generated and exported) that is visible is super important, it means you are thinking about this important step, so its visible and harder to forget.
The classic problem with letting your ORM manage migration is if you are updating your database entity model as part of a wider change, in your ORM and you rename a column, does it know its a rename? or is it going to DROP COLUMN + ADD COLUMN? and lead to data-loss? To cover these cases and all the infinite other permutation you end up needing to give the ORM more and more state about the past, making it just a more complex migration. Making the migration explicit SQL and decoupled from the ORM forces thought, a human is unlikely to write DROP COLUMN..
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.
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 :)
With 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.
SSDT 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.
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.
The 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.
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.
Several 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.
To 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.
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.
46 comments
[ 3.1 ms ] story [ 96.8 ms ] threadhttps://docs.liquibase.com/workflows/liquibase-community/mig...
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.
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.
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
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.
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.
For example, this could mean that you don't delete any column that were still in use in the previous backend version.
This would have the advantage of letting you rollback your backend alone if anything wrong happens.
Doing as you say requires two deployment for each change with a pause in between, and likely two separate merges (does it?). This reduces the deployment velocity. I might have to do as you describe when I start scaling out - there is no hope of updating many servers in sync so might as well embrace multi-version.
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?
> 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".
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
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/Dockerf...
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.
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.
However, ORMs can easily cut the other way. So, it's all about making sure you know what you are doing.
The classic problem with letting your ORM manage migration is if you are updating your database entity model as part of a wider change, in your ORM and you rename a column, does it know its a rename? or is it going to DROP COLUMN + ADD COLUMN? and lead to data-loss? To cover these cases and all the infinite other permutation you end up needing to give the ORM more and more state about the past, making it just a more complex migration. Making the migration explicit SQL and decoupled from the ORM forces thought, a human is unlikely to write DROP COLUMN..
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.)
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.
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 :)
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.
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.
- 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.
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.)
- 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.
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.