5 ms·

It's frustrating watching this space. The problem is so trivially solved that there's almost no product to build. But people want to build something to solve

It's frustrating watching this space. The problem is so trivially solved that there's almost no product to build. But people want to build something to solve it, so they build a much more complicated solution that can be configured to solve any potential database migration scenario, regardless of whether anybody in history has ever needed to handle that scenario. Here's how you actually version databases: change_0030-0031.sql --- alter table buns add column seed_count int update version_number set version = 31 Check that in to source control and you're 90% done. Extra points for building the tool that spins through all the files in source control and executes the ones that come before the current version_number on the server in question. And for automatically creating a change_0031-0032.sql file for the next guy to use (with that update line pre-filled.) You'll be hard pressed to spend an entire day building this, but sadly nobody does. Everybody downloads some overcomplicated monstrosity like the one in the article that describes changes in XML and whatever else. Then spends two days fiddling with config files to get it working. I'd give up complaining about it if I didn't keep ending up working with shops that use stuff like this.

101008 6y ago
I even think that this is what Django does automatically with its migrations, isn't?
cpdean 6y ago
I like alembic because you label your migrations as nodes in a graph so that I can do work in my branch, but properly navigate backwards in the migration history to a common ancestor with my coworker so I can migrate forwards to their branch and pair with them on something. I'm frustrated to see people make a bunch of new tools that ignore the lessons from old tools, but for me this has been solved for a while.
sz4kerto 6y ago
This is what Liquibase does. It does some other stuff for you as well, quite simple ones, but time has proven (to me/us) that they're all useful in real-life scenarios: - exclusive lock to prevent multiple instances running at the same time - rollbacks - forcing immutability > Then spends two days fiddling with config files to get it working. Look, dozens and dozens of developers have been using this for many years at us without problems. If it takes two days to set it up: let it be. And we're writing our migration scripts in SQL, not XML.
- mumblemumble 6y ago
  So, having used both, I've got to say, I had fewer problems with the homegrown solution. Liquibase has its share of gotchas, especially in the SQL syntax. My sense is that most of the problems I experienced had to do with weird edge cases introduced by trying to handle some complex enterprisey scenario that I would rather deal with by just not letting things get that complicated in the first place. That exclusive lock mechanism, for example, isn't foolproof, and I suspect that it has bitten me more times than it's helped me. -but- Roll-your-own is not necessarily the easy option. I'd say that, if your goal is to get something robust enough to use in production, a day to implement is a very optimistic estimate. I'd say that it's one to steer clear of unless you're sure you know what you're doing. Which one is better depends, as usual, on your situation. I think my rubric would probably be: If the XML schema definition language is at all attractive to you, use Liquibase (or similar). That's far and away its most unique contribution. If, on the other hand, the knowledge that anyone would even consider defining their SQL schema in a language other than SQL fills you with holy rage, maybe choose an option that sits closer to the metal. This doesn't necessarily need to be DIY project, but, thanks to Zawinski's Law, that might be the only way to ensure that your solution also stays close to the metal.
  - redis_mlc 6y ago
    I've used both home-grown and Liquibase. You have to use something to create, approve and apply patches if you're subject to compliance audits. I found Liquibase helpful for dev and integration environments where deploy pipelines can easily apply sequences of database patches automatically. But for large-scale production masters, you end up applying patches at certain times after hours (6 pm PT but not on Friday), automatically under 400,000 rows on SSD, and manually if the tables are large. Often patches are applied out-of-order (typically I'll do smallest to largest tables, or no-FKs then FKs, least-busy to most-busy tables, on-calls online or not, ordered by backup timestamp, etc.) The above is one of the main reasons to hire a DBA, and Liquibase alone cannot replace that. Source: DBA.
trampi 6y ago
Wouldn't be a viable solution for an old project I was working on: we had to support different SQL back ends. Liquibase abstracts it away and allows you to use plain SQL where you need it. Your solution looks simple at first glance, but does not scale very well. For small projects your approach will work, but its an instance of "not invented here". > Then spends two days fiddling with config files to get it working. Not really the case in the project I've used liquibase with.
miahi 6y ago
It looks like a simple problem until you have multiple developers/branches/supported DBs/application instances. Then you start to appreciate a tool that is more descriptive, can do rollbacks and auto upgrades at application start, populate a test database and so on.
- jasonkester 6y ago
  Are those all things you've actually done? Or are you making a list of "potential database migration scenarios" like I described above? As a datapoint, I've been a backend guy for coming up on 25 years now, and not only have I never needed to roll back a database migration, I'm not aware of that ever having needed to be done anywhere, even anecdotally. It just never happens in the reality we live in. Yet all migration tools support it, require reverse migration scripts, etc. And you listed it in your "must have" list.
  - miahi 6y ago
    These are features I actually use liquibase for. That's why I know about them. About rollbacks: yes, they are not always useful and you can think of ways to avoid them. But these days when you can rollback a docker application upgrade with a few commands/clicks, this way you can also roll back the DB changes. Not all the changes require this, but there are many ways a schema upgrade can go. Datapoint: changing stored procedures/functions that should match the calling application's version. Yes, not everybody uses logic in the DB, but sometimes you need that performance.
  - zzzeek 6y ago
    merging of branches with migration files, which means you can't just use "30, 31, 32, ... etc" is definitely something people do, all the time.
- cmiles74 6y ago
  I've used a couple of these tools and have ended up using the ones that do less, rather than more. Our team has settled on Flyway for our Java projects and Entity Framework Migrations for C# work. These aren't as full featured as Liquibase but, in my opinion, are somewhat easier for members of the team to reason about. Automatic rollbacks strike me as somewhat risky. My thinking is that if the migration (or upgrade) failed it was likely due to something that hadn't been thought about when the migration script was written. In that case it seems unlikely to me that another automated process (the rollback) will be any more successful.
petr25102018 6y ago
Exactly. For Python project the solution can be https://pypi.org/project/nomad/ https://pypi.org/project/nomad/ and you will even save yourself that one day of development.
cies 6y ago
Without out of order processing of migrations I would not want to use it. When you work on several branches you will at some point need out of order processing of migrations. Also: what about not having a versions (as those may clash between branches) but migration creation datetimes to line them up?
iracic 6y ago
There are lot more here. Idea is to put chances on your side in some possibly long and time constrained migration. How do you handle run that stops in the middle? How to avoid rerun of long running process that finished correctly but exited in step after? How do you revert the change to come to some previous point in time in case it is really really needed? And all that in some environment you maybe have no access but need to guide other person to act with steps defined in advance? How do you handle if some error is fatal to stop the process and other just pass with warning printed? Need to run some actions on each deployment? How do you log and verify what steps were run before? What process to use when multiple teams need to deploy to different environments and maybe different database types (different syntax, different rollback strategies)?
krab 6y ago
See my sibling comment about Django. You can spend a day to build this. And maybe a few hours later for small fixes for corner cases found in production. And then explain to your colleagues. Or you can spend a few hours to learn a tool that has tutorials online (where you can point newcomers). When you move to another project, you can take your little tool with you, but you will need to start again teaching and convincing teammates.
CraigJPerry 6y ago
>> and you're 90% done. Sure but as with just about any problem in this space, the first 80% is the easy part and the final 20% will occasionally just flat out kill you. How do you handle versioning stored procedures and triggers? What if your dev envs don't use partitioning but your QA / prod envs do? How do you rollback? Perhaps you're using the expand-contract pattern to avoid the need for rollback, great, how do you manage the complexity of inflight (expanded) but not yet contracted changes? For basic use cases you definitely don't need liquibase but there comes a point where something like flyway makes a difference without adding much complexity at all.
- jasonkester 6y ago
  Certainly, but none of the things you mention seem all that scary. versioning stored procedures and triggers? Procedures and the like get their own .sql files and live under source control like everything else. At build time, they all get blown away and rebuilt. What if your dev envs don't use partitioning but your QA / prod envs do? SQL still works though, right? How do you rollback? Nobody has ever needed to rollback. It's the scary ghost story that convinces people to build big ugly database build systems. In short, most shops just plain won't ever need any of the stuff they're paying for (in dev hours) to run these big tools. Hopefully somebody will build this small thing as a tool, so that people can download it and therefore be "using a tool" and thus not running up against Invented Here Syndrome.
  - jbman223 6y ago
    “Nobody ever needed rollback” is a very naive statement. Nobody needs rollback, until they do. And the one time you need it, having it will make up for ALL of the time spent on getting it set up correctly. Properly having rollback and other safeguards in place allows you to move faster and make mistakes with less consequences. Everything is a trade off.
  - mattzito 6y ago
    As someone who built (and sold) a company in this space, your solution works for the vast majority of relatively simple databases. However, lots of legacy/enterprise/custom apps have freakishly complicated schemas running on databases that have been so over-engineered as to create problems for themselves (cough Oracle *cough). In practice, you end up having lots of complex restrictions - you have to change your schema definition slightly between dev and prod, because your dev box is linux and your prod machine is AIX and there's a bug in your dbms on AIX. You can't just drop stored procedures, because rebuilding them takes 30 minutes of downtime you can't allow for. Or you have security policies on data in prod that changes the table definition. In an ideal world you wouldn't need these things, but once you have a complicated enough system, you basically have to have a tool that does your database release management.
CapriciousCptl 6y ago
I've personally not spent too much time fighting any migration tool. Configuring Alembic from scratch, for instance, takes about 5 minutes for separate dev and production databases (MySQL/SQLite). Code-generation comes as a bonus. There's 0 chance I'd trade a day of developer time to build an in-house tool re-creating 90% of what Alembic does, with its large audience use-testing it daily, stability and documentation.
yowlingcat 6y ago
Help me understand what your rationale is. Are you saying that SQL + source control is good enough? Do you think it's a fools errand to build tools like this which add additional abstraction layers for schema revision?
mrits 6y ago
31? damn, I also used 31.
- jayd16 6y ago
  This is way better than random ids. You still have order of operation conflicts but now your source control can't tell you at the merge level.
tootie 6y ago
What about data migrations for destructive changes?
paulirwin 6y ago
I hear you, but I'd like to give a shout-out to my favorite database deployment mechanism, which is admittedly SQL Server-only: SQL Server Database Projects in Visual Studio with DACPAC deployments. It is quite a different solution than just about anything out there. (TL;DR: there are no hand-written migrations.) Basically, you have a project with all of your tables, stored procedures, etc. as separate .sql files per object, but they are always "CREATE" statements, never ALTER/DROP/etc. So your project defines a normative schema of how you want it to be. What makes this really interesting is that you can then "compile" your database schema, and you actually get "compiler errors" for things like invalid syntax. (I'm putting quotes around that because it's not 100% the same as a programming language compiler error, but close enough.) And you get warnings and static analysis, that helps you detect issues before you even deploy to your local database. My understanding of how this works during compilation is that it basically spins up a temporary database, deploys your fresh schema to it, and validates that it deploys successfully. It can even validate against specific server target versions for compatibility. Plus, you get IntelliSense since it knows about your full schema, making editing the .sql files easier. Where it becomes very helpful is with changes. There are no migration scripts that you code by hand. Need to add a column? Just add it to the MyTable.sql file's CREATE TABLE statement. No ALTER necessary. You get nice git history showing the column was added to the table, without having to track back through migration history. But for new developers coming on your team, or when you want to just start your local database over again, you don't need to run through years of history of migrations. You just start with the current normative schema and deploy it as only CREATE statements. Of course, you can also add pre- and post-deployment scripts as well for seeding data or whatnot. How this works when it comes time to deploy changes to an existing database is that it builds the temporary database of the normative schema, then does a schema comparison to the target database, and checks for any changes that would cause data loss such as dropping or dangerously altering columns. If no such changes are needed (i.e. if you're only adding a column, or changing a sproc), it can safely go ahead and generate and run that DDL script (i.e. ALTER TABLE ADD whatever, plus your pre- and post-deployment scripts). If you want to proceed allowing the data loss, you can do so if you feel it is safe. But you have that safety check to prevent screw-ups. And all of this is generated into a nice .dacpac file that you can pass around in your CD pipeline, or hand off to a DBA for deployment. You can also generate that script without running it directly on the target if you prefer, and hand that script off as a SQL file. Also, I've found this is quite compatible with git branching, due to the normative-schema nature, whereas with migrations you can easily end up with conflicting migration order when i.e. two branches create migration #101. In the SQL Server Database Project case, if the changes are not conflicting in a git merge (i.e. columns added to two different tables), there's no problem and you don't have to touch any files at all. If they are compatible changes to the same file (i.e. two columns added to the same table), you can usually do an auto-merge and you'll likely end up with a valid schema. And in the rare case of a conflict, you can merge that however you see fit. Is it perfect? No. Is it complex? Yes. But once you get used to how it works, I've almost never had to script out any DDL changes by hand (I often forget the ALTER syntax), I don't think I've ever had to roll back (rollbacks are usually as simple as re-deploying the older .dacpac file), and I've found so many issues due to compiler errors and static analysis that have saved a huge amount of effort. Not having something like this is the biggest thing I miss when I have to work with PostgreSQL or MySQL. If anyone knows of something similar for those databases, I'd love to know!
michael1999 6y ago
I've built that. This works on small, well synchronized teams, on small databases. Once you have to merge a branch, you start fighting over version ids or renumbering like it's BASIC. And remember that any long-lived dev session is effectively a branch. Heaven forbid you have environmental conditionals to apply.
debug-desperado 6y ago
Just got through consulting for a shop that thought a similar bespoke migration system was all they needed. Since the team was quite large and the CI/CD pipeline so slow, tons of time got wasted every sprint resolving the merge conflicts, getting rid of duplicate includes from auto-merging, etc. I agree with the other replies - PLEASE don't do this. Use liquibase, flyway, or any other tool. They both let you write everything in SQL if that's your preference. The initial setup time will be saved later on if the team grows.
djrobstep 6y ago
Even this is vastly over-engineered. Just have a target schema definition somewhere, and use a schema diff tool to generate the changes you nee on demand. No need for version numbers at all. No need to run through change scripts to set up a database from scratch. Almost no need to manually write scripts.
givehimagun 6y ago
I'm not a fan of those DSLs either. We use liquibase with plain old SQL...we write in the dialect of the database. No rollbacks either - always forward. One thing that is nice from liquibase is that you get a nice cli that let's you do some fun stuff like update the database directly and then auto-generate a diff (your example above). See https://docs.liquibase.com/commands/home.html https://docs.liquibase.com/commands/home.html
tiew9Vii 6y ago
Flywaydb https://flywaydb.org/getstarted/how https://flywaydb.org/getstarted/how does pretty much that using plain sql files.

