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

Rethinking Database Programming

acadia.engineering

Post reply on HN
Print view View original

111–120 of 167 posts

Re: Rethinking Database Programming

#111

Post by threethirtytwo »

ltbarcly3 wrote: post #103
elcritch wrote: post #52

Earlier quoted context omitted.

There's a lot of benefit in these systems, though there's rough edges and I agree about the basics like PK's and uniqueness. I've been using Ormin [1] in Nim which works by parsing the SQL tables and uses it to compile time check queries: # Multiple joins with pagination let page = query: select Post(title) join Person(name) on author == id join Category(title) on category == id orderby desc(post.creation) limit 5 of…

Just learn SQL. I believe all these SQL replacement layers are just because people don't like SQL and don't learn it, so they learn a training wheels version of it that will cripple their ability to grow because it's simplifications remove expressiveness that caused SQL to be more complex to begin with. Just learn SQL, it's not that hard. A lot of very very smart people put a lot of effort into it. It's very good. Th…

No. SQL is just bad. It's an old way of doing things. It's not hard but it's not good.

Take this for example. Why do we have static type checking for typescript? Why do we have a build step for this?

Why DON'T we have it for SQL? Why is it runtime strings? So no static checking and the only way to test if a query works is to run it?

The purpose of these replacement layers is to get it all under one language. Once it's all under one language you get full safety and fusion across the two concepts. Query builders and ORMs are shooting for an ideal, and the ideal makes sense. It's just a nightmare to implement and thus fundamentally there are compatibility issues and that's why a lot of people in general don't like orms.

There's also a sync step where the model in the language has to be aligned with the model in the database which is just an extra mutating state layer which further compounds the bugs.

Re: Rethinking Database Programming

#112

Post by setr »

ltbarcly3 wrote: post #103
elcritch wrote: post #52

Earlier quoted context omitted.

There's a lot of benefit in these systems, though there's rough edges and I agree about the basics like PK's and uniqueness. I've been using Ormin [1] in Nim which works by parsing the SQL tables and uses it to compile time check queries: # Multiple joins with pagination let page = query: select Post(title) join Person(name) on author == id join Category(title) on category == id orderby desc(post.creation) limit 5 of…

Just learn SQL. I believe all these SQL replacement layers are just because people don't like SQL and don't learn it, so they learn a training wheels version of it that will cripple their ability to grow because it's simplifications remove expressiveness that caused SQL to be more complex to begin with. Just learn SQL, it's not that hard. A lot of very very smart people put a lot of effort into it. It's very good. Th…

Learning SQL doesn’t absolve you from the fact that, from the perspective of your PL, you’re smashing arbitrary strings together like a Neanderthal, and you can be offered all the support otherwise given to your string smashing problems (exactly none)

It also doesn’t absolve the fact that SQL is not a particularly well-designed language for smashing strings together like a Neanderthal. In fact, you might even say it’s absolutely horrid at it, with random keywords, extraneous syntax, and general lack of compositional capabilities.

The relational model is fantastic — Codd is Godd, after all. The engines are a work of art. The SQL language is a shitshow. PL/SQL and all its variants are a crime upon the PL community. The programmatic interface to a database is a shitshow, because it is SQL and only SQL. The SQL standard is a joke and standardizes nothing.

None of this is contentious, or should be, once you’ve learned SQL.

Re: Rethinking Database Programming

#113

Post by wpollock »

ltbarcly3 wrote: post #103
elcritch wrote: post #52

Earlier quoted context omitted.

There's a lot of benefit in these systems, though there's rough edges and I agree about the basics like PK's and uniqueness. I've been using Ormin [1] in Nim which works by parsing the SQL tables and uses it to compile time check queries: # Multiple joins with pagination let page = query: select Post(title) join Person(name) on author == id join Category(title) on category == id orderby desc(post.creation) limit 5 of…

Just learn SQL. I believe all these SQL replacement layers are just because people don't like SQL and don't learn it, so they learn a training wheels version of it that will cripple their ability to grow because it's simplifications remove expressiveness that caused SQL to be more complex to begin with. Just learn SQL, it's not that hard. A lot of very very smart people put a lot of effort into it. It's very good. Th…

> Just learn SQL....

I agree. In my experience, ORMs are more complex and harder to learn to an expert level than SQL. Knowing Java (but not SQL) doesn't help much with learning Java ORMs (Again, to an expert level). Besides not supporting all the SQL features of some DB, ORMs also covers other things such as caching.

Learning ORMs is likely just as difficult as learning SQL. It is likely harder to learn how to optimize performance with ORMs.

SQL as opposed to code has the advantage that it can be kept in a separate file, and thus modified by experts in databases without changing the code. The article claims the author found migrations harder with SQL than with his framework. I would think it would depend a great deal on the database one is migrating.

I'm not convinced that LLMs make things easier, you still need an expert to verify the generated code, and to tune it, as often the database is business critical with serious consequences if wrong, slow, or turns out to be infringement of someone's copyright.

Just learn SQL!

Re: Rethinking Database Programming

#114

Post by pjmlp »

setr wrote: post #112
ltbarcly3 wrote: post #103

