PostgreSQL example of self-contained stored procedures
1–10 of 81 posts
Re: PostgreSQL example of self-contained stored procedures
#2How would somebody implement this for a team with versioning, source code view, and deployment?
Re: PostgreSQL example of self-contained stored procedures
#3I'm curious if anybody has practical experience developing a reasonably trafficked website in a similar manner.
I wrote apps which consisted mostly of oracle stored procedures when I first got out of college, and it was a pretty awful experience, but it was also at a place where the development knowledge was minimal.
I've since been getting closer and closer to writing postgres in this way. My current app (which I architected) sticks close to postgres and uses many of its features like custom types, enums and json, but we don't rely on it for stored procedures at all. Instead, all application logic lives in the go API app.
I've been considering moving some things directly into the database, but I'm nervous about it because the app runs well and I like the data/logic division. Also go seems easier to learn for new developers than SQL, and it's a lot easier to deploy a new app server than it is to reconfigure a database server.
Re: PostgreSQL example of self-contained stored procedures
#4I'm curious if anybody has practical experience developing a reasonably trafficked website in a similar manner. I wrote apps which consisted mostly of oracle stored procedures when I first got out of college, and it was a pretty awful experience, but it was also at a place where the development knowledge was minimal. I've since been getting closer and closer to writing postgres in this way. My current app (which I ar…
It's "cheating" a bit, but Postgrest is interesting in this space: https://github.com/PostgREST/postgrest
Re: PostgreSQL example of self-contained stored procedures
#5I'm curious if anybody has practical experience developing a reasonably trafficked website in a similar manner. I wrote apps which consisted mostly of oracle stored procedures when I first got out of college, and it was a pretty awful experience, but it was also at a place where the development knowledge was minimal. I've since been getting closer and closer to writing postgres in this way. My current app (which I ar…
You may find Graphile interesting
Re: PostgreSQL example of self-contained stored procedures
#6I'm curious if anybody has practical experience developing a reasonably trafficked website in a similar manner. I wrote apps which consisted mostly of oracle stored procedures when I first got out of college, and it was a pretty awful experience, but it was also at a place where the development knowledge was minimal. I've since been getting closer and closer to writing postgres in this way. My current app (which I ar…
It's "cheating" a bit, but Postgrest is interesting in this space: https://github.com/PostgREST/postgrest
yes! I've seen that and I'm considering writing some sort of similar postgres -> protobuf layer so that I can automatically generate my grpc structs from postgres enums.
Right now a pain point for us is that we have a generated database layer, a generated protobuf layer, and we have to do lots of fiddly manual database -> grpc struct translations. Too easy to get off-by-one errors (because you usually need a null enum element in protobuf that you don't need in postgres, particularly)
Re: PostgreSQL example of self-contained stored procedures
#7How would somebody implement this for a team with versioning, source code view, and deployment?
One can create the stored procedure and store it in an .sql file which can be version controlled like any piece of code. These can then be deployed in a number of ways.
Re: PostgreSQL example of self-contained stored procedures
#8How would somebody implement this for a team with versioning, source code view, and deployment?
I was this guy years ago in a waterfall style shop. We were a SQLServer c# all MS vertical and we did just this.
All stored procedures, table definitions, etc were checked in. There was a whole release process and QA to bundle the right versions of things into deployables which I did. It didn’t work flawlessly but it worked and when it failed it was straightforward enough that figure out why was easy.
Re: PostgreSQL example of self-contained stored procedures
#9I'm curious if anybody has practical experience developing a reasonably trafficked website in a similar manner. I wrote apps which consisted mostly of oracle stored procedures when I first got out of college, and it was a pretty awful experience, but it was also at a place where the development knowledge was minimal. I've since been getting closer and closer to writing postgres in this way. My current app (which I ar…
I have worked in a team which maintained and improved a legacy piece of software generating a few hundred million euros of yearly revenue. Not technically a website but a critical backend piece of a very popular one. The application was written in Java and PostgreSQL, which leveraged stored procedures heavily. This was a _major pain_ for us.
While the separation of concerns was sometimes quite easy to understand between the business logic in the Java code and the business logic in the sprocs, there were times when it was an impossible, tangled mess. Debugging this thing was _hard_ and every deployment was fragile and hard to deal with.
There are of course many reasons as to why this particular piece of software evolved as it did. I can only say that if you plan to move any parts of the business logic to stored procedures, make sure that you have a good reason to do so and clear architectural patterns and rules to communicate and follow.
Re: PostgreSQL example of self-contained stored procedures
#10This is how I do relational databases in 2019. My database is a self contained module more akin to an rpc api than a database. My code has virtually no database logic in it at all. I don't know but it feels like functional databases could be a thing. We should call Martin Fowler and ask.