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.
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.
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.
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.
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).
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.
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 don't have the SQL tools we did to sanitize variables when putting them into queries. Some do, but not all.
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
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)
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.
Companies should do that more!
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 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.
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.
> 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?
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.
Needless to say, I did not get the job, but several years later I still don't know how to answer his question.
I've worked with NOSQL (Mongo/Mongoose, Firebase) and I've worked with ORMs (Prisma, drizzle, Hasura), and I've been able to implement any feature asked of me, across several companies and projects. Maybe there's a subset of people who really do need to know this for some really low level stuff, but I feel like your average startup would not.
I think maybe it's similar to "can you reverse a linked list" question in that maybe you won't need the answer to that particular question on the job, but knowing the answer will help you solve adjacent problems. But even so, I don't think it's a good qualifier for good vs bad coders.