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

131–140 of 167 posts

Re: Rethinking Database Programming

#131

Post by geophile »

pjmlp wrote: post #119
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.

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.)

Re: Rethinking Database Programming

#132

Post by zem »

setr wrote: post #124
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…

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 t…

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.

Re: Rethinking Database Programming

#133

Post by zem »

pjmlp wrote: post #119
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.

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.

Re: Rethinking Database Programming

#134

Post by ModernMech »

tome wrote: post #96
ModernMech wrote: post #82

Earlier quoted context omitted.

SQL is based on the relational model but doesn't really conform to the mathematics e.g. doesn't exhibit set semantics.

Sets and bags are trivially interconvertible so it's really not a big deal: https://h2.jaguarpaw.co.uk/posts/set-bag-irrelevance/

The point is if you're doing relational algebra you want to work with relations. The key reason why set semantics are nice is because the operations are guaranteed to return relations, so you don't have to check or make accountings of which return values are sets and which are bags, or worry about machinery to convert between the two.

It's like how you can store numbers internally as floating points or rationals and trivially convert between the two. But if all you ever do is floating point math, you might prefer to store the numbers as floating points rather than rationals and then convert to floating point.

Re: Rethinking Database Programming

#136

Post by dwohnitmok »

hombre_fatal wrote: post #50
dwohnitmok wrote: post #40

Oh man. If this lobste.rs comment is correct about the subscription terms then this feels like a really hard pill to swallow: https://lobste.rs/s/ykq7ym/rethinking_database_programming#c... Still might be viable, but would be tricky to sell. > SUBSCRIPTION TERMS > This license is subscription-based and will remain valid only for the duration of your active subscription. Upon expiration or termination of your subscrip…

On the other hand, norms in software right now are that suckers build and maintain software for free + "the love of the game should be enough for anyone", so it's shocking when people break the norm.

That's not the norm that's being broken here. Most DB technologies provide a "pay for updates, if you stop paying you keep the last version you paid for" model. This is how Oracle prices its DB tech, this is how jOOQ is priced (which is probably the closest thing to Acadia), this is how MS prices its DB tech etc.

Re: Rethinking Database Programming

#137

Post by vrighter »

gopalv wrote: post #108
senderista wrote: post #98

Earlier quoted context omitted.

The problem with the query part is that query fragments aren't composable.

CTEs are how you compose SQL. I don't quite like how the same CTE lives in 60 different places in my codebase, but at least the WITH clause changed things for me. Also really liked Snowflake's result_scan for composing chains, mostly because I don't rerun expensive parts again and again. You can use ->> as a shortcut, but I don't think it uses results caching internally to skip waiting for them to all re-run & actual…

you create one view and have the ctes query that, to deduplicate the implementation

Re: Rethinking Database Programming

#138

Post by pjmlp »

ameliaquining wrote: post #128
pjmlp wrote: post #114

Earlier quoted context omitted.

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

Stored procedures have the wrong versioning model. If they were version-locked to the application code, instead of to the database schema, they'd be less of a pain and people might be more willing to use them.

There are CI/CD processes for deployment, versioning problem is solved at least for 30 years.

Also it is hardly any different from handling version differences in distributed systems, or split between frontend and backend on Web applications.

Re: Rethinking Database Programming

#139

Post by pjmlp »

zem wrote: post #133
pjmlp wrote: post #119

Earlier quoted context omitted.

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 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.

Re: Rethinking Database Programming

#140

Post by fulafel »

groundzeros2015 wrote: post #71
alpinisme wrote: post #51

Earlier quoted context omitted.

The point is end to end type safety. Whether that is worth the tradeoff of losing direct developer access to the db primitives is another question.

SQL is end to end type safe.

They might mean static typing in queries.
Post reply on HN