Hacker News Reader

Maybe this makes me a grumpy old man, but I feel like if you're primary role is to write software which interacts with a SQL database you should understand how to interact directly with that database. Not because you're going to do it frequently, but because understanding the thing your ORM is abstracting away for you allows you to more intelligently use those abstractions without making whoever runs the underlying database cry.

One of the things that taught me the most about SQL is trying to use Hibernate (There's not enough expletives in the english language to describe that thing).

It's one thing to have a query language (DDL and DML no less) that was built for a different use case than how it's used today (eg: it's not really composable). But then you stack a completely different layer on top that tries to abstract across many relational DBs - and it multiplies the cognitive surface area significantly. It makes you become an expert at Hibernate (JPA), then learn a lot about SQL, then learn even more about how it maps into a particular dialect of SQL.

After a while you realize that the damn ORM isn't really buying you very much, and that you're often just better off writing that non-composable boring SQL by hand.

- assuming you have a decent testing infrastructure in place. Much of the supposed benefit of ORMs is about a form of psuedo-type safety, and making it easier to add more fields. If you have fast running tests that exercise the SQL layer, you might find those benefits aren't very compelling since you have such rapid feedback for your plain SQL anyway.

I've almost never changed the vendor of DB in a project, so that's another supposed benefit that doesn't buy me much. I have often wanted to use vendor-specific functionality however, and often find an ORM gets in the way of that.

To sum it up - I agree completely. If it's your job to wrangle an SQL DB - you ought to learn some SQL.

Can you explain this part to me?

> assuming you have a decent testing infrastructure in place. Much of the supposed benefit of ORMs is about a form of psuedo-type safety, and making it easier to add more fields. If you have fast running tests that exercise the SQL layer, you might find those benefits aren't very compelling since you have such rapid feedback for your plain SQL anyway.

"decent testing infrastructure" is kinda doing a lot of heavy lifting — I love TDD but none of the startups I've worked at agreed with my love of TDD. There are tests, but I suspect they wouldn't fall under your label of decent testing infrastructure.

But let's say we do have a decent testing infrastructure — how does this solve the type safety benefit that you mentioned?

> because understanding the thing your ORM is abstracting away for you allows you to more intelligently use those abstractions

If that's required, then you are working with a bad abstraction. (Which in the case of ORMs you'll probably find many people arguing that they are often bad abstractions.)

I agree in a sense but I'm primarily a front end dev. Our backend devs (whose SQL skills are probably on par with mine) maintain the DB tables and such using our ORM but I would still need to use the ORM to query the DB. I'll hop in and make a DB update on the backend if I need to, but my point is that many front end devs are probably just using the ORM to query and run mutations. Such queries could certainly be optimized a bit by knowing a bit of SQL (adding indices to commonly queried cols for example) but I'd still consider solid knowledge in SQL to be a solely backend thing.

That said, I do know enough basic SQL to understand what ORMs are doing at a high level, but because I almost never write SQL I wouldn't consider myself proficient in it.

It'd been nice if the interviewer had informed you in advance that they were going to ask you to write SQL, so you could have prepared? Or you could have saved some time by cancelling? Why don't more companies do that

The screening call had some sql questions which I was able to answer. Basic sql. I feel like I could have saved a day of interviewing if this simple fkey question was in the screening.

Companies should do that more!

> if you're primary role is to write software which interacts with a SQL database you should understand how to interact directly with that database.

I agree that there should be a general understanding one should be able to interact with it when needed. But at the same time I don't think devs need to be able to spit out queries with the right syntax on the spot in an interview setting.

Unless I'm doing an exercise where the candidate is actually writing software (in which case they can have Google, their favourite IDE, and if they must an LLM) I never expect exactly correct syntax in an interview. I'm assessing whether they demonstrate the sort of thought processes you'd expect from someone who knows what they're talking about, and can get in the same ballpark.

in our particular case, they could use google. that said, I didn't ask them anything particularly complex. the AI depndant coders(I saw nothing in their abilities that demonstrated engineering) broke down trying to update a single record of known id with a new value.

Not everyone wants to make that much money.

I guess this is my first old-senior moment, but even if you use an ORM then you should know basic SQL. That table structure will be the most long-living thing in your system and probably outlive the ORMs and a bad table structure is going to create a mess later on.

I agree. I know _basic_ SQL but because I don't work with it every day I don't consider myself proficient. I read the migration files generated by my ORMs just out of curiosity and to make sure it's not doing anything crazy, but I wouldn't be able to write the migration file myself.

All projects I worked with, that used ORM, were burning pile of shit and ORM was a big part of this. I hate ORM and would prefer SQL any day. In my projects I almost never choose ORM.

I trust that some people can deal with ORM, but I know that I can't and I didn't see anyone who can do it properly.

So, I guess, there are some radical views on this issue. I wouldn't want to work with person who prefers to use ORM and avoids know SQL, and they probably hold similar opinion.

It is really weird to me that someone would call SQL low level. SQL is the highest level language available in the industry, definitely level above ordinary programming languages.

Interesting. The reason I like ORMs is because of type generation in TypeScript. I've never worked in a company that didn't use an ORM which is likely why I prefer it. But if I ever did work in a company that used raw SQL I'd probably just suck it up and learn better SQL. Maybe then I'd be able to make a more informed opinion.

With regards to SQL being low level, I primarily work with TypeScript so a language that talks directly with the DB (SQL) seems pretty low level compared to TS. I'm not sure what you mean by an ordinary programming language though (obviously not machine code).

I spent 5 years working at a place without an ORM. Due to sharding and scalability issues, an ORM wasn't possible (the tech was nearly 20 years old when I left, so they didn't get some of the later database scaling tech). When I went to a company with an ORM, I had problems.

