Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We Can Do Better Than SQL

edgedb.com

Post reply on HN
Print view View original

401–410 of 466 posts

Re: We Can Do Better Than SQL

#401

Post by oarabbus_ »

tqi wrote: post #84

It's pretty arrogant to complain about the syntax being inconsistent across versions and databases and then present your own weird offshoot, as if every other version wasn't introduced for the exact same reason with the exact same lofty delusions of grandeur... SQL is messy because describing the underlying data relationships are messy. The orthogonality example is a great illustration of this. What exactly should th…

>In that case, the result set is no longer a table, it's a dataframe, which is a useful data structure but is also not what relational databases do.

What do you mean here? The difference between a table and a dataframe is that a dataframe is a construct held in memory, while a table is persisted storage written to a database.

Re: We Can Do Better Than SQL

#402

Post by oarabbus_ »

meritt wrote: post #366
jimsparkman wrote: post #315

Earlier quoted context omitted.

WITH (CTEs) make queries so much more readable and digestible. As a programmer who now does data and SQL, I latched on to these as soon as I found I could reduce repetition in a query with them.

Make sure you understand what optimization fences are and how they affect your performance. CTEs are nice to read but routinely destroy the performance. [1] https://thoughtbot.com/blog/advanced-postgres-performance-ti...

>CTEs are nice to read but routinely destroy the performance.

This may be true on archaic versions of MySQL and Postgres but is not the case today, barring some esoteric edge cases (bugs) where the optimizer gets thrown out of whack. Once while doing data science consulting I rewrote a ~1000 line query in Aurora (MySQL flavored) which had a ~2.5s runtime, which was far too slow for the client's use-case.

After rewriting all the CTEs (there were many) into subqueries, there was a 2-3% increase in query speed, barely (on the order of under a tenth of a second). There was a very tiny improvement far smaller than the normal variance of the runtime.

Then I rebuilt the query and the joins, and was able to get the query to consistently run in the range of 0.8 - 1.2s. For my own purposes I then duplicated the query, re-implemented the CTEs, and did validate that indeed there is only a negligible increase in query time when using CTE.

Re: We Can Do Better Than SQL

#403

Post by 013a »

Beefin wrote: post #380
lostjohnny wrote: post #353

Earlier quoted context omitted.

> I would point to MongoDB's query language seriuosly? db.orders.aggregate([ { $lookup: { from: "warehouses", let: { order_item: "$item", order_qty: "$ordered" }, pipeline: [ { $match: { $expr: { $and: [ { $eq: [ "$stock_item", "$$order_item" ] }, { $gte: [ "$instock", "$$order_qty" ] } ] } } }, { $project: { stock_item: 0, _id: 0 } } ], as: "stockdata" } } ]) VS SELECT *, stockdata FROM orders WHERE stockdata IN (SE…

you're comparing $lookup, an operator that nosql isn't designed for to a join, an operator sql was designed for

Lookups are very common in MongoDB; Starting with SQL, lifting the data as-is into Mongo, and translating the queries 1:1 will just result in garbage queries, like that one.

An "idealized" NoSQL schema is far, far more complex than anything anyone used to SQL would arrive at ([1]), but most of that is because in a "pure" NoSQL/Document-oriented database, the query engine simply isn't that powerful (think Dynamo). MongoDB has an inordinately powerful array of tools to get at data in a performant way, and $lookup is available as one of those tools. Can it be misused? Yeah; just look at the parent comment to see clear misuse. But generally, it's very common to see.

Modern thinking around MongoDB schema design is closer to SQL than NoSQL/Dynamo. Arrays are bad, denormalization can be valuable but use sparingly, that kind of stuff.

[1] https://docs.aws.amazon.com/amazondynamodb/latest/developerg...

Re: We Can Do Better Than SQL

#404

Post by 013a »

njharman wrote: post #392
013a wrote: post #336

Earlier quoted context omitted.

A very simple, basic SQL query would be something like "select * from users where foo=bar;" Already, we're introducing a weird inversion of syntax that, in my experience, trips up people learning it: data in SQL is stored as "rows" with "columns" inside "tables". More formally, we've got a hierarchical relationship where Tables > Rows > Columns, yet we write the query as Columns > Table > Rows. There are far more con…

> db.users.find({ foo: "bar" }) I don't know MongoDB query language, but gah! that looks horrible. It uses three different syntaxes; dot notation, curlies/brackets and colon key value. Full of punctuation and doesn't read like english. There is no distinction between noun "users" and verb "find". There's extraneous "db". does foo: "bar" mean equal or is it find() that determines the operator, maybe combo of both? how…

Why would you want your query language to read like English? Most people don't speak English.

Re: We Can Do Better Than SQL

#405

Post by Areading314 »

m000 wrote: post #221
Areading314 wrote: post #141

Earlier quoted context omitted.

Right, it's like criticizing python, or English, for being inconsistent, or "large". Turns out that doesn't matter -- what matters is that the language is useful because it has a wide base of users and libraries, just like SQL does.

Python and English are meant to be general purpose languages, so they are kind of expected to be large and occasionally inconsistent. SQL is (by definition) a domain-specific language which has grown out of proportions. Are there really any SQL libraries in the traditional sense (i.e. reusable/composable SQL code with a well specified API)? SQL "libraries" typically focus on hiding the inconsistencies and the abhorre…

