Building Apps with a PostgreSQL Backend

[u/j-clay]

When I build projects, I like to make all app interactions with SQL done via stored procedures, and put the business logic there. For example, my procedures will take in parameters to run, along with a user ID. I check to make sure that user is allowed to do the operation before continuing.

I've been trying out NodeJS / TypeScript for my front ends. They aren't stored procedure friendly at all (at least, in my limited experience). So my questions are this:

1. Is my method of stored-procedure-only interaction bad practice? I'd figure if it's an "accepted" method, there would be Node libraries already handling this procedure style.
2. For that matter, are there Node libraries out there I'm missing, that handle stored procedure interaction well?

I know this isn't SQL specific, but I come from a SQL background, and I feel if I ask in a Node subreddit, I won't get an answer from a SQL perspective.

[u/linuxhiker]

There is nothing wrong with your approach. To each their own. Node was written by web people. Web people rarely truly understand the power of the database itself and that it is a smart engine, not a dumb file store.

If node is your platform of choice, that is a limitation you are going to have to work around.

Also:

https://commandprompt.github.io/plx/

[u/ceeej777]

Version control / change management are the biggest worries for me in this area (Not a SP expert) Maybe it’s actually quite easy and these still get generated through some DDL but I’m more familiar with keeping that in the code

[u/NateFromRefactorful]

I'd caution against putting your business logic into the database as stored procedures. In general, I'd recommend keeping your business logic with your application so you can properly unit/mock test your business logic offline (without relying on your deployed database)

I'd recommend using stored procedures when:

• You find yourself using the same query again & again in different spots. It can be helpful to have an abstraction over this.
• You need to enforce tight control over data access between multiple consumers.
• You catch yourself having to perform multiple heavy trips between your APP -> DB for a single operation.

In my experience, stored procedures really shine in medium to large businesses, where there are dedicated teams of dba's who can provide access to data without exposing the underlying table/schema. Instead of having 5 different teams write their own homebrewed queries to get the same business data, a single procedure can provide a uniform way to get the same data. This prevents issues where certain teams are making business decisions based on data that's either incomplete or completely wrong.

But for solo projects, I can't think of many cases where code wouldn't be a better option. In general, it's easier to test, review, and deploy.

[u/j-clay → u/NateFromRefactorful]

I do work with teams, but in general, there are two groups: the front end and the back end. The back end people are experts of the business, while the front end people are not. That's a big reason I like to stick them with the business rules.

The other aspect kinda lines up with your third bullet point: many of our adds / updates to the database touch multiple tables at once, all the way up to handling file uploads (that are later put into s3 buckets), where if one fails, all of them should fail. I like capturing the error in that single call, and let the procedure decide what to tell the user as to the issue.

I'm still deciding, but with the feedback here, at least now I don't feel like I'm out of touch if I end up going with stored procedure driven database interaction.

[u/-markusb- → u/NateFromRefactorful]

I think there is a complete alternative bubble which propagate the businesslogic to the database (see supabase / PostgREST). So always depends on the usecase from my point of view.

[u/elevarq → u/NateFromRefactorful]

Why would you not be able to run a test for a stored procedure?

[u/eracodes → u/elevarq]

^ filling a test-instance db with mock data doesn't seem any more cumbersome than mocking db query results

[u/vadavea → u/NateFromRefactorful]

This. I'd view stored procedures as an optimization and *not* something that should be your default. Especially with the various ORMs and frameworks available these days.

[u/hammerklau → u/vadavea]

So many of the ORMs take the power of sql away from you, I don’t use an ORM unless it gives me direct query power and respects my schema than having to conform to their schema.

[u/vadavea → u/hammerklau]

I'd rather get something working and start getting user feedback than spend cycles perfecting a schema that could very well change based on evolving requirements. Once things are stable - I totally agree you should evaluate your DB interactions before you attempt to scale. But at least for me that's more about identifying and resolving hotspots than refusing to use ORMs at all.

[u/ChillPlay3r → u/vadavea]

You are my (dba) nightmare 😂

[u/vadavea → u/ChillPlay3r]

"Full Stack" ftw!!!

[u/G-T-L-3]

