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