[HN Gopher] Dbmate: A lightweight, framework-agnostic database m...
       ___________________________________________________________________
        
       Dbmate: A lightweight, framework-agnostic database migration tool
        
       Author : thunderbong
       Score  : 61 points
       Date   : 2024-06-16 18:40 UTC (1 days ago)
        
 (HTM) web link (github.com)
 (TXT) w3m dump (github.com)
        
       | vaibhav2614 wrote:
       | Dbmate is great. I've been using it for years.
       | 
       | There's very little guesswork about what it's going to do to a
       | database because your migrations are raw SQL statements with no
       | additional layers of complexity or translation.
        
         | hu3 wrote:
         | Can confirm.
         | 
         | I've been using raw SQL for migrations in some projects for
         | years.
         | 
         | Boring and predictable. Just how I like it.
        
         | TN1ck wrote:
         | Agree, makes it easy to apply a migration manually if necessary
         | as it's really just adding a row to a table to indicate that it
         | ran and the migration itself.
        
       | whalesalad wrote:
       | I've always wondered why tools like this cannot become stateless.
       | Most have an up and down action, but I haven't seen one yet that
       | can run a query to determine if a migration has been applied or
       | not. Then no state tables/artifacts are needed.
       | 
       | Instead of one file with an up and down, there could be two files
       | where each has a predicate and then an action, where the
       | predicate would run to determine if the migration has been
       | applied or not.
        
         | netghost wrote:
         | You probably could, but you really want your database
         | migrations to clear, repeatable, and dead simple.
         | 
         | I can also imagine edge cases like a migration dropping a
         | column and another recreating it where it may be unclear what
         | the state is, and honestly you don't want to be surprised when
         | you migrate thousands of paying customers' data.
        
         | simonw wrote:
         | I wrote a version of that as part of my sqlite-utils Python
         | library: https://sqlite-utils.datasette.io/en/stable/python-
         | api.html#...
         | 
         | You can do something like this:
         | db["cats"].create({             "id": int,             "name":
         | str,             "weight": float,         }, pk="id",
         | transform=True)
         | 
         | The transform=True parameter means "if the table already exists
         | but does not match the provided schema, transform it to add
         | missing columns etc".
         | 
         | I don't generally recommend it though, it's a risky way of
         | working compared to stateful migrations.
         | 
         | Here are the tests for that option:
         | https://github.com/simonw/sqlite-utils/blob/577078fe01da6d87...
         | - and the accompanying issue: https://github.com/simonw/sqlite-
         | utils/issues/467
        
         | stanac wrote:
         | > I haven't seen one yet that can run a query to determine if a
         | migration has been applied or not
         | 
         | Entity Framework (both Core and legacy) does that, also DbUp
         | uses migrations table for those that prefer lighter ORMs.
        
         | evanelias wrote:
         | There are a number of declarative schema management tools,
         | which operate on a repo of desired-state CREATE statements. No
         | need to write up/down migrations _or_ predicates /actions. You
         | just have CREATE statements, and can modify them in-place, and
         | the schema management tool figures out the appropriate DDL to
         | transform any live database into the desired state.
         | Conceptually this has many advantages for managing tables [1]
         | as well as stored procedures (and functions, triggers, etc)
         | [2].
         | 
         | My tool Skeema, first released in 2016, provides declarative
         | schema management for hundreds of MySQL/MariaDB based
         | companies, including GitHub, SendGrid, Cash App, Wix, Etsy, and
         | many others you have likely heard of. Safety is the primary
         | consideration throughout all of Skeema's design:
         | https://www.skeema.io/docs/features/safety/
         | 
         | For Postgres, a few declarative solutions include sqldef,
         | Migra, Tusker (which builds on Migra), and Atlas.
         | 
         | [1] https://www.skeema.io/blog/2019/01/18/declarative/
         | 
         | [2] https://www.skeema.io/blog/2023/10/24/stored-proc-
         | deployment...
        
         | AdieuToLogic wrote:
         | > I haven't seen one yet that can run a query to determine if a
         | migration has been applied or not
         | 
         | MyBatis Migrations[0] does that with the use of a migrations
         | management table.
         | 
         | As to why most, if not all, SQL migration tools are stateful,
         | "the good ones" often have migration descriptions and
         | timestamps as well. Since a persistent store is by definition
         | stateful, having tooling state stored alongside the concepts
         | managed makes sense in many cases.
         | 
         | 0 - https://mybatis.org/migrations/
        
       ___________________________________________________________________
       (page generated 2024-06-17 23:01 UTC)