Earlier quoted context omitted.

Just learn SQL. I believe all these SQL replacement layers are just because people don't like SQL and don't learn it, so they learn a training wheels version of it that will cripple their ability to grow because it's simplifications remove expressiveness that caused SQL to be more complex to begin with. Just learn SQL, it's not that hard. A lot of very very smart people put a lot of effort into it. It's very good. Th…

Learning SQL doesn’t absolve you from the fact that, from the perspective of your PL, you’re smashing arbitrary strings together like a Neanderthal, and you can be offered all the support otherwise given to your string smashing problems (exactly none) It also doesn’t absolve the fact that SQL is not a particularly well-designed language for smashing strings together like a Neanderthal. In fact, you might even say it’…

Only because some people are very opinated in avoiding stored procedures, and think smashing strings together is a much better solution.

Re: Rethinking Database Programming

#115

Post by pjmlp »

bazoom42 wrote: post #77
groundzeros2015 wrote: post #71

Earlier quoted context omitted.

SQL is end to end type safe.

Isn’t sql weakly typed? Or does this depend on the engine?

No it is strongly typed, there is no accident that all PL extensions to the base query language have such a Ada/Pascal similarity.

Additional DML has plenty of options to enforce rules that keep data consistency.

While they make the life harder to delete/update/insert items in specific sequences, they can save the day on bad queries.

Re: Rethinking Database Programming

#116

Post by pjmlp »

dminik wrote: post #36
schaefer wrote: post #23

Earlier quoted context omitted.

> …can't help but notice how sql is coming from the c-Era of programming. Having … more modern ways to express my queries would be great to improve correctness … SQL is based in pure mathematics: set theory, relational algebra. The process of applying mathematical rigor to your database design to prove correctness is referred to as normalization. I don’t mind criticisms like “It’s old, yuck”, but criticisms like “it’…

Syntax aside, programmers and mathematicians have a very different view on how things should be done. Programmers look at data and see opportunities for running a pipeline of transformations (map/filter/...). And they tend to write their SQL like this as well. Or use something like Linq or one of the various pipe syntax SQL extensions. I would say that this is a major reason why there is this sentiment of "SQL is yuc…

Or they did a proper Software Engineer degree that teached on how to use SQL properly, including implementing their own toy SQL engine backed by B-Tree indexes, with raw i-node blocks for storage.

Re: Rethinking Database Programming

#117

Post by pjmlp »

huahaiy wrote: post #74
raumgeist wrote: post #9

Looks very nice. Last year I took up rust, coming from c++, and some of the modern features rust brings are just so nice to have (even something as simple as not having to forward declare a class). This year I started working with postgres and you just can't help but notice how sql is coming from the c-Era of programming. Having better and more modern ways to express my queries would be great to improve correctness a…

It is older than C. It is based on COBOL era idea of structured English as a computer language. There are better alternatives, e.g. Datalog.

These kind of comments don't age well in the days of AI programming using English.

Re: Rethinking Database Programming

#118

Post by pjmlp »

senderista wrote: post #102
huahaiy wrote: post #74

Earlier quoted context omitted.

It is older than C. It is based on COBOL era idea of structured English as a computer language. There are better alternatives, e.g. Datalog.

COBOL is indeed the spiritual predecessor of SQL. We have learned a lot since then about PL design, to put it mildly.

Yes, we now programm in straight English, and hope the machine gets it right.

Re: Rethinking Database Programming

#119

Post by pjmlp »

geophile wrote: post #110

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 (anyon…

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.

Re: Rethinking Database Programming

#120

Post by setr »

pjmlp wrote: post #114
setr wrote: post #112

Earlier quoted context omitted.

Learning SQL doesn’t absolve you from the fact that, from the perspective of your PL, you’re smashing arbitrary strings together like a Neanderthal, and you can be offered all the support otherwise given to your string smashing problems (exactly none) It also doesn’t absolve the fact that SQL is not a particularly well-designed language for smashing strings together like a Neanderthal. In fact, you might even say it’…

Only because some people are very opinated in avoiding stored procedures, and think smashing strings together is a much better solution.

PL/SQL is cursed and the unstandardized library system means every DB’s ecosystem is anemic.

Instead of smashing strings, you can code with all the affordances of C90 and still get the chance to smash strings together if you need to do anything beyond utilizing simple variables (EXECUTE) — now with an even worse string manipulation stdlib. And you also get the privilege of working with the some of the most worthless parser errors known to modern man. As an added bonus, DB IDEs are universally worse at text-editing & refactoring than the equivalent application editor

You can reuse code through extensions/external instead, and have access to real programming languages with actual libraries… but now you’re kicked out of managed environments because it’s not whitelisted, and even if you do run it, you’re back to smashing strings together like a Neanderthal trying to communicate to your DB.

Sprocs/functions are useful because they do useful engine things — they run locally with the data, they have an easier time playing with transaction flow, some logic is much easier to express with a cursor instead of set logic and you get to avoid most of the penalties you’d have otherwise.

They do absolutely nothing to make SQL a less terrible interface to your database, except by stuffing it under a rug (CALL).

Post reply on HN