vbilopav

This was my first approach in realizing my PostgreSQL-centric, Db-first, or rather SQL-first vision of software development.

The project was call pgroutiner and it generated bunch of C# code for me out of PostgreSQL.

After doing couple of projects with it, I realized it wasn't enough. The new approach is a dynamic HTTTP REST API generation but, with plugin for code generation of frontend files (Typescript,. Javascript, HTTP files). This iw way better approach. Look it up, it's called npgsqlrest.github.io

Why not Foreign Data Wrappers?

NpgsqlRest Automatic PostgreSQL Web Server

Create REST APIs for PostgreSQL databases in minutes.

https://npgsqlrest.github.io/

- one man project (me) - been doing it well over a year now - no sponsorship, no investors, no backers, no nothing just my passion - I haven't even advertised much, this may first ir second time I'm sharing a link - On a weekdays im building a serious stuff with it - On weekends preparing a new major version with lessons learned from doing a real project with it

Not going to stop. But I migh be seeking sponsors in future, not sure how that will turn out. If not that's ok, I'm cool to be only user.

It looks to me team is clueless about database development.

Typescript ORMz lol.

Soft deletes make indexing, well, problmatic to say at least. Use temporal tabls instead for point in time recovery. Nevertheless it could have been solved on a database level without any help from ORMs, lookup RLS. Still, screws up indexing strategy.

What an awful query example! This should and is simple. Here:

select jsonb_build_object( 'title', title, 'genres', genres, 'actors', json_agg( jsonb_build_object( 'name', actor_name, 'characters', actor_characters ) ), 'directors', directors, 'writers', writers ) from ( select t.primaryTitle as title, t.genres, actor_person.primaryName as actor_name, array_agg(pc.character) as actor_characters, array_agg(director_person.primaryName) as directors, array_agg(writer_person.primaryName) as writers from title t left join principal actor on t.tconst = actor.tconst and actor.category = 'actor' left join person actor_person on actor.nconst = actor_person.nconst left join principal_character pc on actor.nconst = pc.nconst and actor.tconst = pc.tconst left join principal director on t.tconst = director.tconst and director.category = 'director' left join person director_person on director.nconst = director_person.nconst left join principal writer on t.tconst = writer.tconst and writer.category = 'writer' left join person writer_person on writer.nconst = writer_person.nconst where t.tconst = 'tt3890160' group by t.tconst, actor_person.primaryName, t.primaryTitle, t.genres, actor.ordering, director_person.primaryName, writer_person.primaryName order by actor.ordering asc ) main group by title, genres, directors, writers;

Here: https://i.postimg.cc/zB1Bgg1L/movies.png

I did some testing on PostgreSQL 17 and found out that this special function 8s not needed.

why does it look like SQL?

It's already built into code to check does trigger exists, check it out.

As far as changes(alter table ) goes, I need to take history tables into consideration when writing migration scripts. Since I'm doing my migrations always with SQL scripts that not an issue.

However, there's a concept of system event triggers in PostgreSQL that can intercept alter table statements, so I'm thinking to expand my solution to alter history tables automatically. Check it out: https://www.postgresql.org/docs/current/event-triggers.html

Please don't do this. It's a design/ architectural sin. It messes up your queries, commands and indexes and it's constant source of trouble.

Here's a better way: https://medium.com/@vbilopav/custom-temporal-tables-in-postg...

I thought it was because of Eurovision

Here's an alternative I've built for myself https://github.com/vb-consulting/NpgsqlRest

I'll just keep on using Svelte 4

He hates TypeORM but loves Prisma and Prisma can't even do joins, it's doing N+1 by default and always.

What does TypeORM do then?

Just use temporal tables https://github.com/vb-consulting/blog/discussions/7

I wonder how our ancestors ever managed to do anything let alone build a world we live in without having someone to teach them empathy.

It's ridiculous.

We have a pandemic of narcissism thanks to social media and when they see this they go, great I can use that thing called empathy to get what I want.

On other hand I know people who have too much empathy living in constant depression, because, you know, there's suffering in the world. Somewhere.

I wrote a follow-up on yesterday's article on tree processing and recursion with PostgreSQL. This one: https://github.com/vb-consulting/blog/discussions/1

SQL was never intended for this kind of stuff; I'm sure that Fabian Pascal will tell us all about it.

And indeed, recursive CTEs are awful, confusing, and severely limited ... but they look cool and smart, and smart people use them (I hate them).

In any case, I used a procedural approach to this problem and, with a few smart optimizations, managed to squeeze some really spectacular performances (757K tree records with 20K unique nodes in just 5 seconds without any indexes, so it probably can go even faster).

A follow up on this article was written today

[Recursion with PostgreSQL, Follup 1, Perfomances](https://github.com/vb-consulting/blog/discussions/4)

I managed to get some really good perfomances (757K records in 5 seconds).

Also, method 1 contained a nasty bug :(

That's why next time I'll try to focus on debugging and testing.