5 ms·

Nice, it feel similar to how Yii does database migrations but with a lot more packed in. What I'd really like to see is a open source or free tool accomplishin

by ljoshua 11y ago

Nice, it feel similar to how Yii does database migrations but with a lot more packed in. What I'd really like to see is a open source or free tool accomplishing similar things to what RedGate SQL Compare does. Now that's a powerful tool and certainly worth its cost, but in some situations a limited option due to its support for only SQL Server or where funds are lacking.

zamalek 11y ago
What I'd like to see is a portable Data Dude (a.k.a. Visual Studio Data Projects). You basically write your database as a whole bunch of CREATE scripts, Data Dude then does a full parse over that (with all the static compilation bells and whistles - including some static analysis), diffs it against your actual DB (or a previous version, if you keep the 'libs' around) and then spits out the relevant diff script. Obviously you'll sometimes you need to manually pre-script/post-script to upgrade data. It basically makes database dev behave exactly like your average imperative language. The current mainstream DB landscape is ridiculously absurd. We don't write migrations for our C++, so why the heck are we writing migrations for our DBs? We the heck are we committing history (migrations) into a stores that is already historical (SCM)?
- kyllo 11y ago
  Persistent (a Haskell database library) does something like this, in a highly declarative way. You just declare your tables and their columns as Haskell data types, and when it compiles your application it automatically migrates your database if it can do so non-destructively. If your change would destroy data or requires a default value for a new non-null column or something, it will give you an error message that tells you what you need to do. It's by far the best experience I've had working with a database in a programming language. Only problem is that it's Haskell specific, not portable to other languages.
- jacques_chester 11y ago
  > We don't write migrations for our C++, so why the heck are we writing migrations for our DBs? In-memory data structures are ephemeral. You "migrate" by restarting or replacing code entirely. If you have an on-disk format that basically acts as a memory snapshot, someone writes a migration script, which is usually not recognised as such. Databases seem weird and icky and mismatched, but it's because of the D in ACID. Durable data has momentum, more force is required to change its vector than the mist that lives in memory.
- VLM 11y ago
  If I read you correctly (quite possibly not) you're proposing something like the output of "mysqldump -d" be compared to some golden schema dump and then implement something like RANCID for routers such that changes in the DB schema get emailed or otherwise loudly announced and tracked vs the golden schema. I do that. A very small shell script can take care of irrelevant diff outputs (timestamps and default minimum autoincrement numbers the like) On a side issue (kind of), something not covered in the squitch tutorial is if you git-flow then you probably should lay down some discipline that the exact name of your feature branch is the squitch change name, just to keep insanity levels down. Also I've done things like a git-flow feature branch results in a completely new test table name being created and there needs to be discipline that the test table name is "normalTableName-gitflowFeatureBranchName" or some kind of site standard. Its only kind of a side issue, in that your proposed Data Dude app probably should cooperate with git-flow workflows. > so why the heck are we writing migrations for our DBs Sometimes dumb indexing decisions can take hours / days to apply and kill the NAS, run the system out of storage, etc. I've done that and its not entertaining, so its less painful to do "real stuff" by hand, at least to production machines. Tend to find some hilarious scaling mistakes. Very few source code diffs scale the source code length itself exponentially or something equally naughty so its always safe to commit/pull source code changes and automerge them, not so much schema changes.
  - zamalek 11y ago
    > Very few source code diffs scale the source code length itself exponentially or something equally naughty so its always safe to commit/pull source code changes and automerge them, not so much schema changes. Awesome counterargument. However, in the same breath: what's the story with migrations? In RoR they're ordered by date, so what happens if two developers make concurrent and conflicting schema changes in their respective branches? That merge will silently fail, a false positive. At least a merge conflict throws up red flags, causing some human has to look at what is going on.
- dpb000 11y ago
  SCM solves the easy problem of maintaining the history of the DDL required to build an empty database from scratch (and you can even have DML under source control to populate the database with test data once it is built). When version 1.2 of a table's DDL adds a new, not nullable column to the version 1.1 definition and there are a billion rows in production, you are looking at a one-off migration event.
  - zamalek 11y ago
    > you are looking at a one-off migration event. Right, and that's where you roll up your sleeves and describe how to upgrade to that new column; just like you'd handle an out-of-date wire format with C++. No matter how you look at it, you'll always have to manually deal with the most complex upgrade scenarios no matter what you are targeting (RDBMS/CPU/car/washing machine). The question remains: why are we doing that all the time?
- gghh 11y ago
  We don't write migrations for our C++, so why the heck are we writing migrations for our DBs The reason is that your database is much more "stateful" than you binary executables. A db migration (DDL, i.e. when you change the schema of your db) can take days to apply (eg: create an index in a large table), and you don't want to re-do it every time you deploy. That's why database changes are much more "diff oriented" than your code: when you deploy code you just compile the whole thing and replace the old binary with a new one.
- dragonwriter 11y ago
  > We don't write migrations for our C++, so why the heck are we writing migrations for our DBs? Because your DB is a datastore as well as a store of code and structure, and when you apply changes to your production DB, they generally have to preserve the data that is already in the DB. That means that the transition is the important thing to describe and test. Inferring it from create scripts is possible, but it doesn't really gain you anything. And doing it by some process that infers some of the changes but also leaves you to directly script others seems to be strictly inferior to describing the transitions explicitly and not leaving something to infer them partially with you filling in the gaps. It sounds like you are describing a process that leaves you more work to do so that you can be less close to the important part of what the is actually be done, which seems like losing all around -- though it certainly seems like the kind of losing that is inexplicably popular in the enterprise world.