101008 6y ago

I even think that this is what Django does automatically with its migrations, isn't?

cpdean 6y ago

I like alembic because you label your migrations as nodes in a graph so that I can do work in my branch, but properly navigate backwards in the migration history to a common ancestor with my coworker so I can migrate forwards to their branch and pair with them on something. I'm frustrated to see people make a bunch of new tools that ignore the lessons from old tools, but for me this has been solved for a while.

sz4kerto 6y ago

This is what Liquibase does. It does some other stuff for you as well, quite simple ones, but time has proven (to me/us) that they're all useful in real-life scenarios: - exclusive lock to prevent multiple instances running at the same time - rollbacks - forcing immutability > Then spends two days fiddling with config files to get it working. Look, dozens and dozens of developers have been using this for many years at us without problems. If it takes two days to set it up: let it be. And we're writing our migration scripts in SQL, not XML.

mumblemumble 6y ago
So, having used both, I've got to say, I had fewer problems with the homegrown solution. Liquibase has its share of gotchas, especially in the SQL syntax. My sense is that most of the problems I experienced had to do with weird edge cases introduced by trying to handle some complex enterprisey scenario that I would rather deal with by just not letting things get that complicated in the first place. That exclusive lock mechanism, for example, isn't foolproof, and I suspect that it has bitten me more times than it's helped me. -but- Roll-your-own is not necessarily the easy option. I'd say that, if your goal is to get something robust enough to use in production, a day to implement is a very optimistic estimate. I'd say that it's one to steer clear of unless you're sure you know what you're doing. Which one is better depends, as usual, on your situation. I think my rubric would probably be: If the XML schema definition language is at all attractive to you, use Liquibase (or similar). That's far and away its most unique contribution. If, on the other hand, the knowledge that anyone would even consider defining their SQL schema in a language other than SQL fills you with holy rage, maybe choose an option that sits closer to the metal. This doesn't necessarily need to be DIY project, but, thanks to Zawinski's Law, that might be the only way to ensure that your solution also stays close to the metal.
- redis_mlc 6y ago
  I've used both home-grown and Liquibase. You have to use something to create, approve and apply patches if you're subject to compliance audits. I found Liquibase helpful for dev and integration environments where deploy pipelines can easily apply sequences of database patches automatically. But for large-scale production masters, you end up applying patches at certain times after hours (6 pm PT but not on Friday), automatically under 400,000 rows on SSD, and manually if the tables are large. Often patches are applied out-of-order (typically I'll do smallest to largest tables, or no-FKs then FKs, least-busy to most-busy tables, on-calls online or not, ordered by backup timestamp, etc.) The above is one of the main reasons to hire a DBA, and Liquibase alone cannot replace that. Source: DBA.

trampi 6y ago

Wouldn't be a viable solution for an old project I was working on: we had to support different SQL back ends. Liquibase abstracts it away and allows you to use plain SQL where you need it. Your solution looks simple at first glance, but does not scale very well. For small projects your approach will work, but its an instance of "not invented here". > Then spends two days fiddling with config files to get it working. Not really the case in the project I've used liquibase with.

miahi 6y ago

It looks like a simple problem until you have multiple developers/branches/supported DBs/application instances. Then you start to appreciate a tool that is more descriptive, can do rollbacks and auto upgrades at application start, populate a test database and so on.

jasonkester 6y ago
Are those all things you've actually done? Or are you making a list of "potential database migration scenarios" like I described above? As a datapoint, I've been a backend guy for coming up on 25 years now, and not only have I never needed to roll back a database migration, I'm not aware of that ever having needed to be done anywhere, even anecdotally. It just never happens in the reality we live in. Yet all migration tools support it, require reverse migration scripts, etc. And you listed it in your "must have" list.
- miahi 6y ago
  These are features I actually use liquibase for. That's why I know about them. About rollbacks: yes, they are not always useful and you can think of ways to avoid them. But these days when you can rollback a docker application upgrade with a few commands/clicks, this way you can also roll back the DB changes. Not all the changes require this, but there are many ways a schema upgrade can go. Datapoint: changing stored procedures/functions that should match the calling application's version. Yes, not everybody uses logic in the DB, but sometimes you need that performance.
- zzzeek 6y ago
  merging of branches with migration files, which means you can't just use "30, 31, 32, ... etc" is definitely something people do, all the time.
cmiles74 6y ago
I've used a couple of these tools and have ended up using the ones that do less, rather than more. Our team has settled on Flyway for our Java projects and Entity Framework Migrations for C# work. These aren't as full featured as Liquibase but, in my opinion, are somewhat easier for members of the team to reason about. Automatic rollbacks strike me as somewhat risky. My thinking is that if the migration (or upgrade) failed it was likely due to something that hadn't been thought about when the migration script was written. In that case it seems unlikely to me that another automated process (the rollback) will be any more successful.

petr25102018 6y ago

Exactly. For Python project the solution can be https://pypi.org/project/nomad/ https://pypi.org/project/nomad/ and you will even save yourself that one day of development.

cies 6y ago

Without out of order processing of migrations I would not want to use it. When you work on several branches you will at some point need out of order processing of migrations. Also: what about not having a versions (as those may clash between branches) but migration creation datetimes to line them up?

iracic 6y ago

There are lot more here. Idea is to put chances on your side in some possibly long and time constrained migration. How do you handle run that stops in the middle? How to avoid rerun of long running process that finished correctly but exited in step after? How do you revert the change to come to some previous point in time in case it is really really needed? And all that in some environment you maybe have no access but need to guide other person to act with steps defined in advance? How do you handle if some error is fatal to stop the process and other just pass with warning printed? Need to run some actions on each deployment? How do you log and verify what steps were run before? What process to use when multiple teams need to deploy to different environments and maybe different database types (different syntax, different rollback strategies)?

krab 6y ago

See my sibling comment about Django. You can spend a day to build this. And maybe a few hours later for small fixes for corner cases found in production. And then explain to your colleagues. Or you can spend a few hours to learn a tool that has tutorials online (where you can point newcomers). When you move to another project, you can take your little tool with you, but you will need to start again teaching and convincing teammates.

CraigJPerry 6y ago

>> and you're 90% done. Sure but as with just about any problem in this space, the first 80% is the easy part and the final 20% will occasionally just flat out kill you. How do you handle versioning stored procedures and triggers? What if your dev envs don't use partitioning but your QA / prod envs do? How do you rollback? Perhaps you're using the expand-contract pattern to avoid the need for rollback, great, how do you manage the complexity of inflight (expanded) but not yet contracted changes? For basic use cases you definitely don't need liquibase but there comes a point where something like flyway makes a difference without adding much complexity at all.

jasonkester 6y ago
Certainly, but none of the things you mention seem all that scary. versioning stored procedures and triggers? Procedures and the like get their own .sql files and live under source control like everything else. At build time, they all get blown away and rebuilt. What if your dev envs don't use partitioning but your QA / prod envs do? SQL still works though, right? How do you rollback? Nobody has ever needed to rollback. It's the scary ghost story that convinces people to build big ugly database build systems. In short, most shops just plain won't ever need any of the stuff they're paying for (in dev hours) to run these big tools. Hopefully somebody will build this small thing as a tool, so that people can download it and therefore be "using a tool" and thus not running up against Invented Here Syndrome.
- jbman223 6y ago
  “Nobody ever needed rollback” is a very naive statement. Nobody needs rollback, until they do. And the one time you need it, having it will make up for ALL of the time spent on getting it set up correctly. Properly having rollback and other safeguards in place allows you to move faster and make mistakes with less consequences. Everything is a trade off.
- mattzito 6y ago
  As someone who built (and sold) a company in this space, your solution works for the vast majority of relatively simple databases. However, lots of legacy/enterprise/custom apps have freakishly complicated schemas running on databases that have been so over-engineered as to create problems for themselves (cough Oracle *cough). In practice, you end up having lots of complex restrictions - you have to change your schema definition slightly between dev and prod, because your dev box is linux and your prod machine is AIX and there's a bug in your dbms on AIX. You can't just drop stored procedures, because rebuilding them takes 30 minutes of downtime you can't allow for. Or you have security policies on data in prod that changes the table definition. In an ideal world you wouldn't need these things, but once you have a complicated enough system, you basically have to have a tool that does your database release management.

CapriciousCptl 6y ago

I've personally not spent too much time fighting any migration tool. Configuring Alembic from scratch, for instance, takes about 5 minutes for separate dev and production databases (MySQL/SQLite). Code-generation comes as a bonus. There's 0 chance I'd trade a day of developer time to build an in-house tool re-creating 90% of what Alembic does, with its large audience use-testing it daily, stability and documentation.

yowlingcat 6y ago

Help me understand what your rationale is. Are you saying that SQL + source control is good enough? Do you think it's a fools errand to build tools like this which add additional abstraction layers for schema revision?

mrits 6y ago

31? damn, I also used 31.

jayd16 6y ago
This is way better than random ids. You still have order of operation conflicts but now your source control can't tell you at the merge level.

tootie 6y ago

What about data migrations for destructive changes?

paulirwin 6y ago

I hear you, but I'd like to give a shout-out to my favorite database deployment mechanism, which is admittedly SQL Server-only: SQL Server Database Projects in Visual Studio with DACPAC deployments. It is quite a different solution than just about anything out there. (TL;DR: there are no hand-written migrations.) Basically, you have a project with all of your tables, stored procedures, etc. as separate .sql files per object, but they are always "CREATE" statements, never ALTER/DROP/etc. So your project defines a normative schema of how you want it to be. What makes this really interesting is that you can then "compile" your database schema, and you actually get "compiler errors" for things like invalid syntax. (I'm putting quotes around that because it's not 100% the same as a programming language compiler error, but close enough.) And you get warnings and static analysis, that helps you detect issues before you even deploy to your local database. My understanding of how this works during compilation is that it basically spins up a temporary database, deploys your fresh schema to it, and validates that it deploys successfully. It can even validate against specific server target versions for compatibility. Plus, you get IntelliSense since it knows about your full schema, making editing the .sql files easier. Where it becomes very helpful is with changes. There are no migration scripts that you code by hand. Need to add a column? Just add it to the MyTable.sql file's CREATE TABLE statement. No ALTER necessary. You get nice git history showing the column was added to the table, without having to track back through migration history. But for new developers coming on your team, or when you want to just start your local database over again, you don't need to run through years of history of migrations. You just start with the current normative schema and deploy it as only CREATE statements. Of course, you can also add pre- and post-deployment scripts as well for seeding data or whatnot. How this works when it comes time to deploy changes to an existing database is that it builds the temporary database of the normative schema, then does a schema comparison to the target database, and checks for any changes that would cause data loss such as dropping or dangerously altering columns. If no such changes are needed (i.e. if you're only adding a column, or changing a sproc), it can safely go ahead and generate and run that DDL script (i.e. ALTER TABLE ADD whatever, plus your pre- and post-deployment scripts). If you want to proceed allowing the data loss, you can do so if you feel it is safe. But you have that safety check to prevent screw-ups. And all of this is generated into a nice .dacpac file that you can pass around in your CD pipeline, or hand off to a DBA for deployment. You can also generate that script without running it directly on the target if you prefer, and hand that script off as a SQL file. Also, I've found this is quite compatible with git branching, due to the normative-schema nature, whereas with migrations you can easily end up with conflicting migration order when i.e. two branches create migration #101. In the SQL Server Database Project case, if the changes are not conflicting in a git merge (i.e. columns added to two different tables), there's no problem and you don't have to touch any files at all. If they are compatible changes to the same file (i.e. two columns added to the same table), you can usually do an auto-merge and you'll likely end up with a valid schema. And in the rare case of a conflict, you can merge that however you see fit. Is it perfect? No. Is it complex? Yes. But once you get used to how it works, I've almost never had to script out any DDL changes by hand (I often forget the ALTER syntax), I don't think I've ever had to roll back (rollbacks are usually as simple as re-deploying the older .dacpac file), and I've found so many issues due to compiler errors and static analysis that have saved a huge amount of effort. Not having something like this is the biggest thing I miss when I have to work with PostgreSQL or MySQL. If anyone knows of something similar for those databases, I'd love to know!