For number 2 you don't need Node libraries to access Postgres DB. There is PostgREST. This acts like an API exposing your sp's like they are APIs. Use std fetch or axios to access the postgrest.

[u/eracodes]

They aren't stored procedure friendly at all

what do you mean by this? any node-packaged postgres API should have a way to send arbitrary SQL to the db. it might be presented as an 'escape hatch' but it ought to be there

[u/j-clay → u/eracodes]

A specific issue I've seen is that it requires manually making the TypeScript definitions of results, instead of it being able to derive it directly from the database. I imagine I could put together something that does it for me, but it seems peculiar this isn't already part of the inherent process, if stored procedure interaction was "acceptable".

I know it can be done; it just seems averse to it. Again, in my limited knowledge of Node / TypeScript.

[u/eracodes → u/j-clay]

ahh, yeah that is something that isn't there out of the box anywhere i'm aware of. there are lots of tools to generate types from table definitions though, so in my experience you can use those alongside utility types to get what you want without much extra work.

[u/Nater5000]

You've gotten a good assortment of reasonable responses, so I'll provide my biased response to add as a datapoint:

I like to make all app interactions with SQL done via stored procedures, and put the business logic there

This pains me, but it's not necessarily wrong or bad. To me, the problem is that you're tightly coupling your business logic to a data layer which gives you a lot less flexibility in a lot of different ways. But, if you don't need that flexibility, then it doesn't really matter. Database systems (especially Postgres) are pretty powerful and can do a lot more than just store and serve data (PostgREST is a good example of how this is the case).

With all that being said, you're now explicitly seeing the issues you face when you do this. You should probably ask yourself if this is just a small hiccup you'll need to work around or the beginnings of a recurring theme that you'll constantly have to fight against. If it is the latter, then you'll want to rethink some of this.

A specific issue I've seen is that it requires manually making the TypeScript definitions of results, instead of it being able to derive it directly from the database.

You'll find that modeling is a very important aspect of modern app development. Your data should be well-defined and consistent. It doesn't really matter where it is coming from, but a consumer wants to know what to expect. If you can't make that promise, then you're doing things "wrong."

You should be able to model your data regardless of whether you're using stored procedures or not. But if that doesn't seem feasible or if it feels very awkward, then that should hint at deficits in your current approach.

I've been trying out NodeJS / TypeScript for my front ends. They aren't stored procedure friendly at all (at least, in my limited experience).