by ljoshua 11y ago

zamalek 11y ago

What I'd like to see is a portable Data Dude (a.k.a. Visual Studio Data Projects). You basically write your database as a whole bunch of CREATE scripts, Data Dude then does a full parse over that (with all the static compilation bells and whistles - including some static analysis), diffs it against your actual DB (or a previous version, if you keep the 'libs' around) and then spits out the relevant diff script. Obviously you'll sometimes you need to manually pre-script/post-script to upgrade data. It basically makes database dev behave exactly like your average imperative language. The current mainstream DB landscape is ridiculously absurd. We don't write migrations for our C++, so why the heck are we writing migrations for our DBs? We the heck are we committing history (migrations) into a stores that is already historical (SCM)?

kyllo 11y ago
Persistent (a Haskell database library) does something like this, in a highly declarative way. You just declare your tables and their columns as Haskell data types, and when it compiles your application it automatically migrates your database if it can do so non-destructively. If your change would destroy data or requires a default value for a new non-null column or something, it will give you an error message that tells you what you need to do. It's by far the best experience I've had working with a database in a programming language. Only problem is that it's Haskell specific, not portable to other languages.
jacques_chester 11y ago
> We don't write migrations for our C++, so why the heck are we writing migrations for our DBs? In-memory data structures are ephemeral. You "migrate" by restarting or replacing code entirely. If you have an on-disk format that basically acts as a memory snapshot, someone writes a migration script, which is usually not recognised as such. Databases seem weird and icky and mismatched, but it's because of the D in ACID. Durable data has momentum, more force is required to change its vector than the mist that lives in memory.
VLM 11y ago
If I read you correctly (quite possibly not) you're proposing something like the output of "mysqldump -d" be compared to some golden schema dump and then implement something like RANCID for routers such that changes in the DB schema get emailed or otherwise loudly announced and tracked vs the golden schema. I do that. A very small shell script can take care of irrelevant diff outputs (timestamps and default minimum autoincrement numbers the like) On a side issue (kind of), something not covered in the squitch tutorial is if you git-flow then you probably should lay down some discipline that the exact name of your feature branch is the squitch change name, just to keep insanity levels down. Also I've done things like a git-flow feature branch results in a completely new test table name being created and there needs to be discipline that the test table name is "normalTableName-gitflowFeatureBranchName" or some kind of site standard. Its only kind of a side issue, in that your proposed Data Dude app probably should cooperate with git-flow workflows. > so why the heck are we writing migrations for our DBs Sometimes dumb indexing decisions can take hours / days to apply and kill the NAS, run the system out of storage, etc. I've done that and its not entertaining, so its less painful to do "real stuff" by hand, at least to production machines. Tend to find some hilarious scaling mistakes. Very few source code diffs scale the source code length itself exponentially or something equally naughty so its always safe to commit/pull source code changes and automerge them, not so much schema changes.
- zamalek 11y ago
  > Very few source code diffs scale the source code length itself exponentially or something equally naughty so its always safe to commit/pull source code changes and automerge them, not so much schema changes. Awesome counterargument. However, in the same breath: what's the story with migrations? In RoR they're ordered by date, so what happens if two developers make concurrent and conflicting schema changes in their respective branches? That merge will silently fail, a false positive. At least a merge conflict throws up red flags, causing some human has to look at what is going on.
dpb000 11y ago
SCM solves the easy problem of maintaining the history of the DDL required to build an empty database from scratch (and you can even have DML under source control to populate the database with test data once it is built). When version 1.2 of a table's DDL adds a new, not nullable column to the version 1.1 definition and there are a billion rows in production, you are looking at a one-off migration event.
- zamalek 11y ago
  > you are looking at a one-off migration event. Right, and that's where you roll up your sleeves and describe how to upgrade to that new column; just like you'd handle an out-of-date wire format with C++. No matter how you look at it, you'll always have to manually deal with the most complex upgrade scenarios no matter what you are targeting (RDBMS/CPU/car/washing machine). The question remains: why are we doing that all the time?
gghh 11y ago
We don't write migrations for our C++, so why the heck are we writing migrations for our DBs The reason is that your database is much more "stateful" than you binary executables. A db migration (DDL, i.e. when you change the schema of your db) can take days to apply (eg: create an index in a large table), and you don't want to re-do it every time you deploy. That's why database changes are much more "diff oriented" than your code: when you deploy code you just compile the whole thing and replace the old binary with a new one.
dragonwriter 11y ago
> We don't write migrations for our C++, so why the heck are we writing migrations for our DBs? Because your DB is a datastore as well as a store of code and structure, and when you apply changes to your production DB, they generally have to preserve the data that is already in the DB. That means that the transition is the important thing to describe and test. Inferring it from create scripts is possible, but it doesn't really gain you anything. And doing it by some process that infers some of the changes but also leaves you to directly script others seems to be strictly inferior to describing the transitions explicitly and not leaving something to infer them partially with you filling in the gaps. It sounds like you are describing a process that leaves you more work to do so that you can be less close to the important part of what the is actually be done, which seems like losing all around -- though it certainly seems like the kind of losing that is inexplicably popular in the enterprise world.