Hacker News Reader

> Look at all the features supported here:

> https://www.postgresql.org/docs/current/sql-createtable.html

Unironcally, yesterday i was vibe-coding a small app for personal use using Django and was quite shocked to discover that Django's orm does not support something as simple as specifying a database schema other than the default "public" one out of the box.

You either have to add options specific from libpq:

    DATABASES = {
        "default": {
            "ENGINE": "django.db.backends.postgresql",
            "NAME": "mydatabase",
            "USER": "myuser",
            "PASSWORD": "mypassword",
            "HOST": "localhost",
            "PORT": "5432",
            "OPTIONS": {
                "options": "-c search_path=myapp,public",
            },
        }
    }

Or you have to do it from the postgresql side:

    ALTER ROLE myuser
    IN DATABASE mydatabase
    SET search_path = myapp, public;

It's not ergonomic at all.

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

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.

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.

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

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.

Only true when avoiding stored procedures.

Yeah. Most systems avoid stored procedures imo. They way to go imo is to use stored procedures for everything, but the standard pattern has stored procedures as some sort of secondary thing.

Either way the types of the stored procedures do not statically mesh well with the types of the application server. So there's a lot of syncing issues here that can only be caught at runtime.

Runtime strings? No static checking? I think you have used mysql and think that mysql is somehow what you should expect, because you are just saying wildly incorrect things. (You don't know what you're talking about)

i do. default way of doing things is sending a string from server to database.

You don't know what you're talking about.

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.

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.

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

PL/SQL is great and using SQL Developer definitely better than smashing strings together.

If only C90 was half as good.

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.

Version differences in distributed systems (including Web apps) are a real pain! In many circumstances they're unavoidable, and we've developed various techniques to make them marginally easier, but if you can avoid the issue entirely by just not having the thing be distributed, that's the more maintainable choice.

Oracle has a feature called 'editions' that does this. Different DB sessions can have different versions of redefinable objects like stored procs and packages.

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.

Only backend to database. This is talking about typesafe from database - backend - frontend.

Yes, and that’s an architectural choice you’re making.

Instead of using all the consistencies provided in the database process - including types, but also date/time, constraints, transactions, triggers etc. you are exiting the system and losing all guarantees.

This system also doesn’t solve that problem.

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

Which end? This moves one end to reach frontend code

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

SQLite is the only one I know of that doesn’t enforce types by default, but I don’t know what the SQL spec requires.

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.

What happens if a query compares a string to a number?

You get a type error from the database.

But when do you get that type error?

This is the important bit.

You get it after the app is deployed, the query is ran and a result is expected.

When do I get a type error from my language if it's statically typed? That's right, before I even deploy.

As far as I can tell, some engines will implicitly coerce types so “7” = 7

They might mean static typing in queries.

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

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.

https://sqlc.dev/ does this for me. Its been nice!

The problem here is that you still have to do some pointless and tedious conversion between generated data structures and your domain data structures. Maybe with LLMs, some of that tedium goes away, but you still have to test, maintain and understand that part of the code.

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.

A core idea of the relational model is to seperate the logical model from the physical layer including optimizations, indexes etc.

So it makes sense to only expose the logical model at the ORM layer.

The problem comes if you want to define the database schema through the ORM layer, rather than just represet it.

Isn't SQL already a logical abstraction language over a "physical layer"? I'm not updating indexes or deciding when to flush or fiddling with MVCC when I write SQL

The comment mentioned partioning schemes which is defined using SQL but belongs in the physical layer. Indexes are also defined in SQL.

Thanks. Indices are defined in SQL, but they're not updated in SQL. Once defined, an INSERT/UPDATE updates relevant indexes automatically. That's the abstraction layer SQL provides.

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

In Prolog you'd just handle those as metapredicates. There are a million different ways to skin the cat there. For example on partitioning schemes:

  :- vertical_partition(profile/4, [
      core(1, 2),       % UserID, Username -> stored in primary memory
      metadata(1, 3, 4) % UserID, Bio, Preferences -> stored in cold storage
  ]).