Hacker News Reader

I call it "SQL fear", and I agree: people need to get over it. We used EFCore at my previous job, and I don't know how much time figuring out how poorly performing queries are happening. The abstraction leaks like a sieve and isn't worth it.

Now, if we could replace SQL with a purpose-built language, that I would be interested in. I have to concede that SQL doesn't have great ergonomics (e.g. if FROM was first then auto-complete would be better, trailing commas would eliminate much formatting bike shedding, why are INSERT and UPDATE so different).

I see your point for one part, which is that `select([id]).from(Users)` is maybe in the best case as readable as `select id from users`, and in many common cases it'll be less readable than sql. Plus, it's not quite sql, you'll have to look up how to do things a lot of times.

But we aren't just talking about SQL the high level language that you might execute when you are poking the data live. In a web app dynamically constructing sql, we have multiple issues:

1. Deserialization: we need to know what the query returns and plug it into data structures we want to use in our program

2. SQL is dynamically typed, and if you construct queries dynamically you're even more dynamically up a creek. Maybe even "sql injection vulnerability" up a creek.

1 and 2 are solved by strongly typed query builders like this: the type system has a proof that the ways you will construct queries in your program actually result in valid sql queries and that they return the right data shape.

It's actually solving a real problem

(1) is premised on (2), which is not right. Some SQL RDBMSes are statically typed (e.g., PostgreSQL) while others are dynamically typed (e.g., SQLite3).

If you're building on SQLite3 then you're going to have to do run-time type checks anyways (even if you somehow disallow access to the DB that bypasses your application). So maybe don't build on SQLite3.

This is a shame. We really do need a SQLite3-like DB that is statically typed.

sqlite has `STRICT` tables that are statically typed

Every time I've found myself frustrated with a SQL query, there was some horrible misstep in how the underlying schema was designed.

Having the wisdom to craft a high quality schema can be the biggest unlock in the whole enterprise. If the tables, columns & relations are ~1:1 with the actual business (i.e., you went and talked to real, live stakeholders at design time), then it should be deeply intuitive for a non-technical domain expert to interact with.

Often, you don't get a chance to redo the whole thing, but that doesn't mean you can't still design an ideal schema and then shim it with views, CTEs, replicas, ETL, etc.

SQL is a domain specific language. It would be like complaining about how horrible it is to write Lua scripts. The criticism is baseless without understanding the underlying schema/bindings.

I hear you and I 70% agree.

I’ve also been using Prisma for a project recently and I’ve been finding it quite good. It layers several useful features on top of sql, like schema management (checked in to git), code gen (incl typescript types), db connection management, serialization / deserialization from JavaScript objects to sql rows and back, and it lets you follow foreign keys. If you want, you can also easily just make raw sql queries. That works fine too.

Sql is a very old programming language, and it’s showing its age. The design is wacky - does GROUP BY go before or after ORDER BY in the query? Every command has custom, seemingly random words which are actually parameters. They are placed at totally arbitrary, hard to remember spots in the command. And what, we make our program output a string that gets immediately parsed by the sql server? What a horrible, stupid way to do RPC. It also gives terrible error messages if you get it wrong. And it’s awful to learn and debug.

All of that is a huge pity, because modern databases can do so, so much. Doing logic inside the database can often be an order of magnitude faster than doing it in client code. SQL is a fantastic way to represent your data.

One path for fixing this would be to move the sql query parser inside the client library. Then internally, have the client library send optimized RPC instructions to the database. This would take cpu load off the database (usually a good move - databases are harder to scale). And it would open the door for the client library to provide other better, lighter and faster ways to programmatically construct database queries. Ideally without the totally unnecessary loop of constructing then parsing sql.

>One path for fixing this would be to move the sql query parser inside the client library. Then internally, have the client library send optimized RPC instructions to the database. This would take cpu load off the database (usually a good move - databases are harder to scale). And it would open the door for the client library to provide other better, lighter and faster ways to programmatically construct database queries. Ideally without the totally unnecessary loop of constructing then parsing sql.

