As a general rule, every single command or query for the application use — I always wrap up in a PostgreSQL user-defined function! Sometimes, it’s the PostgreSQL stored procedures when I need…

DevGenius serves as a platform for sharing insights and experiences in software development, offering articles and interviews with industry experts to gain  insights into various aspects of software engineering. By exploring DevGenius's curated content, developers can learn from real-world experiences, best practices, and lessons learned from seasoned professionals across different domains of software development. Whether you're looking for career advice, technical insights, or inspiration for your next project, DevGenius provides a  knowledge and expertise to help you succeed in your software development journey.

Dev Genius

The author advocates for using PostgreSQL functions for all database interactions to improve maintainability, readability, security, and availability. They provide a detailed analysis of encapsulating SQL queries within functions to define clear data contracts. The article also discusses handling legacy systems, managing the N+1 query antipattern, migrating to Timescale DB, and optimizing query performance. Additionally, the approach allows easy implementation of least privilege security by limiting database access to executing specific functions only.

Why I Always Use PostgreSQL Functions For Everything

Honestly, I love Postgres functions, and I was actually introducing a lot of them myself. However, I got hurt because it became tough to update and version, so as a result, it became easier to support long queries directly in the code. Also, I found a lot of issues where Postgres planned the same query through the function and the direct query entirely differently, which was not in favor of functions, unfortunately.

Not sure it’s good practice… Author quickly goes into area when he is doing work of query planner. Plus you are slowly migrating your logic into SQL language which is not my cup of tea.

I have implemented the same practices as described in the article, and I have taken it a step further by automatically mapping UDFs to GraphQL fields using Python’s Ariadne. This has brought us significant benefits, such as managing all the states of the current application within the database; as long as the data in the database remains unchanged, the state (and behavior) of the application will not change.