The growth of popularity of non-relational databases was a reflection of the emergence of a different applications and a different approach to development.
The "hate" for SQL was indeed often misplaced, but the need for different kinds of databases was real. The schemas of SQL were powerful but sometimes cumbersome to developers who need to rapidly iterate on their applications. The relational structure of SQL is sometimes ill-suited for the sort of graph queries needed in large social networks.
SQL is sometimes the wrong tool for the job. Other times it's a powerful tool; maybe even most of the time.
In short, there probably isn't any "one-size-fits-all" solution for databases.
But the need for non-relational databases was very real; just ask Facebook, LinkedIn, or Twitter.
---
CSS is another example of a tool that's "going out of fashion" for a real reason: it was never designed for the modular, component-oriented pattern that many developers are using for complex applications on the web.
There need not be a "one-size-fits-all" solution for ALL styling on the web, but CSS is hardly ideal for web applications that are a composition of modular components.
CSS probably won't go anywhere, as it is still useful for the more page-like content that it was originally designed for, but we still need something different for modular front-end application development.