Perhaps libraries doesn't really apply to SQL per se, but instead you have tooling, ecosystem, DB engines, ORMs, extensions etc, that all speak SQL and would be hard to do without.

Re: We Can Do Better Than SQL

#406

Post by 013a »

petepete wrote: post #359
lostjohnny wrote: post #353

Earlier quoted context omitted.

> I would point to MongoDB's query language seriuosly? db.orders.aggregate([ { $lookup: { from: "warehouses", let: { order_item: "$item", order_qty: "$ordered" }, pipeline: [ { $match: { $expr: { $and: [ { $eq: [ "$stock_item", "$$order_item" ] }, { $gte: [ "$instock", "$$order_qty" ] } ] } } }, { $project: { stock_item: 0, _id: 0 } } ], as: "stockdata" } } ]) VS SELECT *, stockdata FROM orders WHERE stockdata IN (SE…

Another huge win for SQL is that it's easy to construct from parts. You can very easily run and debug your subquery or common table expression on its own before combining it into a larger, more-complex query. If you (as I usually do) create plenty of views while analysing a dataset, the approach can be extremely powerful. Doing the same in JavaScript is possible, but it's slow and cumbersome by comparison.

The original article addresses the deficiency in construction from parts, in its "Lack of Orthogonality" section.

MongoDB queries, while being interpretable by javascript, aren't really javascript. You can't interact with the data using javascript (well, you can, using eval, but you shouldn't). You interact with the data via the query language, which is, again, expressed in JS, just like SQL is expressed in English.

It's more accurate to consider the Aggregation Pipeline as being the "composable" system to get at data in MongoDB. And its exceedingly composable; far more than SQL. It's literally a pipeline; a series of steps which fetch, mutate, filter, map, limit, calculate, correlate, relate, and otherwise interact with the data in a database. Each step operates on the output of the previous step, in series. You can programmatically swap steps in-and-out, in production, with no string manipulation or ORM, debug each step in series, remove steps, see the output, get performance characteristics on each step. There's no complex black-boxed query execution planner or compiler, because the query plan is the pipeline.

Re: We Can Do Better Than SQL

#407

Post by 013a »

jolux wrote: post #339
013a wrote: post #336

Earlier quoted context omitted.

A very simple, basic SQL query would be something like "select * from users where foo=bar;" Already, we're introducing a weird inversion of syntax that, in my experience, trips up people learning it: data in SQL is stored as "rows" with "columns" inside "tables". More formally, we've got a hierarchical relationship where Tables > Rows > Columns, yet we write the query as Columns > Table > Rows. There are far more con…

That doesn’t prevent injection, and the solution to injection attacks is using parameterized queries and prepared statements, not switching to MongoDB. Plus ORMs (really query builders) already provide behavior like this against SQL databases anyway.

That's fair, but I didn't say it prevents injection: I said it prevents most injection attacks.

MongoDB is absolutely still capable of being vulnerable to injection; its just harder, because it requires the client to provide an object which is parsed by your application with no data validation. In other words, SQL is vulnerable to injection by-default, because everything is a string, while you have to opt-in to being vulnerable with MongoDB, by writing your application to parse user input with no schema.

In reality, do applications do this? Hell yeah. Wire up a basic Express API, have it auto-parse any JSON its given, pass it straight to mongo, you'll be vulnerable. But, a backend which has any kind of type safety or API schema or GraphQL or something like that will be safer on mongodb than one with all that, on a SQL database with no ORM or parameterized queries or prepared statements.

Re: We Can Do Better Than SQL

#408

Post by goto11 »

marcinzm wrote: post #311
goto11 wrote: post #291

Earlier quoted context omitted.

> SQL is messy because describing the underlying data relationships are messy. No, the relational model is beautiful and consistent! SQL is messy because the syntax is not consistent and elegantly composable. It could have those properties and still present the same underlying data relationships. See Linq in C# as an example for how a more composable query syntax can expose the same data model. For example in Linq yo…

>For example in Linq you can chain arbitrary many select/join/where/group by in arbitrary order. In SQL you need nested subqueries to achieve the same which is a much more convoluted syntax. WITH statements alleviate some of this issue by allowing you to write subqueries in any order.

WITH's are great and definitely solves some of the issues with SQL. Some implementations unfortunately don't optimize them as subqueries, but that is not the fault of SQL.

Re: We Can Do Better Than SQL

#409

Post by stillbourne »

I feel like he is using the work Orthogonal incorrectly, I once had to describe a complex relationship where there was a hierarchy of objects however instead of being a strictly up down relationship there was an up, down and right relationship. From the little chemistry I've learned I was familiar with phenol and its bonds or para, ortho, and meta. I defined the up object as para, the right object as ortho and the descending object as meta.

Re: We Can Do Better Than SQL

#410

Post by michaelcampbell »

bjo590 wrote: post #93

Am I the only full stack dev that likes SQL? SQL is an incredibly expressive and flexible way to read, store, and update data. It's ubiquitous, so the SQL skills I learned six jobs and three industries ago are still relevant and useful to me today. Relational Databases and SQL are heavy lifters that I often relay upon to build projects and get things done.

I use it a lot, because I know it fairly well. Not sure I "like" it; it's like a lot of food - I know what of the food varieties I prefer over others, even when I didn't immediately "like" any of them. More of a "got used to" type of thing.
Post reply on HN