possibly a simple question and possibly a rabbit hole:

in something like django you write model classes and and automated tool comes up with all the sql needed to create the tables for your app

and later on if you change those classes that same automated tool will write the "migrations" to upgrade an old instance of the database (like a previous deploy) to the new schema that your model classes imply without dropping all your data

Eh, it does that to varying levels of success

if we create those tables manually how exactly does one go about writing migrations by hand

I think most people use a migrations framework to run either .clj files representing migrations or .SQL files

ive seen tools like https://github.com/yogthos/migratus

You can find some that are language-agnostic and clojure-spe ific ones like migratus

from the luminus docs

but that is all about organizing "migrations" and making sure they are ordered and whatnot

I still dont understand how to write migrations

For example, at my last gig, we had a directory of migrations that were timestamped, named, and given a direction, but were just SQL files

CREATE TABLE IF NOT EXISTS, SELECT, and INSERT is about the limit of my sql knowledge

We had a simple runner to apply them and track which ones were applied

When we did that, schema and data migrations often lived side by side (albeit in different individual files)

Ah

For that, you should read up on your SQL dialects ddl syntax

like say i had this file

Postgres and mysql both have good sections on it in their docs

CREATE TABLE IF NOT EXISTS MYTABLE (
    id INTEGER PRIMARY KEY,
    a TEXT NOT NULL
);

DDL is the magic keyword you're looking for

how would i write a migration to add a b?

okay thats a start

https://www.postgresql.org/docs/10/sql-altertable.html

Alter table

okay

how would your simple runner track which ones were applied?

It kept a stateful table which inserted the migration id/filename and a timestamp

That's how most of them work

I wouldn't recommend rolling your own

We did for $reasons

NIH syndrome or are most proprietary?

Which weren't very strong

A misplaced desire to have proprietary stuff

do you know any good open source ones that match up with what you had?

clojure or not

Flyway

You can use it as a standalone comma d line tool

Or as a java library

But it can work with a raw directory of .SQL files

I prefer that because I never had cookie-cutter schemas

Even if I used something like Django migrations, I'd end up writing the migrations as raw SQL anyway

Although I hear that Django migrations and their or have come a long way since the 1.5 days I was using the framework

Orm

I haven't looked back from static queries and honeysql as an ast to build dynamic queries using clojure

The power of jdbc tooling and a way to work with SQL as an ast has been a very powerful to utilize

To fully utilize my target database's featureset while reducing/factoring out crud.

reading their docs now

ugh

always annoying to see "pro" and "enterprise" editions

even if it makes perfect real world sense

for a while i thought i knew quite alot about db migration systems ...

but then around 2011 in skype our db team built a tool called relman ... and then we understood that a whole lot of things we had done before were just awful 😄

the essence of the tool was that your repo described the desired state, not the series of mutations ... and the tool figured out that ok, right the tables and schemas are in state A and you want to get into state B , so it has to do the following mutations to it ...

the nice part was that you could always open the repo and understand what is actually there in production, how does it look, what tables are there, what indexes etc.

if you have a repo that has 200 migrations on it (and obviously missing 5 manual ones that dba's applied to put out fires) ... you can see 200 migrations but rarely what the actual state of the production databases is

this gets especially wild if you have similar databases in clusters and you need to understand that a logical replica is skipping a few columns when talking to a specific reporting replica and it's by design, not by accident.

but sadly i haven't seen such a tool in the open source world 😞

another useful feature was that you could just diff repository revisions to understand what has happened to the database , instead of looking at 5-6 migrations and calculating on your fingers what the state could/should have been ... (or instead of manually setting up databases and playing 195 and 200 migrations on them accordingly)

the tool that has awareness of the changes that you want to perform can also be a bit smarter on avoiding/pointing out possible deadlocks on applying. if you just write manual sql that all relies on you 😉

out of curiosity, how long did that internal tool take to develop?

I am guessing there is some sizable set of issues that makes "oh just do a diff" more complicated than it sounds on the surface level

it took a few developer months 🙂

but considering how many devs we had who had to do db stuff without breaking things - it was a good investment

Seems not that hard, although handling possible type changes, differences in indexes, and possible stores procedures and stuff might make things difficult.

yep. but for some reason majority of the tools are stuck in the opposing "track your changes instead" ideology ...

when we write code we change the code ... but for some reason on the databases we tend to write the initial code and then we try to follow up with the patches to the original design 🙂

would feel pretty weird to manage code this way , wouldn't it ?

how involved with the project were you?

...with the implicit "were you involved enough that you would know enough to facilitate making an open source version"

maybe a partial solution would be a tool that runs all the migrations and flattens out what the db is now

Django has a tool exactly like this

yeah but thats attached to the whole django ORM thing right?

not that i don't like the ORM

but part of me is just hunting for a solution to this stuff for clojure

that was totally not-orm 🙂 ...

i was very well aware of the internal workings of the tooling but i have no time on my hands to invest anywhere, family just eats it all away for now

the django tool that @dominicm mentioned , it actually handles transformations from one table state to another ? (meaning when you declare in a newer version that your table has a new column with an index but doesn't have a column that was previously mentioned -> it adds the new column, drops the old one and adds the index concurrently on the side ?)

i don't use django so i wouldn't know 🙂

i must be looking at the wrong django manual ... dominic when you can - point a link to me of what you had in your vision there 🙂

https://ytcropper.com/cropped/Xb5ce05fd7cd7eb

https://docs.djangoproject.com/en/2.1/topics/migrations/ > Your models will be scanned and compared to the versions currently contained in your migration files, and then a new set of migrations will be written out. Make sure to read the output to see what makemigrations thinks you have changed - it’s not perfect, and for complex changes it might not be detecting what you expect. Model examples: https://docs.djangoproject.com/en/2.1/topics/db/models/ (probably links to documentation about generating migrations too)