The problem here is these low level optimizations that the database is doing is freaking complicated. There's a lot of intelligence going on in the background and it's different depending on the database. We can solve it with a low level common IR but that IR will be extremely complex. And that IR will likely give different performance profiles for the same code on different databases.

I can see this being solved for one database. But not across all databases and a common api. We sort of do have a common api and we paid for it with the trade off of it being a easier high level language on top of the part of the web that needs to be most optimized.

> can solve it with a low level common IR

Wouldn't that basically just be a query plan? The language is the easy part. Determining the optimal query plan based on rough statistics about the data and the DBs ability to go about that is the hard part.

There are more general purpose query optimization libs out there, like Calcite

> I can see this being solved for one database. But not across all databases and a common api.

Database client libraries are already database-specific. Why boil the ocean? We could start with one database with this very easily until we figured out a good IR for queries.

It could even be database-version specific if you wanted, through the use of wasm. Make the database client library embed a wasm VM. Then on startup, the database sends any connected client application the latest query optimizer / whatever logic in a compiled wasm bundle. This would allow the database to make improvements to the IR with successive versions without needing to update all the client libraries out there.

> One path for fixing this would be to move the sql query parser inside the client library. Then internally, have the client library send optimized RPC instructions to the database. This would take cpu load off the database (usually a good move - databases are harder to scale). And it would open the door for the client library to provide other better, lighter and faster ways to programmatically construct database queries. Ideally without the totally unnecessary loop of constructing then parsing sql.

Erm, that’s already true. Proper clients will be parsing SQL locally and sending representations of the query, and its parameters separately. They may often be using a wire-format that’s very close to textual SQL, but parsing that SQL is hardly the slow part of executing a query.

DB CPU load doesn’t come from parsing SQL, it comes from the query planning process, and the actual work of reading and filtering data. The planning process alone is extremely complex as the DB will be using all manner of statistical data collected about the type of data stored, and exactly how it’s stored, and how it various columns do or don’t correlate with each other, in order to estimate the best possible way of performing constructing the query plan. A process that factors in additional elements like the relative speeds of reading from disk, vs memory, and CPU cache, and how that varies with working set size.

In addition, the real CPU load comes from the actual execution of that query. Streaming data off disks into memory, building various temporary in-memory data structures to accelerate joins and filters, there’s a lot of bytes that need to be shuffled around, and modern DB codebase are now optimising for the number of CPU cycles needed to operate tight inner loops. None of this work can be moved to the client, not unless you have a mechanism of streaming GB/s of data to your client on every query.

To think of SQL as just an RPC protocol completely misses the point of SQL, or the incredible engineering involved in modern query planners. There a many reasons to replace SQL if something better, but the idea that it’s an inefficient RPC protocol, and that clients should have more fine-grained control of the planning and execution of queries, really isn’t one of them.

> To think of SQL as just an RPC protocol completely misses the point of SQL, or the incredible engineering involved in modern query planners. There a many reasons to replace SQL if something better, but the idea that it’s an inefficient RPC protocol, and that clients should have more fine-grained control of the planning and execution of queries, really isn’t one of them.

But SQL is used as an RPC protocol & wire format for getting data in and out of the database. Basically all web apps built on top of a sql database craft SQL strings for SELECT and UPDATE commands. The queries are very repetitive - since they're crafted once and run with each web request. I'm sure there's a lot of unnecessary work related to doing that over and over again constantly - and if that work isn't parsing strings, its generating the query plan. The format is also really awkward to use. SQL doesn't play very nice with most modern languages - since you generally either write the SQL string by hand, and then the types are opaque to the language. Or you get an ORM to do it - and then you can't easily customize the SQL string itself.

