How do you do database migration/evolution?

Webdev_Tory · April 20, 2018, 3:36pm

Having started my road down Clojure webdev with Luminus, I’ve defaulted into using Migratus for database migration. I’ve had no real problems with this. But as I’ve talked to Play developers, I find find them bemoaning the absence of automated database evolutions in our Clojure projects. This has me wondering, what are your solutions for managing databases for your projects, particularly for team-based projects?

https://www.playframework.com/documentation/2.5.x/Evolutions

pesterhazy · April 20, 2018, 5:07pm

A minimalist approach is to store the migrations as .sql files in a folder, then run them via a command line client like psql or from your Clojure code via JDBC.

For Postgres, I’ve come up with a minimalist framework. It’s so simple that it doesn’t really deserve the name “framework”. In a nutshell the idempotent function ensures that a particular migration (say, CREATE TABLE) is run only once. Subsequent runs are NOOPs. Completed migrations are stored in the migrations table.

Advantages over “real” migration tools like flyway:

it’s fast if there’s nothing to do so you can easily run all migrations whenever your JVM process starts
you know exactly what’s going on
it’s all just SQL so the sql snippets can include data migrations (UPDATE foo ...) as well as schema migrations (ALTER TABLE foo ...)

Limitations include lack of support for rolling back migrations. But if your use case is like mine, you may not need that functionality (and appreciate the fact that the code fits on a postcard).

orestis · April 20, 2018, 6:28pm

I can’t remember which talk was it, but it strongly suggested that reverse migrations are a huge anti-pattern — you can accidentally lose data, and there’s no good reason to use them.

swlkr · April 21, 2018, 7:56pm

I use reverse migrations in development only. When switching branches or when I make a mistake and want to easily drop a few tables/columns.

But yeah on a live server, I never rollback migrations.

myguidingstar · April 22, 2018, 7:43am

I really I the flow of Duct framework which use ragtime for migration, configured with good defaults:

github.com

duct-framework/docs/blob/master/GUIDE.rst

Guide to the Duct Framework
===========================

Preface
~~~~~~~

Duct is a data-driven framework for writing server-side applications
in the Clojure_ programming language. This guide is intended as an
in-depth explanation of how to use Duct, and will focus on web
applications in particular.

This guide assumes you have a working knowledge of Clojure code, and
that you have Leiningen_ installed. A basic understanding of Ring_
will also be useful, but not required.

.. _Clojure:   https://clojure.org/
.. _Leiningen: https://leiningen.org/
.. _Ring:      https://github.com/ring-clojure/ring

This file has been truncated. show original

but you may like joplin, which is built on top of ragtime itself

Webdev_Tory · April 24, 2018, 7:05pm

What are reverse migrations?

orestis · April 24, 2018, 7:41pm

Usually when ORM tools auto-generate migrations (by comparing the current vs wanted schema), they will generate a “forward” migration script that will take you to the desired schema, and a “backwards” migration script that will get you back to the current state.

This opens up some very bad fat-fingerings if you accidentally run it on a production database, as it may drop columns, tables etc that may already have data in…

system · October 24, 2018, 7:50am

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.