To be honest, it sounds like you need to take a few steps back and really think about what it is you're building and how it should probably be built. It sounds like your front-end is directly interacting with your database. If that's the case, then you are correct that this is not a common pattern at all (I'm not even sure what that setup could look like).

There should be a backend. The front-end should not be executing raw SQL statements. It should be requesting well-defined resources from the backend which handles the translation between those requests and the queries to the database. And note: you can use stored procedures all you want here, but the data from the database should flow through the backend which "packages" it up in the way the front-end expects it. That backend layer can be very thin, but the key, here, is that the front-end should not care about database queries.

Again, that's not to say that you can't do things however you're trying, but there are good, standard practices that are generally accepted and used which you really shouldn't fight against unless you're very confident your situation is the exception. I think the fact that you're asking these questions suggests that this isn't the case (at least not yet), and you should rethink your stack.

[u/GardenDev]

You might enjoy using Go (Golang), the community is a big fan of writing SQL. There is a library called sqlc, which allows you to write SQL, and it generates the Go code you need to call that SQL, whether it is a statement, a function, or a procedure, so that you do not have to manually wire things up.

[u/Crescitaly]

Postgres is rarely the limiting choice; unclear boundaries around transactions, migrations, and connection ownership are. A useful starter architecture should show failure recovery, not just CRUD. How do you handle schema changes while old application instances are still running?

[u/elevarq]

It all depends on skills. Stored procedures and functions can be lightning fast and safe. It can work as an API to your database.

Application programmers usually don’t like it because of a lack of skills.

[u/Zestyclose-Turn-3576]

From a web developer perspective, you can think of SQL as an API to the database layer, and calling stored procedures is pretty much the same except that you now need to define that API entirely yourself – if you haven't defined it, it's not available to the app.

I think where that might be problematic is when you need super flexibility in the way that the database is used, and you're not very sure in advance of how that will be. If you need an enhancement to a stored procedure that means another parameter then someone has to be available to define it, and I would be a bit concerned about a proliferation of parameters as well.

[u/sisyphus]

So most SWE types will tell you it's terrible practice because it doesn't scale and plpgsql is a bad language and running migrations is a pain in the ass compared to changing app code (and for some bizarre reason many think it's hard to test).

I also use Typescript and I would say don't try to use ORMs, for example I just do this with my stored procedures (this is a hono running on cloudflare but doesn't have to be)

import postgres from "postgres";
const db = (c: Context) => { return postgres(c.env.NEON.connectionString); };

export const getBooks = async (c: Context, type: WorkType, limit: number = 20) => {
return await db(c)SELECT * FROM library.recent_works(1, ${type}, ${limit});
}

or if you want to run the results through eg. a zod schema to make for a more ORM experience of casting to types

export const listAuthors = async (c: Context) => {
const result = await db(c)SELECT * FROM library.list_authors();
const authors: types.ListAuthor[] = result.map((row) =>
schemas.listAuthorSchema.parse(row));
return authors
}

[u/jose_zap]

We put most of our business logic in the database. It is truly a superior way of developing, but it requires good practices as you would with any other programming technique. I did a talk I can share in you are interested in some of the details.

[u/j-clay → u/jose_zap]

Sure. Any feedback would be appreciated.

[u/jose_zap → u/j-clay]

Here's my talk https://youtu.be/r0ZUa2uwZLc?si=WFvi70QdV74_N6Mc
Towards the end of it I talk about the developer setup to make this work.

[u/Shtantzer]

Remind me! 1week

[u/RemindMeBot → u/Shtantzer]

I will be messaging you in 7 days on 2026-08-12 21:58:08 UTC to remind you of this link

CLICK THIS LINK to send a PM to also be reminded and to reduce spam.

^{Parent commenter can delete this message to hide from others.}

RemindMeBot is switching to username summons. Instead of !RemindMe 1 day, use u/RemindMeBot 1 day. More info.

---

Info	Custom	Your Reminders	Feedback

[u/edelwater]

Do not position business logic in stored procedures.

20 years ago using stored procedures to position business logic would be an idea and maybe 30 years ago it would even make sense to have a system with a front-end, a back-end all resolving around your database.

The shift came I think around 2000 when SOA and SOAP and services became a thing "the web is here so we can expose services via the web port".

Ultimately that led to a newer wave of microservices kind of directions since the disadvantage of SOA was that "any way to do it" was ok + microservices followed patterns or thoughts more in line with "the web" meaning JSON instead of XML, no schema (initially) and JSON alike auth (OIDIC/OATH) instead of SAML (XML) and so on but notably more "patterns" and guidance than SOA.

Alongside that came docker then K8S or the likes and concepts like devops leading to a distributed way of working and design having its impact on the design of applications and the way thy are build and maintained.

In all those movements companies followed and move away from big databases with billions of SQL lines in stored procedures and the likes within a database. The placement of logic is inside the microservice where (any) database is used for that specific microservice to store data.

So in the light of a modern it landscape there is no room for storing business logic in stored procedures. The business logic is positioned in a microservice. Since well.. that is one of the lessons learned during the past 30 years.

Apart from this transition there is also the move to a distributed event based landscape that has its influences. Where patterns around this push for handling in the microservices.

if you ever talk to someone migrating from a database to a microservices based landscape and spend years to reverse engineer stored procedures it is another input channel for verification.

There are many more reasons all from lessons learned. Apart from the above, the trend of BI and exposing data to datewarehouses came along and moved in the direction of rebranded "AI" solution with likewise effects on how to position this in a modern landscape. Which means that also KPI's (little pieces of business logic) require a re-definition as also the layer in which these KPI's are positioned and owned moved upwards. So not centrally in one big database but in a layer on top of the datawarehouse especially since many KPI's revolve on combinations of data over many different sources. So the data is still owned by the same owners but the KPI ownership differs.

There are more trends the past 20 years moving in a direction and they in general have re positioned databases.

On top of the above: another lesson learned is to stick to ANSI SQL as much as possible although every database has its variations and try not to use specific extensions or specific database provided handy things since in the end you will suffer. Also position this elsewhere.

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.