Its complicated, but one approach to solving this would be to bundle up all that query planning code into a wasm blob and send it to database clients when they connect. The blob would contain any complex statistical code that the database uses. This would allow applications to create and save a query plan from a query string, and reuse it with subsequent queries. The wasm bundle would also contain all the binary serialization & deserialization code. Databases could then continue to evolve their query planners as they do today - but performance would be much better. And it would open the door to richer, lower level APIs for applications to customize & understand the query plans. And, I'm sure, figure out what types will be expected and returned from any given query.

> This would allow applications to create and save a query plan from a query string, and reuse it with subsequent queries.

You can already do this. They’re called prepared queries. The application sends the query once, and asks the DB to create a prepared query, and every time the application wants to reuse it, it just needs to pass the query parameters. The DB reuses the already existing prepared query, and often the query plan is reused as well. But that depends on the exact DB implementation.

You also can’t just reuse the same query plan over and over again. The whole point of the query planning process is to optimise the query plan to current state of the data on disk. As that data is appended to and updated, the optimal query plan may change.

It’s also possible for applications to know exactly what types a query is going to return. That information is already returned by the database as part of the query response, it has to be for the client to correctly deserialise the returned data. Most DB uses a binary protocol for most of their clients (but also support a pure text based protocol), and deserialisation the binary response requires information about the response types.

I think you’re drastically underestimating just how complex and magical the inside of a real SQL database is. They represent literally decades of engineering and research into the best ways of storing and querying databases. If anything close to what you’re suggesting was a good idea, someone would have tried it.

> I think you’re drastically underestimating just how complex and magical the inside of a real SQL database is. They represent literally decades of engineering and research into the best ways of storing and querying databases. If anything close to what you’re suggesting was a good idea, someone would have tried it.

I was with you up until the last sentence. I almost certainly am underestimating how magic the internals of databases are, and how much work has gone into them. But there is a mountain of good ideas in CS that simply haven't been tried because of convenience and momentum.

For example, SeL4's microkernel architecture is brilliant - but it'll probably never make it into Linux because of simple inertia. (Its too hard to rearchitect linux like that, and nobody really wants to port all their working software to L4.)

One reason that this approach to database design probably hasn't been tried before is that there's dozens of database client driver implementations. Moving any logic into the DB client would require all of that logic to be reimplemented a lot of times, and thats a lot of inconvenient work. But webassembly changes that calculus - since the database itself can hand a wasm blob to the client to run. Thats a very new capacity that was really hard to pull off a few years ago. (Foundationdb does something similar - the client library is a native library that needs bindings on every language / platform to work - but its incredibly inconvenient to use as a result. And a native apple silicon version of the library took years to come out, even though its maintained by Apple.)

> One reason that this approach to database design probably hasn't been tried before is that there's dozens of database client driver implementations.

Or it could be that the added complexity simply doesn’t provide any benefits. What benefits would splitting up the query planner between the DB and its clients provide? Computing a query plan isn’t the expensive part of executing a query.

You’re suggesting that it’s a good idea for databases to expose their core internal APIs, which are tightly coupled to their underlying storage subsystems, to some kind of magic query planner that runs in the client. Basically ensuring that you can’t ever modify those APIs without breaking every existing client out there. Effectively you would force every DB to require their clients to be updated in perfect lock-step with the DB itself. All to achieve what? A saving of a few milliseconds on un-prepared queries?

Simply put, there are much more sensible ways to reducing the cost of query parsing and planning. As it happens every DB worth talking about already implements those optimisation in the form of prepared queries. What benefit would moving where the query planner is executed provide?

I would also highlight that if you go an look at DBs built from the ground up for massive scale and parallelisation, they still run their query planners on a single node to figure out how best to break up a query for distributed execution. Nobody is attempting to distribute the actual query planning part of a DB.

> The ideal API would be one that is both high level but allows fine grained control... but we don't have one yet.

What kind of fine grained control are you talking about here? I never felt like I was missing anything when doing even complex SQL (unless I am forced to use a crappy MySQL, which problems are not inherent to SQL itself).

