5 ms·
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
by 013a 6y ago
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 consistent and beautiful querying languages than SQL: I would point to MongoDB's query language, which is less of a query language and more of a static javascript-interpretable library, but is still far easier to learn and more consistent than SQL. The same query in MongoDB: "db.users.find({ foo: "bar" });". How is this better? It embeds the operation in the statement ("find"); reading it hierarchically follows how the data is stored (Collection > Rows); the filtering operation is the same shape as the data being stored; and it naturally disallows most injection attacks.
- jolux 6y agoThat 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.
- throwaway894345 6y agoAnd those ORMs have to deal with the SQL composability issues as well, often to the effect of dramatically poorer performance.
- romanoderoma 6y agoI think Elixir ECTO does a very good job at that https://hexdocs.pm/ecto/Ecto.Query.html#module-composition https://hexdocs.pm/ecto/Ecto.Query.html#module-composition
- jolux 6y agoYou’re right, using a query layer to access an SQL database is still using an SQL database. You need to know what you’re doing with your queries. The important part is not the object mapping and model tracking part, it’s the part that allows you to build typed queries in the native language. Diesel for Rust is a great example, as is Ecto in Elixir.
- 013a 6y agoThat'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.
- jolux 6y agoThe difference as minimal. If you know the first thing about what you're doing with SQL you will use prepared statements. It's not some sort of arcane feature that nobody understands.
- guiriduro 6y agoI always thought a SQL query has a very sensible layout, I was never confused as to which I was doing or in what order. For me a query can be simplified to: select <projection> <selection>
- lostjohnny 6y ago> 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 (SELECT warehouse, instock FROM warehouses WHERE stock_item= orders.item AND instock >= orders.ordered );
- petepete 6y agoAnother 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.
- 013a 6y agoThe 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.
- njharman 6y ago> 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 do I do other operations. .Only if you are familiar with programing language that has that same syntax does any of it make sense. Otoh even educated non-programmers are gonna be able to read the SQL as SELECT "these things" FROM "this table" WHERE "these conditions are true".
- doubletgl 6y ago> Only if you are familiar with programing language that has that same syntax does any of it make sense. I'd argue that most relational DB users are familiar with a programming language, and therefore most likely familiar with the C-style syntax. It's better to build on something that most of the potentials users are familiar with already. > There is no distinction between noun "users" and verb "find". There is no such distinction in natural language either (if you see words purely as sequences of characters), you have to know what is what and infer it from the context. > even educated non-programmers are gonna be able to read the SQL as SELECT "these things" FROM "this table" WHERE "these conditions are true". Yeah SQL looks a bit more like natural language at first glance, but that's about it. That familiarity is a false friend, it doesn't really help with the learning curve. This kind of thinking reminds me of the ruby community trend a decade ago when DSLs were created to look beautiful and like written language. It's useless and confusing for long-term, practical purposes. Same with BDD style testing languages. The promise that non-technical people will feel right at home and can start contributing rarely lives up to reality.
- 013a 6y agoWhy would you want your query language to read like English? Most people don't speak English.
- IHasFingers 6y agoI don't think SQL would be better if it read like Mandarin.