What do you use for database migrations

I'm kind of looking for a RMDB migration tool. Ideally one that:

Isn't tied to an application framework (e.g. it can use some programming language for its operation, but shouldn't push me to use that language for the rest of my own application).
Works for many SQL dialects.
Free (gratis) & open source.
Easy to install and run (preferably not rocket science to write).

And nice to have:

Backward migrations are nice, but not required.
Ideally can be put in VCS to match data and code versions. So e.g. no consecutive numbering, as that gets messed up when there are many concurrent feature branches.
Nice if it supports schema and data migrations.
Squashing migrations would be nice.
Plugins

No need to generate access code (ORM stuff), those can be based on the resulting schema.

So my question is: what do you use?

We use Sequelize. Sequelize is an ORM for NodeJS, but it works very well in migrations. Our current projects don't use Sequelize as ORM, but we still use their migration feature.

Hopes, prayers, and a lot of booze to steady the nerves before I push the button to start it.

github.com/elwinar/rambler
phinx

this would be one thing the problem is not the up / down part where the DDL is concerned. it's the transformation parts where you cannot actually go back anymore :) Where you don't change the structure but the values.

Writing a simple migration thing is pretty easy ->

db table <migration_timestamp>_file_name

if it's SQL -> execute directly as batch if it's custom -> execute it as the language

you have up or down which can be just files to keep it simple

up.sql
down.sql

you order the files with 000 prefixes so they execute in order and this actually suffices most use cases in my experience

Usually applications don't change their SQL infrastructure and if they do they do it after 2-3 years of development and at some point some custom functions was applied to leverage the engine.

But that's just me.

TL;DR;

complete language agnostic transformations of the values can be problematic
writing a small migration software is very easy
if you never need to duplicate business-logic within the application no problem :D

my experience :) but I'm very interested if someone has a general hook system where you can hook your application into the SQL migration while still being language agnostic maybe with a small wrapper for stdin|stderr

Mark If it is about portability it's basically a compiler and some form of orm. or you just use the intersectional language of all SQL dialects which is hard enough.

an enum in MySQL and an enum in PSQL are quite different things for example also the behaviour of a DDL within a transaction.

in MySQL you cannot rollback a DDL in a transaction so you have to think about the behavioural patterns.

Also you cannot use the internal functions to some degree or how they apply indexes / sequences of ids.

And than don't forget internal versions the compile target, semantics, morphism you already to some degree arrive to an ORM even without using complex logics within the ORM and just keeping it a dumb query machine to encapsulate your values.

Maybe I am making it overly complex and if we isolate the problems between handling the data and creating the structure it's just a compiler who does certain transformations based on the compile target but that would need some kind of meta SQL.

maybe something like this?

but they mostly fokus on DML not DDL

Maybe applying different levels of isolation and application would help to come to better conclusion ;D I am just 'thinking out loud' :)

Yeah I was thinking along the lines of compiling to different SQL dialects. The original language doesn't need to be like SQL, maybe it'd be JSON... (Liquibase uses XML)

Then need some way to track dependencies (maybe numbers, maybe explicit dependencies), a way to see what has been run or reverted and whether it hasn't changed, and maybe a way to run user scripts for more complex data migrations.

I guess it's like an ORM in that it abstracts the SQL dialect yeah... Although I tend to think... And I guess it does object mapping... But I feel like ORM is more for DML :p

Mark as long as you can validate the JSON with a schema why not. Although JSON at some point gets less readable than XML since it's not that expressive. But that's taste ;D

you mean like a subregister? a number is cool, but in this case it lacks the explicit historical information i would store the steps in a human readable form. It's easier to spot that a specific file went missing than number 4 is now number 3. (I am just deducing my approach where you just count +1 for every step).

;D Yes DML I think now we have all the D's ;D .... so who get to call bingo? ;D

Thread

What do you use for database migrations

Responses(3)

Recent threads

Search Hashnode

What do you use for database migrations

Responses(3)

Recent threads