[deleted]
Rethinking Database Programming
101–110 of 167 posts
Re: Rethinking Database Programming
#102Looks 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.
COBOL is indeed the spiritual predecessor of SQL. We have learned a lot since then about PL design, to put it mildly.
Re: Rethinking Database Programming
#103The 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 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. The things that are annoy you about it are often there because of something you don't yet even realize is something you need to be aware of, or because your fundamental understanding of things is just wrong or incomplete.
Re: Rethinking Database Programming
#104Or just let the language be the database like https://en.wikipedia.org/wiki/MUMPS ;)
There are tons of problems with this, but the simplicity is comfy.
Re: Rethinking Database Programming
#105The idea of treating database programming more like regular programming is nice. I'm just not sure how much complexity this actually removes versus moving that complexity somewhere else
Re: Rethinking Database Programming
#106Earlier 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…
I already know SQL which is why I like the above. It's SQL with some tweaks to match Nim syntax and to have less ambiguous table/column identification.
Meanwhile embedding SQL in a string with `?` everywhere, manually converting the results, and remembering some of the SQL syntax is annoying.
Re: Rethinking Database Programming
#107Earlier 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…
I think the issue is that while ORMs etc, stuff like ecto…whilst they’re never going to be database native like actual SQL, the value in the abstraction isn’t making querying easier, but making more robust and useful the integration into the host language. It brings it out of database domain and into application domain so that doesn’t have to to constantly reinvented.
You can always be more expressive and portable in raw SQL, that’s obvious, but the things you’re doing have to be used somewhere, so at some point the things you are doing have to cross a barrier. For the 90% use case, ORMs are a pragmatic choice because the good abstractions aren’t about the syntax, they’re about allowing you to talk about and mutate data within the language paradigms that everything else is written in.
Re: Rethinking Database Programming
#108Earlier quoted context omitted.
The part that has stood the test of time and genuinely seems to carve reality at the seams is the query part. The data definition and data manipulation parts are just ok.
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 & actually optimizes the whole thing.
Re: Rethinking Database Programming
#109Earlier quoted context omitted.
SQL is end to end type safe.
Only backend to database. This is talking about typesafe from database - backend - frontend.
You can write raw sql and use the "describe" clause in script, and then generate code with the result. This gives full db-backend-frontend type safety with raw sql queries
Re: Rethinking Database Programming
#110I 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.