[HN Gopher] I don't need your query language
___________________________________________________________________
I don't need your query language
Author : polyrand
Score : 298 points
Date : 2023-06-17 10:52 UTC (12 hours ago)
(HTM) web link (antonz.org)
(TXT) w3m dump (antonz.org)
| alecco wrote:
| Datomic's Datalog is much better but it's not easy to unlearn
| SQL.
| OliverJones wrote:
| As an experienced (===old) developer, I have learned that data
| long outlasts the programs that access it. The lifetime of data
| is measured in decades, but programs last for years. Most SQL-
| based RDBMS teams have figured out workable version migration
| paths allowing old data to run on newer servers. Because this
| kind of migration is a very common and economically valuable
| operation, the vendors make sure it works correctly.
|
| Sometimes a project, especially a greenfield project, looks like
| it will benefit from more recently invented data storage and
| query tech than your grandmother's SQL. That's always possible.
| And as developers we hope for, and work for, continued progress.
| But consider what may happen when the project succeeds.
|
| If you're still on the project, you'll wake up one day and
| realize your oldest data is 20 years old. What happens if your
| storage and query engines are also 20 years old, because they
| didn't succeed to the extent needed to pay for maintenance and
| upgrades? You'll be in the software equivalent of the century-old
| subway system where you have to make all your replacement parts
| yourself, or get gouged by vendors that can't spread their costs
| among many customers.
|
| Build for the ages, not for the moment!
| rwiggins wrote:
| I agree with the premise of the article, I think, but I find the
| "good SQL" versions... uncompelling.
|
| (1) Switching `left join` to the default inner `join` changes
| query behavior. I'm guessing it's intentional on the author's
| part? But it feels like the wrong change to make when trying to
| compare syntax like-for-like.
|
| (2) I am also in camp "SQL keywords really don't need to be
| uppercase", so keep fighting the good fight, brother. That said:
| it's an uphill battle and far from universal. Most SQL
| "formatters" I've used automatically uppercase everything.
|
| (3) Dropping the alias in `Actors.name AS actor_name` is another
| case where you're not doing like-for-like. Just using
| `Actors.name` means, for example, the first example's output
| table will have two columns: title and name. I'd argue for most
| uses title and actor_name are better output column names.
|
| Those points aside, the primary simplification seems to be
| switching `join ... on` to `join ... using`. Big +1 from me on
| that.
| bazoom42 wrote:
| If this is about NoSQL databases, I dont think SQL is useful for
| databases which does not follow first normal form. But any
| alternative to SQL for relational databases will fight an uphill
| battle. While SQL is somewhat clunky, it is also deeply
| entrenched.
| Olreich wrote:
| SQL selection works perfectly on tables in poor normal forms.
| If you have the columns you need to query pre-joined into the
| table you're querying, you just skip the joins. Updates are
| what gets fun if you don't have normal form.
| bazoom42 wrote:
| First normal form disallows nested tables and SQL does not
| support querying nested tables.
| vunoo wrote:
| I wonder if the author has tried CodeQL? One of the best query
| languages I've ever used, even if it is very domain-specific.
| jrumbut wrote:
| I think of SQL as one of the few good things we have in software
| development, so like the author I consider it best to try to do
| as much in SQL as possible.
|
| It's not too uncommon I run into code in other languages where I
| just don't understand what it does, or to write code myself that
| behaves in ways that surprise me. That almost never happens in
| SQL.
|
| Even a big hairball of a query just takes time to figure out
| (unless a database-specific function with odd behavior is used).
| nickpeterson wrote:
| I tell new developers that SQL is one of those few things in
| our field you get to keep forever.
|
| That JavaScript framework that takes a year to understand will
| no longer be used in 7 years. SQL is going to be here forever
| and learning it is useful your whole career.
|
| Other common entries on this list of forever tools: regular
| expressions, emacs, bash/shell scripting, excel, probably more
| I'm forgetting.
|
| Devs always push back on the suggestion to learn something like
| excel (actually learning it, not just clicking around), but
| when they first start they really underestimate how often it's
| the tool the business speaks and feels comfortable giving
| feedback on technical questions in.
| YeGoblynQueenne wrote:
| >> Other common entries on this list of forever tools:
| regular expressions, emacs, bash/shell scripting, excel,
| probably more I'm forgetting.
|
| Oh yes, many more: Java, JCL & COBOL, Windows Forms, PhP,
| Wordpress, Joomla, Drupal, Perl, Visual Basic, IIS, SSIS,
| Ruby on Rails, and so on and so forth.
|
| Maintaining enterprise software is a special circle of hell
| reserved for developers. Just sayin'.
| waboremo wrote:
| The problem is that a lot of developers consider things
| forever tools when they are not forever tools and not even
| close. SQL is one of the forever tools, but even in your
| short list I would absolutely strike down two of them as
| "forever tools". They're more like "my favorites that I've
| invested decades into".
| alexchamberlain wrote:
| Which 2 would you strike down out of interest?
|
| I wholeheartedly agree that engineers massively
| underestimate the power of Excel.
| cratermoon wrote:
| > how often it's the tool the business speaks and feels
| comfortable giving feedback on technical questions in.
|
| Yes, but as often as not business people end up using Excel
| as a glorified text editor with built-in tabular formatting.
| Some of the spreadsheets I've been handed by project
| stakeholders would make 90s-era pre-CSS HTML using tables for
| formatting look clean and simple by comparison.
|
| After writing applications in "4GL" sql tools early in my
| career, I've spent the entirety of the rest of my career
| trying to avoid writing SQL or having anything to do with the
| inner guts of those gigantic global variables known as
| relational databases.
| sp33der89 wrote:
| > that JavaScript framework that takes a year to understand
| will no longer be used in 7 years.
|
| Surely there is a lot more nuance for having a nicer database
| query language than comparing it to JS frontend practices.
|
| SQL is something that'll stick for a long time, but I support
| attempts from people that don't want to let SQL be the
| endgame.
|
| Of course don't go around deploying highly experimental shiny
| things on production! :P
| forgetfreeman wrote:
| Nuance? Not really. Bullshit flash in the pan tech stacks
| are what they are regardless of how much marketing drapery
| one adorns them with.
| thr0waway001 wrote:
| > That JavaScript framework that takes a year to understand
| will no longer be used in 7 years. SQL is going to be here
| forever and learning it is useful your whole career.
|
| Nothing seems more ephemeral than JavaScript framework du
| jour.
| hosteur wrote:
| I would love if sql would support a slight syntax change of
| accepting
|
| From table select col;
|
| as an optional alternative to
|
| select col from table;
|
| This would allow autocompleting col names in editors.
|
| Other than that I quite like sql being the standard db query
| language.
| amai wrote:
| Have a look at https://prql-lang.org/. They got this right.
| hosteur wrote:
| > Have a look at https://prql-lang.org/. They got this right.
|
| Yes, they absolutely got this right. But I agree with the
| author of TFA in that I don't want another query language. I
| just want this specific change to SQL. Maybe others as well.
| But I do not want another query language. PRQL is yet another
| query language.
| masklinn wrote:
| That only fixes trivial selects. But you still have issues with
| e.g. GROUP BY, especially since the dependency is circular:
|
| - barring extensions you can only select grouping expressions
| or aggregates
|
| - but instead of repeating grouping expressions you can refer
| to a select expression (by index, some databases also allow the
| alias)
|
| The "spec" order of evaluation for queries is WITH, FROM,
| WHERE, GROUP BY, HAVING, SELECT, DISTINCT, merge, ORDER BY,
| LIMIT. Although databases might decide to move SELECT after
| ORDER BY and LIMIT if they can, in order to avoid unnecessary
| evaluations.
| hosteur wrote:
| > That only fixes trivial selects
|
| Absolutely true. But a huge amount of queries are in fact
| trivial selects. And this change alone would make
| autocompleting them easy for various editors/IDEs.
|
| Perfect is the enemy of the good, etc.
| marcosdumay wrote:
| You can easily change it to make `group by` behave much
| better, and `having` redundant with `where`, like it should
| always have been if you just evaluate from start to end,
| without any hidden reordering.
| masklinn wrote:
| > You can easily change it to make `group by` behave much
| better
|
| Change what? Make "group by" behave better how?
|
| > `having` redundant with `where`
|
| They filter different things, how do you make `where`
| perform both jobs?
|
| > like it should always have been if you just evaluate from
| start to end, without any hidden reordering.
|
| The only "hidden reordering" is an optimisation.
| rubyfan wrote:
| I used to bash SQL. Then I learned how to use it.
| kagevf wrote:
| I was exposed to Kusto Query Language this week. I used it to
| query logs in Azure. At first, I thought "what? Another query
| language to learn? " But I find myself liking it. It reminds me
| of ML style piping expressions, and it's very explicitly lays out
| how each operator works on top of previous clauses, which makes
| it very clear how and _when_ each clause works. I 'm assuming /
| hoping the queries that are actually executed are deferred - I
| would think they would have to be!
| DaiPlusPlus wrote:
| SQL does have a significant drawback w.r.t. how databases are
| used today (imo): a SELECT query can only return a single
| resultset of uniform tuples: if you want to query a database for
| hetereogenous types with differing multiplicity (i.e. an object-
| graph) then you either have to use multiple SELECT queries for
| each object-class - or use JOINs which will result in the
| Cartesian Explosion problem[1] which also results in redundant
| output data due to the multiplicity mismatch - SQL JOINs also
| lack the ability to error-out early if the JOIN matches an
| unexpected number of rows.
|
| And there are often problems when using multiple SELECT queries
| in a batched statement: you can't re-use existing CTE queries.
| Not all client libraries support multiple result-sets. It's
| essentially impossible to return metadata associated with a
| resultset (T-SQL and TDS doesn't even support named result
| sets...), which means you can't opportunistically skip or omit a
| SELECT query in a batch because your client reader won't know how
| to parse/interpret an out-of-order resultset, and most
| importantly: you need to be careful w.r.t. transactions otherwise
| you'll run into concurrency issues if data changes between SELECT
| queries in the same batch ()
|
| [1] https://learn.microsoft.com/en-
| us/ef/core/performance/effici... and
| https://learn.microsoft.com/en-us/ef/core/querying/single-sp...
| nurettin wrote:
| You don't really need multiple result sets, you can do lateral
| joins (cross apply in microsoftish) to bring in any unrelated
| data and distinct the result. And fancyql doesn't provide any
| alternatives, so it makes no sense to even bring this up.
| tlarkworthy wrote:
| I keep staring at the output of sqlalchemy's "select in" eager
| loader trying to figure out if has managed to pipline the
| follow up queries into a single DB round trip for the problem
| of tree shaped data. Still don't know, maybe someone does.
|
| https://docs.sqlalchemy.org/en/14/orm/loading_relationships....
| gozzoo wrote:
| Isn't your comment judging a fish by its ability to climb a
| tree?
| Psyladine wrote:
| You're asking those whose object problem model is a FOR loop
| not finding set based operations intuitive, efficient or ever
| necessary?
| post-it wrote:
| If the task is to climb a tree, it's reasonable to not hire a
| fish.
| eimrine wrote:
| https://www.google.com/search?q=can+fish+climb+tree
| post-it wrote:
| Thank you eimrine, very cool!
| zapov wrote:
| Those are problems in MS Sql, certainly not in object
| relational DBs such as Postgres or Oracle. And its rather sad
| that instead of embracing that we ended up with Json as poor
| man replacement for such advanced usages. I guess non
| portability across DBs certainly doesnt help. I tried showing
| that 10 years ago: https://github.com/ngs-doo/revenj/ but it
| just resulted in confusion.
| nicoburns wrote:
| This is no longer true in database that have JSON support
| (which is most of them these days). You can aggregate the
| result of a subselect into a single column (the underling data
| doesn't have to be stored as JSON, you can convert as part of
| the query)
| masklinn wrote:
| That further increase the complexity of the queries, and then
| you start hitting weird corner-cases like postgres's
| difficulty (inability?) to convert a JSON array of JSON text
| elements to an array of text.
| n_e wrote:
| select array_agg(e) from
| jsonb_array_elements_text('["a","b"]'::jsonb) e; ?
| throwaway2990 wrote:
| I donno about other databases but it doesn't add complexity
| to PostgreSQL. And you can convert json array to text
| array.
| tomnipotent wrote:
| > further increase the complexity of the queries
|
| Small price to pay for improving RDBMS throughput and eking
| out more from limited hardware. There is no shortage of use
| cases where this just makes sense. Doing all of that work
| in a single SQL query also makes sure there's less buffer
| cache thrashing.
| mattmanser wrote:
| I think you're wrong, happy to be corrected though. As
| far as I can tell, if you change a subselect column into
| JSON, it's much more expensive in CPU and marginally more
| expensive in network bandwidth.
|
| 1. The data has to be serialised into JSON on the DB
| server, which costs CPU.
|
| 2. It then has to be deserialised on the application
| server (unless your backend is written in javascript and
| then your throughput problem is that you're using
| javascript instead of a better, compiled language)
|
| 3. The network bandwidth is actually larger as you've got
| all those extra {} in your result set, compared to the
| raw data in column format
|
| It might "look" bigger to a human as there's more
| columns, but the data is exactly the same. So by
| definition, youre doing extra CPU work of
| serialising/deserliazing JSON and adding all the object
| markers of extra characters like {} and "" and : means
| the payload is bigger too.
| tomnipotent wrote:
| > which costs CPU
|
| Which costs very little CPU in 2023.
|
| > deserialised on the application server
|
| This is true regardless. The low-level libraries are
| still parsing the stream into meaningful in-memory
| structures. With JSON, the low-level library only has to
| parse a variable length string, then JSON decode. I'm
| unfamiliar with any language in 2023 that doesn't have
| incredibly fast and efficient JSON parsers.
|
| > bandwidth is actually larger as you've got all those
| extra
|
| This is true, but generally negligible. I run into very
| few scenarios where network saturation is more of a
| problem than CPU or memory issues. If you have network-
| constrained problems, obviously optimize accordingly.
| littlestymaar wrote:
| > Which costs very little CPU in 2023.
|
| That's a very bad argument for an RDBMS, since you want
| to make it scale vertically as much as you can (unlike
| your application server, horizontally scaling your
| database is a completely different matter, and isn't
| straightforward at all).
| tomnipotent wrote:
| The added cost of JSON serialization is easily offset by
| reducing the total number of overall queries and implicit
| (or explicit) transactions. The additional
| parallelization the RDBMS can achieve is generally
| greater than the added JSON serialization and extra
| network bandwidth.
| littlestymaar wrote:
| Maybe, but this isn't the same as saying CPU cost doesn't
| matter because it's cheap.
| tomnipotent wrote:
| > same as saying CPU cost doesn't matter because it's
| cheap
|
| A straw man argument you've pulled out of thin air.
| mattmanser wrote:
| No, you can't use 'negigibly' worse as a defence.
|
| It's either better, or not. Your comment does not make it
| better, it's still worse. So it won't improve
| performance, but degrade it, even if it's negilible.
|
| So there's no reason to do it.
|
| Plus you've made a crazy SQL select instead of a normal
| one, which is harder to maintain.
|
| So it's worse performance and worse maintenance.
|
| i.e. don't do this, it's dumb, especially for the reasons
| claimed which are factually incorrect as it will not
| improve throughout but make it worse
| tomnipotent wrote:
| > you can't use 'negigibly' worse as a defence
|
| Absolutely you can when the increase in something
| (bandwidth) in a system with surplus supply with the
| trade-off of optimizing a more constrained supply (CPU or
| memory).
|
| > made a crazy SQL select instead of a normal one, which
| is harder to maintain.
|
| Purely subjective. Myself nor the people I've hired would
| have a problem maintaining a more complex SQL query using
| CTE's and JSON serialization than not.
|
| > it's worse performance
|
| I cannot imagine that's the case in the context we've
| been discussing. An RDBMS duplicating JSON output of
| tuples multiple times in a single transaction is not
| particularly expensive compared to the alternative.
| 411111111111111 wrote:
| The funnest part of outputting JSON from the query is
| that your API now basically becomes a RPC, as you don't
| have to do any marshalling/deserialization before
| returning the response with Content-Type application/json
|
| Response times are really fast like that, as you probably
| already know. Always fun to see <10ms round trips in the
| browser network tab on some requests
|
| Also no worry about n+1 queries, as they're fundamentally
| impossible to do like that
| password4321 wrote:
| I've long wondered if there would be any performance
| advantage for an API server to zero-copy the DB response
| to the browser, deserializing from the DB wire protocol
| on the front end.
|
| Or perhaps sending DB query results directly from DB
| server to browser, with the API server just initializing
| and securing.
|
| Thanks for pointing out how JSON from the DB is another
| option for moving a bit of processing elsewhere in the
| stack.
| vbezhenar wrote:
| TLS everywhere makes the whole point of zero copying
| obsolete. Encryption eats so much CPU that copying data
| around does not change anything.
| 411111111111111 wrote:
| It does add a few ms, but you're overstating it. For LAN
| traffic, it's usually 5-8ms last time I checked on my
| servers.
| tomnipotent wrote:
| You've basically described Firebase and its archetype of
| database.
| mattmanser wrote:
| That not what we're talking about though, we're talking
| about aggregating the the result of a subselect to reduce
| the throughput of a SQL query.
|
| I'm just pointing out it doesn't achieve that.
| mattmanser wrote:
| It's not subjective, it's.objective.
|
| This solution for tuples is objectively worse in every
| way, throughout, network bandwidth, code complexity,
| maintainability, error likeliness.
|
| You're just clearly someone who can't admit when they're
| wrong.
| tomnipotent wrote:
| > it's.objective.
|
| Prove it.
|
| > You're just clearly someone who can't admit when
| they're wrong
|
| You don't have a good technical argument so you jump to
| ad-hominem's?
| remram wrote:
| They've presented plenty of drawbacks, which you've
| dismissed as "it's not that bad", while not presenting
| any benefit.
| tomnipotent wrote:
| I've presented the bandwidth vs. memory and CPU trade-
| off, was that not obvious? Not to mention reduced network
| round trips, and less overhead on transaction management.
|
| If the biggest issue in my product is JSON deser, I'd be
| a happy camper.
| remram wrote:
| Bandwidth and memory are probably both worse, JSON adds
| overhead. Round trip latency only happens if you wait for
| the results of one query to send the other.
| xupybd wrote:
| >You're just clearly someone who can't admit when they're
| wrong.
|
| I suggest avoiding statements like this. Yes they help
| vent your frustrations but they destroy the otherwise
| constructive and interesting debate the two of you were
| having.
| [deleted]
| dbanon9 wrote:
| 1. Read the source. It's very efficient: https://github.c
| om/postgres/postgres/blob/a14e75eb0b6a73821e...
|
| 2. You don't have to do deserialization in the
| application layer. If all you're using JSON for is to
| convert to OOP objects, just deserialize in the db --
| which again, trivial.
|
| 3a. This is wrong on many counts. If you want efficient
| passing of JSON, use JSONB which is the binary encode of
| the JSON, as a tree structure. It will not include the
| structural characters.
|
| 3b. Bandwidth is also cheap.
|
| 4. Don't optimize without profiling. A few extra CPU
| cycles is not going to make-or-break your scaling
| journey, you'll most likely run into larger problems
| before that happens.
|
| 5. You can get "non-uniform" tuples by using UNIONs and a
| smart flagging system that points to tuple schemas --
| rather than using JSON; the difference is entirely
| ergonomic.
|
| 6. If you're in a low-latency environment and the CPU
| cycles are absolutely critical, write your own extensions
| to handle what you're trying to do, instead of twisting
| Postgres into doing your bidding.
| nicoburns wrote:
| It's still a big saving overall if the alternative is a
| JOIN causing a cartesian explosion in the number of rows
| [deleted]
| erikpukinskis wrote:
| > _You can aggregate the result of a subselect into a single
| column (the underling data doesn't have to be stored as JSON,
| you can convert as part of the query)_
|
| For anyone (like me) who is not quite able to visualize this,
| here is an example: SELECT json_agg(trips)
| FROM ( SELECT json_agg(
| json_build_object( 'recorded_at',
| created_at, 'latitude', latitude,
| 'longitude', longitude )
| ) as trips FROM data_tracks GROUP by
| trip_log_id )s
|
| From StackOverflow user S-man
| https://stackoverflow.com/a/53087015
| rbalicki wrote:
| As someone who would like to use SQL to return a tree-like
| structure, how well does this scale (in terms of
| readability, performance, etc.) when the query is extremely
| large and nested? For example, if we are attempting to
| replace GraphQL with some sort of SQL.
|
| I'm super unfamiliar with this space, and would love to
| know whether it is a feasible/worthy goal to replace
| GraphQL queries with generated SQL.
| summarity wrote:
| Very well, I run huge nested CTEs with json aggregation
| in SQLite for findsight.ai and there's really not any
| noticeable performance overhead.
|
| A good editor (DataGrip) helps.
| paulddraper wrote:
| You might be interested in PostGraphile. [1]
|
| [1] https://www.graphile.org/postgraphile/
| paulddraper wrote:
| But now you have to work in...shudder...JSON.
|
| It's nice being able to use datetimes, 64 bit ints, binary,
| etc
| tomnipotent wrote:
| Absolutely. JSON serialization is so well optimized in most
| RDBMS vendors now it puts very little additional CPU work on
| the database, and duplicating data in the output for large
| graph-based results is a small price to pay to breaking out
| of set-based constraints.
| DaiPlusPlus wrote:
| > This is no longer true in database that have JSON support
|
| Doing that means losing foreign-key referential integrity...
| jmull wrote:
| The comment alludes to this, but to clarify:
|
| You can have your data stored in tables with all the
| constraints you might want, but then use json in queries to
| return the results in whatever form you want.
| DaiPlusPlus wrote:
| My mistake - to be fair, it is 5am here
| conradev wrote:
| Generated columns (i.e. some_json->>'id') can have
| constraints just fine, maintaining whatever integrity you
| want
| mattmanser wrote:
| He specifically addressed that in the comment. In the
| sentence right after the bit you quoted.
| mborch wrote:
| JSON has pretty limited choice of data types though. Want
| better precision for numbers, use a string. Want dates? Use a
| string. Etc.
| yencabulator wrote:
| The crap mini-language of Postgres functions to manipulate
| JSON makes otherwise reasonable queries unreadable, though.
| DaiPlusPlus wrote:
| That "crap mini-language" is now in ISO SQL. Now we're
| stuck with it forever. Even in Oracle :o
| riku_iki wrote:
| I think they are adding many readable function versions
| lately
| gemstones wrote:
| Yup, this right here. This aspect of SQL is overwhelmingly why
| I insist on ORMs, too. Any efficiency gains you get by having a
| senior dev write raw SQL for a complex query are immediately
| negated by a junior turning what an ORM would write as a single
| query into three DB calls. All because SQL insists on a flat
| result set you have to turn into a nested collection yourself,
| without an ORM doing it for you with eager loading.
| nine_k wrote:
| From my experience, ORMs in hands of junior.developers who
| happen to not yet know SQL are a disaster. However hard the
| ORMs may try, the code ends up making a ton of small queries
| instead of one efficient query, and fetching a ton of unused
| columns. The developers then end up doing joins manually in
| application code, some distance further from the place of the
| original queries.
|
| ORMs also tend to sneak "live" objects into unexpected
| places, triggering surprise DB accesses.
|
| Not that ORMs are completely useless. We just need to stop
| pretending that there can be a smooth and performant
| automatic mapping between relational tables living in a DBMS
| and Business Objects living in some idealized world without
| storage limitations, where any connection between them is
| like following a pointer in RAM.
|
| Most ORMs provide tools to write composable, reusable queries
| and parts thereof. These are the best parts.
| drbawb wrote:
| Every time I have used an ORM I end up supplementing or
| replacing it with a "query DSL" like jOOQ, Linq, Arel,
| Ecto, diesel, etc. I seem to have the opposite problem of
| OP: I don't often find myself wanting to hydrate some
| complex object graph, what I really want is some small
| fraction of what constitutes "an object": "get me the
| distinct values of this column, sorted by another column",
| or "get me a list of user IDs and e-mails that are
| subscribed to this topic", etc. Trivial to do in SQL, and
| much faster to do that sort of thing _in the database_,
| where the data is already _memory/cache resident._
|
| ORMs, by design, bring unnecessary data over the wire for
| the sake of inflating parts of an object graph you don't
| care about 90% of the time. Most of that data will either
| be unused, or you are going to transform and then discard
| anyways. If you go out of your way to actually optimize out
| unused fields: now you're passing around objects with
| nulled-out references around your application, which is
| just a disaster waiting to happen.
|
| Having a query DSL that actually maps result sets to your
| language's type system is the only way I've found to
| actually write robust, performant, maintainable code. My
| result sets being "too big cartesian disasters" is just not
| a problem I have, because I don't think in objects. I ask
| the database for what I want to get the job done.
| x-shadowban wrote:
| Often we just want "totally adhoc result set, but
| constrained using some common where or join." We keep on
| with the orm, but it's basically a slow and complicated
| form of a view at this point
| 0x445442 wrote:
| These problems have been "solved" by designing micro
| services that don't perform any joins but shift that
| responsibility to the applications. What was once a single
| API call for the application, backed by a SQL query joining
| over tables has been replaced by multiple micro service
| calls. It's now the application's responsibility to hold
| these MS call results in memory and then relate them once
| all the data has been fetched. This works well because the
| failure rate of http requests is so much lower than a DB
| query. And after all, the further away the code from the
| data the better equipped it is to reason about the data.
| DaiPlusPlus wrote:
| > These problems have been "solved" by designing micro
| services that don't perform any joins but shift that
| responsibility to the applications
|
| This is utter nonsense. There is _absolutely nothing_
| about microservices architecture that relates to
| performing JOINs in SQL.
| kmeisthax wrote:
| thatsthejoke.jpg
| eastbound wrote:
| I've had a first-of-class linuxian excellent developer but
| junior, tell me that we need Kafka because our SQL requests
| took 3 seconds.
|
| It should be a single INSERT, but through an ORM that
| multiplies it. The only upside of Kafka is not having the
| ORM...
| gemstones wrote:
| In this case, you are the senior that needs to bypass the
| ORM and just use whatever raw parameterized query support
| exists in your ORM of choice.
| DaiPlusPlus wrote:
| > whatever raw parameterized query support exists in your
| ORM of choice
|
| That doesn't help with conditional-predicates - that's
| another major shortcoming in SQL.
| j16sdiz wrote:
| If you need pre-fetching and 2-phase-commits, a database
| designed for queues can easily work 3x better than a
| genetic SQL database.
|
| That's being said, Kafka is not one of them.
| Hendrikto wrote:
| Funny how differently things can be framed.
|
| You can either call SQL proven and battle-tested, or crusty and
| outdated, depending on your agenda.
|
| Same for the fancy new alternative: It is either fresh and
| innovative, freed from the shackles of legacy and standard-
| compliance, or reinventing the wheel in a non-standardized
| manner.
| croes wrote:
| For data in databases I prefer crusty and outdated
| jrm4 wrote:
| I feel like this entire debate is _strongly_ influenced, if not
| soon made obsolete and pointless, by the presence of the so
| called AI tools.
|
| Feels like there's a universe of difference between the
| experience of "carefully craft the query yourself" and "describe
| the query and let AI write the code for it."
| bshacklett wrote:
| This honestly feels like a great advertisement for fancy-ql.
| Writing queries that take the form of the dataset you want back
| is awesome.
| asylteltine wrote:
| [dead]
| bob1029 wrote:
| SQL is only ever as good as the schema relative to the business
| or problem domain. The focus on the syntax of the language was
| always a mystery to me. It's a domain- _specific_ language. It 's
| up to _you_ to make it not suck.
|
| If you are forced to work with a schema that is poorly-aligned
| with the logical reality it intends to represent, you would
| definitely walk away with a bad taste in your mouth. Hacking
| around bad normalization is 99% of what makes SQL suck for me.
|
| If you ever get a chance to design the whole thing yourself from
| zero, you should almost always insist on one big database/schema
| and routinely review the table structure with the business owners
| before you actually go to prod.
|
| The moment you start doing things like putting data for service A
| into database A and service B into database B, you lose a lot of
| power. Sometimes this is required, but most of the time it's an
| org-chart alignment meme. There are ways to join these separate
| databases, but it starts to fall down pretty quickly. The true
| magic of SQL is having all of those dimensions in _one_ place at
| one moment in time so you can put a pin in anything without
| complex distributed transactions.
| Jenk wrote:
| The "domain" in the DSL of SQL is "relational data" not "your
| business domain"
| bob1029 wrote:
| What does this "relational data" (hopefully) represent?
| SeanLuke wrote:
| What happened to Datalog?
| grose wrote:
| I was hoping for a Datalog shoutout.
|
| I like SQL, but it has a few disadvantages compared to Datalog.
|
| - In Datalog, queries and the data itself are homoiconic; the
| structure for both is exactly the same. Uses first-class
| variables to represent unknowns.
|
| - Easy to compose queries (just tack on another predicate)
|
| Of course it has the disadvantages that it's not widely
| supported (outside of Datomic/Clojure/Prolog ecosystem?), and
| not as popular, and maybe even more difficult to optimize
| [citation needed].
|
| I would love to see a new Datalog-based DB. SQL-but-slightly-
| different-syntax is "lipstick on a pig" as they say, not very
| compelling IMO (and not really novel either, you can see it
| reinvented in every ORM).
| benrutter wrote:
| Maybe I didn't grok this article but all the examples made me
| think "fancyql" (as it calls it) looks a lot better than SQL.
|
| If I could compile back and forth between SQL and "fancyql" then
| using fancyql feels like an absolute no brainer to me?
|
| I'm a lot less sympathetic to the "everyone already knows it"
| argument after dealing with SQL queries that are many hundred
| lines long.
| frognumber wrote:
| I'm working on a query language right now!
|
| Why not SQL?
|
| Lack of tooling for SQL.
|
| Yes, SQL lacks tooling. There's a ton of stuff to build a SQL
| client, obviously. However, on the other side:
|
| - I have no sane way to parse SQL
|
| - I have no sane way to comprehend SQL
|
| Writing a SQL query system would be many months of work. Tossing
| together a good-enough query language with standards like JSON or
| YAML means I can json.loads(query) in Python and JSON.parse in
| JavaScript.
|
| SQL would be an ideal fit if there was good tooling, and it fits
| in more places than most people realize. Web API query a whole
| bunch of stuff stored in all sorts of complex ways. SQL is better
| on paper than RESTful / AJAXy / GraphQL / etc. APIs.
|
| It's not better if it means that the query language takes more
| time to build out than the entire rest of the system.
|
| TL;DR: If you want to build a high-visibility open-source project
| and guarantee employment for the rest of your life, an elegant
| SQL parser, especially for building web APIs, would be a great
| thing to do.
| deely3 wrote:
| Could you tell me what you want to accomplish by reimplementing
| query system? I really curious, because generally query systems
| used to query data from DB..
| t8sr wrote:
| This comment is full of hyperbole. Writing a precedence-
| climbing SQL parser should take a few days. I know because I've
| done it. I don't know where you're getting "months of work" -
| mine ended up being like 600 lines of Python.
|
| And I don't know what you mean by "no sane way to comprehend
| SQL" - I guess the millions of data people in the industry are
| just insane?
|
| Cobbling it together with YAML or JSON is a reasonable trade
| off if you're in a hurry, but I don't understand how we got to
| the point where we're throwing out estimates like "writing a
| parser is months of work" and "it's a PhD project to render
| some glyphs [1]"
|
| 1: https://news.ycombinator.com/item?id=28743687
| mcdonje wrote:
| I'm generally on the side of OP, but this is reasonable.
| RyanHamilton wrote:
| Show me an elegant SQL version for the queries in this article:
| https://www.timestored.com/b/kdb-qsql-query-vs-sql/ Particularly
| when you are trying to run queries where order matters, e.g. top
| 3 posters by topic on HN. You will find it much more annoying.
| Fundamentally SQL is based on the concept of tuples/sets which
| have no order so there's no way to avoid it being messy. What you
| want is a database based on the concept of an ordered list,
| suddenly what is complex in standard set SQL becomes easy in
| almost any other language. My second big complaint would be that
| SQL isn't really a programming language. Parts have been bolted
| on by various vendors or they now let you run python/java on the
| SQL server but considering how heavy SQL already is, having a
| full blown language may actually be less cognitive load than
| learning all the sub variations of language implementations.
| iLoveOncall wrote:
| It's easy to cherry pick. I guarantee you there are a lot more
| queries that are easier to write in SQL than in your favorite
| FancySQL (or even worse, NoSQL) variation.
| rak1507 wrote:
| There aren't. Feel free to try to come up with something.
| 'SQL' is pretty feature light (without specific extensions).
| Qsql is really great, it's a shame it's locked behind a
| proprietary language.
| dan-robertson wrote:
| But one doesn't write arbitrary queries. It is very easy to
| end up frequently wanting to write the kinds of analytic
| queries described in the GP rather than the kinds of things
| which SQL expressed better (which you fail to describe).
|
| People pay a lot of money for kdb so clearly they see some
| value in it despite the lack of sql.
| interleave wrote:
| I had a similar experience this month. We've been pair-
| programming using Dbt to write "long-form" SQL to bubble up a
| report to our business users.
|
| After an initial "Uh-oh, I haven't manually written complex SQL
| in a while..." it all came back fast enough (Thanks, first-
| semester relational algebra!). Turns out, sql _is_ well-suited
| for business "in-queries"!
|
| The things that made us scratch our heads came from how the
| schema had evolved over time. We now have those hairballs at
| least 'contained' and visible. And it's all pretty readable imho.
|
| I guess my initial unease came from using ORMs for CRUD
| persistence and very rare exploration. And holy moly, I'm
| grateful for ORMs. I wouldn't want to manually write those
| inserts and updates.
|
| So, I guess it depends on what you want to accomplish with your
| database.
|
| Btw: A HUGE shout-out to Dbt and Dbt cloud for letting us treat
| sql as code. Didn't expect to love it that much. How was this not
| a thing earlier?
| revskill wrote:
| How about nested selection ? SQL is dump because it doesn't allow
| nested selection by default.
| quickthrower2 wrote:
| Why not both. If kibana supported sql that would be cool in
| addition to it's 2 (or more) distinct syntaxes.
|
| The issue is with non relational dbs like redshift that do
| support sql it is very easy to write an innocent query that takes
| hours to run if you don't use specific keys in the query (ones
| used for sharding). But then some kind of warning or query plan
| indication would help there.
| 0x69420 wrote:
| people who intentionally choose sql databases often make every
| effort to avoid writing any of the stuff by hand, which should
| say something about how much of the value proposition lies in the
| language itself
|
| > SQL has a solid standards committee that maintains and improves
| it.
|
| so does c++. so does javascript. so does cobol
| pjmlp wrote:
| Lovely, right on the subject of all those wannabe replacements.
| tudorg wrote:
| The ecosystem of tools and learning resources around SQL is so
| large that I think generally any FancyQL is a liability. It would
| need to bring a 10x improvement over SQL and that is hard to
| believe.
|
| _However_ , I'd have said the same about JS a few years ago, and
| now we have TypeScript. Perhaps a language that is a strict
| superset of SQL and that compiles to SQL might be something worth
| trying.
| manojlds wrote:
| Probably end up with English powered by LLMs
| wwilim wrote:
| Especially the last point makes me realize that many frustrations
| with SQL are transferred frustrations with the suits (from the
| 70s or not) that we're all working for, and the company culture
| they've created
| jmartrican wrote:
| In the lists of most popular languages, SQL always to seem up at
| the top.
| mritchie712 wrote:
| I felt the same way when Malloy[0] launched. It has some
| interesting features, but I couldn't see myself ever using it.
| Nothing makes a big enough difference to spend the time to learn
| it.
|
| Would love to hear from anybody that's using it regularly
|
| 0 - https://www.malloydata.dev/
| Mizoguchi wrote:
| This is like an airline startup offering you to fly on their much
| better, in-house designed/built aircraft. I think it's cool but
| no thank you, maybe I'll check back in 10-20 years to see where
| they are.
| n_e wrote:
| The biggest advantage of SQL is that it's so common that if you
| deal with data a lot you tend to know it well enough. Sure, there
| are small differences between databases but joins/grouping/window
| functions tend to work similarly enough.
|
| On the other hand, when I have to do a somewhat complex query in
| Elasticsearch, or MongoDB, or gorm, or Django ORM, I have to
| check each time in the docs how it's done.
| karmakaze wrote:
| > Here is another common argument: SQL was designed with 1970s
| businessmen in mind, and it shows.
|
| That is a funny way of looking at it. I see that SQL is based on
| the work of a computer scientist vs DSLs being made by hobbyists,
| and it shows.
| [deleted]
| ayhanfuat wrote:
| In case anyone is wondering he is talking about EdgeDB
| (https://www.edgedb.com/)
| bokwoon wrote:
| https://twitter.com/edgedatabase/status/1620582614703964160
|
| EdgeQL: select Child {name} filter
| .<child[is Parent].name = 'Uma Thurman';
|
| SQL: select child.name from child
| join parent_child_rel using (child_id) join parent
| using (parent_id) where parent.name = 'Uma Thurman';
|
| IMO EdgeQL is going too far with the sigils
| nalgeon wrote:
| No. I'm talking about "SQL shaming" and about my preference for
| SQL over yet-another-query-language.
|
| I have absolutely nothing against EdgeDB or its creators. As
| far as I can tell, it's a great product.
| ayhanfuat wrote:
| All your example queries and quotations are from the EdgeDB
| landing page. Even if you are talking about "SQL shaming" you
| are very specifically talking about EdgeDB's SQL shaming.
| dewey wrote:
| You are missing the point, there's a reason why they don't
| name the database or link to it. It's a general behaviour
| that comes up with many new data stores or tools where you
| can query data. You could replace the images and examples
| with a different database that does something similar and
| the point would still stand.
| ayhanfuat wrote:
| > there's a reason why they don't name the database or
| link to it
|
| That's a very commonly known technique where you
| purposefully take only the overly simplified points that
| you want to counter so that's easy to build arguments or
| say things like "What can your language offer besides
| being created in the 2020s?". This is not to say the
| author or majority of the readers would find what EdgeQL
| offers, other than being created in the 2020s, valuable
| but at least you wouldn't be fighting a straw man.
| smitty1e wrote:
| The joy of SQL is that it's so high-level.
|
| Other responses note some (to me) esoteric enterprise use-
| cases for which SQL may not sufficiently describe exotic data
| vistas. Sure.
|
| But most of the "shaming" one encounters seems to be about
| advertising some sort of magic wand product more than
| pointing out a substantial woe in a system that has been
| prominent for a half century.
| move-on-by wrote:
| Perhaps I'm lucky, but I've never experienced SQL shaming. What I
| have experienced is referencing shaming, where I'm allowed to
| write SQL, but all table references in that SQL need to come from
| the model instead of being hard-coded in the SQL. I suppose it's
| nice to have all the join tables' models being included in the
| file. It makes it easy for a search to find all the usages in
| case there is a big refactor. It also makes the SQL look a lot
| more complicated then it really is and a lot less clean then
| these examples- at least in the code.
| divan wrote:
| FancyQL, which he mentions, is, of course, EdgeQL - an insanely
| good query language of EdgeDB.
|
| The truth is EdgeQL is so good that you never want to go back to
| SQL ever after. It's even a bit depressing when you realize how
| much time has been spent crafting SQL queries and dancing around
| it. EdgeQL renders most of those struggles obsolete.
|
| The author of this post has written a book about SQL Window
| Functions, and probably developed an attachment with his SQL
| expertise. He probably doesn't need another query language -
| nobody likes to return to the "beginner" level after their
| identity has been attached to the "expert" level.
|
| But people who hadn't developed abusive relationships with SQL
| expertise, they absolutely need "your query language".
| divan wrote:
| Shameless plug - I wrote a post with my experiences with EdgeDB
| last year. It's a bit outdated already, EdgeDB 3.0 launch is
| happening next week, but I can only add good things to the post
| so far.
|
| My experience with EdgeDB (Jul 26, 2022)
|
| https://divan.dev/posts/edgedb/
| llimllib wrote:
| > So if you're a hardcore SQL user proud of their 20+ years
| of SQL experience - don't try EdgeQL. It's always hard to
| downgrade your identity from "master in something overly-
| complicated" to "newbie in better-and-less-complicated".
|
| My eyes rolled so hard they actually flipped completely
| around
| dan-robertson wrote:
| I don't really find the examples convincing. Like, I get that sql
| could maybe be written in a slightly less horrid way but I would
| prefer something a lot less horrid.
|
| I think I'm much more motivated by analytics queries than the
| kinds of thing in this example though. I find sql is poorly
| suited in this case because it is verbose and written backwards,
| and often requires many layers of subqueries. That said, one can
| usually still express queries in SQL that other systems do not
| allow.
|
| For these kinds of queries I think there are just better ways to
| express them. Another issue with sql is that has some quite
| strange semantics.[1]
|
| An example query I wrote yesterday is: select
| group, min, max, (max-min)/1e9 range from (select
| group, min(size) min, max(size) max from
| (select time, instance, sum(size) size, regexp_replace(name,...)
| group from X group by
| regexp_replace(name,...), time, instance) group by
| group) order by range desc limit 10
|
| Which is neither pleasant to write nor iterate on interactively.
|
| With something like dplyr instead: X %>%
| mutate(group=regexp_replace(name,...)) %>%
| group_by(group,time,instance) %>%
| summarize(size=sum(size)) %>% group_by(group) %>%
| summarize(min=min(size),max=max(size),range=(min-max)/1e9)
| %>% arrange(-range) %>% head(n=10)
|
| And that can be built up interactively pretty easily by adding
| onto the end of the pipeline.
|
| I would also note that, due to sql being painful, the query is
| not exactly the one I wanted and instead I would have wanted
| something better capturing the change over time, but the thought
| of doing that in SQL seemed too unpleasant.
|
| An example of an actual query language that tries to be better
| for analytics: https://prql-lang.org/
|
| [1] from someone who spent a lot of time working on databases and
| sql: https://www.scattered-thoughts.net/writing/against-sql and
| just on semantics: https://www.scattered-
| thoughts.net/writing/select-wat-from-s...
| nalgeon wrote:
| I think the problem here is with the query, not the language.
| You can immediately improve its maintainability and readability
| by using CTEs.
|
| https://antonz.org/cte/
| dan-robertson wrote:
| Maintainability is not relevant to ad-hoc analytics queries.
| That may be dealt with once the correct query has been
| determined from sufficient iteration.
| rjbwork wrote:
| This exactly the comment I was going to leave. If I get past
| one sub query, I will refactor to CTE.
| markisus wrote:
| The author's second example is extremely unconvincing for me.
| Why would I want to be forced to use a SELECT expression to
| calculate a mean? Relational algebra is a great abstraction.
| SQL, however, seems to be poorly thought out and ad hoc. It's
| just the first implementation of a relational algebra language
| that worked. But why should we be stuck with it forever?
| croes wrote:
| Why do you need the outermost query?
| dan-robertson wrote:
| I think I had originally written something like select
| min(size) min, max(size) max, max-min range, but that didn't
| work as the newly introduced names 'weren't in scope' and I
| didn't want to type those aggregations out again. You're
| right that it could have been avoided.
| throwaway2990 wrote:
| You can't mix/max a summed group? The sum would be in the same
| group level as min max...
| dan-robertson wrote:
| I corrected my example (the inner group was meant to be by
| more columns)
| reese_john wrote:
| You could probably use window functions
|
| Ex: min(sum(size)) over(partition by group)
| min, max(sum(size)) over(partition by group) max
| egeozcan wrote:
| Query language for database queries! I thought the argument were
| going to be against application query languages, like the JQL for
| Jira and so on, which I actually like.
| ellisv wrote:
| Ugh I hate those. I've been using Jira for nearly a decade and
| have written numerous filters for dashboards, etc and I
| _always_ have to look up the bespoke nuances of JQL.
|
| Recently I was burned by a bad query in Google Log Explorer.
| There was no feedback my query was wrong, just no data.
| ur-whale wrote:
| > I don't need your query language
|
| With LLM's, no one is ever going to need _any_ query language
| starting effing now.
|
| And good riddance to all of them too, I've yet to see one that
| made any kind of sense from the ease of use perspective.
| amai wrote:
| SQL got the order of key words wrong. Instead of
|
| ,,Select ... from ..."
|
| it would be much better to write
|
| ,,From ... select ...".
|
| PRQL got that right: https://prql-lang.org/
| rubyn00bie wrote:
| _sickos.jpg_ YES!
|
| I can't count the number of services or things I've had to use
| which invent their own query language instead of using SQL. In
| every case the language is empirically worse than SQL, especially
| when it's some mangled hybrid to make things "easier" _cough_
| NRQL _cough_. The silliest part is, if we all just fucking used
| SQL the tooling and integration would be much fucking easier and
| /or free. Not to mention, in almost no organization will you have
| the time or resources to not make something that's a half-
| implemented, poorly spec'd, rubbish version of SQL.
|
| I think the biggest problem with SQL is that folks feel like they
| don't need to know it or that it's not useful. Oh dear beebs,
| it's fucking useful. Next time I personally need to build a rich
| query interface, I'm just using row level security and opening up
| Postgres.
| civilized wrote:
| It's funny how some devs are negative towards SQL but fiercely
| protective of other much more obscure tools from the 1970s, like
| unix utilities.
| vlovich123 wrote:
| It's more likely devs are a heterogenous group and you are
| mixing opinions from unrelated people? Unix utilities from the
| 1970s are shit. Many of their descendants today aren't that bad
| but have lots of problems (eg gawk and gnu sed can do magic in
| the hands of masters but getting that proficiency is probably
| not worth the effort).
|
| SQL is a problem not because of the era in which it was
| developed, but because somehow we haven't evolved any
| meaningful successors. We have a bunch of dominant programming
| languages and only 1 data mining language? What's up with that?
| Why is there this pretense around only having one language?
| Multiple languages are healthy because ideas cross-pollinate.
| How long after MongoDB did it take database vendors/OSS
| projects to start adding JSON support to their SQL databases?
| LunicLynx wrote:
| The main issue with sql is, that it is the wrong way around,
| which eliminates all tooling support.
|
| You need to state what you want (select a, b, c) before you tell
| it from where to get it (from). And no tooling can predict that.
|
| So switching this, moving from and joins in front of select,
| might be everything needed to fix sql.
| Etheryte wrote:
| While I agree with your point, this isn't really as big of a
| stopper for tooling as you make it seem. Many programming
| languages use something akin to import foo from bar and their
| tooling is just fine.
| mrjin wrote:
| Almost exactly what I was going to say, then saw your comments.
| To me that the biggest problem. What I have been doing to
| workaround this is to just write select *, then finish the from
| join etc, go back and fix the result columns.
| ako wrote:
| Always start with the end in mind, first what your goal is then
| how to achieve it.
|
| Also, i don't actually see the problem because you never write
| a query in a lineair way. Usually start with "select * from
| table limit 10", look at the columns and data available, and
| then start refining. By now, code completion works as the table
| is known. Wouldn't help much to write it table first.
| LunicLynx wrote:
| Exactly, you could actually drop the `select` in this case
| and just say `from table limit 10` don't state what you don't
| need.
|
| What you describe is learned behavior to get along with a
| design flaw. SQL won't change, so no reason to worry.
|
| My point is: People keep creating new versions of it, because
| it is not as `easy` to work with as it could be.
| ttfkam wrote:
| In Postgres, it's TABLE foo LIMIT
| 100;
|
| No SELECT with columns and no FROM keyword.
| ayhanfuat wrote:
| That's valid DuckDB syntax
| ttfkam wrote:
| Because the DuckDB folks used the Postgres query parser.
|
| It's been an alternative in Postgres for decades.
| mcdonje wrote:
| DuckDB has some really nice syntax updates that I hope
| get added to the ISO spec, like GROUP BY ALL and GROUP BY
| aliases.
|
| I like their approach of adding thoughtful quality of
| life improvements instead of coming up with a new
| language.
|
| https://duckdb.org/2022/05/04/friendlier-sql.html
| zX41ZdbW wrote:
| Most of the improvements in the article, including
| unrestricted aliases, were added in ClickHouse and
| subsequently influenced DuckDB. They still have to
| implement many usability and language improvements from
| ClickHouse.
| mcdonje wrote:
| Didn't know that, thanks.
| fbdab103 wrote:
| I would love if base SQL were improved. On the other
| hand, we are just slapping lipstick on a pig. It is
| already a gargantuan mess which needs a path to
| replacement.
|
| Then again, Oracle just this year added support for
| booleans, so asking the incumbents to switch to a new
| query language seems an impossible ask.
| magicalhippo wrote:
| > GROUP BY aliases
|
| We're migrating from Sybase SQLAnywhere to MSSQL, and not
| being able to use aliases in WHERE, GROUP BY and ORDER BY
| is such a pain in the behind.
|
| Almost all our non-trivial queries have to be nested
| multiple levels due to this, which doesn't exactly help
| readability.
| idoubtit wrote:
| > Usually start with "select * from table limit 10", look at
| the columns and data available, and then start refining.
|
| An experienced person won't do that. For any moderately
| complex SQL query, before writing it I already have in mind
| the several jointures I'll need, since I usually know the
| tables and FK I'm working with. It's like following the edges
| of a graph, all in my head. But I don't know all the fields
| of these tables, so I rarely write their names from memory.
| So I have to write "SELECT 1 FROM ..." and then go back to
| that "1" once my FROM is complete. That's not the end of the
| world, but it does smell.
| marcosdumay wrote:
| If you mean a developer with deep and recent experience on
| the exact tables your query will use, then yeah, this one
| won't do that.
|
| But if you mean any other kind of experience or expertise,
| you are wrong. Those do not correlate with how a person
| assembles a query.
| kubanczyk wrote:
| > have in mind the several jointures
|
| I typically keep all JOINs I've ever used on that schema in
| a single file, one per line.
|
| Before writing a new query I can just copy paste some
| JOINs, simply skimming through table names like lego
| bricks.
|
| That way it's surprisingly easy to beam from domain problem
| to a new query that uses 10 or 20 tables.
|
| I've just realized that it might be all archaic now, in LLM
| era.
| lostdog wrote:
| This is a great idea in practice, but it really points to
| how sql could improve.
|
| You have a library of useful joins that you've checked
| for correctness. Saving the library as reusable code
| would be even more useful.
| jmull wrote:
| > An experienced person won't do that.
|
| I'm very experienced and do that kind of thing all the
| time.
|
| Also, your "SELECT 1" technique seems like practically the
| same thing, except I guess you discover column names from
| autocomplete tooling rather than query output. (To my mind
| the difference is inconsequential.)
| weinzierl wrote:
| Me too and I don't even bother with LIMIT 10. However,
| the "proper" experienced way would be DESCRIBE I guess.
| ako wrote:
| I consider myself experienced, started professionally with
| sql in 1994 with oracle 6, and it really depends on how
| familiar you are with the database. I often work with
| databases I'm not familiar with, so exploring the structure
| by looking at examples of the data is where I start.
| danielheath wrote:
| This is also my primary issue with vi.
|
| d3w (`d`elete `3 w`ords) cannot be highlighted / indicated in
| any way ahead of time. If the motion specifier came first, it
| could be.
| wryanzimmerman wrote:
| If you want to highlight something, use `v` for "visual" mode
| Fissionary wrote:
| You might like kakoune (https://github.com/mawww/kakoune),
| which does exactly that: first you select the range (which
| can even be disjoint, e.g. all words matching a regex), then
| you operate on it. By default, the selected range is the
| character under cursor, and multiple cursors work out of the
| box.
|
| It's also generally lean and follows the Unix philosophy,
| e.g. by using shell script, pipes, and built-in Unix
| utilities to do complex operations, rather than inventing a
| new language (vimscript) for it.
|
| (Not affiliated with the creator, but kakoune has been my
| daily driver for years now.)
| kwkelly wrote:
| Helix[1] is another editor which heavily borrows from
| kakoune's "selection then action" paradigm. The editor is
| very good, but still in heavy development, so it lacks
| plugins and has the occasional rough edge. [1]
| https://helix-editor.com
| mmh0000 wrote:
| I know you specifically mentioned 'vi', but I'm going to
| blindly assume you meant 'vim'. In which case, as others
| already pointed out, you can use visual model to get exactly
| that behavior.
|
| A good mindset to have in regards to Vim is, "Vim can do
| anything, even make you coffee". The trick with Vim is
| actually figuring out /how/ to do it.
|
| I highly recommend you start with the VimCasts[1] video
| series. They're short, 5 minute, videos. With each covering a
| specific functionality of Vim. They straight to the point,
| and the author provides samples code for all videos.
|
| [1] http://vimcasts.org/episodes/page/8/
| xyzzy_plugh wrote:
| v3wd?
| Olreich wrote:
| `3w` is a command all its own. There's an implicit move
| command baked in. Moving gets pretty clunky if you don't have
| first order movement (adding a specifier or something to
| clear selection). You can mimic this by mapping a single
| button to <C-v> and disabling all movement commands in normal
| mode. It's rough, but may be learnable.
|
| I think something that might work better is adding a "commit"
| signal to operations. So you type `d3w` and the editor
| highlights the next three words with a strike through or red
| or whatnot. Then you can hit enter to commit the delete or
| escape to cancel it (cursor resets to start position,
| highlight goes away).
| wryanzimmerman wrote:
| If you use visual mode, you can do `v3w` to highlight three
| words, and then you can manipulate that selection before
| you do something with it, like `d` for delete.
|
| You can also use that with ex commands, like if you do
| `vap:s/foo/bar/g` you will replace `foo` with `bar` only
| within the block of code you visually highlighted with `ap`
| (which I remember as "a paragraph).
|
| So if you want "delete three words", do `d3w`. If you want
| "highlight three words" do `v3w` and then issue another
| command like `d` to do something with what you highlighted.
| PhilipRoman wrote:
| I think these two concepts could be reconciled by having
| selection be combined with moving. So 3w could select three
| words but also move the cursor to the end of selection,
| ready to process another normal mode command. The
| highlighting would need to be scaled back a little (perhaps
| a dark gray background), as you don't want your entire
| screen to light up when moving but I think it's doable.
| Kamq wrote:
| > So 3w could select three words but also move the cursor
| to the end of selection
|
| I mean, if you hit v first, that's exactly what it does.
| PhilipRoman wrote:
| Yes, but in terms of editing speed and comfort, one
| keystroke is the difference between life and death. As
| long as the motion can be described in a single command,
| visual mode is overkill.
| SaidinWoT wrote:
| That's exactly the model used by the Kakoune editor[0].
| It definitely feels more intuitive to me, but I
| personally didn't stick with it due to vim's ubiquity.
|
| [0] https://kakoune.org
| mcdonje wrote:
| I get around this when hand coding by doing a quick 'SELECT *
| FROM', adding my joins, then going back and filling in the
| fields using intellisense. Intellisense doesn't know what
| columns are available until the FROM clause is handled.
|
| To me, this is a slight annoyance with an easy workaround. If
| the SQL spec gets updated to allow switching the clauses
| around, I'll be pleased. But I'm not about to change languages
| over it.
| cfiggers wrote:
| That's exactly what I do too. 'SELECT * FROM', then write the
| rest of the query, then double back and replace the * at the
| end.
|
| Is it ideal? No. But it's muscle memory now, so...
|
| -\\_(tsu)_/-
| croes wrote:
| >eliminates all tooling support.
|
| That's an exaggeration. Usually I just start with Select * and
| build all the necessary joins. For that the tooling support
| works without problems and when I select the columns in the end
| it works too.
| n_e wrote:
| In most tooling, you can write SELECT FROM table, then go back
| to the select list and have autocomplete work.
|
| The situation could certainly be better, but at least this
| works today.
| smitty1e wrote:
| The query is just a requirements statement.
|
| After parsing and analysis steps, the system is going to do
| what it does with the statement, no?
|
| The syntax is for the user, not the system.
| psnehanshu wrote:
| Sure, but the parent is speaking about how it is difficult
| for tooling to present with options when it doesn't know
| which table to select from.
| mabbo wrote:
| I agree with you and think this backwards model leads to
| developers having a poor mental model of what they are doing.
|
| Step 1: build the dataset you want (FROM and JOIN) with all
| columns.
|
| Step 2: filter out the rows you don't want (WHERE).
|
| Step 3: choose which columns/values you want (SELECT).
|
| Maybe it's just me but this model makes so much more sense to
| me! I'm sure not every developer has the same way of thinking,
| but it sure does seem more logical to me.
| [deleted]
| madsbuch wrote:
| This is definitely something that makes tooling harder. But I
| think it is because tooling is though up wrongly.
|
| Consider `SELECT 0 AS some_num`. This does not have a FROM
| clause.
|
| While this example seems contrived, there are several examples
| of queries where the FROM clause is not just a listing of
| tables. Especially when moving into larger projects in SQL.
| zaxomi wrote:
| If I could change one thing with sql, this would be it.
|
| It makes more sense to have the select last.
| wood_spirit wrote:
| Yeap, simply supporting FROM ... SELECT ... WHERE ... would
| make sql much more autocompleteable?
| iLoveOncall wrote:
| This is a strength, not a weakness. It forces you to only
| select what you actually need, and state where it comes from
| afterwards, to only limit your query to exactly what you need.
|
| This is paramount for performance.
|
| If autocompletion is your issue, just get a better client, it's
| perfectly possible to autocomplete field names even before
| specifying database or table name.
| daxvena wrote:
| I think you're missing what they're trying to say. You can
| still select what you actually need in a different order, but
| changing the order gives more immediate feedback from
| autocomplete. The order has pretty much zero impact on the
| performance of a query. A parser would still have to read out
| the whole query, and it's not expensive to unravel into a
| more efficient implementation if it would really help.
|
| The problem is that SQL forces you to think about what to
| select before you even say where you're selecting from.
| There's nothing a client can do to recommend columns if it
| doesn't know where you're selecting from. It's pretty
| cumbersome to have to SELECT * FROM x and then go back and
| erase the * to actually get auto-completions.
|
| It basically forces you to tell it what you want before you
| even know what the options are.
| iLoveOncall wrote:
| You are missing what I said.
|
| I know that from the interpreter's standpoint, it doesn't
| matter which one is written first.
|
| What I meant is that as a human, if you have to think first
| of the columns you want to bring in, it will guide you
| towards the joins that you need and only those, rather than
| thinking "let me join all those tables because I need
| _some_ data from the entities inside".
|
| My point about "autocompletion is still position" was not
| connected to the first part of my comment.
| xboxnolifes wrote:
| Even if I know the exact query I want to write, your
| suggestion does nothing to improve autocomplete for
| typing it in.
| iLoveOncall wrote:
| Yes because that's not the main point of my comment.
| Disregard completely the part about autocomplete if you
| want.
|
| I just added it as a separate point, to say "you can have
| autocomplete no matter the order in which you write your
| query"...
| [deleted]
| XorNot wrote:
| FROM users SELECT name, id, location WHERE name LIKE 'a%';
|
| You know I think you're right.
| frogulis wrote:
| Go further: `FROM users WHERE name LIKE 'a%' SELECT name, id,
| location` - after all, you can WHERE on things that aren't
| projected by SELECT
| [deleted]
| 6510 wrote:
| A human would grab the paper with the right table on it
| then search for the row he wants with his finger and
| finally gets the phone number from the row.
| user3939382 wrote:
| There was an article on HN about this maybe 6 months ago. I'm
| in agreement.
| ellisv wrote:
| Eh. I'm not convinced by this. I may know what I want to
| query as often or more than knowing where it comes from.
|
| I suppose it could be nice if the user could specify clauses
| in an arbitrary order but it'd certainly add complexity.
|
| I don't find it difficult to jump around a bit from clause to
| clause while writing a query. In fact, it's incredibly rare
| to write a query straight through and have it do what you
| want it to do.
| 6510 wrote:
| O well, if we are going to be like that?:
|
| FROM users WHERE name LIKE 'a%' SELECT name, id, location
|
| (I should refresh the page before posting)
| blipvert wrote:
| I might be wrong, but there's nothing to stop a front end UI
| from accepting this syntax and then writing out canonical SQL
| to do the actual query, is there?
| valenterry wrote:
| Exactly, it puzzles me that tooling doesn't do that...
| camgunz wrote:
| Just start with `SELECT *` and sculpt w/ autocomplete
| afterwards as you like.
| jojobas wrote:
| Ain't broke.
| kristiandupont wrote:
| GP clearly shows why it is. I think SQL is great but their
| point is spot on.
| badestrand wrote:
| The wrong order in the statements is a minor inconvenience
| but the widespread use and having an industry standard is
| 100x as valuable.
| ellisv wrote:
| Agreed. The last thing I need is more flexibility in how
| people write queries. It would be shortly followed by
| numerous aggressive query formatting tools. Things would
| get messy fast.
| yencabulator wrote:
| Since you express that opinion, I trust you're already aware of
| this, but just in case: https://prql-lang.org/
| tester756 wrote:
| C#'s LINQ (query syntax, not methods) got it right
|
| var result = from s in stringList where s.Contains("Tutorials")
| select s;
| Luctct wrote:
| Tcl dicts do something very similar, and Tcl is very well
| integrated with SQLite.
| DaiPlusPlus wrote:
| or just `var result = stringList.Where( s =>
| s.Contains("Tutorials") )`
|
| I can't stand the non-extension-method Linq syntax: the
| _only_ place where it offers a readability improvement over
| ext-methods is using `join` - but I hardly ever do that in
| Linq anyway.
|
| Also, in both my code and yours, `result` will be a lazy-
| evaluated `IEnumerable<String>` which may be undesirable -
| which means it's probably a good idea to use `.ToList()` to
| materialize it - which means having to use ext-methods
| _anyway_ - and mixing both syntaxes in the same expression is
| aesthetically atrocious.
| rjbwork wrote:
| >I can't stand the non-extension-method Linq syntax: the
| _only_ place where it offers a readability improvement over
| ext-methods is using `join`
|
| Hah! I tell the juniors this all the time :D. This seems to
| be basically the consensus among the C# community these
| days as far as I can tell as well.
| recursive wrote:
| I guess I'm not in the C# community. `let` is pretty
| annoying to reproduce with extension methods. Also, when
| you use multiple `from` clauses, you get access to all
| scopes, whereas with `SelectMany` you only get the
| parameters of your current lambda.
| DaiPlusPlus wrote:
| > you get access to all scopes
|
| Read: "you create new heap-allocated closures which wreck
| your Linq expression's runtime performance"
|
| Or:
|
| "you create Linq queries that cannot be translated into
| SQL"
| recursive wrote:
| It was all in memory. To the extent that it's a
| performance tradeoff, it might be one that's worth making
| depending on the use.
| DaiPlusPlus wrote:
| Aye - but the annoying thing is that it was never
| necessary: with a few subtle changes to Linq it's
| possible to have allocation-free closures by passing
| state via hidden parameters on the stack - but just like
| every language out there we're now hobbled by decisions
| made 15 years in the past.
|
| ----
|
| On a related note, it's interesting just how unpopular so
| many new C# language features are (just by looking at the
| numbers of Thumbs-down reactions on the GitHub Issues/PRs
| - stuff like top-level Main. It feels like C#'s LDT wants
| to be like Swift, but without Swift's willingness to
| ditch ill-conceived features after a few years... but I
| think C# would be well-served by taking an axe to some
| language-features by now - like keyword-Linq and CLS-
| compliance (honestly, do any ISAs today still lack
| hardware support for unsigned ints?)
| recursive wrote:
| Interesting.
|
| I think there are some otherwise-seldom-used linq
| overloads for doing just that. Maybe on Join() or
| GroupJoin() or something like that. Query expressions use
| them for compilation to keep closure use down, but I
| don't know too much about it.
|
| Anyway, if top-level statements are wrong, I don't ever
| want to be right. I had no idea there was any controversy
| on that. It's trivial to add the boiler-plate back in if
| you want it.
|
| The main thing that bugs me is that Expression<> is stuck
| with language features that existed in C#4, even when
| there are trivial lowerings. e.g. `is not null` could be
| `!= null`. (This example ignores operator
| implementations, but most IQueryables ignore more than
| that already) Even better, add AST node types for the new
| language features.
| Jochim wrote:
| I'm not sure the Github issues are the best reflection of
| the popularity of those features.
|
| Top level statements and the new HostBuilders are
| fantastic, they're much cleaner and easier to follow than
| the previous mess. Add Minimal APIs to the mix and C# is
| finally a viable choice for spinning up something
| quickly.
| Volrath89 wrote:
| I completely agree with you, in the context of C# code. But
| also take into account not everybody in HN is a C#
| developer, and OP syntax works better as an example of his
| point. All non C# devs will understand his "sql like"
| example easier.
| recursive wrote:
| > the _only_ place where it offers a readability
| improvement over ext-methods is using `join`
|
| No love for let? from item in items
| let frob = Expenseive(item.P1) where frob > 3
| selec new { frob, item }
| DaiPlusPlus wrote:
| That can be done with ValueTuples instead of Anonymous
| Types: `items.Select( i => ( i, frob: Expensive( i.P1 ) )
| ).Where( t => t.frob > 3 );`
|
| ...and ValueTuples are superior to Anonymous Types
| because you can actually return them from a function or
| use them as parameters - whereas Anonymous Types cannot
| cross method-call boundaries (excepting using generics
| for pass-through).
|
| Anonymous Types in C# were a massive mistake. They should
| be [Obsolete]'d, IMO.
| recursive wrote:
| The tuple might be gone by the time the scope ends. I'm
| aware it's possible to reproduce the behavior without
| let. The code just looks worse.
| tester756 wrote:
| chill,
|
| I've been just showing that *approach* of starting SQL
| query from "from" part is viable, because that's how
| Microsoft timplemented in one of two LINQ "API"s"
|
| I'm not trying to convince anyone to use LINQ's Query
| Syntax.
| Reitet00 wrote:
| I wonder if that could be addressed at the spec level
| allowing reverse order of these keywords. It doesn't seem
| complex on the surface and the the engines could slowly add
| support for it.
| christophilus wrote:
| LINQ and proper reflection are what I miss most from C#. (I'm
| all Typescript at the moment.)
| rjbwork wrote:
| Gods, I tried to do some reflection stuff in a Node TS
| project I got put on about 18-24 months ago, and it was a
| total shit show. It was really disappointing to see how
| lacking the actual runtime capabilities of TS are.
| hfkwer wrote:
| There are no TS runtime capabilities. This isn't a thing.
| rjbwork wrote:
| There are some libraries that allow you to do some stuff.
| You can attach metadata to some objects about types in
| some contexts. But obviously it's bolted on because under
| the hood it's all Javascript.
| baq wrote:
| What runtime capabilities? There shouldn't be any?
| DaiPlusPlus wrote:
| What does "proper reflection" mean to you?
| ledgerdev wrote:
| I recall Anders saying on some podcast they did that so they
| could provide auto-complete. Funny thing is that I used it
| for a couple years, but almost never use the linq syntax any
| more, and not sure why.
| cerved wrote:
| Probably because if you wanted to write something like SQL,
| you would write it in SQL, and if you want to write C#, you
| write C#
|
| Linq query syntax is just dumb
| jmartrican wrote:
| I'm so jealous that Java does not have its own LINQ.
| turboponyy wrote:
| The work was already done for them, as LINQ is just do-
| notation from Haskell.
| DaiPlusPlus wrote:
| No, Linq is *not* in any way like Haskell's `do`.
|
| Linq is a (reasonably) compromised, non-referentially-
| transparent, implementation of a restricted form of
| relational-algebra with side-effects permitted, so I'm not
| comfortable describing Linq as "monadic".
|
| Whereas Haskell's `do` _is_ strictly monadic.
| bad_alloc wrote:
| Also important: The behaviour of SQL is well understood, a new
| query language always introduces the risk of defects in the query
| language itself or developers making mistakes in an unfamiliar
| language.
| mcs_ wrote:
| I agree with many points, however, it depends on the abstraction
| that you need and the abstraction depends on the architecture you
| are adopting.
|
| For example, if you are doing DDD and your repository
| implementation is about SQL, adding another layer of abstraction
| is not worth. But if your design is less sophisticated, or you
| are in an early stage of the project, you may find appealing to
| use that abstraction.
| raybpurchase wrote:
| I'd also stick to SQL until my boss says we'll be using something
| else because is cheaper.
| dools wrote:
| I feel exactly the same way about ActiveRecord ORMs. That's why I
| created PluSQL:
|
| https://github.com/iaindooley/PluSQL
| rebataur wrote:
| Actually SQL is pretty cool and by using concatenative concepts,
| you could do some pretty cool stuff.
|
| We built a datascience tool to quickly build data apps which can
| be extended from the frontend, including data wrangling and
| datascience functions. Most exciting part was using the
| PostgreSQL and SQL to process, clean and enhance data and write
| extensions and bring it all together.
|
| We open sourced alpha version yesterday, more documentation to
| come.
|
| https://github.com/rebataur/rapidiam
| galaxyLogic wrote:
| The problem with SQL is that it is not a (very) composable
| language.
|
| The documentation for EdgeDb goes into some detail about that and
| shows an alternative better language for data-queries.
|
| https://www.edgedb.com/showcase/edgeql
|
| To understand why SQL is bad, you must first be shown something
| better, and EdgeDb seems to be such better more composable
| language.
| fbdab103 wrote:
| I like PRQL [0]. It fixes a lot of the SQL warts and compiles
| down to regular SQL (think Typescript to JS).
|
| The syntax example on the PRQL homepage from
| invoices filter invoice_date >= @1970-01-16 derive
| [ transaction_fees = 0.8, income = total -
| transaction_fees ] filter income > 1 group
| customer_id ( aggregate [ average total,
| sum_income = sum income, ct = count, ]
| ) sort [-sum_income] take 10 join c=customers
| [==customer_id] derive name = f"{c.last_name},
| {c.first_name}" select [ c.customer_id, name,
| sum_income ]
|
| Trailing commas, select at the bottom, filter is both a WHERE
| and HAVING replacement, easy filtering of created columns, etc
|
| [0] https://prql-lang.org/
| ttfkam wrote:
| I'd much rather debug an SQL query with CTEs, which is what
| that is approximating.
|
| The notion that SQL is not composable is either a lie or
| simply repeated by folks with only a cursory knowledge of SQL
| from 20 years ago.
|
| Views, set-returning functions, CTEs, and more: all examples
| of composability in SQL.
|
| Then of course there's the issue of security where folks tend
| to put all of their access constraints into their middleware
| AFTER the data has already been returned over the wire in
| bulk. Take a moment to consider role-based access control and
| row-level security policies. Instead of trying to track down
| every possible spot where a JOIN could have crept in past the
| code reviews (you code review your DDL and DML, right?), you
| set your GRANTs, REVOKEs, and POLICYs at the points in your
| data model that need them; restricted data never makes it
| into intermediate result sets let alone the final result set
| and the wire.
|
| Don't misunderstand me. GRANT, REVOKE, and POLICY can be a
| real PITA, but that's because *security* is a PITA, not the
| SQL syntax to enforce it. Anyone who tells you their app
| solves your data security problems in the app tier with a
| point and click is a lying salesman.
| nalgeon wrote:
| SQL is infinitely composable. Each SELECT returns a relation
| that another SELECT can query (or combine with another relation
| using set operators like UNION etc).
| default-kramer wrote:
| I suppose it's infinitely composable in that very limited
| dimension, but SQL's critics are asking for composability in
| other dimensions. For example, say I have a pretty long query
| of some Order table. Now I want the exact same query, but it
| should start from the OrderArchive table instead. How do you
| do this? Dynamic SQL? The world has lambasted Javascript for
| much less, but somehow the fact that so many tasks require
| dynamic SQL (or copy-paste) is considered acceptable.
|
| For the record, I make heavy use of SQL because relational
| databases are awesome and SQL is the least bad option I've
| found so far. But the many shortcomings of SQL still annoy
| me.
| camgunz wrote:
| The answers are stored procedures and templates. People
| argue against them, but they're basically the data versions
| of software affordances we already have.
| interleave wrote:
| > The problem with SQL is that it is not a (very) composable
| language.
|
| I thought the same until a few weeks ago. Then we used the WITH
| operator for pre-processing and giving things human-readable
| names.
|
| That helped us manage complexity. The final SELECT statement
| was very easy to reason about.
|
| Not sure if this is a best (or worst) practice but it helped us
| ship it.
| cratermoon wrote:
| Interestingly, EdgeDB is exactly the unnamed tool that the
| article criticizes.
| scottcodie wrote:
| SQL in the context of a single query struggles with a
| composable features. But looking slightly outside the scope of
| queries with assignment statements and view/materialization
| dags then you start getting some of that composition back. I
| think SQRL is an good example of SQL with a bunch of composable
| feature bolt-ons.
|
| https://www.datasqrl.com/blog/sqrl-high-level-data-language-...
| IanCal wrote:
| Malloy is another thing to check out in this space
|
| https://www.malloydata.dev/
| pier25 wrote:
| Nothing is perfect but given that SQL solves the problem, is
| ubiquitous, has tons of tooling and educational material, and is
| extremely mature... It's not going anywhere.
|
| It's not a dinosaur, it's a shark.
|
| Would I like to have something more streamlined and less clunky?
| Absolutely. But it's going to take a lot of effort for anything
| to become as ubiquitous as sql.
| bdcravens wrote:
| The irony is that most of those pushing back against SQL are
| using Javascript, another language that is less than perfect
| but is winning because of reach.
| thomasmg wrote:
| We write programs with Python, Java, Rust, Javascript and so on.
| Yet we use a very different language, eg. SQL, to query and
| modify data. Why? Why don't we use eg. Python as well? SQL is
| different from other languages: it is declarative, meaning it
| doesn't dictate how to do it, but what the response should be.
| Maybe that's the reason? But if that's the case, why are
| declarative languages not more popular? SQL and other language
| are not compatible, and that's often a problem. You have this
| hard border (related to impedance mismatch).
|
| SQL (or GraphQL) is often used as a remote API: The client sends
| a SQL statement, the server processes it and sends the response.
| SQL is very powerful, but also dangerous: the statement might be
| very expensive, for example because an index is missing. Sure,
| you can shoot yourself in the foot also with Python or Java, but
| I argue it's harder. SQL is one more technology in your stack,
| one more thing to learn. And actually, there are many many SQL
| dialects.
|
| I wish databases have better, faster, and standardized support
| for a fast procedural language. So that clients can send
| programs, the server processes it and sends the response. A way
| to access tables and indexes like a hash table or ordered map.
| That way, there is no border. There is no slow query due to a
| missing index. You have to think about how data is access, which
| indexes are needed. But you have everything under your control.
| There is no risk of a missing index, or risk of the database not
| picking the index it should.
|
| (I wrote 3 relational database engines and 4 SQL parsers:
| HypersonicSQL, H2 database, Apache Jackrabbit Oak, PointBase
| Micro. I also wrote a GraphQL parser and engine, and a Key-Value
| store. It's not that I hate SQL.)
| mappu wrote:
| SQLite somewhat works this way - the frontend compiles the
| query into bytecode instructions, that the data layer executes
| procedurally. It would be possible to expose that and write the
| bytecode directly (but it would take more effort to make it
| ergonomic.)
| ako wrote:
| The problem with procedural is that it doesn't optimize very
| well. The fastest way to get your data for a large number of
| random queries often depends on the data size, the available
| indexes, but is also influenced by changes in data size, etc.
| What is fast for a small dataset might be slow for a larger
| dataset. The right algorithm also depends on your filters and
| caching.
|
| It's almost impossible to write procedural queries that always
| perform, that is a really hard task. That's why we use a
| declarative query language, I tell the database what data I
| need, and the database optimizer will determine the best
| performing algorithm to fetch the data based on all dynamic
| statistics it has.
|
| Don't underestimate how much hard work the database optimizer
| takes care of, I'm glad I don't have to program all of that
| myself.
|
| Better get used to this way of working, it resembles pretty
| much how AI assists us.
| thomasmg wrote:
| I have written 4 query optimizers: HypersonicSQL, H2
| database, PointBase Micro, and Apache Jackrabbit Oak. I know
| procedural language don't have such optimizers. But I argue
| that you don't always need them. I argue that the database
| engine query optimizer is often more a risk than a help. I
| have seen missing indexes far too often. SQL doesn't require
| that the programmer thinks how the data is accessed, so the
| result is that too many programmers don't think about it, and
| so don't add indexes, request too much data, and so on. The
| SQL statements work fine with small (development) data sets,
| so the same statements are used with production databases,
| and you run into problems too late.
|
| I don't think it's hard to write procedural queries that are
| always fast. You write a loop or map/filter/collect method.
| You just explicitly need to mention which index to use, is
| all.
| ako wrote:
| Agree on the missing indexes, was in a debugging session
| yesterday where missing indexes turned out to cause some
| issues.
|
| But I have little faith that people who forget indexes are
| capable of writing procedural queries that are always fast.
| These less experienced devs are the ones that benefit most
| from the query optimizer doing the hard work. I think the
| real solution is for database to automatically create
| indexes where needed, as some database already do. And some
| better visual query editor like ultorg.
| erikpukinskis wrote:
| > But if that's the case, why are declarative languages not
| more popular?
|
| Declarative programming is not very popular because it's hard
| to debug. You can't step through it and inspect intermediate
| representations. It presents a black box to the developer.
|
| That said, they do seem to show up around certain very complex
| APIs sometimes.
|
| Besides SQL, CSS is the obvious one.
|
| And I think many parts of otherwise functional/imperative APIs
| are pseudo-declarative.
|
| For example, React is famously functional in its current form,
| with render functions and hooks being functions... but then
| common hooks take a dependency array which is declarative. And
| at the boundary of these hooks the code is no longer
| procedural, it disappears into the framework.
|
| > I wish databases have better, faster, and standardized
| support for a fast procedural language.
|
| That's a fascinating idea. It would take someone with deep
| knowledge of a query engine to encapsulate it with a procedural
| API instead of a declarative one.
|
| That's not me, but I would love to see it.
| [deleted]
| agumonkey wrote:
| not defending sql really but sql is more stable and well
| defined than operational programming languages, it's somehow of
| an asset in times of feature / paradigm volatility to me
| hcks wrote:
| "You can write SQL queries in lower case"
| SushiHippie wrote:
| I know this, but I can't. Muscle Memory, even though I don't
| use SQL that much.
| tikhonj wrote:
| Having used (a lot) of Hive SQL, I absolutely _do_ need your
| query language. SQL is fundamentally incapable of expressing any
| sort of abstraction, so non-trivial queries quickly become
| completely incomprehensible, unmaintainable and bug-prone.
|
| Learning a new language is a one-time, up-front cost. Dealing
| with an awkward, inexpressive query language that integrates
| poorly with my main language, my types or my interface
| description languages is an ongoing source of painful friction.
| Learning something new should not be nearly the barrier to
| adoption that it seems to be for most people!
|
| SQL's shortcomings seem so clear and omnipresent that I
| legitimately do not understand why everybody seems so drawn to
| it. Are the usage patterns for code against a transactional
| database so different from the sort of Hive queries I've had to
| deal with for data engineering and machine learning? Is everybody
| happy with abstractions layered over SQL like ORMs?
|
| Why can't we have, I don't know, some typed variant of Datalog or
| something instead?
| 9dev wrote:
| Not that I fundamentally disagree with you, but SQL has been
| the dominant query language for more than half a century. This
| lends it some credence for being a quite passable solution,
| don't you think?
| tikhonj wrote:
| It's evidence that SQL isn't entirely unusable--but I've
| worked with too much popular technology to believe it means
| anything more than that.
| jeltz wrote:
| Because the alternatives are worse. It is that simple.
| alexchamberlain wrote:
| Aren't Views SQL's answer to an abstraction?
___________________________________________________________________
(page generated 2023-06-17 23:02 UTC)