I will share one that has served me well for over 20 years: learn RDBMS and SQL. Learn normal forms, good schema design, and how to write complex queries. NoSQL adopters often avoided schemas like the plague and ended up with unmaintainable messes after a few years. I have seen more than a few NoSQL -> SQL conversions by now. Data is the most important thing in your app. Give it a great design.
Plus databases like Postgres have key/value and JSON data types. Once you are sure that is what you need it’s still there.
Rob Pikes 5th rule of programming: Data dominates.
To expand on this, learn about some of the neat things you can do within a SELECT statement.
Learn how the JOIN syntax works[0], and how to use OUTER JOINs.
Learn about WINDOW[1] functions and what kind of problems they can solve. In particular, many reporting needs can probably be solved with WINDOW functions instead of tracking state as you loop through a result set in application code.
Learn about Common Table Expressions[2]. While these usually aren't necessary, they can make your queries a LOT more readable.
The thing about learning this type of stuff is that it doesn't matter what database you learn on, you can use it on virtually any SQL database (possibly requiring minor syntax changes).
This. Specially if you work with Node.js. The JavaScript community is susceptible to ignoring all previous knowledge, experience, wisdom and tries to reinvent everything. I appreciate the energy, but it is often misguided. Understand the difference between merit and marketing bullshit (stack names that include Mongodb?). Understand context. If you are using Node.js in green field project, you are probably better off using Postgres. Understand Lindy effect. Sql isn't going away, but the vendor specific query language that you are spending time with and hoping will replace sql is probably going to.
Do you have any recommendations on how to learn RDBMS/SQL? I'm currently using NoSQL at my day job (and agree with your points on it), but have struggled to find a good side project to really dig into RDBMS/SQL. I have used SQL briefly in the past (side projects/class projects) but I'm looking to really cement the skill in my mind.
Agreed, though interviews seem to focus on trendy framework of the month plus some random algorithm that I learned 20 years ago then forgot as I never needed it.
Any references in particular you recommend to learn schema design, particularly with an orientation towards performance (e.g. for high-volume sites)?
A couple years ago I got dinged on a take-home project that involved building a db schema, but didn't get any specific feedback on my work, and it's sort of haunted me to this day (especially since SQL is one of my primary languages)
Although it is 10 years old, not much has changed in the universe of SQL basics. If I were to capture the essence of good schema design it is mostly about keeping data normalized until you have a really good reason not to. Denormalization is almost always an optimization choice.
And before you optimize you should have basic things covered, like indexes, etc. I have fixed more than one "slow" query by simply adding indicies to everything people are joining on. So, check out a tool like pgAdmin that has a cool query planner optimization feature. What is happening under the hood doesn't matter a /lot/ when learning SQL, but it is really insightful to see how indicies of various types impact performance. I believe this book basically covers it all from a theoretical perspective. Optimization and indices aren't super well covered in SQL for smarties, which make sense, it isn't about optimization but is a little higher level.
There are /tons/ of data sets out there now a days. CSV files, etc. Find some interesting data and start challenging yourself with interesting ways to design that data into a database. I actually design most of my SQL databases using an ORM these days, but, my bedrock knowledge of SQL makes it very efficient and I can avoid committing "SQL sins" (denormalization) prematurely. You will be surprised at how much you can learn on simple data sets :)
Take a look at the "Relational Design Theory"[1] mini-course on Stanford/Lagunitas. Though I had used SQL for years, I took it as part of the early MOOC on databases that was offered in 2011. It is likely to match up with the intuition you already have about what works well in practice, but it can still be a challenge and rewarding to formalize your intuition and understanding.
The way the course is broken up now, you may need some of the other sections [2] like Intro to Relational Databases or Relational Algebra as prerequisites, since I do not remember if it used SQL syntax.
Reminder, Stanford is taking down this course on March 26th, 2020. They let you download courses that are setup for download (this course is not, unfortunately).
>NoSQL adopters often ... ended up with unmaintainable messes
learning SQL seems much more straightforward than learning NoSQL. personally I'd like to find a good resource that will help me use a non-relational database without creating a mess
> I'd like to find a good resource that will help me use a non-relational database without creating a mess
My plan for the day when someone requires me to use NoSQL is to say that postgres supports JSON/JSONB perfectly so I can use that as a NoSQL database and then use the relational part to keep me out of the mess...
Normalization is a thing in NoSQL and SQL. When you end up with data skip and tons of duplication it can get pretty crazy. Being able to normalize data fits with most data models so much better because data is mostly a collection of related bits of information.
Plus databases like Postgres have key/value and JSON data types. Once you are sure that is what you need it’s still there.
Rob Pikes 5th rule of programming: Data dominates.