Comment by geophile | Hacker News Reader

Comment by geophile | Hacker News Reader

I have a very long history with language interfaces to databases.

- As a grad student in the 80s, I read a lot about "database programming languages", which aimed to provide persistence and query capabilities to conventional programming languages, in a seamless way.

- The next step to putting those ideas into practice: Participated in a research project on adding database capabilities to a programming language (anyone remember Ada?)

- I designed and developed most of the modeling and query language features of one of the major object-oriented database systems, back in the early 90s.

- I also designed and contributed to a SQL interface to our OODB, as well as an ORM, taking our model and query language, and mapping it to SQL.

- Turned down an offer from a software giant of the late 90s, to add database capabilities to one of their main languages, (basically bringing to their language what I had built at the OODB company).

- Designed and built a Java ORM (late 90s).

And after working on this stuff for something like 20 years, I concluded that it's all misguided. For all of its ugliness and weirdness, SQL was designed to address a certain set of requirements, and has succeeded wildly. New database programming languages face huge problems of acceptance, and needing to solve the exact same problems that SQL handles now. (This was easier 30 years ago since it was still early days for SQL. Now it's basically impossible.) ORMs are a terrible idea, in the "now you have two problems" category. Not only do you need to write high-performance queries, but you have to get your ORM to actually issue those queries. (Yes, ORMs have escapes to raw SQL. The existence of these escapes proves my point.) And schemas change, and the mapping to your language model has to change, and it's a mess.

Just use SQL. It's the right tool for the job it was designed for. Use a database driver to integrate with your language. It's just not that hard.

The thing I’ve never understood is why SQL itself is not the target of attack. There’s already an inherent language abstraction with the planner; Postgres in theory could be the JVM with any number of languages implemented on top. Including a language that lends itself to composition and auto generation of PL functions.

ORMs are fundamentally difficult because of the mapping problem, but SQL code builders should be trivial. Auto-generating and exposing every DB functionality as a type-safe $LANG function should be trivial. Instead, they’re also accidentally difficult because building SQL is difficult.

Outside of SQL, you’ve got datalog… and that’s about it. And I guess whatever horrors the NoSQL crowd keeps coming up with

agreed! I feel like basic ocaml syntax would map very well to a higher level SQL - `let` to define reusable subexpressions, `let ... in` to define inline pieces of a large query, partial application to fill in variable values, and a final function call to execute the query.

SQL engines already had have multiple languages support for stored queries for at least 30 years.

C, C++, Perl, Java, CLR at least. GraalVM was originally designed as repurposing the MaximeVM ideas into a new Oracle SP engine.

You can even use Oracle or SQL Server as application server, having a Web frontend calling into stored procedures exposed as API endpoints.

Given your experience, what is your opinion on stored procedures?

I love them, think that what can be done in the database should stay in the database, and many of these abstraction on top are all ways to avoid just having to implement them.

And the main reason, DB portability, seldom happens in reality, most product die still using the database they were original created with.

I agree with you on all points. SPs are incredibly useful. DB portability is such a strange goal. Very common for some reason, but rarely actually needed.

I think there are probably two reasons for the hate that SPs get. 1) Come on, I learned SQL, isn't that enough? I have to learn SPs too? 2) Architecture astronauts love them their tiers, and logic belongs in the tier above the database, not the database tier itself. (I expressed this opinion in a job interview -- without disparaging any group of techies -- and I believe this is the reason I was not invited back.)

I like the idea behind stored procedures, but the ergonomics of developing and maintaining them are not great. if they could be made to look like a library of code sitting in a directory somewhere, and transparently compiled and imported by the database but still workable with using external tools like git, I think they would feel a lot less strange.

The ergonomics are the same as any language, when using IDEs with the SQL vendors plugins, instead of vi and CLI admin for queries.

> when using IDEs with the SQL vendors plugins

Do these plugins mean you don't get to store them in git? You're just going to open up the developer studio and YOLO a change to the stored procedure, live in production? Because the whole argument is that the way we do version control, code review, bisecting, single-artifact deployment, etc is generally at odds with how stored procedures work. Saying "but my IDE has a good plugin" solves maybe 1/100th of the problem.

Some answers to doing stored procedures in a version control system that I've seen:

- Put everything in a migrations directory, and every time you change the stored procedure, introduce a new migration that completely rewrites it. (Merge conflicts are hell with this, plus all the massive amount of waste it generates in the checked-out tree)

- Put the stored procedures in a directory as normal code and then "sync" them to the database at runtime (with all the massive foot-guns this entails, trying to detect if they've changed versus what's in the database, etc)

- Eschewing stored procedures in favor of using prepared statements and having your ORM figure out when to use them

There may be others but I think they're all going to look like some form of one of the above.

Reply on news.ycombinator.com