Native support for migrations

[3noch]

This is a future idea, but it would be cool if Project: M36 had high-quality, built-in support for migrations. Some ways having a migration library could help:

• Using a migration DSL you can have type safe migrations and type-level verification that migrations that produce a desired model
• Schemas can be checked against live production databases before making changes

[3noch]

Some interesting example work for PG: https://github.com/ameingast/postgresql-simple-migration

[agentm]

Thanks for the link- I wasn't aware of that project.

A good start on this ticket would be a sort of schema diff tool and schema hashing.

I think that Project:M36 will have a better story for migrations in general because migrations can be tested in staging and production on separate transaction graph branches. This reduces the risk of lost data by mistaken migrations. This would also reduce or eliminate downtime.

I suspect a lot of the pain of migrations is due to hosting the migrations outside of the database. It would be interesting to research making the database more aware of schema changes so that it could ostensibly service old schema and new schema requests against an updated schema. I made some steps towards this in the nascent isomorphic schemas feature.

[3noch]

Ah, I hadn't connected how well isomorphic schemas would help with this problem. Indeed, if there were some sort of migration language that was able to produce backward compatible isomorphic schemas, that would be utterly game-changing!

[zanzix]

I'd welcome a brief tutorial on migrations using M36.

I have one particular project that will require a lot of migrations so if M36 offers a superior story than my current favorite (acid-state) then I would be very happy to use it as the db of choice.

I agree that M36's ability to have separate transaction graph branches for production and testing is an amazing feature that will be useful for this. It's such a unique feature that I think will be a massive selling point for M36 in itself; I'd be interested to know how people leverage it and in what kind of projects they found it to be most helpful.

I'd be happy to write a blog post comparing acid-state to M36 once I get comfortable with migrations in the latter.

As far as building a database that is aware of schema changes, have you checked out David Spivak's et al work on functorial data migration ? They used category theory to formalise data migration across schemas and built a language that blurs the boundary between a database and a programming language.

There's a lot of cutting edge ideas in their work that overlaps with what M36 is aiming to do, so I think you'll find it interesting!

[3noch]

This looks like a fantastic resource! Thanks!

[agentm]

Thanks for the link @zanzix. Interestingly, Project:M36 implements many of the same transformations through its isomorphic schemas feature. This allows clients which expect different schemas to operate against the same data by transforming the incoming queries using the rules suggested also in the FQL approach. However, the schemas must be isomorphic, which is not always the case when making migrations, so I agree that Project:M36 could be improved there.

What I would like to do is store and version migrations server-side. I think one of the failures of current migration systems is that they rely on a lot of client-side state which makes it impossible for the server to validate that the migrations actually make sense. Historical migrations can also come in handy.

[zanzix]

I'm glad you found it interesting!

I had a feeling that there was overlap between the two projects. Isomorphic schemas look like something sorely missed in other databases that I've tried so I look forward to trying them out.

I agree that that would be a fantastic feature, and being able to query migrations as data on the server would make a ton more sense than keeping them lumped with the application source code. It sounds like this could enable M36 to have a similar kind of time-travel that datomic has.

Would you guys be able to share some mid-level-complexity migration code with me? I'm not at the stage where I need to migrate anything yet, but I'd like to have a reference ready for when I'd need to do so.

[agentm]

There is currently no migration-specific code in Project:M36. Schema manipulation is effectively manual using Define and Undefine. To add an attribute to a relation variable X, for example, one needs to extend X with the new attribute, assign it to a new relvar Y, undefine the old relvar X, and rename Y to X. Sorry- that's not very smooth, but it works.

[YuMingLiao]

There is a lot of things that can be considered in a schema migration: atomtypes, relations, functions, constraints, and isomorphic schemas. ConstructedAtomTypes affect the set of AtomTypes. (data / undata) And RelationTypes validity depends on the set of AtomTypes. (define/undefine for a whole new world, or project/extend for lazy evaluation referencing a parent.) And AtomFunctions' validity depends also on those AtomTypes. And RelationalExpressions' validity, such as constraints and isomorphic schemas, needs a sufficient set of AtomTypes and relations with right attributes also.

And it seems typed lamdba is a good solution for this problem.

[benjaminweb]

There is currently no migration-specific code in Project:M36. Schema manipulation is effectively manual using Define and Undefine. To add an attribute to a relation variable X, for example, one needs to extend X with the new attribute, assign it to a new relvar Y, undefine the old relvar X, and rename Y to X. Sorry- that's not very smooth, but it works.

Is that manual approach documented somewhere?

[agentm]

Sorry, it's not documented- we should add a schema migration guide. In the meantime, here's a sample session:

TutorialD (master/main): x:=relation{a Integer, b Integer}
TutorialD (master/main): :commit
TutorialD (master/main): x_tmp:=x:{c:=0}
TutorialD (master/main): undefine x
TutorialD (master/main): x:=x_tmp
TutorialD (master/main): :showexpr x
┌──────────┬──────────┬──────────┐
│a::Integer│b::Integer│c::Integer│
└──────────┴──────────┴──────────┘

Note how x starts with attributes a and b and ends with c, too.