michael1999 6y ago

I've built that. This works on small, well synchronized teams, on small databases. Once you have to merge a branch, you start fighting over version ids or renumbering like it's BASIC. And remember that any long-lived dev session is effectively a branch. Heaven forbid you have environmental conditionals to apply.

debug-desperado 6y ago

Just got through consulting for a shop that thought a similar bespoke migration system was all they needed. Since the team was quite large and the CI/CD pipeline so slow, tons of time got wasted every sprint resolving the merge conflicts, getting rid of duplicate includes from auto-merging, etc. I agree with the other replies - PLEASE don't do this. Use liquibase, flyway, or any other tool. They both let you write everything in SQL if that's your preference. The initial setup time will be saved later on if the team grows.

djrobstep 6y ago

Even this is vastly over-engineered. Just have a target schema definition somewhere, and use a schema diff tool to generate the changes you nee on demand. No need for version numbers at all. No need to run through change scripts to set up a database from scratch. Almost no need to manually write scripts.

givehimagun 6y ago

I'm not a fan of those DSLs either. We use liquibase with plain old SQL...we write in the dialect of the database. No rollbacks either - always forward. One thing that is nice from liquibase is that you get a nice cli that let's you do some fun stuff like update the database directly and then auto-generate a diff (your example above). See https://docs.liquibase.com/commands/home.html https://docs.liquibase.com/commands/home.html

tiew9Vii 6y ago

Flywaydb https://flywaydb.org/getstarted/how https://flywaydb.org/getstarted/how does pretty much that using plain sql files.