[HN Gopher] Pgmigrate: Write migrations using plain SQL
___________________________________________________________________
Pgmigrate: Write migrations using plain SQL
Author : puuush
Score : 35 points
Date : 2021-12-14 21:00 UTC (2 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| givehimagun wrote:
| 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.
|
| https://docs.liquibase.com/workflows/liquibase-community/mig...
| oneplane wrote:
| This seems to assume SQL-first development instead of using a
| DBMS just as a storage back-end.
| hpen wrote:
| Besides raw performance, why would I ever want to write SQL
| instead of using some type of ORM or query builder??
| pizza234 wrote:
| 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.
| cogman10 wrote:
| 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.
| hpen wrote:
| 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.
| cogman10 wrote:
| 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.
| nhoughto wrote:
| 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..
| davidhyde wrote:
| 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 :)
| saurik wrote:
| 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.)
| PaulHoule wrote:
| 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.
| octopoc wrote:
| > 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.
| Avalaxy wrote:
| 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?
| mongrelion wrote:
| 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".
| autarch wrote:
| 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.
| vechagup wrote:
| https://flywaydb.org/ is another contender in this space, that
| also allows for migrations written in Java if needed.
| nhoughto wrote:
| very mature at this stage too, lots of good defaults.
| a_person_2017 wrote:
| You might want to look at https://flywaydb.org/download. 1. This
| is a redgate product 2. They do have a community edition (no
| money option.)
| electroly wrote:
| With SQL Server 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.
| evanelias wrote:
| 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.
| jeltz wrote:
| How do you handle locks and transforming data? Usually those
| two things is what forces you to write migrations manually.
| legulere wrote:
| 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.
| mschaef wrote:
| 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.
|
| 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 wrote:
| If this interest you, both dbmate
| [https://github.com/amacneil/dbmate] and golang/migrate
| [https://github.com/golang-migrate/migrate] are in very similar
| spaces---and can be provided as a single executable.
| onnnon wrote:
| +1 for dbmate, it's good.
| bob1029 wrote:
| We 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
|
| 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 wrote:
| 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.
| DenisM wrote:
| 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?
| megous wrote:
| Shouldn'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...
| bob1029 wrote:
| 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.
___________________________________________________________________
(page generated 2021-12-14 23:01 UTC)