another approach that works great is to use `create type`[1] with an `input_function` and `output_function` to build on top of an existing scalar type. For example, using that method would allow semver to be persisted as a string natively. The only downside to that is you have to be superuser.
[1] https://www.postgresql.org/docs/current/sql-createtype.html
If space abuse is a vector under consideration, given there doesn't seem to be a restriction on the number of entries (if I somehow missed one my apologies) you could easily stuff it with a really big array of 255 character entries so I wonder if 2048 plus some sort of reasonably generous limit on the number of elements might not achieve the goal better and provide a better UX/DX as an added bonus.
(I'm currently working on a design in a somewhat related area so I've been doing a fair bit of thinking about this - I may of course still be completely wrong, but if so I can at least claim I put effort into ending up wrong ;)
Then, instead of informing them of best practices and giving them time to re-work the code, you'd just fire them?
Interesting strategy.
A relational database isn't always fronted by the same tech. You have RESTful APIs, GraphQL APIs, Low Code/No Code Solutions (like Retool), reporting tools, ETL systems, etc.
You have company mergers where that one familiar ORM is now five to six unfamiliar ORMs.
Ensuring data integrity is key and easiest way to do that is to push constraints as close to the persistence layer as possible.
Putting logic directly in the database is much faster at runtime, doesn’t require redundant entity definitions, is substantially more likely to detect schema problems, and is way easier to test.
And it removes the dependency on a specific language and toolchain. Your logic is accessible by everyone.
Unfortunately, the tooling around managing this stuff is not great. The language is a bit boring; idiomatic upper case is the first thing I get rid of.
But it’s the best tool for the job. Far from being “way easier” in application code, plpgsql reduces LOC, improves performance and removes dependencies.
If someone on my team ignored all of these benefits, well, I’d explain it to them and show them examples until they “got it”.
Way easier than firing them.
It’s not particularly annoying to debug - there are logging, and even single step debugging tools available - and it’s so much easier to test than writing unit tests to do the same thing.
I agree it’s painful to deploy, but as I’ve discovered this is a tooling problem; it’s not at all intrinsic to the medium.
It’s certainly possible to create tooling to make it easier, and who knows? Maybe I’ll have something to show HN one day.
> Many developers today barely know SQL.
If true - a very big if - it’s an inditement of the industry and the incentives we use, not a reason to avoid something that makes code faster, safer and smaller.
Objections to using these languages are a lot more about willingness to learn and try new (old) things, than they are about the capabilities of the platform.
1) Its only been a few years since the trend of testing on sqlite locally and running postgres in production went away in favor of postgres everywhere (probably thanks to docker). That prevented using any feature that wasn't equally supported by both.
2) There's definitely a knowledge gap, and not just among developers. The features are most useful for building rich applications, so even DBAs didn't have much incentive to use them prior to tools like supabase using the database as the data model source-of-truth.
3) Companies are increasingly deciding that data is their competitive advantage and interest in data integrity is growing. Database constraints are unparalleled at that because they can't be sidestepped
But there are far fewer tutorials and best practices on how to, say, maintain a library of Postgres functions, types, and stored procedures that can be iterated on. I'd venture that most people have no idea how powerful their databases can be.
Bad support for testing, for version controlled schemas, language types, etc.
This is the reason for the spread of ORMs and similar abstractions to hide this lack of support.
Is that true?