3 ms·

TBF, 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

TBF, 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 ago
Fascinating 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 ago
  I 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.
  - NikolaNovak 7y ago
    >>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. See, that's exactly why I indicated I'm not a theoretician and do not feel comfortable making such a sweeping "Should" statement (much as I would've made an opposite one:). Immediately my question is "Why?" and "What data do you have to back it up?". I understand model-view-controller etc is a design pattern, but are we certain that it's always the right one? From my trenches perspective: relational RDBMS has been around for neigh half a century. It's an INCREDIBLY mature, optimized, understood (by experts), common, standardized (for all the individual RDBMS differences), safe technology. I have team members who have been doing hard-core SQL for 20+ years, and a market full of similar, serious, hardened experts. And it'll survive changes in the higher-up stacks - in my own meager career of ~20 years, relational RDBMS has been the most stable part of the stack. I can get a 3-5 year 'expert' on a particular programming stack, or a 20-year expert on SQL, who has seen things and will safeguard customer's data and business priorities with their life. Again, a very personal experience, but the dozens of clients I've been with, have broadly similar priorities, objectives and concerns on database level; and occasionally vastly different ones on layers above. It's a brilliant unifying common denominator. Without a fun discussion over a drink and whiteboard, and it could be my inability to see forest for the trees, but I'm just not convinced that "all joining and such should happen somewhere else" :-\ [note, I didn't downvote your comment - I don't necessarily share the same conclusions, but I think it contributes to discussion :-]
    - Aperocky 7y ago
      I think your point is perfectly valid, I put should when I might have said ‘I Believe should’. But nevertheless I stand by my own point and is not quite convinced, both of us bringing anecdotes from our own experience. It is always good to hear different opinions, though.
xrisk 7y ago
You can't optimize well enough if your abstraction layer is too far away from your application layer.
dagss 7y ago
I strongly disagree. For many backends/purposes, SQL is really the thing doing all the real work and the backend's just some fancy authentication and validation layer. Stick close to the technology doing the heavy lifting. If people abstract away SQL too much they just start reimplementing things in buggy underperforming code, using 1000 lines of Go or Java or whatever what should be done in 20 lines of SQL. Also, don't get med started on people thinking they can abstract away tye database for testing. The automated test suite should always include the real database you are working against and all queries executed. I will never write a backend again without testing against the real database (whether sql or nosql). Some people justify abstracting away the database saying "one needs to be able to switch to an arbitrary storage engine". Well..storing things is probably the main purpose of your backend. I sometimes turn that around and say, "I want to write things in SQL so that I can easily change what language the backend is written it".
- xrisk 7y ago
  Testing against the real database? Won't keeping a copy of the database just for that be expensive? Also, should devs really be seeing potentially sensitive data? On a side note, "I use SQL so I can change the backend language" is the hottest take I've heard in some time heh.
  - dagss 7y ago
    With database I meant an instance of whatever database engine you are using, not the actual data. My current project uses mssql. Each test run probably spins up and destroys 100 databases (inside one mssql docker container that is spun up for each test run). Each test function populates a DB from scratch (using the same sql migration scripts that we have used in prod), runs the test, drops the database. Can do that quite some times in the minute it takes to run the full suite. Point is to actually execute the SQL (or whatever NoSQL you use) as part of the test.
- Aperocky 7y ago
  And that's a problem - my opinion remain that individual sql queries are better left for analyst and data engineers to craft so they can discover whatever correlation they might be interested in. But for a full scale app it shouldn't be in the forefront, at least not in the same package as other logic. The entire idea of programming is the one where you abstract away things and then abstract away the thing you're currently writing. I agree that it has gone too far in some of the places, but abstracting SQL away is the first and almost one of the best abstraction you can make.

NikolaNovak 7y ago

Fascinating 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 ago
I 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.
- NikolaNovak 7y ago
  >>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. See, that's exactly why I indicated I'm not a theoretician and do not feel comfortable making such a sweeping "Should" statement (much as I would've made an opposite one:). Immediately my question is "Why?" and "What data do you have to back it up?". I understand model-view-controller etc is a design pattern, but are we certain that it's always the right one? From my trenches perspective: relational RDBMS has been around for neigh half a century. It's an INCREDIBLY mature, optimized, understood (by experts), common, standardized (for all the individual RDBMS differences), safe technology. I have team members who have been doing hard-core SQL for 20+ years, and a market full of similar, serious, hardened experts. And it'll survive changes in the higher-up stacks - in my own meager career of ~20 years, relational RDBMS has been the most stable part of the stack. I can get a 3-5 year 'expert' on a particular programming stack, or a 20-year expert on SQL, who has seen things and will safeguard customer's data and business priorities with their life. Again, a very personal experience, but the dozens of clients I've been with, have broadly similar priorities, objectives and concerns on database level; and occasionally vastly different ones on layers above. It's a brilliant unifying common denominator. Without a fun discussion over a drink and whiteboard, and it could be my inability to see forest for the trees, but I'm just not convinced that "all joining and such should happen somewhere else" :-\ [note, I didn't downvote your comment - I don't necessarily share the same conclusions, but I think it contributes to discussion :-]
  - Aperocky 7y ago
    I think your point is perfectly valid, I put should when I might have said ‘I Believe should’. But nevertheless I stand by my own point and is not quite convinced, both of us bringing anecdotes from our own experience. It is always good to hear different opinions, though.

xrisk 7y ago

You can't optimize well enough if your abstraction layer is too far away from your application layer.

dagss 7y ago

I strongly disagree. For many backends/purposes, SQL is really the thing doing all the real work and the backend's just some fancy authentication and validation layer. Stick close to the technology doing the heavy lifting. If people abstract away SQL too much they just start reimplementing things in buggy underperforming code, using 1000 lines of Go or Java or whatever what should be done in 20 lines of SQL. Also, don't get med started on people thinking they can abstract away tye database for testing. The automated test suite should always include the real database you are working against and all queries executed. I will never write a backend again without testing against the real database (whether sql or nosql). Some people justify abstracting away the database saying "one needs to be able to switch to an arbitrary storage engine". Well..storing things is probably the main purpose of your backend. I sometimes turn that around and say, "I want to write things in SQL so that I can easily change what language the backend is written it".

xrisk 7y ago
Testing against the real database? Won't keeping a copy of the database just for that be expensive? Also, should devs really be seeing potentially sensitive data? On a side note, "I use SQL so I can change the backend language" is the hottest take I've heard in some time heh.
- dagss 7y ago
  With database I meant an instance of whatever database engine you are using, not the actual data. My current project uses mssql. Each test run probably spins up and destroys 100 databases (inside one mssql docker container that is spun up for each test run). Each test function populates a DB from scratch (using the same sql migration scripts that we have used in prod), runs the test, drops the database. Can do that quite some times in the minute it takes to run the full suite. Point is to actually execute the SQL (or whatever NoSQL you use) as part of the test.
Aperocky 7y ago
And that's a problem - my opinion remain that individual sql queries are better left for analyst and data engineers to craft so they can discover whatever correlation they might be interested in. But for a full scale app it shouldn't be in the forefront, at least not in the same package as other logic. The entire idea of programming is the one where you abstract away things and then abstract away the thing you're currently writing. I agree that it has gone too far in some of the places, but abstracting SQL away is the first and almost one of the best abstraction you can make.