8 ms·
I love databases, but I loathe SQL. And no, I don't mean NoSQL is better - that's throwing out the baby with the bathwater. To me, SQL is the Common Lisp of r
by Pxtl 12y ago
I love databases, but I loathe SQL. And no, I don't mean NoSQL is better - that's throwing out the baby with the bathwater.
To me, SQL is the Common Lisp of relational languages - a brilliant invention of its time that has since long-overstayed its welcome and should be replaced by modern considerations of the problem it solves. The difference is that there are a million rethinks and descendents and redesigns of LISP out there that happily threw out the mistakes and made great strides in the language. You could argue that every modern programming language is a descendant of Lisp thanks to the prevalence of great concepts like lexical closures. SQL, on the other hand, has a teeny tiny few spiritual fringe descendents like the various attempts at Date and Darwin's "Tutorial D".
I love the relational model, but who says the only way to manage the relational model is this hoary old thing? It's immensely frustrating that every implementation of SQL bolts on a tacky and half-assed procedural language, but doesn't solve simple underlying frustrations.
Simply accessing related objects is immensely wordy for a "relational" language. In an algol-derived language, I can say Group.Manager.Person.Address.PostalCode to walk the graph. In SQL, I have to deal with a zillion joins.
Yes, some SQL variants let you join by the foreign key name to make the join a little more terse, but it's still hairy compared to every modern functional or procedural language.
And the APIs - maybe the reason so many sites have SQL injection problems is the hideous APIs. Ever tried to build a WHERE IN (id1, id2, id3... idN) statement with a proper parametrized queries? Holy crap what a icky mass of boiler plate. I mean, it's not a hard problem, but how many times have you solved it, and how many times have you found a tedious bug in your solution? Just give me a proper way to concatenate the parametrization inline with the query FFS.
db.RunQuery("""
SELECT *
FROM MYTABLE
WHERE ID IN + " + db.SomeParameterListFunc(a, b, c) + "
ORDER BY HOLYCRAP_WAS_THAT_SO_HARD"
""");
The above syntax would be trivial in any language with operator overloading on the "+" sign, on the off chance that your SQL dialect is so messy it's impossible to safely build a properly-escaped initializer for the list containing a,b and c in text form.
And that's not even getting into real actual first-class language support like ORMs give you.
And speaking of APIs, the fact that a single "SELECT" is the baseline operation... that you work on one resultset at a time. I don't want a single pile of rows. This is not an excel spreadsheet, it's a relational database, and that means I want a graph of data. I don't want to write three queries to get my Customers, their Personnel, and their Addresses, nor do I want a single row of CustomerPersonnelAdddresses. Once and Only Once is good for the data, why the heck isn't it good for result sets?
Where's the code reuse? Why can't I have a pile of SELECTs and a pile of WHEREs and combine them however I see fit? Oh right, I can use a VIEW... but see the previous point, a VIEW is a single glorified Excel spreadsheet, not a proper graph of data. If I want to bundle a bunch of SELECTs together, I have to just write a stored procedure, but then I can't use the proc with a JOIN statement against my VIEW that provides a custom WHERE clause. You could do something monstrous with table-valued parameters, I guess, but those aren't generally well-supported at the API level. This is not a hard problem in every modern language (except Go, of course - yes, you do freaking need map/reduce/filter).
Namespaces. Real, actual, organizational tools for your giant list of 9000 tables and their related objects. No, schemas don't freaking count - you can't nest them and they're overly tied to the security model - using schemas for organization instead of security leads to madness, besides the fact that you can't nest them.
And of course, so many common problems simply aren't nice to work with using the relational model. How do I make a nice audited row where I have the full history of all the row's changes? Well, I can insert it every time, but that's a lot of wasted space. Yes, again, there are ways to do this, but it's something I'd expect to come out-of-the-box since it's such a common problem. Common problems should be solved by the standard library. But SQL can't solve things like this with the standard library, because a SQL standard libraries are limited to crude things like functions and views and procs and not actual large-scale reusable constructs. It's like a programming language where they gave you a bunch of general tools for manipulating unicode points and dynamically sized arrays but no coherent "string" object.
Or trees. Holy crap, you have a "relational" database where a relationship like a "tree" is a nightmare to actually query out! I know that's not what "relational" means, but still - this ins't exactly a rare edge-case, y'know? But it's not in the standard library because the standard library is limited to crude objects like data-types, functions, procedures, etc. that work below the row-level. Any concept of reusable schema concepts is completely left off the table.
/rant
- BadassFractal 12y agoSeems like there might be an opportunity here. Definitely a lot of hard work, and adoption would be pretty hard, but you have to start somewhere. Is there nothing quite like what you described yet?
- boyter 12y agoThe language issue you raise is in part solved by C# and Entity framework. Because of the lazy way it works you can define a single select over a table, then apply extension methods, joins etc.. over the return of this select and you end up with a highly reusable clean easily tested data layer. Done right you get code like the following, var url = _urlRepo.GetUrl().OrderByCreateDate().ByCreatedUser("boyter"); var url2 = _urlRepo.GetUrl().OrderByCreatedUser().ByDomain("geocities"); Or with joins, var users = _userRepo.GetUser(); var locations = _locationRepo.GetLocation(); var result = from user in users join location in locations on user.locationid equals location.id && location.name = "Parramatta" select user; I wrote about this a bit here http://www.boyter.org/2013/07/clean-repository-data-access-c/ http://www.boyter.org/2013/07/clean-repository-data-access-c... and it is to date the cleanest way I have come across in any language to deal with SQL. Compile time checked, reusable and testable. Its the best ORM I have found so far across any language. I would dearly love to have it ported to other languages and frameworks.
- NDizzle 12y agoIt's funny you mention entity framework. That's what blows up tempdb on one of my many environments with some of the stupidest queries I've ever seen hit the server. Maybe your experience with entity is better than mine, but I personally think it and most other ORMs are complete garbage.
- dasboth 12y agoI agree with this (sort of). I find Entity Framework very usable and it does help you write descriptive and maintainable code, but the SQL queries it ends up writing makes it a performance nightmare. YMMV of course but for applications where performance is important I haven't found EF to perform adequately. As ORMs go all I really want is an abstraction layer and the mapping from query to POCO classes without the performance hit. Currently using Dapper.