[HN Gopher] Half a century of SQL
___________________________________________________________________
Half a century of SQL
Author : pseudolus
Score : 104 points
Date : 2024-06-03 11:04 UTC (1 days ago)
(HTM) web link (www.theregister.com)
(TXT) w3m dump (www.theregister.com)
| parpfish wrote:
| i love SQL but it was _really_ hard to get started for two
| reasons:
|
| - it's hard to start tinkering unless you have a database to
| write queries against. most tutorials never really get past the
| most rudimentary hello-world-esque stuff because they're limited
| by the size and complexity of the example data.
|
| - imperative programming teaches you to be very precise and
| explicit about what _exactly_ should happen, so it feels very
| weird to start working with something declarative where you can
| just trust the computer to figure it out and do things well
| automatically.
| giancarlostoro wrote:
| Which is why I think we should always teach SQL basics with
| SQLite. You can just share the db file as-is. You can probably
| find dozens of GUIs and even web GUIs for SQLite that you could
| load a db into.
| hnthrowaway0328 wrote:
| It's not that hard. You can use any SQL tutorial site for
| practice. If you want real life data, find the stackoverflow
| data dump on archive.org and install a PostgreSQL instance to
| play with it.
|
| IMO the biggest issue of SQL is that it brings bread and butter
| for the BI/DA people, who are so close to the business that
| they usually don't care about code quality and such. It's not
| the fault of the language, but the fault of erroneous team
| structure.
| pmarreck wrote:
| SQLite is good for this.
| fifilura wrote:
| I use to say that you have learned SQL the moment you forgot
| everything about C/C++.
|
| Imperative programming is a strong habit to kick. You just have
| to let go. And once you have you will see things from a very
| different perspective.
|
| Also in other programming you'll start seeing the
| map/fold/groupby/dict patterns everywhere.
| parpfish wrote:
| every once in a while i start thinking "it'd be really nice
| to build a declarative language to do this", but then I
| realize that:
|
| - that just means that I'm bored and would rather be writing
| out the logic to handle a more general/abstract
| representation of the problem
|
| - whenever I come across a DSL that somebody else made to
| "make a really simple declarative-like interface", i hate it
| and wish that they didn't do it
| fifilura wrote:
| Was your point that that goes for SQL?
|
| One nifty thing with SQL is that you get all the
| threading/remote processing for free without having to
| think about it. That can be pretty hairy to write yourself.
| simonw wrote:
| I've been building SQL tutorials against my Datasette web UI
| for SQLite as a solution for getting people started without
| having to install anything:
| https://datasette.io/tutorials/learn-sql
| refset wrote:
| Integrating live code editors within docs and tutorials is
| great.
|
| Another example of this I saw recently using SQLite (compiled
| to Wasm) in the browser:
| https://docs.sqlitecloud.io/docs/sqlite
|
| And if you ever want something similar for more general
| backend APIs (without relying on Wasm or the browser to run
| the software), https://codapi.org/ looks very slick e.g. as
| demonstrated in https://antonz.org/sql-upsert/ - discussed on
| HN previously [0]
|
| Inspired by https://www.db-fiddle.com/ my colleagues ended up
| building a fairly bespoke setup for XTDB's docs (XTDB doesn't
| yet compile to Wasm) shortly before I came across Codapi,
| although our requirements were even more particular, e.g. see
| https://docs.xtdb.com/tutorials/financial-usecase/time-in-
| fi... - the backend here is https://github.com/xtdb/xt-fiddle
| which runs purely on top of Lambda Snapstart, and embedded
| within docs based on Astro's Starlight [1] and Web Components
|
| [0] https://news.ycombinator.com/item?id=38663717
|
| [1] https://starlight.astro.build/
| itishappy wrote:
| Agreed, but data is so easy to find online! My breakthrough was
| that Python comes with SQLite built-in. Pointing DuckDB at a
| CSV file looks even easier.
|
| https://docs.python.org/3/library/sqlite3.html
|
| https://duckdb.org/docs/data/csv/overview.html
| parpfish wrote:
| sure, you can find data and spin up your own db but you still
| need a tutorial to a) explain what the data/tables are and b)
| guide you through some things to build. for most programming
| languages you can make some sort of little widget or
| itneractive program that lends itself well to learning via
| curious exploration. SQL does not lend itself to that because
| you don't really make something 'fun'. You just make more
| tables. People aren't going to naturally come up with the
| kind of boring-but-challenging things that you use SQL for
| like "come up with a daily aggregate of pageviews, but
| exclude users that had account status X in the previous 30
| days OR if they have a special status set in this other table
| as of the date of the pageview. And make sure that days with
| 0 page views show up with 0 pageviews and are not just
| missing from the table".
|
| Also, it's very easy to make queries that complete and look
| correct but give wrong or incomplete results. You've not
| going to notice that from just running queries in self-
| directed practice.
| edmundsauto wrote:
| From a slightly different perspective, some people like
| these tasks :). That's part of the mindset of being a good
| data engineer, for example - I rarely have a problem coming
| up with my own set of queries to try out a new database or
| dataset. But I'm also not really interested in a lot of
| SWE-specific things.
|
| Maybe this just isn't something you really want to dive
| into. That's ok!
| AlisdairO wrote:
| self-plug, but you could give https://pgexercises.com/ a
| try. No need to spin up your own DB etc.
| madcaptenor wrote:
| Also, if you don't know any SQL yet what are the chances
| you'd feel comfortable spinning up your own db?
| andy800 wrote:
| Forget online tutorials, you want to pick up thick red Wrox
| SQL books from your local used bookstore or co-op. Nobody
| selling you anything, densely packed, comprehensive info.
|
| SQL has changed very little over the past 25 years, and
| while there are some syntax variations across different
| databases, the most important 80+% is universal. Even if
| the book is for SQL Server the vast majority will work on
| Postgres, SQLite, etc.
| craigkerstiens wrote:
| This is very much why we built the Postgres playground, which
| has Postgres embedded in your browser with guided tutorials -
| https://www.crunchydata.com/developers/tutorials
| kccqzy wrote:
| I think subqueries are a useful feature that doesn't get taught
| often to beginners. Using subqueries it's much easier to think
| clearly and intuitively. Even imperative programmers who don't
| have a ton of functional programming experience still
| understand nested function calls `f(g(h()))`. With this you
| don't even have to know when the different parts of a SELECT
| are applied, and you don't even have to know things like
| HAVING.
| therobots927 wrote:
| Actually with R it's very easy. Just install the sqldf package.
| Then you can read in a CSV or whatever format you're using to R
| (even if you don't know R, ChatGPT could probably write the
| code for you). Once the data is inside an R data frame you can
| query it directly with sqldf like: sqldf("select count(*) from
| csv_dataframe")
| bradford wrote:
| I view SQL right now similar to the way I viewed C++ in early
| 2000s:
|
| I hate it, but there's little point in complaining because it's
| so ubiquitous.
|
| More robust criticism is provided here
| (https://carlineng.com/?postid=sql-critique#blog), which pulls on
| an interview here (https://www.red-gate.com/simple-
| talk/opinion/opinion-pieces/...) The quote I usually drag out is
| from Chris Date, who helped pioneer relational DBs:
|
| "At the same time, I have to say too that we didn't realize how
| truly awful SQL was or would turn out to be (note that it's much
| worse now than it was then, though it was pretty bad right from
| the outset)."
|
| As an example of a language that does it better, I think kusto-
| query-language (KQL, https://learn.microsoft.com/en-
| us/azure/data-explorer/kusto/...) has been a dream to work with.
| (disclaimer, Kusto is a Microsoft product, and I'm a Microsoft
| employee).
| wvenable wrote:
| I kind of disagree; the problem with SQL is that fundamentally
| it's actually pretty good. So alternatives either tend to be
| too radical (throwing out the baby with the bathwater) or
| simply not enough of an improvement to gain any momentum.
|
| I feel like rational database querying is effectively solved
| and there's little point in re-litigating it. But still I'd be
| happy to switch to the perfect replacement if someone develops
| it.
| vkazanov wrote:
| What does "good" mean in this context? SQL is not modular,
| most features are highly context-dependent and there numerous
| handguns.
|
| Sql might be ok for trivial things, as in OLTP that
| programmers tend to work with.
|
| But anything even slightly more advanced is... not nice.
|
| And the standard is unique in its uselessness.
|
| The underlying relational algebra model is brilliant thought.
| rangerelf wrote:
| > SQL is not modular, most features are highly context-
| dependent...
|
| Examples?
|
| > SQL might be OK for trivial things, as in OLTP...
|
| What is the threshold for triviality? I've seen
| understandable fairly complex queries, but they're not
| mind-twisters by any means; if you know what you need, and
| understand your data, >>and are not a layperson regarding
| databases<< it's doable without much sweat.
|
| > But anything even slightly more advanced is ... not nice
|
| Again, what is the threshold, or at least what is your
| threshold, for triviality vs. non-trivial?
|
| You say it's "unique in its uselessness" but "the
| underlying relational algebra model is brilliant", can you
| explain a bit further what you mean by that?
| wvenable wrote:
| Good means it gets the job done in a fairly logical and
| readable way. SQL queries are not giant programs and
| shouldn't be. I've written some very advanced queries with
| plenty of common table expressions, subselects, etc. Could
| it be more modular? Sure. Could the syntax be better? Yes.
| But would that radically change how queries are written?
| Not really.
|
| The worst SQL I've ever seen is when someone attempts to
| program it imperatively. It takes a different mentality to
| write SQL then to write imperative code.
| indymike wrote:
| All of this is true.
|
| "Being ok for trivial things... that programmers tend to
| work" with is precisely why SQL has won over the years. SQL
| has never been a great general-purpose language, but is
| fantastic at the things programmers tend to work with. That
| SQL (and associated databases) keeps getting extended to
| meet new data storage/retrieval problems has really helped
| SQL stick with us.
| theLiminator wrote:
| Honestly for OLAP use cases, I think dataframe apis are
| superior.
| metrognome wrote:
| I gained a a few years of experience in SQL-based OLAP
| systems at my current job. In this time I developed a
| strong appreciation for SQL, especially for its
| composability. Recently, I started a project in Google
| Colab, gluing together queries from several systems with
| Pandas DataFrames. I can honestly say that I've never
| been more frustrated learning an API than I have with
| Pandas.
|
| Need some window function like LAG() or LEAD()? Too bad,
| I hope you like writing Python "for i in range(...):"
| loops. My notebook is littered with ".reset_index()"
| calls, ".replace(np.nan, None)", "axis='columns'",
| "foo.assign(bar=lambda df: df.apply(lambda row: ...))".
| groupby is especially confusing to me, as a Pandas
| GroupBy is difficult to compose with . Compare this to
| SQL, where a subquery is a subquery, whether or not it
| has a GROUP BY clause.
|
| The Pandas documentation also leaves a lot to be desired.
| Take the documentation of pandas.NaT[1] for example.
| "pandas.NaT: alias of NaT". Ok? That still doesn't tell
| me what NaT is, nor does it link to the thing that it
| aliases. The groupby documentation[2] also caused me some
| headaches, as it covers only the simplest aggregation
| use-cases.
|
| Pandas is clearly better for some use-cases, but mostly
| for simple operations that are well-supported by the API
| (perhaps numeric operations that are implemented with
| native numpy routines). But if I'm doing some interactive
| OLAP stuff, I'll reach for SQL. Perhaps the problem is
| I'm trying to use Pandas like it's SQL, when it's not.
| But for manipulating data, I'd rather use a _language_
| than a _library_.
|
| [1] https://pandas.pydata.org/docs/reference/api/pandas.N
| aT.html [2]
| https://pandas.pydata.org/docs/user_guide/groupby.html
| barryrandall wrote:
| I only ever need to know KQL when something isn't working well,
| which turns 1 problem into 2: the original problem, and how to
| express exactly what I need in a language that, when things are
| going well, I forget quickly.
| mrtimo wrote:
| > SQL is great at expressing simple needs very well, but in more
| advanced cases, it can get "mind-twistingly complicated" even for
| a mathematically included brain.
|
| I have found Malloy[1] to be easier to read and write, even for
| really complex queries. Malloy complies to SQL. As an example
| check out [2].
|
| Malloy can query .csv files directly (enabled by duckdb). You can
| also join .csv files. This makes getting started a breeze for
| beginners, and is a boon for data scientists. Much easier than
| Pandas in many cases.
|
| With the visualization capabilities it has built in, Malloy sort-
| of competes with Tableau and PowerBI.
|
| Most people only write SQL to get data out of databases, for
| these people, Malloy is an interesting tool to consider.
|
| [1] https://www.malloydata.dev/ [2]
| https://docs.malloydata.dev/blog/2023-10-26-malloy-bump-char...
|
| Not affiliated with Malloy, I've just played with it for a while
| and been impressed. It's also MIT licensed.
| mikpanko wrote:
| Malloy is great. Why do you think it is not taking off if it is
| a clear significant improvement on SQL and even compiles to it?
| bdcravens wrote:
| Limited support
|
| https://docs.malloydata.dev/documentation/
|
| "Malloy currently works with SQL databases BigQuery,
| Postgres, and querying Parquet and CSV via DuckDB."
| myaccountonhn wrote:
| There's also recutils for those that have simpler data storage
| needs.
| DaiPlusPlus wrote:
| I want to know why there hasn't been much innovation in the less-
| sexy parts of ISO SQL. My go-to for this is to point-out how
| SQL-92 defined the same small set of constraints that we have
| today (PK, FK, UNIQUE, CHECK, and that's pretty-much it).
|
| It's been 30+ years and the expressiveness of SQL DDL constraints
| for data-modelling is completely unchanged. So far, only Postgres
| has extended it with EXCLUDE constraints; what I'd love to have
| is a "Non-unique foreign key" constraint and its inverse: a "NOT
| EXISTS" constraint - these alone would go far.
|
| Another huge need is for safe-and-sound data-structure invariants
| - it's almost impossible to correctly store a linked-list on a
| table without running until concurrency issues.
| SpicyLemonZest wrote:
| Active development in data systems has focused heavily on
| larger datasets where complex global state isn't practical to
| enforce on write. Some systems don't even enforce their PK or
| FK constraints.
| codeulike wrote:
| Yeah thats it, I've never seen contraints used that much in
| the real world. Unique keys but thats about it. Clever
| database-level contraints usually come back and bite you in
| the arse when you're trying to do some large scale change or
| upgrade or fix.
|
| And if you're building an app you usually want the validation
| of input to be done as close to the user as possible (like in
| the UI), not relying on a round trip to the database and
| handling an exception. And some might say 'well have
| validation in both places' but then you've got a load of
| complex stuff to keep in sync which will be a pain in the
| arse if it gets out of sync (if your db rules differ slightly
| from your ui rules)
| tanelpoder wrote:
| FWIW, Oracle now has data domains that allow more complex
| constraints, including JSON schema based ones:
|
| https://docs.oracle.com/en/database/oracle/oracle-database/2...
| DaiPlusPlus wrote:
| Domain-constraints are just reusable CHECK constraints on
| steroids :3
| jandrewrogers wrote:
| Many constraints are extremely expensive to enforce at scale to
| the point of being prohibitive. You are essentially turning
| your relational database into a graph database under the hood,
| with the infamously poor scalability and performance implied. A
| legitimate argument for the obsolescence of SQL DDL
| (independent of the DML) is that it defines some features that
| inherently scale too poorly to be used in many modern databases
| and it assumes certain implementation details that aren't
| actually true in some modern database architectures.
| sethammons wrote:
| are you saying that graph databases suffer scalability issues
| or are you saying relational databases used as graph
| databases suffer scalability issues?
|
| I've done some high scale stuff off mysql with 10s of
| billions of operations a day across sharded clusters with
| some nodes holding terabytes of data -- fks, triggers,
| cascading deletes and such tended to be too expensive. I've
| not had the opportunity to use something like neo4j, which
| supposedly has a great horizontal scaling story.
|
| edit: not that my downvoters will see, but, seriously? I'm
| asking a question to understand if I'm following. What are
| you downvoting? And yes, I've heard you are not supposed to
| question downvotes; I'm honestly perplexed.
| PeterCorless wrote:
| The best scalability I knew about was JanusGraph running on
| ScyllaDB. You can also run JanusGraph on Cassandra, which
| is horizontally scalable. ScyllaDB will be more performant
| and vertically scalable.
|
| * Docs: https://docs.janusgraph.org/storage-
| backend/scylladb/
|
| * Detailed use case:
| https://www.scylladb.com/2019/05/14/powering-a-graph-data-
| sy...
|
| * Video: https://www.youtube.com/watch?v=7WZyVUTwYJ4
|
| Note this was a few years ago. I haven't seen a TigerGraph
| vs. JanusGraph head-to-head. The TigerGraph people are
| pretty sharp. If anyone has deeper knowledge would love to
| see comparative benchmarks.
|
| Both JanusGraph and TigerGraph are rated about the same in
| terms of popularity on DB-engines.com (between 100-150
| rankings). Neo4j is still far more popular/well-known, at
| rank #21 this past month. But there are other options you
| can explore.
| refset wrote:
| > Many constraints are extremely expensive to enforce at
| scale to the point of being prohibitive
|
| Incremental View Maintenance engines might be the solution
| we've been waiting for here.
| DaiPlusPlus wrote:
| We use indexed-views in MSSQL for this and they're great
| for many kinds of things - but they also kinda become
| almost like TRIGGERs and might ruin your day if the
| execution-plan-generation gods feel like it.
|
| ...and the lack of a LEFT OUTER JOIN also kills it for a
| lot of things too.
|
| One problem is that a Txn won't commit until all secondary
| indexes are updated, but I'd prefer it if there was a way
| for a Txn to unblock its caller once the base-table update
| is saved; updates to secondary indexes could then continue
| asynchronously. (I know this is hardly an original idea,
| but I haven't read why this can't be done)
| DaiPlusPlus wrote:
| Well, yes - that's ISO SQL "Assertions" right there.
|
| I appreciate that eschewing RDBMS-provided data-integrity
| features makes sense when it makes sense to - especially when
| it's your application code, and yours alone, that guards
| access to the underlying store.
|
| But such-as-it-is, my current industry (think: incredibly
| unappealing on-prem CRMs) is all about treating the RDBMS as
| canon; reason 1 is because there's a dozen companies all
| offering value-add services and systems that all integrate
| into each other solely by having an on-prem agent daemon that
| just logs-in to the first Oracle box it sees on the LAN and
| execs its raw SQL, not just SELECT, but plenty of UPDATE and
| ALTER - all without the decency of a TRANSACTION.
|
| So having even only the most rudimentary of FK and CHECK
| constraints is what keeps hundreds of thousands of small-to-
| medium-sized business from imploding from a hosed production
| DB. They pay for themselves.
|
| ...but ifs not enough. There's still plenty of data-anomalies
| even in the most well-designed aspirational 6th Normal Form
| DB you could make today simply because the RDBMSs are
| awkwardly rigid and inflexible in that regard. I wouldn't be
| complaining about the lack of (cheap, high-perf!) EXISTS/NOT-
| EXISTS constraints in MSSQL/Oracle/MariaDb if those RDBMS
| were feasibly extendible in that regard. Postgres is the
| exception here, but unfortunately for me absolutely zero of
| the systems I work with use it; instead they're more likely
| to run on Progress AS/400 or some proprietary xBase
| derivative.
| rqtwteye wrote:
| I think SQL is pretty good but I would like to see a tighter
| integration with the application code and the database. Like
| managing the database source in the repository, integrate the
| database into tests easier, maybe express queries in the
| application language instead of SQL. You can do all of these to
| to some degree with things like ORM and LINQ but the database
| still feels very separate from the rest of the project.
|
| I think SQL as a language could also be improved a lot to make it
| easier to understand. It looks and feels like FORTRAN 77.
| kccqzy wrote:
| You are really discussing an improvement in programming
| languages rather than in databases. Innovation in programming
| languages themselves tend to be in languages like Haskell which
| give you much more abstraction power. You should take a look at
| Haskell beam https://haskell-
| beam.github.io/beam/tutorials/tutorial1/ or Esqueleto
| https://github.com/bitemyapp/esqueleto and you will find that
| these libraries achieve much tighter integration between the
| database and the rest of the language. I'm especially fond of
| Beam: the way to define data types and tables, the way to write
| queries just feel incredibly intuitive to me.
|
| > managing the database source in the repository, integrate the
| database into tests easier
|
| These are much easier problems to solve. Many people have
| already solved it.
| maweki wrote:
| As I see it, we haven't really made any progress in terms of
| impedance mismatch.
|
| Otherwise, jooq is a fine addition to the Java world, as it
| generates a type-safe DSL from your ddl file where every
| statically correct jooq/Java statement is also a statically
| correct SQL query.
| dventimihasura wrote:
| I see it differently. In the 25 years I've been working in
| this industry I see a welcome trend toward doing more in the
| database, such that the impedance mismatch dissipates. This
| is from a low-water mark 20 years ago or so, when most Java
| developers I knew kept the database at arm's length,
| insisting it met them on their terms rather than the other
| way around.
|
| https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f.
| ..
|
| https://supabase.com/
|
| One way to eliminate the Java-SQL impedance (for example)
| mismatch is to delete Java altogether, along with JOOQ,
| Hibernate, and Spring (for example).
| rqtwteye wrote:
| I think more code in the database could work but then the
| database should have better programming languages. The
| current SQL dialects are terrible for writing regular code.
| dventimihasura wrote:
| That's a matter of taste. I find most of the business
| logic I need to express involves finding and transforming
| relational data, a task to which SQL is better suited
| than any general purpose programming language. Often, SQL
| is all that's needed. Less often, I have to resort to
| PLpgSQL, PL/SQL, or T-SQL. When that happens, it's not
| that bad.
| dventimihasura wrote:
| One way to have tighter integration between the database and
| the application _logic_ is to abandon the application _code_
| altogether. Express the application logic right in the database
| using (custom) data types, domains, defaults, constraints,
| views, triggers, and procedures. Then you can delete much or
| all of the "application tier", delete the ORM, and simplify
| the entire stack. That's one way to avoid having "the database
| [feel] very separate from the rest of the project."
| panstromek wrote:
| This make sense to me logically, but whenever I try to
| actually do it, it becomes a disaster.
| dventimihasura wrote:
| Fair. I've only ever had success with this strategy as a
| solo hacker. It was once claimed to me that this would be a
| disaster on large teams and I had to admit that I had no
| evidence one way or the other. Don't know if that resonates
| with your experience. Just anecdotal.
| sethammons wrote:
| application logic needs to live with application code in
| source control. I've not seen a solution that leverages the
| database the way you suggest and achieves that. It leads to
| people yeeting things into production and hoping they work
| because half the logic is not in front of them
| dventimihasura wrote:
| The customers I work with tend to use database migration
| tools like Flyway or Liquibase, have their DDL in source
| control, run tests, and have fairly conventional CI/CD. As
| I said above, I've never personally worked on a large team
| doing this, but our customers seem to make it work.
| TeaBrain wrote:
| This idea is largely regarded as making the codebase less
| maintainable since it can be more difficult to test discrete
| logical units of the SQL code than doing so for the
| application code.
| dventimihasura wrote:
| I'm aware of that belief, but I do not share it.
| mulmen wrote:
| [delayed]
| wvenable wrote:
| Fundamentally the database is separate from the rest of the
| project. It's quite often a completely separate service hosted
| on a completely separate machine.
|
| Do you feel the same about HTML, CSS, JavaScript, REST APIs,
| etc?
| rqtwteye wrote:
| HTML, CSS, JavaScript, REST APIs are usually stored in a
| repository like the application code. Much easier to manage.
| I understand that the database is usually hosted separately
| but in most cases they are tightly coupled so should be
| managed together.
| wvenable wrote:
| I store my database structure in the repository either as
| scripts or as migration code (and sometimes both)
|
| I'm not sure what else you need. This seems like a basic
| tooling problem.
| quercusa wrote:
| _Stonebraker said Oracle had a head start and was achieving
| strong growth using "sales tactics I would not condone."_
|
| Well, I never...
| dventimi wrote:
| It helps to have friends in high places.
|
| https://news.ycombinator.com/item?id=32596903
| pphysch wrote:
| SQL-the-standard is one of the great mistakes of computing, a bad
| abstraction of Titanic proportions. The standard should have been
| abandoned long ago in favor of the actually-existing reference
| implementations like PostgreSQL.
|
| "Standard SQL is good and portable and worth spending enormous
| resources supporting" -- statements dreamed up by the utterly
| deranged
| dventimi wrote:
| I regard this as hyperbolic.
___________________________________________________________________
(page generated 2024-06-04 23:01 UTC)