Clojurians Log v2
sql
hi, what do you use for SQL migrations? currently using migratus and I think it could use a feature to list ALL migration id's . wdyt? cc @yogthos > I think it would be very useful to have a way to list the migration ids that migratus knows about (maybe with file path?) > Migrations up / down can use the ID's but to my knowledge there is no easy way to print them. > I'm using clj-migratus, but I think this should be part of core and exposed in clj-migratus.
https://gist.github.com/geraldodev/f4cc25376331ebd37d2eb8a4d3ccf8a9 Mybatis migrations with babashka
Hello, i have added API support to display more informations about migrations If you guys have some time to look at: https://github.com/yogthos/migratus/issues/240#issuecomment-1688354449 Thanks.
You can just find/grep the resources/migrations directory (locally)
Aside from Migratus, I used Ragtime as well. It’s a great library and it might be used with nosql DBs, e.g Cassandra
I know, but that would not give me the migrations that migratus discovers / considers
Go create an issue on the repo. Having some options to list all / applicable migrations without applying them sounds useful.
there is option to see pending migrations but not all
I was going to create the issue but decided to have a discussion first, get some consensus
I can definitely see value in a migrations library being able to produce a list of all possible migrations, indicating which have been applied an which haven't (if the system is able to track on a per-migration basis whether it has been applied or not). Our hand-rolled system at work is "linear" (it has a monotonically-increased "version" number and just tracks the highest version applied) and if I was starting over again, I'd use Migratus and I would love the "report" feature that provided more information, especially if it could list the date a migration was applied, etc.
nice feedback. I will add it to the issue
I use FlywayDB (https://flywaydb.org/) - been using it for years, from java, to kotlin and now on Clojure
works a treat.
I've also used flyway (also has a cli). On the project I joined they are using migratus though. flyway seems more polished, but it's been around for longer and also has some financial backing behind
having an option to list all migrations would make sense, and should be pretty quick to add, effectively would just be a matter of factoring out the parts of find-migration-files and find-migration-resources that find the migrations, and exposing that as list-migrations or something
the relevant namespace is here https://github.com/yogthos/migratus/blob/master/src/migratus/migrations.clj#L77
hi, i have added some design ideas on adding list options for migratus https://github.com/yogthos/migratus/issues/240#issuecomment-1600540973
Hello, i have made a PR with some work in progress https://github.com/yogthos/migratus/pull/244 If you have the time to give me some input, it will help me a lot. Thanks.
thanks. I'll add your notes to the issue. Also, I think it makes sense to list the migrations applied in DB as well as local migration files
agreed, might be good to return a map with all the available migrations as well as the ones applied, the second bit will have to be read from the migrations table
cc @andrei.stan
On the topic of tools for migrations. So, first perhaps unpopular opinion, it’s hard (in general) to write down migrations. At ardoq we write them, but we have yet to actually roll back a migration. In general, we roll forward. Secondly we see two (or perhaps even three) types of migrations that we do. One is creating new or adjusting old tables, adding constraints and those kinds of things. These are normally fast, and are nice if run early in the boostrapping of the app. Then we have data migrations which can be expressed in the language of the database, These will typically touch customer data, and can be longer running. It’s no fun to wait for these when starting up the app. Thirdly we have the kind of data migrations that for various reasons are hard to express in the language of the database, so it’s more convenient to write a clojure fn for them. These can also be timeconsuming to run. And, we don’t need support for umpteen different databases, we currently have two, aiming for one. So, where am I going with this. I guess I’m sortof wanting a migration tool which basically only has ups, and its only job is to record the running of a fn in a table somewhere so that we run the fn once an only once. Does anything as simple as that exist? Or should I write it myself, or isn’t it that simple.
Not sure if that exists. But it is also extremely simple, I would just do it yourself. (I have done so multiple times)
In our case we don't do the extremely slow migrations that way, those have to happen while the application is running (background jobs)
Yah, makes sense to do it as a background job.
I think there have been some efforts recently to create more "declarative" migration tools that try to avoid the "apply every migration that's ever happened to reproduce a database" workflow that seems to create a lot of hassle https://atlasgo.io/getting-started#declarative-migrations
I have no idea if this translates into lower operational overhead or reduced processing time while migrations are running, but in theory an approach like this might help eliminate unnecessary steps in the migration process
Sounds like it would be nice for development. But I think the general approach is to just periodically take a snapshot of your schema (Sql DDL), then archive the migrations that have been run to result in that schema (or have insert statements for them).
@slipset We rolled our own up-only SQL migration code. We also have a separate notion of "data migrations" which are either integrated into batch jobs -- if they're going to be ongoing for a while or more -- or done "manually".
In all cases, the migrations are bundled into JAR files and deployed as part of our normal staging/production process. The up SQL migrations are applied automatically as part of that deployment (and so we make sure up migrations are always non-breaking/backward compatible). The "batch" data migrations happen automatically too by virtue of automated deployment/restarts and or existing cron jobs (that they are added to). The "manual" migrations are run as needed by an engineer using an automatically deployed artifact java -jar the.jar ticket#1 ticket#2 ticket#3 where the (Jira) ticket numbers correspond to specific data migrations that need running.
Those data migrations usually need to run for dev/test/CI we add them to a list of "local automated" migrations that run as part of DB setup.
There are two different approaches for migrations: 1) you write up & down migrations, and 2) you write only up. The problem with 2) is, should you mess up on prod, you've got to rollback asap and that's where 1) benefits because you already have a down part. With 2), you have to write it which takes time.
@igrishaev: sometimes migrations change data and that part is not so easy to undo. Even if you save the old data, if the table is large enough and the system is running, you might get into a situation where you can't rollback. Not without some manual intervention. I trie to avoid these kinds of migrations of course.
aslo a snapshotting feature does not sound that bad 🙂