Namely, the ORM got in my way so much. I knew exactly which query to run and how to word it efficiently, but getting the ORM to generate sane SQL was nearly impossible. I eventually had to accept my fate of generating shitty SQL at every company since then...

That being said, I'll always advocate for ditching an ORM if given the chance and the expertise is available. If nobody knows why you generally wouldn't want to put an index on a boolean column, we're probably good. If people think it will help performance on a randomly set boolean field, we should probably stick with an ORM.

Most ORMs I've worked with have a special method that lets you write raw SQL. Probably not the most ergonomic way, but it is an escape hatch. Which ORM were you using?

Most teams I've been on will automatically reject a PR writing raw sql due to maintainability concerns. I would never consider it in a professional context unless it can be done in a way that guarantees it is easy to maintain without using concatenation.

Most ORMs don't have the SQL tools we did to sanitize variables when putting them into queries. Some do, but not all.

Gotcha, that makes sense.

Java, Python, JavaScript, TypeScript are more or less the same and on level below SQL (when it comes to querying structured data).

The SQL is declarative query language. You describe the query, and database engine automatically builds a plan to execute the query. This plan automatically uses statistics, indices and so on. You don't generally specify that this query must use this index, then iterate over this table, then sort it, sort another table, merge them, the database engine does it for you.

Imagine that you have few arrays of records in JavaScript and you need to aggregate them, sort them, in an efficient way. You'll have to write your logic in an imperative way. You'll have to write procedures to maintain indices, if necessary. SQL does it better.

It it an interesting exercise to imagine programming in a language with built-in RDBMS (or object database system) for local or global variables. For example React Redux uses structures, which are somewhat similar to database. I don't really know if it would be useful or not, to write SQL instead of functional API (and get performant execution, not just dumb "table scan") but I'd like to try. C# have similar feature (LINQ), but it's just API, no real engine behind it.

Thanks! That makes sense.

I would assume he means creating a foreign key relationship from the posts and users table. Doesn't rails or prisma have a SQL migration tool? (Upon looking it looks like it's Active Record)

So the equivalent of

`rails db:migrate` after doing what you suggested in the interview. You could write in SQL as..

``` ALTER TABLE posts ADD COLUMN user_id INT, ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id); ```

I don't know if that's what he was after but that's what my mind jumped to immediately. I'd recommend learning a bit as sometimes I've found that orms can be a lot slower than writing plain SQL for some more complex data fetching.

I’ve written manual SQL for years in previous roles, but because I haven’t touched it in 6 months I’d have had to double check how to write that with a quick google. It’s just a bad interview technique to require write learned syntax.

I agree but lots of companies do similar things in their interview processes so we just have to know everything I guess.

What’s more likely is you get lucky one day and the company that hires you is the one that happens to have asked you the questions you brushed up on recently, rather than the company that’s the best fit.

Or if you have the luxury to choose, which can happen later in your software engineering career, you can simply turn down companies with bad interview processes. Personally I’m a fan of this method, but it’s a luxury for sure.

I've been fortunate in that I haven't had to do a formal interview for any job I've ever had, except for my first job. I've just been working either with people with whom I've worked, or referrals of those people. I know that's an extremely privileged place to be, but it's the best place to be IMO.

100%. Your experience and references should be able to speak to your abilities better than some challenge would anyways.

Thanks! I think I was (conceptually) missing the constraint/references part. Prior to that I had only worked with firebase and Mongo so I was just like "OK so I just put userID column on post table right?" and apparently no, not right, lol.

What's nice about prisma and hasura is that you can actually read the sql migration files generated, and you can set the logging to a level where you can read the sql being run when performing a query or mutation. I found that helpful to understand how sql is written, but since I'm not actually writing it I can't claim proficiency. But I can understand it.

Wait, people still unironically use ORMs instead of writing queries directly? Not surprising then that everything works like shit

Every startup I've worked at has at least done their first few MVPs using an ORM. I imagine because it's just a quick way to bootstrap a project. Since performance isn't really an issue before scale, sql vs ORM is just a matter of dev velocity, but I guess at scale most companies just never repaid the tech debt.

One thing is reversing a linked list during a white board interview. Another write a simple JOIN between two tables.

Come on guys, working on backend applications and not having a clue about writing simple SQL statements, even for extracting some data from a database feels...awkward

With NOSQL becoming more ubiquitous (for better or worse), it's not unfathomable that someone simply never had an opportunity to do something as simple write a join between two tables. Someone replied to my comment and taught me how in 5 lines of code. I read it and I'm like, oh that makes sense. Cool. I won't remember it exactly but I understand it. I wouldn't hold it against a front-end developer who's only ever worked with Vue to understand what happens when a React node rerenders.

My point is that there are acceptable levels of abstraction in all parts of software. Some companies will have different tolerances for understanding of that abstraction. Maybe they want a front-end dev to understand the CSS generated from tailwind. Or maybe they want them to know exactly what happens when a React node is rerendered. Or maybe the company doesn't care as long as the person is demonstrably productive and efficient at building stuff. What some consider basic knowledge can be considered irrelevant to others. Whether or not that has lasting consequences is to be seen, but that just brings us full circle back to the original problem at hand (is it good that people can vibe code something and not understand the code it generates)

It would be a crap shoot if I nailed the syntax for creating the tables and the foreign key constraint.

This might be something I'd ask about in an interview, but I'd be looking for general knowledge about the columns, join, and key constraint. Wouldn't expect anyone to write it out; that's the boring part.