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.

https://github.com/vb-consulting/blog/discussions/5

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.

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.

Ever heard of ORM lol

Google hypergamy

John Carmac is a legend

I disagree on both points.

Edit: but I was referencing specific performances claims, that you will somehow take some load of database server. I just don't see it.

I don't understand it either.

Author seems to be arguing against long functions/procedures. But if you move that to the client, presumably with ORM support - you're going to be executing more or less the same sequence of SQL queries and commands. Only difference is that when doing it on client you will have a lot of latency.

Yes, you can cache some data in between those commands to avoid same multiple queries, but if you use temp tables to do so, they will use memory only if it is available, otherwise you are limited with the actual storage size.

Only time I had memory issues with PostgreSQL when I used too much data in arrays and json's. Those are memory only variables. For example, I'd return a huge json to client and I'd run out of memory on PostgreSQL. I started streaming row by row and problem solved.

https://www.reddit.com/r/dotnet/comments/yuxkk7/how_fast_is_...

In my opinion it's a waste of time and energy if you're not using it.

That dude shouldn't use projects located on mounted /mint) dusk in windows files system. That's why it's so slow.

There is, I made something similar for my needs.

Docs could be better, but it's a open source and free I did in my free ti lme without sponsors or any money.

https://github.com/vb-consulting/PgRoutiner

They are reverting it back https://github.com/dotnet/sdk/pull/22262

And how the hell can admins figure out from your answers you are LGTB.

If they can do that, your answer should be deleted and not because you are LGTB. Because it probably has nothing to do with the question.