8 ms·
In the recent two or so years I was surprised to see how many people don't _really_ know about SQL. Sure they can write a simple insert and update query and may
by dathinab 7y ago
In the recent two or so years I was surprised to see how many people don't _really_ know about SQL. Sure they can write a simple insert and update query and maybe a join, assuming they can just ignore that there actually are different types of joins. But if you ask them about other _still fundamental_ knowledge like a _rough_ idea about what the different transaction isolation level are/mean/imply for you, they fail hard.
Realizing this allowed me to understand why:
1. There is so many used ORM(ish) libraries which make it in practice harder to access the database for anything but trivial queries.
2. NoSqlish databases seem to have became so successful even in application where non of there "benefits" (wrt. scalability and similar) matter and you would normally prefer to avoid some of there drawbacks (e.g. eventual consistency, no "system wide-ish" transaction, enforcement of correct schemata and some parts of data consistency). (Sure there are other reasons for there success, too. Like being fancy, modern or no clear requirements analysis and therefore no idea about scaleability requirements ...).
I mean if SQL and relational databases are for you just structs with a bit more basic types then JSON but which in turn are flat requiring annoying foreign key references and a bunch of ceremony around this with very little added benefits then yes it makes so much more sense to just use a nosql database and be done with.
(PS: Yes I'm aware that for certain use-cases the resulting databases can be very complex and hard to use, what I mean is that it's a skill you have to learn to use efficiently and a scarily large amount of people a came in contact with in the recent years not only don't have that skill but are not aware that, if they don't want to mess up larger databases they work on, they will have to learn that skill.)
- jackcodes 7y agoYou really don’t need to know transaction isolation to do the vast majority of work on a typical web app.
- taffer 7y agoI wouldn't say so. Concurrency bugs, like lost updates, can be very subtle and hard to debug, and common databases don't prevent these kinds of bugs by default.
- momokoko 7y agoIf someone on my team was writing a web app and spending time meddling around with transactions outside of whatever abstraction layer we were using on top of the DB without an incredibly good reason, we'd definitely be having a coaching moment, and if it continued, I'd likely help them find a role with another org where they might be a better fit. Any quality database abstraction provides fairly simple means to perform transactions.
- vbezhenar 7y agoTransactions are hard. You can't just abstract it. Those different isolation levels are there for a reason.
- vsareto 7y agoJust slap READ UNCOMMITTED on everything to get your speed back /s
- deleted 7y ago[deleted]
- coldtea 7y ago>If someone on my team was writing a web app and spending time meddling around with transactions outside of whatever abstraction layer we were using on top of the DB without an incredibly good reason, we'd definitely be having a coaching moment, and if it continued, I'd likely help them find a role with another org where they might be a better fit. And most people with knowledge of DBs should run, not walk, away from a team that operates like that...
- momokoko 7y agoBecause if you are writing a fairly standard web app, you don't need this. There isn't the scale to make it important. Software is a very large field, with a wide variety of use cases. Obviously an exchange trading platform is going to have much more focus on these DB issues than a SaaS web app for business that expects at most 5,000-10,000 users at a time. With very few of those user modifying the same records.
- mpartel 7y agoIt can seem that way, but the consequence of not knowing them is lots of subtle race conditions. Granted, most webapps probably don't have enough users to trigger them too often, and the impact is often limited e.g. because the kinds of inconsistencies you can introduce don't actually matter that much in many cases. For the majority of webapps, I'm not sure the default isolation level of MySQL and Postgres is an appropriate tradeoff, since I'd rather have correctness by default than performance by default.
- jackcodes 7y agoThe vast majority of work on web apps is probably writing CRUD controller methods, proxies to upstream services, or React components. It’s like saying CSS transitions are vital; they aren’t, but probably affect the user experience for the average Joe just as much if not more than a phantom read. At that level of depth you just hand it over to the CSS champ or the DB champ.
- vsareto 7y ago>At that level of depth you just hand it over to the CSS champ or the DB champ. Developers are blaming other developers for not knowing everything they do because businesses forced them to learn everything (Fullstack), but that's actually a bad arrangement and somehow the solution is to just punish the developers more rather than hiring DBAs.
- vageli 7y ago> >At that level of depth you just hand it over to the CSS champ or the DB champ. > Developers are blaming other developers for not knowing everything they do because businesses forced them to learn everything (Fullstack), but that's actually a bad arrangement and somehow the solution is to just punish the developers more rather than hiring DBAs. If the organization views developers as cogs or something like a software assembly line, the desire for interchangeable, unspecialized workers seems like a natural consequence.
- mpartel 7y ago
- brightball 7y agoMy go to interview question to test basic SQL knowledge is use of “HAVING”. It’s a surprisingly good filter. EDIT: To clarify since this got more attention than I expected...I don’t disqualify any candidate based on the answer to a single question. I just use that question to assess their actual SQL experience. If you’ve spent any amount time writing raw queries by hand for reports or just to pass through to a web service, you’re going to have run into HAVING. It’s a simple part of the basic SELECT syntax without needing to involve joins, subqueries, index optimizations or specific knowledge of database internals. It’s a fair question and the only way you wouldn’t have run into it is from working purely with ORMs or NoSQL. There’s a lot you can do without it, but it goes a long way in determining my ability to ask you to open up a query analyzer in different environments. It’s also going to tell me a lot about the way you think about data problems and where you are going to gravitate for certain types of logic.
- sitkack 7y agoI like to structure my questions such that they could using HAVING or a CTE or subquery and not test on the piece of knowledge directly, but the application of knowledge. Like in Python if you asked lots of questions with list of numbers ... and then generalized it to infinite lists of numbers. If the first case I'd like to see list comprehensions, and then generator expressions or possibly the use of itertools.
- brightball 7y agoThat tends to be how it leads in. We’ll talk through building a simple CRUD application with a single table and then ask questions about how to get specific data for somebody in marketing. There are a lot of ways to answer it, but HAVING is the simplest.
- rockinghigh 7y agoHow would you know it’s a good filter? What position are you interviewing for?
- kradroy 7y ago
- StriverGuy 7y agoRegarding your 2nd point: commercial grade NoSQL db's can do all of these things now. I think the notion that they cannot is from the v1-2 era of Mongo about 7 years ago. You should read the Dynamo white paper and subsequent documentation on write consistency, transactions etc. and also look at the feature set available on Mongo V4.2.
- VectorLock 7y agoWe all still get a good laugh at the "MongoDB is webscale" video.
- MiroF 7y agoThere are still serious concerns in terms of speed for anything involving analytic applications.
- ken 7y agoCommercial grade databases have done all these things since the 1980's. We just hadn't invented the name "NoSQL" for them, because we hadn't yet realized we were supposed to hate SQL. We called them Object Databases, because it was the decade when objects were cool. It also gave us C++ and Objective-C and Object Pascal. Basically we were just gluing objects onto everything we could. I've used one. It was great. They (mostly) failed because of business and politics and a whole lot of other reasons unrelated to the technology. I'm sure by now everybody here knows that systems don't succeed or fail based purely on the technical qualities of their design and implementation.
- JustSomeNobody 7y agoThis is a side effect of the extremism that happens in our industry. A decade and a half ago there was a hard push on ORMs, to the point that people would refuse to work on a code base that didn’t use one. So a lot of developers simply “grew up” not learning SQL (properly). We can easily enumerate dozens of other technologies that developers hard push on only to years later realize they’ve swung too far.
- kevindong 7y agoWith SQL, I started out using an ORM at my first internship and I've been using an ORM ever since. Over time, I've grown to dislike ORMs for anything more than anything trivial (a simple select statement with a handful of 'WHERE' clauses, an insert, or a delete). Most ORMs try to generalize the behavior between various RDBMS (Postgres, MySQL, SQLite, Oracle, MS SQL, etc.) and end up being exceptionally leaky abstractions. When combined with tools that create a schema from code (which are in of themselves quite leaky), the resulting schema is frequently atrocious and lacking in both safety and speed.
- he0001 7y agoCould you describe what you mean by “leaky” in your experience?
- kevindong 7y agoFor ORMs, the ORM is theoretically supposed to hide all details of the underlying RDBMS from the developer and only expose a set of common APIs. In actuality, you have to be acutely aware of the subtle differences between the various RDBMS and how they interact with the ORM. Generally the most frequently used functions are fine. But the less frequently used functions (which tend to be some of the most useful functions) may be dramatically different in behavior dependent upon the RDBMS [0]. For tools to generate a schema from code, the common data types are simple since the tooling is usually good enough to handle those common cases. But once you get into the non-trivial types (notably numerical types with custom scale and precision and timestamps), things go haywire very quickly. Another example is that all of the migration tools I've ever used have required the developer to define the model separately from the actual migration code which inevitably causes drift in the schema/migration code at some point. Anyone that tries to define non-trivial relationships between tables/entities using non-SQL code is usually doomed to strange relationships in the RDBMS that'll make it arduous/near-impossible to ever use a different migration tool/ORM. I've seen some horrifying schema designs due to a near religious dependency on ORMs/automatic migration tools (foreign key references to the same table, join tables lacking foreign key constraints, columns intended to be foreign keys lacking a foreign key constraint, tables lacking in primary keys, etc.). [0]: Notice how constraints are deferrable and adding comments onto columns are only available on certain RDBMS. https://sequelize.org/v5/manual/models-definition.html https://sequelize.org/v5/manual/models-definition.html
- MatekCopatek 7y agoWhat would you say is the special thing about this particular bit of knowledge that made you point it out? To be perfectly honest, it seems quite arbitrary to me. I could say I'm surprised to see how many people don't know anything about the browser rendering pipeline and consequently produce badly performing interfaces. Someone else would complain about people's inability to do even the most basic Linux administration tasks. But at the end of the day any single bit of knowledge doesn't say much about your general ability to be a developer. The term "web development" covers an extremely wide array of topics. Luckily, there are many wonderful pieces of software out there that abstract things away and allow you to produce a finished product even if you aren't an expert in everything involved.
- yowlingcat 7y agoIt's not arbitrary because screwing up your data integrity is far and away one of the most painful and business critical mistakes you can make as an engineer. Your product isn't finished if it looks like it works, but is either storing data improperly at rest or doing the wrong thing with it.
- jackcodes 7y agoI think the point to be made was that if you screw up your browser rending to the point of it being unusable on old devices, that could be just as business critical as the data integrity issue. Making either of those your line in the sand for what a developer should and shouldn’t know is arbitrary. I don’t know a ‘Scala’ full-stack developer that could do advanced optimisation on React render times, or a ‘React’ developer that knows transaction isolation. Not knowing either of these does not make you an unworthy developer and unless you have a specific need for these skills it probably does more harm than good expecting everyone to know them.
- yowlingcat 7y agoThe difference is that if you screw up your browser rendering, you're not messing up your data integrity at rest and creating compound technical and potential business debt. If anything in the critical path from pricing to payments that talks to your data store makes a mistake, you can risk anything from not recording your transactions properly to charging the wrong amount or not enough at all. It's very expensive to acquire customers. It gets even more expensive when you have dirty data from incomplete domain modeling.
- chaostheory 7y agoI’ve used both relational DBs and nosql professionally. While in super specific use cases, NoSQL does shine; it’s a giant mistake to use them as a generic, general data store like with a traditional SQL database. You end up doing a lot more avoidable work in the long run especially if you’re doing reporting ie something that SQL can easily handle, you end up having to replicate it in your own code base
- nostrebored 7y agoWhy is making a materialized view for reporting hard? Shouldn't components be aware of what they need to enable analytics? I don't see how adding logic to a service at runtime is any different than fiddling with queries and optimizing them
- chaostheory 7y agoWith SQL the logic of filtering and joining is already mostly done for you. You’re basically now reimplementing this logic if you’re not using it. In most cases it isn’t difficult but it still time consuming and more prone to bugs vs using SQL I don’t see the point of reinventing wheels unless you have to, unless you have lots of time and energy to waste
- gigatexal 7y agoI agree. Use the right abstraction and the right number of them: I’ve yet to find an ORM that didn’t kick you in the face In terms of performance to only hold your hand with queries. SQL queries can be done in code in a sane way to avoid sql injection or you could implement stored procedures and functions to make operations more ORM-like but not pay any of the ORM tax. It’s just annoying to see the DB treated so flippantly by devs who are Hussein Bolt quick in grabbing an ORM when it’s not always the right tool or even necessary. They’re great for prototyping but not for production imo
- notduncansmith 7y agoI think HugSQL[1] (Clojure library) has really opened my eyes to what DB interaction should be like. It’s not exactly an ORM, but it protects you from SQL injection and makes it convenient to leverage the capabilities of your SQL database. Also, his name is Usain Bolt[2]. [1] https://github.com/layerware/hugsql https://github.com/layerware/hugsql [2] https://en.m.wikipedia.org/wiki/Usain_Bolt https://en.m.wikipedia.org/wiki/Usain_Bolt
- jareds 7y agoI've found Mybatis[1] to be a better alternative to Hibernate. At least with the version I was using you had to do complex mapping in XML instead of annotations which isn't great but I'll make the trade-off to avoid the ORM. Not sure if this is improved in 3.5. [1] https://github.com/mybatis/mybatis-3 https://github.com/mybatis/mybatis-3
- gigatexal 7y agoSure I am not saying crafting queries explicitly and preventing sql injection etc in code is elegant but it can be done and you can write super thin abstractions over the boring bits and still maintain a ton of power over your queries and know exactly what is going on without having to add another dependency. The one huge win for ORMs in my mind is the standing up of and the iteration of schemas, defining a schema and then summoning a database in alembic vernacular is really neat whereas one would have to manually manage a set of init scripts and migrations oneself. In the end there's no really clean way to do DB maintenance and work it's just work and has to be done, imo.
- naner 7y agoA simpler explanation is that some people will just "learn" (via googling) the basic SQL they need to get something done. This is not uncommon across all domains in our industry.
- Aperocky 7y agoTBF, sql queries is something you _don't_ want to have in your code stack. It should be abstracted away in some kind of DAO, and no one should be worrying about SQL queries when they're developing new features.
- NikolaNovak 7y agoFascinating statement which I feel is probably agreed upon by many. I can see how that can be true; but once again,I feel, demonstrates that HN has a specific subset of "developers"/"techies"/"enthusiasts". In any "Enterprise" environment I've ever been in - core back-office enterprise resource planning apps or similar - SQL is right there front and center. "Should" it be? I don't know, I'm not a theoretician; but all developers around me for 20 years have had near-DBA level of SQL skills. Their core skills really are a) Understanding business requirements and b) Understanding SQL. For any complex business requirement, by time, we find in transactions code takes 1-10% and database activity takes 90%. So skillset in optimizing database performance of your code vastly outbenefits the skillset of optimizing your code. As much as possible, we push everything to database - queries, looping, even business logic if we can - because it's a robust, mature, optimized product and we don't have to invent optimizations from scratch... Edit: If it may prove illuminating, even team leads & management on any of the 30+ projects I've been on, understands runstats, index reorg, transaction isolation, database maintenance basics, and a few basic optimizer dos and don'ts. They just wouldn't be able to survive without that knowledge - it's 90%+ of both good stuff we develop, and bad stuff that goes Bang in the night (e.g. a process which worked for 10 minutes for 2 years but suddenly decided to asymptotically explode and not finish before heat-death of the universe :P)
- Aperocky 7y agoI think the phenomenon you're describing is exactly what led to the rise of no-sql dbs. SQL allow you to do too many things, a lot of things that could happen in programming. From a design point of view, that's not a good thing, you want a data store to do just what it is - a data store, all joining and such can happen somewhere else - just make sure it's in the same transaction. I see many of the problem stem from a undesirable design of the tables - not splitting them when you can, having too many foreign keys, and generally being a interconnected mess. Having dealt with nosqls, I attest that it is much better in that it forces you to design things in an atomic way, preventing many of the potential problem you might have down the line from the beginning.
- jackschultz 7y agoTons of replies to your comment already, but for point #1, this was absolutely the case for me. ActiveRecord is incredibly, incredibly good for pretty much all basic use cases. However, it comes with drawbacks like you said. It took me too long to realize I was behind on knowing sql to get to the next level. To the point where I'm working today to fix slowness in a client's app where it relies way too heavily on the ORM and plain ruby code instead of going more in depth with sql. Knowing sql more in further depth is the number one skill I'd want to tell backend devs to learn. Also, I'm mainly a python guy, and if we're talking about ORMs, I will say that SQLAlchemy doesn't live up to ActiveRecord, to the point where I built my own db connection code for queries in some of the other flask / general python apps I'm working on.
- mmsimanga 7y agoSome months ago I sat in a meeting to discuss design and implementation details for a medicine delivery system. The fact that it was about medicine I thought it would be obvious to everyone that we would have to use a relation database with ACID properties. Last thing we want is to send medicine at best effort, I assumed everyone would be on the same page on the need to use transactions, foreign keys and all other data integrity mechanisms baked into relational databases. I was taken aback when the app developers really could not understand why we needed to use a relational database. A document database was going to be fine. Needless to say it was a long meeting and I wasn't invited back.
- geitir 7y agoSo they went with a non relational?
- mmsimanga 7y agoI wasn't invited back so I don't know. The back story is there had been some initial discussions before the meeting I attended. I was invited because my area is data analytics so the project owner wanted to keep me in the loop. It appears I upset the apple cart with my opinions. I was polite but firm. I don't regret it because I spend my life cleaning up messy data from systems not using core relational principles to get analytics out. Very few systems use foreign keys.
- wehrkeoruw 7y agoThere are lots of ways to interact with folks who don't understand something that get your point across without making them feel bad. I'm guessing you didn't employ any of those strategies.
- mmsimanga 7y agoThe truth is probably somewhere in between. I was firm but polite. I was called in because I was going to be analyzing the data. Build data warehouse and import other data. I had to be firm because I know how much time it takes to clean up data before you can analyse it.
- MaxBarraclough 7y ago> But if you ask them about other _still fundamental_ knowledge like a _rough_ idea about what the different transaction isolation level are/mean/imply for you, they fail hard. More a property of concurrency than of SQL, I believe; your point applies for programming languages too. It's possible to get by as a programmer with no real understanding of how concurrency works in the relevant languages. If concurrency bugs were always obvious, doubtless things would be different.
- jugg1es 7y agoMy company is in the midst of a modest hiring blitz and it is really eye-opening how few applicants are actually writing SQL in the coding exercise as opposed to using a highly-opinionated ORM. More damning - in my opinion - is that they mostly have blinders to how much time they are spending working around ORM limitations on a day-to-day basis.
- free652 7y agoThat's because a different database may have different transaction isolation than an another one. Fairly complex SQL queries cannot be used between different major databases. I am one of those who don't care about SQL, I assume it will fail and in general avoid transaction as much as possible.
- henrik_w 7y agoI never learnt SQL "properly" until I took one of the first MOOC courses, Introduction to Databases by Jennifer Widom. Really good for getting a better understanding. Also, for a fantastic source on database transactions and isolation levels, check out "Designing Data-Intensive Applications" (chapter 7) by Martin Kleppmann. Really a great book! I have written about both these here: https://henrikwarne.com/2011/12/18/introduction-to-databases-on-line-learning-done-well/ https://henrikwarne.com/2011/12/18/introduction-to-databases... https://henrikwarne.com/2019/07/27/book-review-designing-data-intensive-applications/ https://henrikwarne.com/2019/07/27/book-review-designing-dat...
- kjerzyk 7y agoWhat would you recommend to pass the level where you can use SQL to get the data you need and move on to a more advanced level? Any particular book/course?
- Guest42 7y agoItzik Ben Gan has excellent books and sql for smarties by celko is great as well.
- lesservehicle 7y agoNot everyone is a DBA?
- marcosdumay 7y agoYou mean that only DBAs need to know SQL?
- pmoriarty 7y agoI really don't get all the hoopla about learning SQL. There's really not much to it. It can be learned in an afternoon. It's by far the easiest language I've ever learned. To all the naysayers and downvoters: There's a difference between knowing SQL and being an SQL ninja or a master of database architecture. Those are three different things. The former, and what my original comment concerned, was just learning the language itself. I concede that learning to be able to craft complicated, performant queries and design complex databases with any desired property you might dream of is not quick or easy, but I still maintain that learning the language itself (sans extensions) is.
- deleted 7y ago[deleted]
- vsareto 7y agoThe finer points probably require a bit more than that. If you're already technically proficient in general programming (or Excel), you're likely to pick up SQL quickly. There is immense, unwarranted filtering of people from jobs for relatively minor things.
- AlphaSite 7y agoBasic SQL sure, but the moment you start getting into complex operations, upserts, extensions, functions, custom aggregate operators, arrays, HStores, etc it gets complicated. At least for me. I would not be able to do this super quickly: https://stackoverflow.com/a/42939280 https://stackoverflow.com/a/42939280
- andybak 7y ago> which make it in practice harder to access the database for anything but trivial queries. I did my time. I understand normalization. I've got a vague understanding of the various normal forms (except 6NF which nobody seems to understand ;-) I can use a query profiler and I've occasionally read up on the different index types and their performance profiles. But I still prefer to use an ORM. SQL syntax just never fitted my brain. And the ORM I use can handle aggregation, annotation and a bunch of stuff that you couldn't really describe as "trivial queries"
- andybak 7y ago(Actually - just read up my Codd again - I think it's 5NF that always had me stumped)
- scarface74 7y agoThere is nothing about Nosql databases that force you to have eventual consistency or lack Transactions. As far as forcing the correct schema, if you are using a statically typed language, it can enforce a “schema”. For instance with C#, LINQ and the standard Mongo driver, you work with strongly typed MongoCollection<T> and perform LINQ queries with IMongoQueryable<T>. Amazon’s DynamoDB for instance supports transactions and optionally consistent reads. With Mongo you can set your WriteConcern to force strong consistency. Mongo also supports transactions.