Quite the contrary actually: despite having this amazing high level of abstaction, most developers are ignorant or don't care to properly index the data, add integrity constraints, manage the locks or think about transactions.

In 99% of the non-SQL code that I see, developers don't even go further than nested loops containing IOs, so I don't think that letting them decide which tree/trie is appropriate for a user case (for example) would help in any way.

> It's basically a declarative statement that's almost english-like and it's specifically DESIGNED to be more readable and easier to use by humans

It has categorically failed at this task. That is what we have an entire ecosystem of tools to allow people to query and interact with databases without using SQL.

Developers need to understand that SQL is an abstraction for developers, analysts, data engineers etc not end users.

And only basic SQL statements are English like.

I think the sweet spot is a query builder that just ensures you emit syntactically valid SQL to send to the database. So there are no surprises about what the database is executing. But protects against typos and security issues from building SQL through string concatenation.

sqlx (the rust library) does this, while also validating your sql statement, input types, and output types against your local db. For me it doesn’t get much better.

> >My opinion is that SQL should be for computers to write. This would put it firmly in the same category as LLVM IR.

> This is nuts.

> SQL is a high level language. [...]

It's... not completely nuts. It's the reason that LinkQ exists, and that's not bad. The main reason this approach is good is that too many programmers accidentally create SQL injection vulnerabilities. Another way to prevent SQL injection vulnerabilities would be to disallow all literal values in SQL queries (making them all parameterized, though there could be compile-time and run-time parameters).

But for the SQL injection issue I think this would be... not quite nuts, but IMO unwise, because now I'd have N relational query languages instead of 1 (SQL). Sure, SQL means N variations due to differences among N RDBMSes, but mostly much smaller than the variations one would expect in different SQL-free query libraries.

One thing is clear: this sort of scheme does not make it any easier to reason about what the query engine does under the covers since expressing a query as an AST does not preclude the query engine performing algebra on your queries to optimize (or pessimize) them.

> We don't want to do meta programming where we have another language living as a string in our web app.

Strings are fine in theory, but the trouble with SQL is that, while too high-level in all the wrong places, it is also too low-level in all the wrong places. Developers want to be able to do things like define the shape of the data beyond tables and compose queries, which SQL fails hard at. In order to accomplish what developers need, you either end up extending SQL into a new language, or you can use the programming constructs you already have, treating SQL as a compiler target, where necessary. The latter is considerably easier to implement.

> almost english-like

Real world SQL from the analytics, DE, BIE side of things is _extremely far_ from readable. It's a form of actual hell.

That's a good point.

It would be very nice to have a LLVM IR-style language for database queries, though. As a language, SQL is... well, a little behind times. ORMs explore lots of interesting design choices, but as you mention, by piling up high-level decisions on top of something that, by many aspects, are even higher decisions.

I wonder what an LLVM IR-like would look like in this space.

I agree there should be a common api. The sheer number of dialects with SQL contributes to a lot of issues with ORMs.

But SQL is definitely not something designed for computers to only write or compile another language into (which is what the OP is promoting).

Yeah.

I wonder if something like GlueSQL or the IndexedDB layer of most browsers could be used as such an API.

I tend to agree.

The main trouble with SQL is that the query parameters are mixed in with the query, which results in string escape problems. That dates from when people were expected to query a database by hand from SQL, which is sometimes useful.

For machine generated use, SQL queries should consist of a well defined fill-in-the-blanks system, with a text SQL query and a machine data structure. Something like this:

Query in SQL text as a constant string:

    "SELECT foo FROM BAR WHERE name=NAME1"

Parameters: key/value pairs in a safe native format for the language in use.

    {"NAME1" : "Smith"}

That gets you out of the curse of character escaping and SQL injection, the only real problem. It avoids trying to rewrite SQL in yet another ORM.

Microsoft has something which works this way, but it's unnecessarily clunky.[1]

[1] https://learn.microsoft.com/en-us/sql/relational-databases/n...

