11 ms·
Most programmers who are at all familiar with functional programming, DSLs, configuration languages, or optimizing compilers are very well versed with asking th
by nostrademons 8y ago
Most programmers who are at all familiar with functional programming, DSLs, configuration languages, or optimizing compilers are very well versed with asking the computer for what you want rather than telling it what to do. At least when I was there, this was a very large percentage of Googlers.
My issue with SQL is that a programming language should allow you to compose and name building blocks, and then recombine them to build ever more useful software. SQL doesn't have this structure. If you have a query that almost does what you want but you need to add one more filter, you need to reissue the query (modulo views/temporary tables, which is why I said "very clumsily compositional" rather than "not compositional"). If you have an expression that computes some quantity and then you want to use it with slight modifications in a lot of places, you usually end up copy & pasting it (modulo stored procedures). Modern programming languages have made this sort of abstraction really easy, but it's quite clunky in SQL. You can do it (by using stored procedures, views, triggers, subqueries, etc.), but then most of your application ends up written in SQL and it starts to feel like something out of the 1970s.
My preferred interface would be something like the relational algebra where relations are represented as typed values in the host programming language and operators are normal method calls (or binary operators, depending on language flavor) and importantly, intermediate results can be assigned to variables. I don't care what the particular execution strategy is of the query, but I do think it should be possible to refine, join, project, and subquery using values you've already defined.
- zepolen 8y agoTrue, if you modulo all the features like views, stored procedures, functions, foreign keys, triggers there is no reusability in SQL... On the serious side: It's not a bad idea to contain critical business logic in the database. It's shared by any app using the database. Foreign keys esp link and let you cascade changes with no extra code. Less overall code. Higher guarantees. Everyone agrees it's a good idea to use a `datetime` field for a `created` field instead of `varchar` and letting the application parse/extract it's own format in there. It's also a good idea to add triggers that do specific things to keep your db in a consistent state upon actions. Maybe adding/deleting a row needs to update a counter in a statistics table for quick access because count(*) on that table takes too long. You could do that it in your app - but what happens if someone changes the data without going through your app. Instead you add it as a trigger. Now you can be sure that the number in the stats table is always the same as the actual number.
- sly010 8y agoNot sure why are you being downvoted, I would like to at least hear the counter argument.
- da_chicken 8y agoI don't disagree with him (though I don't necessarily agree as strongly with respect to triggers), but the counter argument is that it's extremely easy to put up a web service that offers an API and interacts with JSON. Now all the complicated bits are in the API service, and the multiple apps don't need to know anything at all about the underlying data store. The counter to that is that it can be very difficult to get an API that is as comprehensive and flexible as SQL is, and eventually you'd find yourself re-implementing SQL if you ever tried. Certain reporting scenarios or mass data manipulation scenarios really require actual data store access in order to perform remotely well.
- zepolen 8y agoYour counter counter argument on not being as flexible as SQL is spot on. A DAL that sits in front of storage mechanisms is not a bad idea though. I've found moderate success in doing the reverse and using communication channels from SQL => services either via NOTIFY or queues. ie. Dispatching an event that signals a non SQL service to do an action. eg saving a file to S3, then updating the database when the action is complete. This doesn't really work when retrieving data synchronously but in most cases if the data is stored elsewhere - then you probably want to access it via non db methods anyway (ie. for an S3 file, you'd use the url) WRT JSON, please for the love of god no. Someone made the same argument a month ago, my reply here: https://news.ycombinator.com/item?id=18870838 https://news.ycombinator.com/item?id=18870838
- da_chicken 8y ago> WRT JSON, please for the love of god no. I don't like it either, but for most scenarios that this sort of thing works for a RESTful API or microservice or equivalent (or whatever you want to call it these days since I think the expiration date on those terms has elapsed) and not all data can be meaningfully coded without a structure like JSON or XML. Or, rather, you can, but, you're reinventing the wheel just like you would with an API trying to be as flexible as SQL. You may run into various system limits on URL length, too. Events and queues are great, but not all requests work that way. If you're USPS and you're providing an address and ZIP code resolver, you've got different requirements than getting data from one system to cascade across a series of systems with a dozen different asynchronous widgets. You'd have a burden of using a format that your customers would prefer, too.
- ako 8y agoI don't see views and subqueries as clumsy. Especially with sets, you think in creating new sets, and combining those into other sets. In postgres i've created financial year reports, with monthly summaries per category just by having a few layers of views. I think it's really valuable i can think in logical sets, and the database will takes all those layers of views and combine those into one optimized query plan.
- tremon 8y agoBut views and functions still require you to persist those objects in the database first. There's no such thing as a query "variable" that you can then re-use in multiple subsequent statements. Of course, you can use table variables or temporary tables to hold intermediate data, but those are eagerly evaluated, whereas functions, views and CTE's are lazily evaluated, and that allows for a massive performance boost (due to optimization). I can see the appeal of such a construct in SQL. The requirement that reusable objects are persisted in the database requires a top-down design approach, and that doesn't really blend well with modern coding practices.
- garyclarke27 8y agoThere is no limit to reuse of views?? You can create as many intermediate or base views as you like, within a view definition, CTE’s have local scope and are similar to variables, if you need schema scope, just define the query block as a view instead. UDFs are simple to create are lazy and can be easily reused and composed, in Postgres they are first class objects, scalar UDFs can be called anywhere in SELECT and Set Returning UDFs in the WHERE clause, use more than 1 and they also automatically Lateral Joined - which is incredibly powerful and composable - when you get your head around them
- tremon 8y agoNo, CTEs do not have local scope, at least not like variables. CTEs are statement-scoped; no matter how many SQL statements you have in your current scope, your CTE vanishes after the first statement terminates.
- garyclarke27 8y agoViews - Create a general top level view, then build more specific views on views using more filters, to go down, ie more granular - then just join these with yet more views to combine, or aggregate to go back up. How is that not composable? If you hit performance issues, they are easily solved by using a few materialized views. Also CTEs and User defined Functions (I use pure SQL functions but in Postgres you can easily use Python or Javascript instead)
- magicalhippo 8y agoAt least in the DB we're using, Sybase SQLAnywhere, materialized views comes with a hefty price tag. They must be dropped and recreated every time you touch any of the base tables, like adding a column, which in turn requires any indexes on the materialized views to be recreated. For a few of our customers, that meant that a 15 minute DB change (adding a column) turned into a several hour DB change (rebuilding materialized view). If they're refreshed immediately, they also incur a penalty on the underlying tables, since any data changes essentially runs a trigger to see if the materialized view needs changes as well. In our case we had one such immediate refresh materialized view which caused such a performance degradation of an underlying table that we had to find an alternate solution.
- ryanbrunner 8y agoMaterialized views for sure add some complexity and overhead, but I think that's true of any caching mechanism. They should probably be one of the last tools you reach for when trying to optimize queries, but in certain critical situations they can be super effective.
- jbverschoor 8y agoWhat's the difference between that and a non sql approach? Writing a program that makes a temporary table somewhere, and if there's a mistake you have to start all over again?
- magicalhippo 8y ago
- haskman 8y agoPreach! I just started building something like this in Purescript which might interest you - https://github.com/ajnsit/purescript-consequence https://github.com/ajnsit/purescript-consequence. It allows writing things like - type PersonRel = Relation (name :: String, age :: Int) type EmployeeRel = Relation (employeeName :: String, managerName :: String) -- Get all managers older than 60 oldManagers :: PersonRel oldManagers = employeeTable # renameField (SProxy :: SProxy "managerName") (SProxy :: SProxy "name") # join (RProxy :: RProxy (name :: String)) personTable # filter (\r -> r.age > 60) # project (RProxy :: RProxy (name :: String, age :: Int))
- ako 8y agoResembles R dplyr pipelines using magrittr, although i find the dplyr version easier to read.
- haskman 8y agoI am not familiar with R dplyr. The code snippet I posted doesn't adequately demonstrate it but the DSL is strongly typed with full type inference at every step. So for example, it knows that the type of the table after the "join" is has a field "age" of type "int", and that filtering on it is okay. Trying to filter on a non-existent field will cause a compile (not runtime) error. Second, the "steps" are actually plain functions. `#` is simply the reverse function application operator defined as `a # f = f a`. Which means you can abstract operations. For example, you can define a generic function that can take any relation with an age field and filters for age > 60. Thanks to strong types, using it incorrectly (i.e. on a relation without an age field of type integer) would cause an compile error. Also, the language itself is a deeply embedded DSL. So "ordinary" code also compiles down to SQL. You can do things like loops etc. (for example, create a function that filters over a list of age limits rather than just one) and it will try to generate the best SQL possible. of course the SQL generation is currently not implemented, so all this is theoretical, but I'm getting there!
- JohnBooty 8y ago> My preferred interface would be something like the > relational algebra where relations are represented > as typed values in the host programming language and > operators are normal method calls (or binary operators, > depending on language flavor) and importantly, > intermediate results can be assigned to variables. Much if not all of what you're asking for can be done today at the database level with modern SQL affordances (CTEs, etc) and vendor-specific stuff like stored procedures. You can write functions that return tables/resultsets, assign those results to variables, etc. There's not a host programming language database library that exposes those things in quite the manner you seem to be asking for, but that's not a limitation of these SQL-based RDBMSs themselves. In the realm of what's possible right now, you could of course write your stored procedures directly in .NET languages (MSSQL) Python (Postgres) etc.
- leethargo 8y agoThere is also the approach of https://querycombinators.org https://querycombinators.org which is inspired by functional programming but results in a declarative language. Queries are composed of functions that can be named and tested separately.
- goto11 8y ago'With'-clauses (aka Common Table Expressions) allows you to name and compose subqueries much more cleanly. Your preferred interface sounds like Linq. An IQueryable<T> interface represent a query and can be composed and assigned to variables, and the type T represent the type of the relation or projection. Linq composes a lot more elegantly than SQL itself. The instances does not represent intermediate results though, they represent composed queries, which are executed in one go on demand. But I think that is what you want anyway.
- jerf 8y agoThere are indeed a number of decent swipes at the idea, like Linq. But they all suffer from some impedance mismatch. It would be better if the native language underneath was written to support it more natively. As you and others cite, there's a variety of features that have crept up on the idea over the years, but they're all bodging things on to the side of something they can't really change. I'd love to see the Rust of database querying to SQL's C++. I draw that comparison because I think a lot of the same structural problems are in play preventing it; SQL is just so good that it is actively prevented from being fundamentally improved. It can be incrementally improved, but not fundamentally.
- da_chicken 8y ago> 'With'-clauses (aka Common Table Expressions) allows you to name and compose subqueries much more cleanly. These are great, but their implementation varies pretty significantly. Notably, MySQL didn't have them at all until v8.0, and PostgreSQL's CTEs are... wonky, for lack of a better term (I believe they use an "evaluate and store" method that's closer to a temp table, and as a result they don't optimize well and have been known to have strange side effects). Not all RDBMSs support recursive CTEs, either.
- dustingetz 8y agoDatomic has host-process value compatibility see #2 http://www.dustingetz.com/:datomic-in-four-snippets/ http://www.dustingetz.com/:datomic-in-four-snippets/ and composes #4
- collyw 8y ago> My issue with SQL is that a programming language should allow you to compose and name building blocks, and then recombine them to build ever more useful software. When I write a complex query it's kind of like this. I start with one table (viewed in my head as an excel style grid of results). I join another table then filter on the join / where clause. Again the output is effectively another table. Rinse repeat. The difference is that SQL is very powerful at what it does, so you don't need to compose blocks in the way that you would in other languages. Just think of everything as a table. The result of every join or filtering clause is another table. That's your building block. You are going to need significantly less SQL than Java / Python to get the same results from your data.
- kragen 8y agoYou may be interested in my project "Binate"; I have some incomplete notes at http://canonical.org/~kragen/binary-relations http://canonical.org/~kragen/binary-relations about it, exploring a possible design space in which we might find a more concise and compositional query language. Of course that's not in a host programming language; for that there do exist some things like SQLAlchemy and SQLObject which, as a bonus, can compile the resulting query to SQL. The most popular of these is Django's "ORM", which unfortunately is very limited.
- silasdavis 8y agoI think you make a valid point about compositionality. SQL lacks elegant composition at the source/module/syntax level. Views and stored procedures are all stateful ways of achieving something similar by using them also involves migrating that state. I don't think you're saying SQL is not expensive which seems to be the rebuttal offered by siblings.
- thom 8y agoThis is a very real problem I bang my head against regularly. There just seems no way to achieve all three of readability, maintainability and performance in a large enough SQL codebase. You can piece together views right up until the moment the query planner forgets to push where clauses down. You can wrap a query in a function to guarantee the where clause is evaluated then and there, but now you have to maintain that _and_ your view/table. You can slowly rewrite your code bit by bit, adding complexity to force the query planner (in this supposedly declarative language) to behave the way you want it to, for identical results. You can wrap all of it in a materialized view so you don't have to care about performance anymore, right up until the point it takes 12 hours to refresh. I've be very much in the market for something more modular than SQL, which had a much more customisable query planner so you could say "I don't care if you go away and compile this for an hour as long as you make it as quick as physically possible and then save the query plan forever".
- dorgo 8y ago>save the query plan forever is this even possible? Afaik, the query plan can depend on the data distribution and needs to be re-optimized when the data distribution changes.
- thom 8y agoYeah, some of it is based on statistics, although in my experience, the relative distributions of rows don't massively change in my databases, so an ostensibly optimal query plan on day one isn't likely to be rubbish later. You can obviously imagine other people's mileage varying.
- rspeele 8y agoI am the author of a small parser/typechecker for a subset[0] of SQL, roughly matching that supported by SQLite, and I agree with you COMPLETELY. What follows is my rant directed at those who don't. It is an ugly, verbose language. You can be very familiar with thinking in sets, and still not like SQL. It's what we've got, and the SQL databases available are very, very good products. But I do wish a cleaner language could have won. As you know, anyone who's used LINQ in C#, particularly by directly calling the extension methods .Select(...).Where(...).OrderBy(...), sees how much better it is from a composability standpoint. SQL is the anti-Lisp. Lisp's design is about 4 pieces of syntax and a few fundamental operations from which all else is built. Conversely, nearly every operation in SQL is a tacked-on special case to the ridiculously complex SELECT syntax. Filtering results? That's a clause of SELECT. Ordering? Clause of SELECT. Filtering after aggregating? Oh, that's a different clause of SELECT. Once this philosophy has infected the brain of a SQL implementer, it spreads like wildfire. That's why you even see custom syntax pop up even in good'ol function calls sometimes, like in Postgres: overlay('abcdef' placing 'wt' from 3 for 2). SQL fans often talk about the beauty of relational algebra. Once you achieve relational enlightenment, SQL is supposed to be beautiful. But if we wrote math like SQL, you wouldn't say 2 * 3 + 4. There would be a grand COMPUTE statement with clauses for each operation you could wish to perform. So you'd write COMPUTE MULTIPLY 2 ADD 4 FROM 3. Of course, the COMPUTE statement is a pipeline, and multiplication comes after addition in the pipeline, so if you wanted to represent 2 * (3 + 4) you'll push that into a sub-compute, like COMPUTE MULTIPLY 2 FROM (COMPUTE MULTIPLY 1 ADD 4 FROM 3). SQL clauses could have been "functions" with well-defined input and output types, if the language designers had come up with a type system to match the relational algebra. WHERE - table<row type> -> predicate<row type> -> table<row type> JOIN - table<row x> -> table<row y> -> predicate<row x, row y> -> table<row x, row y> ORDER - table<row type> -> list<expression<row type>> -> table<row type> These could be pipelined, rather than nested, either with an OO-style method call syntax or a functional style pipe operator. You understand the idea. Again, as you've mentioned, the LINQ methods[1] are a great resource for people not familiar with this style. But, counterargument. Languages with minimal syntax and great power are often claimed to be unreadable. Sometimes it's nice to have special syntax, to help give a recognizable shape to what you're reading, instead of it being operator / function call soup. So what did SQL accomplish by making everything a special case of SELECT? Well, it's got this sensible flow to every statement. You see, the execution of SELECT logically flows as I've numbered the lines below. SELECT 8 DISTINCT 7 TOP n 5 column expressions 1 FROM tables 2 WHERE predicate 3 GROUP BY expressions 4 HAVING expressions 6 ORDER BY ... I sure am glad they cleared that up. If it had been a chain of individual operations I'd have been utterly baffled. Ignoring completely the syntactical design, the lack of basic operations is a pain too. Why can't I declare variables within queries? I often would like to do something like: select let x = compute_something(...) in x + q as column1 x + r as column2 from ... Instead, when I really need that, I end up wrapping the whole thing in an outer query and computing X in the inner query. Hooray for SELECT, the answer to all problems! Oh and yes, views and stored procedures and UDFs are no answer to the need for one-off local composability within queries. Then you have the sloppy design of the type system in virtually all SQL dialects. SQL Server doesn't even have a boolean type. There is no type you can declare for `@x` that will let you `set @x = (1 <> 2)`. And don't even get me started on the GROUP BY clause, the design of which contorts the whole rest of the language. If you GROUP BY some columns, you must not refer to any non-grouped columns in the later parts of your query (refer to my table above for which parts are "later"). Unless, that is, you are referring to them in aggregates. Then the HAVING clause was tacked on so that you'd have a way to do a filter -- the same thing as WHERE -- after the GROUP BY. Does it all make sense once you understand it? Yes, in that you can see how you'd end up with this system if you were adding things piece by piece and never went back to redesign from square one. Wow, I have a lot of ranting to do about SQL. I feel like I haven't even scratched the surface. And hell, I still pick SQL databases every time I start a project! The damn language is useful enough and the products work great. But it has all the design elegance of the US tax code. [0] https://github.com/rspeele/Rezoom.SQL https://github.com/rspeele/Rezoom.SQL [1] https://docs.microsoft.com/en-us/dotnet/api/system.linq.enumerable?view=netframework-4.7.2 https://docs.microsoft.com/en-us/dotnet/api/system.linq.enum...
- svsucculents 8y agoThis is what glue languages do with data. Not what SQL does with data. Why ever would you want it to?