6 ms·
The problem with sql is what happens when you fall off the SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY LIMIT cliff. The simple stuff in sql reads like Engli
by tibiapejagala 5y ago
The problem with sql is what happens when you fall off the SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY LIMIT cliff. The simple stuff in sql reads like English, but for that case ORM would generate a pretty efficient query anyway. The complex stuff in sql looks terrible in my experience and ORM bail out quickly. Once you can’t get the result with a simple SELECT then sql stops being declarative. Instead of writing what you want to get, you write something like a postmodern poem while having a stroke, just to convince postgres benevolent spirits to give you something almost right. Complex UPDATEs and DELETEs with joins are even worse.
Also lack of syntax sugar doesn’t help. SELECT list could support something like “t1.* EXCEPT col1, col2”. Maybe JOIN ON foreign key would be nice. IS DISTINCT FROM for sane null comparisons looks terrible. Aliases for reusing complicated statements are really limited. Upsert syntax is painful. Window functions are so powerful that I can’t really complain about them though.
We use a lot of sql for business logic, but some code I have to reread from zero every time I need it. Maybe we modeled our data wrong or there is some inherent complexity you can’t avoid, but I mostly blame sql the language. Unfortunately I have no idea how it could be improved.
Anyway I think the sql cliff is real. Once you take a step outside the happy path prepare for a headache. For me sql definitely is in some local maxima, after all I use it every day at work.
- hiptobecubic 5y agoI have used those features that you say "Would be nice to have." I didn't realize they weren't ubiquitous. I agree they are excellent.
- oblio 5y agoThe biggest thing is... SQL is not reusable, period. Why don't we have SQL libraries? I know that data models are kind of special snowflakes, but some models pop up over and over and over again and code reuse is always 0 with SQL. To give you an example of a common problem, SLAs or the like for teams with regular business hours. A team has to respond to a request within N hours. To calculate that I need to take into account 8 business hours per day, excluding weekends, excluding holidays (ideally localized holidays), etc. It's a nightmare with SQL. It's precisely the kind of thing you want in a library. Plus, obviously, standard SQL doesn't have a way to share and distribute any libraries, even if they were made. It's pre-C in terms of stuff like that.
- jbverschoor 5y agoPostgis and many other libraries exist. They are widely used.
- oblio 5y agoIs Postgis implemented in SQL?
- jbverschoor 5y agoThat's how I remembered it from back in early days. There was some pl/sql you had to execute, and you'd have some extensions. I don't think it's like that anymore
- mulmen 5y agoThe data model is the code reuse. You can model business hours and SLAs with relationships. Join on time and team. SELECT support_request.request_time + team.response_sla AS respond_by_time FROM support_request JOIN team_sla ON support_request.assigned_team = team_sla.team_id AND DATE_PART('day', support_request.request_time) = team_sla.day_of_week AND DATE_PART('hour', support_request.request_time) BETWEEN team_sla.start_hour AND team_sla.end_hour;
- oblio 5y agoIt's quite impressive how you've missed the core of my message. Now bundle up your proposal, put it up on Github, license it as MIT, and publish it available on sqlpm.org (SQL Package Manager) so that I can re-use it. What's that you say? I can't? There's no sqlpm.org? Not even a postgresqlpm.org? Where's the SQL ecosystem? Oh, wait, there isn't any because SQL is not really reusable. It's <<all>> one-off scripts, like back in the Dark Ages of software development.
- mulmen 5y agoI always install https://github.com/awslabs/amazon-redshift-utils/tree/master/src/AdminViews https://github.com/awslabs/amazon-redshift-utils/tree/master... on my Redshift clusters. What you are describing as code reuse exists for databases, but they are called applications and generally utilize a general purpose programming language. It doesn’t make sense to have a SLA data model library because every use case is different. It’s a database, not procedural code.
- BatteryMountain 5y agoNo man you are right. There is nothing arcane here: sql is just an ugly and unintuitive language. I think sql works the opposite ways normal brains work so unless you are doing a simple query, you have to be able to parse and simulate what sql will do in your head. After doing functions/MV's with CTE's that are 100+ lines per query daily, you kinda get used to but also not. I've concluded that although sql is awesome, it is also full of warts. What helps me is to code all of it in lower case and use something like that Datagrip with a good theme. That way you get something that is readible, colour coded and has autocomplete (very good with joins). It's the only way I've managed to keep my sanity as my experience with it grew. Bad data models doesn't reallllly impact it that much, sql is still sql even with a clean model. I've built mini database engines in the past because of my frustrations with sql but I still use and prefer an actual rdms as opposed to trying to reinvent the wheel. There are so many features we take for granted it's not even funny. Try building your own production-ready storage system and you would quickly appreciate how deep the rabbit hole really goes.
- deleted 5y ago[deleted]