We detached this subthread from https://news.ycombinator.com/item?id=11859027 and marked it off-topic.
I have absolutely no idea what a "SQL migration" is or why it would be so hard. I assume it's just a bogeyman made up by the MongoDB snake-oil salesmen. It is certainly not something "traditional" database people actually do, or worry about.
Transforming data to conform to a new schema. It's a tedious process that could use some automation, but ditching schemas because data migrations are tedious is like ignoring traffic rules because you're in a hurry.
> I assume it's just a bogeyman made up by the MongoDB snake-oil salesmen.
It's just like dynamic type marketing: “If you never define the structure of your data, you never need to worry about it!” The only problem is that it isn't true.
It's not uncommon however in large organisations with mature applications, without strict IT policies.
Complex applications that are undergoing active changes shouldn't be constrained in this manner, and in that context a SQL migration is more of an in-place upgrade of the data structures that hold the application data between different versions of the application.
Adding columns, adding tables, replacing columns with other columns and managing how the data should look in the new schema, based on the 'old' data structures.
These are solved problems, have been for decades.
Business logic doesn't belong in a database. data does.
Or you can rewrite all the third-party apps your organisation uses now and in the future to use your own.
Both solutions are unpalatable: "the database is the integration point we can't ever change" is bad and "no apps have a central ground truth database" is also painful.
I'd prefer the latter with small APIs and more duplication, than the common enterprise solution with a massive holy unchangable db directly serving apps.
In the end it means you give the data an elevated role in the system, and you write the code around the data. When the code would be cheaper to write or maintain with a different data representation - you can't. It's a comfortable situation for a while, but when you eventually grow out of the current system it's a world of hurt.
Like I said - it's two bad (non) solutions but I very much prefer duplication, isolation, inconsistency, a forest of small obscure APIs etc. where I can add/remove/normalize/denormalize/add caching etc - to "the data is the ground truth and the schema we can only change through addition" non-solution.
That's how things are meant to be. The structure of a program naturally follows that of the data it manipulates.
> When the code would be cheaper to write or maintain with a different data representation - you can't.
Who says that? The real problem is that most programming languages aren't capable of integrating database schemas into the type-checking process, so when you change a schema, it's difficult to make sure applications follow through in a consistent manner. That's what makes change unnecessarily risky and painful. But it's possible to do better than that: http://impredicative.com/ur/
Application logic can undergo active changes and refactoring without changes to the data permanent storage, indeed, a 1-to-1 correspondence between DB and application data structures isn't expected. It's just as with backwards compatibility with file formats, changing the application version shouldn't require incompatible in-place changes to the backing data persistence layer - you can need to add some new features, but the old version of the app should work fine with the updated DB.
You heard it here first folks. None of us know anything.
You've just taken the first step toward's web-scale enlightenment.
Also, the cloud is Turing complete and has infinite tape.
http://www.winestockwebdesign.com/Essays/Eternal_Mainframe.h...
It honestly makes it really hard not to dismiss whatever you say out of hand. If you don't really care about people taking you seriously then by all means keep it up, but if you're trying to participate in a grown up conversation, you might want to stop throwing tantrums. You know, the basic rule of human interactions, show people the respect you expect them to show you?
I've written a ton of SQL migrations this year, none of them are scary or even really count as "big bunch of sql". If this describes your code, you should probably stop and rethink what you're doing, because that's bad practice.
The difference between, eg, merging your "first_name" and "last_name" fields into a single "name" field with Mongo or a SQL DB is basically that the SQL DB has better tooling so it'll be cleaner, faster, and more concise.
> Now in your migration script you have to hack SQL and language-based ORM commands
Also false. Nothing requires you to use the ORM commands if they don't make your life easier; literally last week I wrote a migration for some ORM based code using pure vanilla SQL because it was more appropriate. If you know what your ORM is doing, it's trivial. And if you don't, well, you have bigger problems.
> Actually they can be just as explicit and documented.
With a relational DB: Check the table schema.
With mongo: Dig through the app code and try and reverse engineer what fields the code is expecting. (Or pull up a few documents and see what fields actually exist, then ponder how you know whether you're looking at document that uses the latest schema, or maybe an outdated or dead document using an outdated schema. Also, how do you know what optional fields might be missing? Or what possible values the currently null fields might have? Good luck with that.)
> Mongo even has schemas now I think.
No. Mongo still lists flexible schemas as a feature, ie, they do not enforce any specific schema.
> SQL constraints are very limited and literally every application has additional constrains
Yeah, but what relational DBs are very good at is enforcing foreign key constraints. If your data is relational at all (and let's be real, very, very little world data isn't at least somewhat relational), you'll need it, and Mongo doesn't have it. (Well, Mongo doesn't even have foreign keys, but it lacks the equivalent feature for dealing with denormalized data too.) Managing to set some non-numeric characters in your phone number field when some of your app code contains a hard assumption it will be numeric is bad, yeah. But when you start screwing around with relations, foreign keys, failing to properly propagate changes to every copy of a denormalized data structure...oh man, you can spend days trying to untangle that mess. And it's a class of error that relational DBs don't have unless you misuse them badly.
In short, you seem to be suggesting that as long as you're blindly using an ORM, Mongo is almost as good and flexible as Postgres. Which...sure, okay? I guess?
Nitpick: the "relational" in "relational database" is not a reference to the concept of foreign keys, but to "relational algebra", the mathematical model for regular data structures that SQL is based on.
I disagree with this. After doing a bunch of work with SQLAlchemy and Hibernate over the years, I've developed a major dislike to ORM's as a replacement for SQL and would much rather write my queries in plain SQL. My currently favourite SQL library is HugSQL[1], which lets you write your queries in SQL in .sql files and HugSQL will load them and provide functions which you can call to invoke the query.
I find SQL quite solid for querying and updating data. Its what it was designed to do. Much more solid that the query API's I've seen in ORM's.
However, more importantly, when performance matters, it is very difficult to properly understand performance characteristics of complex ORM queries (I often find myself making the ORM generate raw SQL, which I then manually review to determine what its doing (eg what indexes it uses, etc)) and I find it much easier to optimise SQL than ORM calls.
My point isn't to badmouth ORMs. If you like them and they solve your problems, that is awesome. My point is that calling SQL a "shitty language" is purely subjective and your opinion and other people have different opinions. SQL is not an objectively shitty language. I think its a pretty good data query and manipulation language. Not perfect, but definitely not shitty.
And no, I'm not a DBA. I'm a programmer who hates having to mess around with databases. It is for this reason that I want things to just stay out of my way so I can get my work done with minimal touching of the database. I've recently found myself forced to do things I never wanted to do (1. change data capture on a MySQL database; 2. query and table optimisation to make a database perform and scale better) and for #2, my life would have been a lot easier if I could have worked with raw SQL.
All your comments come across as confused mate. Enforcing arbitrary constraints belongs in the business logic of your app, but dealing with consistency issues does not. Postgres handles row constraints just fine, you can enforce any arbitrary logic (i.e colX != colY and its not the first Tuesday of the month), combined with stuff like expression indexes, json type and actual schemas leaves me confused as to why you would advocate mongo so hard.
Just because you write your migrations in a language like JavaScript doesn't mean it's any better than pure SQL.
The first can easily be modelled in SQL, the second should be modelled in your app. The first are not arbitrary, the second are. Not hard to comprehend mate. Just because SQL can't model all constraints doesn't mean you should throw it out the window and reach for mongo.
Comments like 'none of you understand this because youre not programmers and therefore know nothing' is incredibly insulting. I'm of the opinion that you know nothing, and if you're happy knowing nothing with fellow mongodb users then so be it. Just keep your nonsense away from me thanks.
It's not arbitrary, because the constraints at least in Postgres are themselves constrained to validating a single row in a single table. Your app code can be truly arbitrary, you could trigger a device that posts a carrier pigeon to someone many miles away and waits for it to return with a result. That's arbitrary.
> Just in case you forgot the original claim, it was that if you use a SQL database, the constraints are in your database, but if you use mongo, the constraints are in your code.
Sorry, that's not what I was claiming. I was stating, correctly, that constraints relating to the data belong in the database. Constraints belonging to the business logic that powers your app belong in your app. You can be 100% sure that a constraint in your database will be respected, whereas the same can't be said of your apps code.
If you are given a specification that says "no username can be longer than 12 characters", that's what you add to your database constraints. A specification that says "No emails from temporary email providers are allowed" belong in your app code. Clear separation of concerns, that mongodb has no clue about. 'Just validate everything in your code, and if you get junk data in your database then I fucking hope your code can handle it'. Please.
> Therefore, saying that it is a great win that a SQL database can do some basic constraints checking
It can do pretty complex constraints. On the data. The more complex, completely arbitrary constraints belong in your app code.
> is a ridiculous argument because it hardly contributes any benefit due to the fact that even more complex constraints are in the code.
What. That makes no sense. So having 100% certainty that no column value in your database will violate any of the constraints you place on it 'hardly contributes any benefits' because... you have other, unrelated, business constraints in your app? What logic is this?
The only limitation on an SQL check constraint is that you cannot use columns in other tables.
As has been pointed about by many of us slow learners, it's far easier to understand constraints in SQL than it often is to dive into a code base.
You moron, that's not a good thing.
SQL is a DSL. It has pros and cons for data manipulation. For many combinations of (particular programmer) + (particular thing to get done) + (set of considerations), SQL turns out to be a better tool than a more general-purpose programming language. YMMV, obviously.
> Err no it isn't false. It is based on real-world experience, rather than "theory" about "what you can do". In the real world, people use ORMs and the ORM migration library, and then they find that the migration library doesn't do what they need it to do, so they have to use raw SQL. But then they run into the problem that because the ORM handles all of the generation of indexes and column names and so on, they have to figure out the correct naming of everything so that the ORM understands what you have done. This often results in failure because the ORM does not want you to tinker with the schema yourself.
It sounds like you're making a decent argument against manually modifying a database schema at the SQL level, behind the back of some ORM's which can't gracefully handle such surprises. But I don't think anyone is disagreeing with you about that.
> Which requires you to context switch from the code to the schema and piece together all of the relations based on foreign keys.
Many programmers can handle that complexity. Maybe the additional layer of indirection / complexity added by your ORM is the particular systems you deal with too hard for you to reason about?
Unless that's a reference to a house in the room class, that is not in any way a good design. And if it is a reference, then you implement it like this:
House is a table
Room is a table that has a foreign key that references the House primary key.
Can't get much simpler than that!
Your other comments already make you sound like a petulant child and a moron (or at the very least, someone with no actual real-world experience), but this just the cherry on the cake.
SQL is extremely powerful, and using it effectively doesn't take much more effort than learning another new mainstream programming language.
Anyway, enjoy your tortured use of shitty ORMs + Mongo for your toy projects; move aside and let the real programmers go on with their work.
Aren't mongo queries written in a subset of JavaScript?
SQL syntax is a bit crappy, but it is still one of the easiest languages to comprehend what is going on when reading code written by someone else.
You must really not understand SQL. SQL is one of the easiest languages to work with sets of data.
Are you one of those people who pull back 100k records from the db in order to get a random 25 using a 'real' programming language?
SQL is old but it isn't shitty.
Reasons:
1) It is based on a solid mathematical theory[1].
2) It is declarative. Replacing SQL with a procedural language would be a spaghetti code disaster.
Oh really? What exactly cannot be expressed in PostgreSQL check constraint, given the fact you can write it in any programming language you prefer?
Anyway, any sufficient modern RDBMS implementation provides pretty good level of performance even with serializable isolation level, thanks to decades of tuning and research in the field.
And, yes, performance should in general still be good, but there is less knowledge out there about how to solve the performance issues specific to serializable since there are few people who use it (at least in the PostgreSQL world).
Broadly speaking, of course there are some quirks in the field, let's start from "A critique of ANSI SQL isolation levels"[1] by Jim Gray et al., an author of highly respected fundamental book about transactions [2]. But the very kind of problems discussed in RDBMS world, is a rather contrasting with an "ACID? why do we need it?" attitude that is so often in the world of "web scale NoSQL".
[1] http://dl.acm.org/citation.cfm?id=223785
[2] https://www.amazon.com/Transaction-Processing-Concepts-Techn...?
That's a funny way to say that. :-) Because what it really means is, "wanting to enforce a total lack of parallelism when under parallelism." I mean I get what you're saying, it was just funny to read.
Also: how do you enforce such constraints with MongoDB exactly? If the answer is "do it in the application", then your answer applies to relational databases too.
When I have solved this case I have done it in the application by making sure we are only using a small set of carefully audited stored procedures to modify the event table.
MongoDB does not even try to solve this kind of problem, and I am the wrong guy to ask if you want someone to try to make a case for using it anywhere.
Or you deny direct table inserts, and provide a stored procedure for inserting multiple rows in one transaction. But I see how it could be useful in the generic case.
Some people sound like they haven't even tried Mongo.
It's great that PG has JSON support now, but a few years ago people were having together stuff on hstore, storing their geo objects as binary blobs that you can't read without extensions etc.
SQL databases have been playing catch up with JSON, MSSQL being the worst. Even with JSON support, it's still a pain looking at some SQL queries that have to be written.
If postgres does it for you, don't go bashing everything else that tries to be an alternative I feel.
People bash on MongoDB because they care. People just want to raise awareness that there are two types of MongoDB users: Those who understand it is a deeply broken system, and those who haven't used it thoroughly. The truly careless and anti-social approach would be for people to leave no negative comments about MongoDB and let others fall into the same trap.