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

51–60 of 167 posts

Re: Rethinking Database Programming

#51

Post by alpinisme »

mike_hearn wrote: post #45

The issue with defining schemas in a non-SQL programming language is they always lag behind what the underlying database can do. Sure, your ORM-like framework can define basics like primary keys and maybe uniqueness constraints, but can it define partitioning schemes, compression methods or more advanced constraints? Look at all the features supported here: https://www.postgresql.org/docs/current/sql-createtable.html…

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.

Re: Rethinking Database Programming

#52

Post by elcritch »

mike_hearn wrote: post #45

The issue with defining schemas in a non-SQL programming language is they always lag behind what the underlying database can do. Sure, your ORM-like framework can define basics like primary keys and maybe uniqueness constraints, but can it define partitioning schemes, compression methods or more advanced constraints? Look at all the features supported here: https://www.postgresql.org/docs/current/sql-createtable.html…

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 offset 10
I think that's better since defining SQL should be the source-of-truth for the DB and the code. ORM's always ended up causing trouble in my experience.

Things like indexes, defaults, partitions, etc generally aren't expressible in code without a lot of kludges. Then each DB engine have pretty different rules, syntax, etc for tables.

However having the queries compile time checked, type conversions handled, and the nuances between SQL query syntax handled is rather nice. As you mention it's a much easier subset.

1: https://github.com/Araq/ormin

Re: Rethinking Database Programming

#53

Post by adzm »

mike_hearn wrote: post #45

The issue with defining schemas in a non-SQL programming language is they always lag behind what the underlying database can do. Sure, your ORM-like framework can define basics like primary keys and maybe uniqueness constraints, but can it define partitioning schemes, compression methods or more advanced constraints? Look at all the features supported here: https://www.postgresql.org/docs/current/sql-createtable.html…

Agreed with you here. In my experience the best solutions go the opposite way, and parse the SQL in ways that can be used from the application.

Re: Rethinking Database Programming

#54

Post by whattheheckheck »

alpinisme wrote: post #51
mike_hearn wrote: post #45

The issue with defining schemas in a non-SQL programming language is they always lag behind what the underlying database can do. Sure, your ORM-like framework can define basics like primary keys and maybe uniqueness constraints, but can it define partitioning schemes, compression methods or more advanced constraints? Look at all the features supported here: https://www.postgresql.org/docs/current/sql-createtable.html…

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.

I agree with end to end type safety but that needs more details to sell what problem its solving. Folks dont buy it for itself

Re: Rethinking Database Programming

#55

Post by crabmusket »

Reading this, I mistook it for a slightly different idea: using these functional languages directly inside the database process, avoiding SQL altogether.

I've wanted to try that out with e.g. Roc and a reimplementation of SQLite's on-disk format. (Of course, that's a non-starter for production use, but it could be an interesting experiment to see what that programming model was like.) The database would become kind of like a library you use to build your tables and queries with.

Also, thank you for calling it a 1+n query, not an n+1 query ;)

Re: Rethinking Database Programming

#56

Post by let_rec »

It seems like this is a few things:

1. An Elm-like programming language that lives in .db files

2. A compiler from this language to strongly-typed database procedures in a target backend language

This has more in common with a semantic layer than an ORM.

What you gain is a shared language that connects the table definitions (say a SQL migrations folder) and your API language (often handwritten SQL). This can be type checked and optimized for you.

But for me the big question is what functionality do you lose? Can I express everything that PostgreSQL can?

Re: Rethinking Database Programming

#58

Post by Smalltalker-80 »

mike_hearn wrote: post #45

The issue with defining schemas in a non-SQL programming language is they always lag behind what the underlying database can do. Sure, your ORM-like framework can define basics like primary keys and maybe uniqueness constraints, but can it define partitioning schemes, compression methods or more advanced constraints? Look at all the features supported here: https://www.postgresql.org/docs/current/sql-createtable.html…

Agreed, that's why I chose to implement a simple ORM for my language's multi-platform database library. It has a mandatory 'id' column, for simple updating and deleting, but table creation and complex queries are done in plain SQL.

Re: Rethinking Database Programming

#59

Post by dboreham »

Hmm. I've skimmed the article. It looks to be another ORM/FRM type thing. There are many issues with such things, but for me the most troubling is this: in most systems (obviously...it depends) you don't want to wind the database around the axle of any one software component or language. Having the data separate from the code, and defined/managed with a language that suits data management is a feature not something to be designed out. My hunch is that people who come up with these "solutions" fail to realize this. They then condemn everyone using their layer to endless hair pulling trying to figure out "what SQL did it make from that?" and "how do I make it do this SQL?".

Re: Rethinking Database Programming

#60

Post by sgerrand »

OhMeadhbh wrote: post #57

Anyone else just see a blank page when hitting this link? Maybe it doesn't like Firefox or is doing some sort of JavaScript shinanigans to defeat our AI overlords. I don't have enough coffee yet to debug it.

Try the Internet Archive's cache: https://web.archive.org/web/20260818090328/https://acadia.en...
Post reply on HN