This suggests a simple API using a Rust macro:

    fill_in_sql!(query_string, query_params)

Where query_params is a structure containing the parameters:

    struct some query {
        name: &str,
        id: u32,
    }

The macro would parse the query string, find the variables to be filled in, match them to fields in the "params" structure, and generate the code to safely escape and fill in the params. Any matching failures are found at compile time.

This is similar to what the usual "derive" macros do - examine a data structure at compile time and generate appropriate code.

I've used systems like this in Scala. It's actually pretty magical to write something like:

    val foos = sql"SELECT * from foos WHERE status = 'frobbed'".as[List[Foo]]

... and have the macro system parse the query at compile time and verify that the query will indeed return a `List[Foo]`.

I think this is a nice middle ground, but constructing dynamic queries often can't be checked at compile-time.

(I'm probably getting the syntax somewhat wrong; I haven't done Scala in quite a few years at this point.)

I don't really think SQL injection bugs are the main driver. Certainly everyone should be using a query builder that protects them from this (and yes, I know, there are still people who screw this up), but I think the issues with SQL go far beyond that.

Personally I would much rather deal with a database table as a collection of objects with various fields in them. In a language with a reasonable collections library, I want to be operating on that data using functional combinators like filter, map, group_by, sort/order_by, etc. That feels much more natural to me than writing out stringly-typed SQL by hand. This is sorta an ORM, but not quite: it's somewhere in between writing raw queries, and using a full-fledged (usually inefficient) ORM.

The downside, of course, is that the performance of my queries is at the mercy of whatever the query generator underneath it is doing. But the nice thing is that if I need to optimize a query, I can always bypass the generator and write it by hand. But I wouldn't do that until I've measured things; no reason to prematurely optimize.

> For machine generated use, SQL queries should consist of a well defined fill-in-the-blanks system, with a text SQL query and a machine data structure. Something like this:

Erm, that’s exactly how modern DB and DB clients work. The client passes the SQL query with placeholders for parameters, and then sends the parameters separately. It’s also a core part of how prepared SQL queries work.

Every SQL client in any worthwhile language implements this pattern of passing a SQL query and its parameters as separate data structures, and they have done for decades. SQL injection only happens when people don’t use their SQL client properly, and attempt to compose SQL queries using naïve string templating. Effectively doing an end run around all the protections DB and clients provide by default.

> You can't get any higher level than SQL.

Can’t you? Wouldn’t higher level be a goal oriented natural language like “what’s the most popular music file in the database” and an AI agent just figured out how to give you the answer?

Well. You know what I mean.

AI is also unreliable. Place chatGPT over a database and it's going to start lying about what is in it eventually.

That being said my statement likely isn't strictly true even when you account for ML. I'm sure there are even higher level languages that are not probability based and thus deterministic with expected results. But in terms of a software developers average experience, SQL is the highest level you can get without getting into the minefield that is LLMs.

> SQL is the highest level you can get without getting into the minefield that is LLMs

You can use NLPs to safely write SQL in more business language.

And of course there are GUIs.

> SQL is the highest level you can get without getting into the minefield that is LLMs

That's trivially false.

I'm currently playing with sea-orm[0], and I can write things like:

    let db = Database::connect(...).await?;
    let yesterday: chrono::DateTime<chrono::Utc> = ...;
    let foos: Vec<foos::Model> = Foos::find()
        .filter(foos::Column::Status.eq(FooStatus::Frobbed))
        .filter(foos::Column::UpdatedAt.gt(yesterday))
        .left_join(Bars)
        .also_select(Bars)
        .all(&db)
        .await?;

How is that not higher-level than SQL?

[0] https://crates.io/crates/sea-orm

Why not just write SQL, it would be much more readable.

> SQL is the highest level you can get without getting into the minefield that is LLMs.

I mean, not really true. Datalog is relational, like SQL, but gives you a higher level rule-oriented mechanism for querying that can avoid a lot of the mechanics of explicitly joining things, for example.